View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004092 | SymmetricDS | Bug | public | 2019-09-19 09:09 | 2025-03-26 17:24 |
| Reporter | MrKich | Assigned To | |||
| Priority | normal | ||||
| Status | acknowledged | Resolution | open | ||
| Product Version | 3.10.3 | ||||
| Summary | 0004092: SymmetricDS keeps recreating transaction_id function during startups | ||||
| Description | We 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 Information | Used 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 | ||||
| Tags | dialect: mysql/mariadb | ||||