Error Provisioning a table with TEXT field

Error Provisioning a table with TEXT field

Postby Murtada » Sun 07 Sep 2014 19:38

Hi ,

I'm getting a MySqlException when trying to provision a table with a text field
the exact error is coming when trying to create the table_insert procedure
am using the latest professional version
steps to reproduce the issue

if I remove the description field the provisioning and the sync will be completed.
the error is coming from this procedure

Code: Select all
CREATE PROCEDURE `Products_insert` (
  p_id INT  ,
  p_name VARCHAR(100) CHARACTER SET LATIN1 ,
  p_categoryid INT  ,
  p_isavailable TINYINT  ,
  p_description TEXT[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
  OUT sync_row_count INT)
BEGIN

DECLARE rowexist BIGINT;
SET sync_row_count = 0;

SELECT COUNT(*) INTO rowexist FROM `Products_tracking` t WHERE t.`Id` = p_id;

  IF (rowexist = 0) THEN
    INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`)
     VALUES (p_id, p_name, p_categoryid, p_isavailable, p_description);
    SET sync_row_count = ROW_COUNT();
  END IF;

END


Table structure MySQL
Code: Select all
CREATE TABLE IF NOT EXISTS `Products` (
  `Id` int(11) NOT NULL,
  `Name` varchar(100) DEFAULT NULL,
  `CategoryId` int(11) DEFAULT NULL,
  `IsAvailable` tinyint(1) DEFAULT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Products`
--

INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`) VALUES
(1, 'HP 2000-2b19WM ', 1, 1, 'Description 1'),
(2, 'Dell Adamo XPS', 1, 0, 'Description 2'),
(3, 'DELL LAPTOP I14Z', 1, 1, 'Description 3'),
(4, 'HP DC5750 Desktop Computer Dual Core/ ', 2, 1, 'Description 4'),
(5, 'Apple iMac All In One', 2, 0, 'Description 5'),
(6, 'Sharp - AQUOS - ', 3, 1, 'Description 6'),
(7, 'Samsung ST76', 4, 1, 'Description 7'),
(8, 'Canon PowerShot', 4, 1, 'Description 8'),
(9, 'Olympus OM-D E-M5', 4, 1, 'Description 9');


Table structure SQL Server
Code: Select all
CREATE TABLE [dbo].[Products](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](max) NOT NULL,
   [CategoryId] [nvarchar](max) NOT NULL,
   [IsAvailable] [bit] NULL,
   [description] [TEXT] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
   [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




Code used:

Code: Select all
        static void Main(string[] args)
        {


            SqlConnection clientConnection = new SqlConnection("server=thermo-pc\\dev2012;database=syncdb;integrated security=SSPI;");
            MySqlConnection serverConnection = new MySqlConnection("host=localhost;protocol=SSH;user=root;password=admin;database=syncdb;");
            serverConnection.SshOptions.AuthenticationType = SshAuthenticationType.Password;
            serverConnection.SshOptions.User = "vagrant";
            serverConnection.SshOptions.Host = "10.37.129.3";
            serverConnection.SshOptions.Password = "vagrant";


            DbSyncScopeDescription scopeDesc1 = new DbSyncScopeDescription("DevCategoryScope1");
            DbSyncScopeDescription scopeDesc2 = new DbSyncScopeDescription("DevCategoryScope2");

            DbSyncTableDescription tableDesc = MySqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConnection);
            DbSyncTableDescription tableDesc1 = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", clientConnection);



            scopeDesc1.Tables.Add(tableDesc);
            scopeDesc2.Tables.Add(tableDesc1);
     
            MySqlSyncScopeProvisioning devProvision = new MySqlSyncScopeProvisioning(serverConnection, scopeDesc1);
            devProvision.Apply();

            SqlSyncScopeProvisioning productionProvisioning = new SqlSyncScopeProvisioning(clientConnection, scopeDesc2);
            productionProvisioning.Apply();

            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            syncOrchestrator.RemoteProvider = new MySqlSyncProvider("DevCategoryScope1", serverConnection, null, null);

            syncOrchestrator.LocalProvider = new SqlSyncProvider("DevCategoryScope2", clientConnection, null, null);

            SyncOperationStatistics stats = syncOrchestrator.Synchronize();

            Console.WriteLine("{0}: {1}", "Total Download changes", stats.DownloadChangesTotal);
            Console.WriteLine("{0}: {1}", "Total Download changes Failed", stats.DownloadChangesFailed);
            Console.WriteLine("{0}: {1}", "Start time", stats.SyncStartTime.ToString());
            Console.WriteLine("{0}: {1}", "End Time", stats.SyncEndTime.ToString());
            Console.WriteLine("{0}: {1}", "Download Changes Applied", stats.DownloadChangesApplied);
}


Message:
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
  OUT sync_r' at line 6

Sql Profiler
Code: Select all
140907 22:18:20      66 Connect   root@localhost on syncdb
         66 Query   SHOW KEYS FROM Products FROM syncdb WHERE Column_name LIKE '%'
         66 Query   SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = 'Products'
140907 22:18:24      66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
         66 Query   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
         66 Query   BEGIN
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'sequences'
         66 Query   CREATE TABLE `sequences`
(
  name VARCHAR(16) NOT NULL,
  curr_value BIGINT UNSIGNED NOT NULL,
  inc_value BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (name)
) ENGINE = INNODB;

INSERT IGNORE INTO `sequences` VALUES ('new_timestamp', 1,1)
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'get_newtimestampfunc' AND `type` = 'FUNCTION'
         66 Query   CREATE FUNCTION `get_newtimestampfunc` ()
  RETURNS BIGINT UNSIGNED
  NOT DETERMINISTIC
  READS SQL DATA
BEGIN
  DECLARE curr_value BIGINT UNSIGNED;

  SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE;

  UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp';

  RETURN curr_value;
END
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scopeinfo'
         66 Query   CREATE TABLE `scopeinfo` (
  scope_local_id INT AUTO_INCREMENT,
  scope_id BINARY(16) NOT NULL,    
  scope_name VARCHAR(100) NOT NULL,
  scope_sync_knowledge BLOB NULL,
  scope_tombstone_cleanup_knowledge BLOB NULL,
  scope_timestamp BIGINT NULL,
  scope_cleanup_timestamp TIMESTAMP NULL,
  scope_user_comment VARCHAR(200) NULL,
  scope_config_id BINARY(16) NULL,
  scope_restore_count INT default 0,
  PRIMARY KEY (scope_local_id),
  CONSTRAINT scopeinfo_name UNIQUE (scope_name)
)
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
         66 Query   CREATE TABLE `schemainfo` (
  schema_major_version INT NOT NULL,
  schema_minor_version INT NOT NULL,
  schema_extended_info varchar(100) NOT NULL,
  PRIMARY KEY (schema_major_version,schema_minor_version)
)
         66 Query   INSERT INTO `schemainfo` (schema_major_version, schema_minor_version, schema_extended_info ) VALUES ('2','1','')
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scopeconfig'
         66 Query   CREATE TABLE `scopeconfig` (
  config_id BINARY(16) NOT NULL,
  config_data TEXT,
  scope_status char(1) NULL,
  PRIMARY KEY (config_id)
)
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scope_parameters'
         66 Query   CREATE TABLE `scope_parameters` (
  sync_scope_name VARCHAR(100) NOT NULL,
  parameter_data TEXT NOT NULL,
  CONSTRAINT PK_scope_parameters PRIMARY KEY (sync_scope_name)
)
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'scope_templates'
         66 Query   CREATE TABLE `scope_templates` (
  template_id INT AUTO_INCREMENT,
  template_name VARCHAR(100) NOT NULL,
  template_config_id BINARY(16) NOT NULL,
  template_description VARCHAR(100) NOT NULL,
  CONSTRAINT PK_scope_templates PRIMARY KEY (template_id),
  CONSTRAINT scopeinfo_name UNIQUE (template_name)
)
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'update_scopeinfo' AND `type` = 'PROCEDURE'
         66 Query   create procedure `update_scopeinfo`
(
  sync_scope_name varchar(100),
  sync_scope_knowledge blob,
  sync_scope_cleanup_knowledge blob,
  sync_check_concurrency INT,
  sync_scope_timestamp BIGINT,
  out sync_row_count INT
)
begin

  update `scopeinfo` set
   scope_sync_knowledge = sync_scope_knowledge,
    scope_tombstone_cleanup_knowledge = sync_scope_cleanup_knowledge
    where scope_name = sync_scope_name
AND (sync_check_concurrency = 0 OR scope_timestamp = sync_scope_timestamp);

  set sync_row_count = ROW_COUNT();
end
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'get_newtimestamp' AND `type` = 'PROCEDURE'
         66 Query   CREATE PROCEDURE `get_newtimestamp` (
  out sync_new_timestamp bigint
)
BEGIN 
set sync_new_timestamp = `get_newtimestampfunc`();
END
         66 Query   INSERT INTO `scopeconfig` (config_id, config_data, scope_status) VALUES(0xA0E92253D93F2749AC783F7CE53E9673, '<SyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="false"><Adapter DelTrig="`Products_on_delete`" GlobalName="Products" InsTrig="`Products_on_insert`" UpdTrig="`Products_on_update`" Name="`Products`" TrackingTable="`Products_tracking`" SelChngProc="`Products_selectchanges`" SelRowProc="`Products_selectrow`" InsMetaProc="`Products_insertmetadata`" UpdMetaProc="`Products_updatemetadata`" DelMetaProc="`Products_deletemetadata`" InsProc="`Products_insert`" UpdProc="`Products_update`" DelProc="`Products_delete`"><Col name="Id" type="int" prec="11" scale="0" size="0" pk="true" /><Col name="Name" type="varchar[charset=latin1][collate=latin1_swedish_ci]" prec="0" scale="0" size="100" null="true" /><Col name="CategoryId" type="int" prec="11" scale="0" size="0" null="true" /><Col name="IsAvailable" type="tinyint" prec="1" scale="0" size="0" null="true" /><Col name="description" type="text[charset=latin1][collate=latin1_swedish_ci]" prec="0" scale="0" size="0" null="true" /></Adapter></SyncProviderScopeConfiguration>', 'P')
         66 Query   INSERT INTO `scopeinfo` (scope_id, scope_name, scope_config_id, scope_user_comment, scope_timestamp) VALUES(0x4A834F27E4EB2F40B39A77256EC0C6E6, 'DevCategoryScope1', 0xA0E92253D93F2749AC783F7CE53E9673, '', `get_newtimestampfunc`())
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'schemainfo'
         66 Query   SELECT schema_major_version, schema_minor_version, schema_extended_info FROM `schemainfo`
         66 Query   SHOW KEYS FROM `Products_tracking` FROM syncdb WHERE Column_name LIKE '%'
         66 Query   SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = '`Products_tracking`'
         66 Query   SHOW KEYS FROM `Products_tracking` FROM syncdb WHERE Column_name LIKE '%'
         66 Query   SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `Null` , COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`, COLLATION_NAME as `Collate`, CHARACTER_SET_NAME AS `CharacterSet`
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA= 'syncdb' AND TABLE_NAME = '`Products_tracking`'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'Products'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'Products_tracking'
         66 Query   SHOW TABLE STATUS FROM syncdb LIKE 'Products_tracking'
         66 Query   CREATE TABLE `Products_tracking` (
  `Id` int(0)    NOT NULL,
  update_scope_local_id INT NULL,
  scope_update_peer_key INT,
  scope_update_peer_timestamp BIGINT NULL,
  local_update_peer_key INT,
  local_update_peer_timestamp BIGINT,
  create_scope_local_id INT NULL,
  scope_create_peer_key INT,
  scope_create_peer_timestamp BIGINT,
  local_create_peer_key INT,
  local_create_peer_timestamp BIGINT,
  sync_row_is_tombstone INT,
  restore_timestamp BIGINT,
  last_change_datetime TIMESTAMP NULL,
 
  PRIMARY KEY (`Id`) 
)
         66 Query   INSERT INTO `Products_tracking` (`Id`, create_scope_local_id, local_create_peer_key, local_create_peer_timestamp, update_scope_local_id, local_update_peer_key, restore_timestamp, sync_row_is_tombstone, local_update_peer_timestamp)
SELECT m.`Id`, null, 0, `get_newtimestampfunc`(), NULL, 0, NULL, 0, `get_newtimestampfunc`()
FROM `Products` m
LEFT OUTER JOIN `Products_tracking` t ON m.`Id` = t.`Id`
WHERE t.`Id` IS NULL
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   SELECT s.curr_value
  INTO curr_value
  FROM `sequences` s
  WHERE s.name = 'new_timestamp'
  FOR UPDATE
         66 Query   UPDATE `sequences` s
  SET s.curr_value = s.curr_value + s.inc_value
  WHERE s.name = 'new_timestamp'
         66 Query   select
  `TRIGGER_SCHEMA` as `Database`,
  `TRIGGER_NAME` as `Name`,
  `EVENT_OBJECT_SCHEMA` as `TableDatabase`,
  `EVENT_OBJECT_TABLE` as `Table`,
  `EVENT_MANIPULATION` as `Event`,
  `ACTION_TIMING` as `Timing`,
  `ACTION_STATEMENT` as `Body`,
  `SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_insert'
         66 Query   select
  `TRIGGER_SCHEMA` as `Database`,
  `TRIGGER_NAME` as `Name`,
  `EVENT_OBJECT_SCHEMA` as `TableDatabase`,
  `EVENT_OBJECT_TABLE` as `Table`,
  `EVENT_MANIPULATION` as `Event`,
  `ACTION_TIMING` as `Timing`,
  `ACTION_STATEMENT` as `Body`,
  `SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_update'
         66 Query   select
  `TRIGGER_SCHEMA` as `Database`,
  `TRIGGER_NAME` as `Name`,
  `EVENT_OBJECT_SCHEMA` as `TableDatabase`,
  `EVENT_OBJECT_TABLE` as `Table`,
  `EVENT_MANIPULATION` as `Event`,
  `ACTION_TIMING` as `Timing`,
  `ACTION_STATEMENT` as `Body`,
  `SQL_MODE` as `SqlMode`
from information_schema.`TRIGGERS`
where `TRIGGER_SCHEMA` like 'syncdb' and `TRIGGER_NAME` like 'Products_on_delete'
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectchanges' AND `type` = 'PROCEDURE'
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectchanges' AND `type` = 'PROCEDURE'
         66 Query   CREATE PROCEDURE `Products_selectchanges` (
  sync_min_timestamp INT,
  sync_metadata_only INT,
  sync_scope_local_id INT,
  sync_initialize INT)
BEGIN   
  SELECT
    t.`Id`,
    m.`Name`,
    m.`CategoryId`,
    m.`IsAvailable`,
    m.`description`,
   
    t.sync_row_is_tombstone,
    t.local_update_peer_timestamp as sync_row_timestamp,
    CASE
    WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
        THEN
        CASE
            WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
            ELSE t.restore_timestamp
        END
    ELSE t.scope_update_peer_timestamp
    END AS sync_update_peer_timestamp,

    CASE
    WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id) THEN t.local_update_peer_key
    ELSE t.scope_update_peer_key
    END AS sync_update_peer_key,
   
    CASE
    WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_timestamp
    ELSE t.scope_create_peer_timestamp
    END AS sync_create_peer_timestamp,

    CASE
    WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_key
    ELSE t.scope_create_peer_key
    END AS sync_create_peer_key
  FROM `Products` m
  RIGHT JOIN `Products_tracking` t
  ON m.`Id` = t.`Id`
  WHERE (t.update_scope_local_id is NULL OR t.update_scope_local_id <> sync_scope_local_id OR
  (t.update_scope_local_id = sync_scope_local_id AND t.scope_update_peer_key <> CASE WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
  THEN t.local_update_peer_key ELSE t.scope_update_peer_key END))
  AND t.local_update_peer_timestamp > sync_min_timestamp;
