View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005059 | SymmetricDS | Bug | public | 2021-07-19 13:16 | 2021-11-17 16:00 |
| Reporter | markus.boremski | Assigned To | rudiejd | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.13.0 | ||||
| Target Version | 3.13.0 | Fixed in Version | 3.13.0 | ||
| Summary | 0005059: var_row_data is defined as mediumtext | ||||
| Description | While 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 | ||||
| Tags | dialect: mysql/mariadb | ||||
|
|
Has anybody seen this? Can anyone confirm this? |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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 |
| 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 |
| 2021-11-17 16:00 | admin | Status | resolved => closed |