Error Provisioning a table with TEXT field

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Murtada
Posts: 1
Joined: Sun 07 Sep 2014 19:14

Error Provisioning a table with TEXT field

Post by 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

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Error Provisioning a table with TEXT field

Post by 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

Post by 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.

Post Reply