View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005719 | SymmetricDS | Improvement | public | 2023-03-03 15:12 | 2024-01-25 19:35 |
| Reporter | AAdarve | Assigned To | |||
| Priority | normal | ||||
| Status | closed | Resolution | no change required | ||
| Product Version | 3.13.4 | ||||
| Summary | 0005719: MariaDB update causes triggers for table operations to be created incorrectly | ||||
| Description | Having symmetricds on a mariadb database, a version upgrade of maridb from v5.5.x to version 10.6.11 has been performed causing the triggers of table operations not to be generated correctly, leaving the sentences of those fields related to dates incomplete. The problem is generated by a comment that maridb inserts when upgrading automatically migrates old date formats to new date formats: Column Name Data Type createddate datetime /* mariadb-5.3 */ lastupdateddate timestamp /* mariadb-5.3 */ This generates a 'Column' object with wrong 'jdbcTypeName' and so wrong mappedType attributes at runtime for that column in the symmetric library at the moment of generate the table operations triggers, causing that the triggers are not composed correctly for the part of the code related to those date type columns: Trigger generated correctly before ddbb upgrade includes this fragment of code related to date type columns: trigger SYM_ON_I_FOR_xxx__xxx after insert on `xxx_db`.`xxx_table` for each row begin if 1=1 and @sync_triggers_disabled is null then insert into `xxx_db`.sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) values( 'xxx_table', 'I', 29, concat( if(new.`id_act_activationtransaction` is null,'',concat('"',cast(new.`id_act_activationtransaction` as char),'"')),',', if(new.`createddatetime` is null,'',concat('"',cast(new.`createddatetime` as char),'"')),',', if(new.`lastupdateddatetime` is null,'',concat('"',cast(new.`lastupdateddatetime` as char),'"')),',', if(new.`xxx_column` is null,'',concat('"',cast(new.`xxx_column` as char),'"')),',', ... ... ... And the trigger not generated correctly after ddbb upgrade does not include code related to the date type columns: trigger SYM_ON_I_FOR_xxx__xxx after insert on `xxx_db`.`xxx_table` for each row begin if 1=1 and @sync_triggers_disabled is null then insert into `xxx_db`.sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) values( 'xxx_table', 'I', 60, concat( if(new.`xxx_table` is null,'',concat('"',cast(new.`xxx_table` as char),'"')),',', '',',', '',',', ... ... ... This problem is discussed in this mariadb's own jira: https://jira.mariadb.org/browse/MDEV-19906 and it is related to what is commented by the mariadb documentation itself in the internal format section for the timestamp, datetime and time data types, as can be seen in this link for the first of them: https://mariadb.com/kb/en/timestamp/#internal-format The solution we have implemented is: 1. dump ddbb data after the maridb update 2. import this generated dump so that mariadb no longer recognises this date type data in old format and does not write the comment in its data type. This way symmetric will generate the triggers correctly. Another solution as indicated in the mariadb documentation link I have shared before for timestamp date data types, is to execute the following statement: ALTER TABLE table_name MODIFY table_col TIMESTAMP; so that the database itself converts the data type from the old to the new format and the comment in its data type disappears. | ||||
| Tags | dialect: mysql/mariadb | ||||
|
|
What version of the MariaDb driver are you using? If you upgrade to the 3.1.2 version of the driver, does this issue persist? |
|
|
We are using mysql-connector-java driver version 5.1.22, which as far as I know is older than the driver you are talking about. We are going to do a test by upgrading the driver to mariadb-java-client version 3.1.2 and as soon as I have results I will post back again. At first we played with this new driver but finally the update was not carried out and we didn't think that it could solve the problem directly. Thanks. |
|
|
Sorry for taking so long to respond. Due to a version incompatibility problems in our project between flyway and mariadb dependencies, we have not been able to test the proposed solution as completely as we would have liked. However, as far as we have been able to test the proposed mariadb driver instead of the old mysql driver, it seems that the triggers are generated correctly and there is data synchronization. Thank you very much for the suggestion. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2023-03-03 15:12 | AAdarve | New Issue | |
| 2023-03-03 15:16 | AAdarve | Tag Attached: mariadb;sync-trigger | |
| 2023-03-08 18:38 | jvanmeter | Note Added: 0002285 | |
| 2023-03-09 14:07 | AAdarve | Note Added: 0002291 | |
| 2023-04-12 11:35 | AAdarve | Note Added: 0002307 | |
| 2023-04-12 17:58 | jvanmeter | Status | new => closed |
| 2023-04-12 17:58 | jvanmeter | Resolution | open => no change required |
| 2024-01-25 19:35 | elong | Tag Attached: dialect: mysql/mariadb | |
| 2024-01-25 19:35 | elong | Tag Detached: dialect: mysql/mariadb | |
| 2024-01-25 19:35 | elong | Tag Attached: dialect: mysql/mariadb | |
| 2024-01-25 19:35 | elong | Tag Detached: mariadb;sync-trigger |