View Issue Details

IDProjectCategoryView StatusLast Update
0004092SymmetricDSBugpublic2022-08-02 19:39
ReporterMrKich Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.10.3 
Summary0004092: SymmetricDS keeps recreating transaction_id function during startups
DescriptionWe are using SymmetricDS for synchronization between oracle and 0000740:0000050 mariadb nodes
After upgrading to 3.10.3 from version 3.7.28 we noticed that at every startup SymmetricDS tries to reapply his scheme (some ddl change), and deleting sym_transaction_id_post_5_7_6 function.

This sometimes leads to a sutuation when some replication data is being skipped during starting of engines.

After inspecting logs we found out that SymmetricDS tries to apply this:
DROP INDEX `sym_node_communication_PK` ON `lane`.`sym_node_communication`
ALTER TABLE `lane`.`sym_node_communication` DROP PRIMARY KEY
ALTER TABLE `lane`.`sym_node_communication` ADD CONSTRAINT `sym_node_communication_PK` PRIMARY KEY (`node_id`, `queue`, `communication_type`)

In my opinion the main problem is that in both mariadb and mysql (just checked latest mariadb:10.4 and mysql:8) primary key does not have NAME. (So, symmetric finds difference and reapplies ddl again and again during startups)
It is always showed as named just "PRIMARY" and not "sym_node_communication_PK" even after I applied this ddl directly:

show create table sym_node_communication;

CREATE TABLE `sym_node_communication` (
  `node_id` varchar(50) NOT NULL,
  `communication_type` varchar(10) NOT NULL,
  `lock_time` datetime DEFAULT NULL,
  `locking_server_id` varchar(255) DEFAULT NULL,
  `last_lock_time` datetime DEFAULT NULL,
  `last_lock_millis` bigint(20) DEFAULT 0,
  `success_count` bigint(20) DEFAULT 0,
  `fail_count` bigint(20) DEFAULT 0,
  `total_success_count` bigint(20) DEFAULT 0,
  `total_fail_count` bigint(20) DEFAULT 0,
  `total_success_millis` bigint(20) DEFAULT 0,
  `total_fail_millis` bigint(20) DEFAULT 0,
  `queue` varchar(25) NOT NULL DEFAULT 'default',
  `skip_count` bigint(20) DEFAULT 0,
  `batch_to_send_count` bigint(20) DEFAULT 0,
  `node_priority` int(11) DEFAULT 0,
  PRIMARY KEY (`node_id`,`queue`,`communication_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Additional InformationUsed mariadb version: 10.0.23

Symmetric log:
2019-09-18 17:10:29,543 INFO [113_70] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Initializing SymmetricDS database
2019-09-18 17:10:29,543 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] Checking if SymmetricDS tables need created or altered
2019-09-18 17:10:31,127 INFO [113_70] [DatabaseUpgradeListener] [symmetric-engine-startup-1] Just uninstalled sym_transaction_id_post_5_7_6
2019-09-18 17:10:31,208 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] There are SymmetricDS tables that needed altered
2019-09-18 17:10:31,220 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] DDL applied: DROP INDEX `sym_node_communication_PK` ON `lane`.`sym_node_communication`
2019-09-18 17:10:31,275 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] DDL applied: ALTER TABLE `lane`.`sym_node_communication` DROP PRIMARY KEY
2019-09-18 17:10:31,316 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] DDL applied: ALTER TABLE `lane`.`sym_node_communication`
    ADD CONSTRAINT `sym_node_communication_PK` PRIMARY KEY (`node_id`, `queue`, `communication_type`)
2019-09-18 17:10:31,418 INFO [113_70] [DataLoaderService] [symmetric-engine-startup-1] Requesting current configuration {symmetricVersion=3.10.3, configVersion=null}
2019-09-18 17:10:31,756 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] Done with auto update of SymmetricDS tables
2019-09-18 17:10:31,759 INFO [113_70] [MariaDBSymmetricDialect] [symmetric-engine-startup-1] Installing SymmetricDS database object:
create function sym_transaction_id_post_5_7_6()
 returns varchar(50) NOT DETERMINISTIC READS SQL DATA
 begin
    declare done int default 0;
    declare comm_value varchar(50);
    declare comm_cur cursor for select TRX_ID from INFORMATION_SCHEMA.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID();
    declare continue handler for not found set done = 1;
    open comm_cur;
    fetch comm_cur into comm_value;
    close comm_cur;
    return concat(concat(connection_id(), '.'), comm_value);
 end

Tagsdialect: mysql/mariadb

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-09-19 09:09 MrKich New Issue
2022-08-02 19:39 elong Tag Attached: dialect: mysql/mariadb