END
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectrow' AND `type` = 'PROCEDURE'
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_selectrow' AND `type` = 'PROCEDURE'
         66 Query   CREATE PROCEDURE `Products_selectrow` (
  p_id INT  ,
  sync_scope_local_id INT
)
BEGIN
  SELECT
    t.`Id`,
    m.`Name`,
    m.`CategoryId`,
    m.`IsAvailable`,
    m.`description`,
   
    t.sync_row_is_tombstone,
    t.local_update_peer_timestamp as sync_row_timestamp,
    CASE
      WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id)
       THEN
          CASE
            WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
            ELSE t.restore_timestamp
          END
      ELSE t.scope_update_peer_timestamp
    END AS sync_update_peer_timestamp,
    CASE
      WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> sync_scope_local_id) THEN t.local_update_peer_key
      ELSE t.scope_update_peer_key
    END AS sync_update_peer_key,   
    CASE
      WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_timestamp
      ELSE t.scope_create_peer_timestamp
    END AS sync_create_peer_timestamp,
    CASE
      WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> sync_scope_local_id) THEN t.local_create_peer_key
      ELSE t.scope_create_peer_key
    END AS sync_create_peer_key      
FROM `Products` m
RIGHT JOIN `Products_tracking` t
ON m.`Id` = t.`Id`
WHERE t.`Id` = p_id;
END
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_insert' AND `type` = 'PROCEDURE'
         66 Query   SELECT `db` as `Database`, name as `Name`,`created` as `Created`, `modified` as `Modified`, `language` as `Language`,`sql_data_access` as `SqlDataAccess`, `is_deterministic` as `IsDeterministic`,`security_type` as `SecurityType`, `body` as `Body`, `definer` as `Definer`,`sql_mode` as `SqlMode`, `comment` as `Comment` FROM `mysql`.`proc` WHERE `db` like 'syncdb' and `name` like 'Products_insert' AND `type` = 'PROCEDURE'
         66 Query   CREATE PROCEDURE `Products_insert` (
  p_id INT  ,
  p_name VARCHAR(100) CHARACTER SET LATIN1 ,
  p_categoryid INT  ,
  p_isavailable TINYINT  ,
  p_description TEXT[CHARSET=LATIN1][COLLATE=LATIN1_SWEDISH_CI] CHARACTER SET LATIN1 ,
  OUT sync_row_count INT)
BEGIN

DECLARE rowexist BIGINT;
SET sync_row_count = 0;

SELECT COUNT(*) INTO rowexist FROM `Products_tracking` t WHERE t.`Id` = p_id;

  IF (rowexist = 0) THEN
    INSERT INTO `Products` (`Id`, `Name`, `CategoryId`, `IsAvailable`, `description`)
     VALUES (p_id, p_name, p_categoryid, p_isavailable, p_description);
    SET sync_row_count = ROW_COUNT();
  END IF;

END

Source:
Devart.Data.Synchronization
Murtada
 
Posts: 1
Joined: Sun 07 Sep 2014 19:14

Re: Error Provisioning a table with TEXT field

Postby MariiaI » Mon 08 Sep 2014 13:16

Thank you for the report on this. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Error Provisioning a table with TEXT field

Postby MariiaI » Mon 29 Sep 2014 07:57

The bug with invalid script when table has column with the TEXT data type for Sync Framework support is fixed.
New build of dotConnect for MySQL 8.3.254 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=2&t=30479.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL