Qlik Replicate - Row size large error

[Megha_More]

Hi Team,

Source is Oracle while target DB is MySQL . Encountered the following errors while loading into the target database. It got error and unable to load. Is there a workaround?

Oracle_MYSQL_QMS replication task encountered the following error:
Error forwarding bulk
Failed to create net changes table for bulk apply Execute create net changes table statement failed. CREATE TEMPORARY TABLE `marine_master`.`attrep_changes4F2D60216E5DC700` (  `seq` INTEGER NOT NULL, `col1` VARCHAR(4000), `col2` VARCHAR(4000), `col3` VARCHAR(4000), `col4` VARCHAR(250), `col5` VARCHAR(250), `col6` VARCHAR(250), `col7` VARCHAR(200), `col8` VARCHAR(200), `col9` VARCHAR(200), `col10` VARCHAR(200), `col11` VARCHAR(150), `col12` VARCHAR(120), `col13` VARCHAR(120), `col14` VARCHAR(90), `col15` VARCHAR(80), `col16` VARCHAR(80), `col17` VARCHAR(75), `col18` VARCHAR(60), `col19` VARCHAR(50), `col20` VARCHAR(50), `col21` VARCHAR(50), `col22` VARCHAR(50), `col23` VARCHAR(50), `col24` VARCHAR(50), `col25` VARCHAR(45), `col26` VARCHAR(41), `col27` VARCHAR(37), `col28` VARCHAR(37), `col29` VARCHAR(37), `col30` VARCHAR(37), `col31` VARCHAR(37), `col32` VARCHAR(37), `col33` VARCHAR(37), `col34` VARCHAR(37), `col35` VARCHAR(37), `col36` VARCHAR(37), `col37` VARCHAR(37), `col38` VARCHAR(30), `col39` VARCHAR(30), `col40` VARCHAR(30), `col41` VARCHAR(30), `col42` VARCHAR(30), `col43` VARCHAR(28), `col44` VARCHAR(25), `col45` VARCHAR(20), `col46` VARCHAR(20), `col47` VARCHAR(20), `col48` VARCHAR(20), `col49` VARCHAR(20), `col50` VARCHAR(20), `col51` VARCHAR(20), `col52` VARCHAR(20), `col53` VARCHAR(20), `col54` VARCHAR(20), `col55` VARCHAR(20), `col56` VARCHAR(20), `col57` VARCHAR(20), `col58` VARCHAR(20), `col59` VARCHAR(20), `col60` VARCHAR(20), `col61` VARCHAR(20), `col62` VARCHAR(20), `col63` VARCHAR(20), `col64` VARCHAR(20), `col65` VARCHAR(20), `col66` VARCHAR(20), `col67` VARCHAR(20), `col68` VARCHAR(20), `col69` VARCHAR(20), `col70` VARCHAR(20), `col71` VARCHAR(20), `col72` VARCHAR(20), `col73` VARCHAR(20), `col74` VARCHAR(20), `col75` VARCHAR(20), `col76` VARCHAR(20), `col77` VARCHAR(20), `col78` VARCHAR(20), `col79` VARCHAR(20), `col80` VARCHAR(20), `col81` VARCHAR(20), `col82` VARCHAR(20), `col83` VARCHAR(20), `col84` VARCHAR(20), `col85` VARCHAR(20), `col86` VARCHAR(20), `col87` VARCHAR(20), `col88` VARCHAR(20), `col89` VARCHAR(20), `col90` VARCHAR(20), `col91` VARCHAR(20), `col92` VARCHAR(20), `col93` VARCHAR(20), `col94` VARCHAR(20), `col95` VARCHAR(20), `col96` VARCHAR(20), `col97` VARCHAR(20), `col98` VARCHAR(20), `col99` VARCHAR(20), `col100` VARCHAR(20), `col101` VARCHAR(20), `col102` VARCHAR(20), `col103` VARCHAR(20), `col104` VARCHAR(20), `col105` VARCHAR(20), `col106` VARCHAR(20), `col107` VARCHAR(20), `col108` VARCHAR(20), `col109` VARCHAR(20), `col110` VARCHAR(20), `col111` VARCHAR(20), `col112` VARCHAR(20), `col113` VARCHAR(20), `col114` VARCHAR(20), `col115` VARCHAR(20), `col116` VARCHAR(20), `col117` VARCHAR(20), `col118` VARCHAR(20), `col119` VARCHAR(20), `col120` VARCHAR(20), `col121` VARCHAR(20), `col122` VARCHAR(20), `col123` VARCHAR(20), `col124` VARCHAR(20), `col125` VARCHAR(20), `col126` VARCHAR(20), `col127` VARCHAR(20), `col128` VARCHAR(20), `col129` VARCHAR(20), `col130` VARCHAR(20), `col131` VARCHAR(20), `col132` VARCHAR(20), `col133` VARCHAR(20), `col134` VARCHAR(20), `col135` VARCHAR(20), `col136` VARCHAR(20), `col137` VARCHAR(20), `col138` VARCHAR(20) ) CHARSET=utf8mb4
RetCode: SQL_ERROR  SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.23]Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs Failed (retcode -1) to execute statement: 'CREATE TEMPORARY TABLE `marine_master`.`attrep_changes4F2D60216E5DC700` (  `seq` INTEGER NOT NULL, `col1` VARCHAR(4000), `col2` VARCHAR(4000), `col3` VARCHAR(4000), `col4` VARCHAR(250), `col5` VARCHAR(250), `col6` VARCHAR(250), `col7` VARCHAR(200), `col8` VARCHAR(200), `col9` VARCHAR(200), `col10` VARCHAR(200), `col11` VARCHAR(150), `col12` VARCHAR(120), `col13` VARCHAR(120), `col14` VARCHAR(90), `col15` VARCHAR(80), `col16` VARCHAR(80), `col17` VARCHAR(75), `col18` VARCHAR(60), `col19` VARCHAR(50), `col20` VARCHAR(50), `col21` VARCHAR(50), `col22` VARCHAR(50), `col23` VARCHAR(50), `col24` VARCHAR(50), `col25` VARCHAR(45), `col26` VARCHAR(41), `col27` VARCHAR(37), `col28` VARCHAR(37), `col29` VARCHAR(37), `col30` VARCHAR(37), `col31` VARCHAR(37), `col32` VARCHAR(37), `col33` VARCHAR(37), `col34` VARCHAR(37), `col35` VARCHAR(37), `col36` VARCHAR(37), `col37` VARCHAR(37), `col38` VARCHAR(30), `col39` VARCHAR(30), `col40` VARCHAR(30), `col41` VARCHAR(30), `col42` VARCHAR(30), `col43` VARCHAR(28), `col44` VARCHAR(25), `col45` VARCHAR(20), `col46` VARCHAR(20), `col47` VARCHAR(20), `col48` VARCHAR(20), `col49` VARCHAR(20), `col50` VARCHAR(20), `col51` VARCHAR(20), `col52` VARCHAR(20), `col53` VARCHAR(20), `col54` VARCHAR(20), `col55` VARCHAR(20), `col56` VARCHAR(20), `col57` VARCHAR(20), `col58` VARCHAR(20), `col59` VARCHAR(20), `col60` VARCHAR(20), `col61` VARCHAR(20), `col62` VARCHAR(20), `col63` VARCHAR(20), `col64` VARCHAR(20), `col65` VARCHAR(20), `col66` VARCHAR(20), `col67` VARCHAR(20), `col68` VARCHAR(20), `col69` VARCHAR(20), `col70` VARCHAR(20), `col71` VARCHAR(20), `col72` VARCHAR(20), `col73` VARCHAR(20), `col74` VARCHAR(20), `col75` VARCHAR(20), `col76` VARCHAR(20), `col77` VARCHAR(20), `col78` VARCHAR(20), `col79` VARCHAR(20), `col80` VARCHAR(20), `col81` VARCHAR(20), `col82` VARCHAR(20), `col83` VARCHAR(20), `col84` VARCHAR(20), `col85` VARCHAR(20), `col86` VARCHAR(20), `col87` VARCHAR(20), `col88` VARCHAR(20), `col89` VARCHAR(20), `col90` VARCHAR(20), `col91` VARCHAR(20), `col92` VARCHAR(20), `col93` VARCHAR(20), `col94` VARCHAR(20), `col95` VARCHAR(20), `col96` VARCHAR(20), `col97` VARCHAR(20), `col98` VARCHAR(20), `col99` VARCHAR(20), `col100` VARCHAR(20), `col101` VARCHAR(20), `col102` VARCHAR(20), `col103` VARCHAR(20), `col104` VARCHAR(20), `col105` VARCHAR(20), `col106` VARCHAR(20), `col107` VARCHAR(20), `col108` VARCHAR(20), `col109` VARCHAR(20), `col110` VARCHAR(20), `col111` VARCHAR(20), `col112` VARCHAR(20), `col113` VARCHAR(20), `col114` VARCHAR(20), `col115` VARCHAR(20), `col116` VARCHAR(20), `col117` VARCHAR(20), `col118` VARCHAR(20), `col119` VARCHAR(20), `col120` VARCHAR(20), `col121` VARCHAR(20), `col122` VARCHAR(20), `col123` VARCHAR(20), `col124` VARCHAR(20), `col125` VARCHAR(20), `col126` VARCHAR(20), `col127` VARCHAR(20), `col128` VARCHAR(20), `col129` VARCHAR(20), `col130` VARCHAR(20), `col131` VARCHAR(20), `col132` VARCHAR(20), `col133` VARCHAR(20), `col134` VARCHAR(20), `col135` VARCHAR(20), `col136` VARCHAR(20), `col137` VARCHAR(20), `col138` VARCHAR(20) ) CHARSET=utf8mb4'.

Regards,

Megha

[SachinB]

Hello @Megha_More ,

Thank you for reaching out to the Qlik community!

The simplest workaround is to use "Transactional apply".

the error
RetCode: SQL_ERROR  SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.23]Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

This is due to the limitation of MySql DB.

Storage for variable-length columns includes length bytes, which are counted toward the row size. For example, a VARCHAR(255) CHARACTER SET utf8mb3 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

Instead of having a lot of varchar fields, you can simply convert them to text type so you can fit in a lot more fields

Please refer the below links for more information and involve your DBA to address this issue.

https://help.mydbsync.com/docs/display/DCR/Row+size+too+large.+The+maximum+row+size+for+the+used+tab...

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

Regards,

Sachin B

[Megha_More]

Hi @SachinB ,

On the target side, tables have not been created, so how can we change the varchar to text?

Regards,

Megha

[SachinB]

Hello @Megha_More ,

Could you please set task to "Transaction Apply" and resume the task, Else try to create table manually at target DB.

Regards,

Sachin B

[Megha_More]

Hi @SachinB ,

We have set the task to transaction and resume it.

Manual table creation with CREATE TEMPORARY TABLE `marine_master`.`attrep_changes4F2D60216E5DC700` this same name as mentioned in error.

Regards,

Megha

[SachinB]

