View Issue Details

IDProjectCategoryView StatusLast Update
0005059SymmetricDSBugpublic2021-08-13 14:53
Reportermarkus.boremski Assigned Torudiejd  
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.13.0 
Target Version3.13.0Fixed in Version3.13.0 
Summary0005059: var_row_data is defined as mediumtext
DescriptionWhile we are using a table with a MEDIUMBLOB-column at nearly max-size (9M in one field) this leads to the following error:
"Operation failed: There was an error while applying the SQL script to the database.
ERROR 1406: 1406: Data too long for column 'var_row_data' at row 1"

I think this is caused by maximum size of mediumtext.
Steps To Reproduce* Create a table with a column with datatype MEDIUMBLOB
* sync it with SymmetricDS
* Fill data of a MEDIUMBLOB-Field with nearly maximum of size
Tagsdialect: mysql/mariadb

Activities

markus.boremski

2021-08-02 07:29

reporter   ~0001972

Has anybody seen this?
Can anyone confirm this?

rudiejd

2021-08-10 18:54

developer   ~0001977

I am assuming your source for replication is MariaDB since you mentioned "MEDIUMBLOB". I was not able to reproduce this issue on MariaDB Ver 15.1 for Linux and a multi-homed setup with an H2 server. Can you give some more context on this, or steps for reproduction.

I used a table like this:
CREATE TABLE medblob (
foo INT PRIMARY KEY,
bar MEDIUMBLOB
);

Then I inserted a 9MB string into this table from https://onlinefiletools.com/generate-random-text-file. I did not run into any errors.

rudiejd

2021-08-10 21:13

developer   ~0001978

Last edited: 2021-08-10 21:18

View 2 revisions

I was able to reproduce this error with the setup above when issuing an *update* instead of trying an initial load.

The problem seems to be that for our updateTriggerTemplate for MySQL/MariaDB, we use a variable with type MEDIUMTEXT to store intermediate results (the old data in the row, the new data in the row). Shoutout to Eric for pointing this out.

Most likely something (either symmetric or your application) issued an update, triggering the update trigger. Since your 9Mb value near the MEDIUMTEXT limit was concatenated with other column values, it likely overflowed the limit.

I submitted a PR changing the variable to type LONGTEXT so that it can handle values near the limit. My updates went through at this point.

markus.boremski

2021-08-11 07:22

reporter   ~0001979

Sorry for giving not enough informations, have been a bit in a hurry. :)

We use a MySQL5.7-Database, running on a WinServer2012-VM.
The "steps to reproduce" correctly should have been "update data of a [...]".
Sorry for beeing unclear here, have been a bit in a hurry. :)
I am glad to see that you could reproduce and found a way to handle this case.

One question/hint regarding your mentioned solution:
Would this solution also work if we e.g. use a LONGTEXT for our table we want to sync?
Means, sync something like this:
CREATE TABLE longtext (
foo INT PRIMARY KEY,
bar LONGTEXT
);
I wonder if this - also with your solution - might be a problem.

rudiejd

2021-08-11 13:31

developer   ~0001980

No problem! I think we were able to figure it out from the details you provided.

You are right in thinking that this method could not work if you need to have a 2GB LONGTEXT sync. We are running up against the limitations of the database here, since 4GB is a hard limit on the size of a column and the encoded column that we store in the temporary trigger local variable var_old_data plus other columns will exceed this limit.

MySQL and other platform to platform replication solutions solve this problem through log-based replication, but Symmetric has not yet implemented log based replication for MySQL/MariaDB. However, it is on our to-do list.

Your patch should be checked into the 3.13 branch for our upcoming minor version release

Issue History

Date Modified Username Field Change
2021-07-19 13:16 markus.boremski New Issue
2021-07-19 13:56 markus.boremski Tag Attached: dialect: mysql/mariadb
2021-08-02 07:29 markus.boremski Note Added: 0001972
2021-08-10 18:54 rudiejd Note Added: 0001977
2021-08-10 21:13 rudiejd Note Added: 0001978
2021-08-10 21:18 rudiejd Note Edited: 0001978 View Revisions
2021-08-11 07:22 markus.boremski Note Added: 0001979
2021-08-11 13:31 rudiejd Note Added: 0001980
2021-08-11 13:40 rudiejd Assigned To => rudiejd
2021-08-11 13:40 rudiejd Status new => assigned
2021-08-13 14:53 elong Status assigned => resolved
2021-08-13 14:53 elong Resolution open => fixed
2021-08-13 14:53 elong Product Version 3.12.2 => 3.13.0
2021-08-13 14:53 elong Fixed in Version => 3.13.0
2021-08-13 14:53 elong Target Version => 3.13.0