Hello @Megha_More ,

This is due to the limitation of MySQL DB.

Kindly consider changing impacted BLOB columns to use the TEXT data types. These types store large amounts of data more efficiently. However, be aware that TEXT columns have some differences in behavior compared to VARCHAR and other string types.

Please refer the below links for more information and involve your DBA to address this issue.

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html


Regards,

Sachin B

[Heinvandenheuvel]

There are a total of  17201 character in that temp table. They are utf8mb4 which means a maximum of 4 bytes per character for storage, which will be allocate for each character. Total is 68804, which is bigger than the 64KB max.

Now the actual storage needed is likely to be closer to 17KB, as likely only a few - if any !?) - input char will need a 4 byte representation and most are likely a single byte, but those are the UTF rules.

What is the source character set? Can you make the target be less byte hungry than UTF4?

You are not very far, so perhaps some of columns there are likely somewhat arbitrarily selected to be 2000 char 'just in case' can be verified for their actual needs with a MAX(LENGTH(xxx)) on the source. If you even find just 1 with the actual max length to be less than a 1000 and you'd substr that you'll be all set.

As suggested, Transactional apply will be a workaround, but it applies to all tables and with that might have an unacceptable impact on performance. You may have to split the task in two (or 3 with a logstream source reader task).

Replicate really should get a per-table setting to indicate singleton apply for which it already has the logic build in.

Hein.

[DesmondWOO]

Hi @Megha_More ,

Regarding "On the target side, tables have not been created, so how can we change the varchar to text?", you can change it in the transformation page. For example, you can change col1 from varchar(4000) to CLOB.

Regards,
Desmond

[Megha_More]

Hi @DesmondWOO ,

Temporary table which name as `marine_master`.`attrep_changes4F2D60216E5DC700` is not listed in selected tables. This tables must be created by Qlik replicate. So how we can transform it in a transformation page ?

Regards,

Megha

[Heinvandenheuvel]

>> Temporary table which name as `marine_master`.`attrep_changes4F2D60216E5DC700` is not listed in selected tables. This tables must be created by Qlik replicate. So how we can transform it in a transformation page ?

As you correctly point out the attrep_changes 'helper' table also referred to as 'net changes' tables is entirely managed by Replicate. The exact management depends on the target DB type.  For most targets there is a  single helper table which 'services' all target table. That helper table is a 'superset' of all tables with changes 'thus far'.

The columns are ordered by size. Say table A needs 3 x 2000 bytes and 3 times 100 bytes.  and table B needs 4*1000 bytes, 1 time 50 and 1 time 200 bytes then helper tables will be 2000, 2000, 2000, 1000, 200, 100 bytes columns in that order. Do you see the 'superset at work? For Table B changes only 1000 bytes out of the available 2000 in the first 3 columns will be used. For table A changes only 100 bytes out of the available 1000  and 200 for columns 4 and 5.

If a new table 'pops up', that is found in the transaction log, and it have more, or large columns than the superset thus far, the net changes table will be re-created.

The net changes table closely matches (all of ) the target tables, not the source, and any transformation changes the size will affect how the net changes table is created.

If could be an 'unimportant' table with some XXL columns pushed it over the (64KB) edge. Remove that table, or move it to a dedicated task and all may be well.

Hein.