topic Re: Qlik Replicate - Row size large error in Qlik Replicate

Qlik Replicate - Row size large error

Megha_More — Thu, 23 Nov 2023 05:57:06 GMT

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 bulkFailed 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=utf8mb4RetCode: 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

Re: Qlik Replicate - Row size large error

SachinB — Thu, 23 Nov 2023 06:07:32 GMT

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+table+type%2C+not+counting+BLOBs%2C+is+65535

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

Regards,

Sachin B

Re: Qlik Replicate - Row size large error

Megha_More — Thu, 23 Nov 2023 06:24:22 GMT

Hi @SachinB ,

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

Regards,

Megha

Re: Qlik Replicate - Row size large error

SachinB — Thu, 23 Nov 2023 06:33:52 GMT

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

Re: Qlik Replicate - Row size large error

Megha_More — Thu, 23 Nov 2023 06:42:20 GMT

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

Re: Qlik Replicate - Row size large error

SachinB — Thu, 23 Nov 2023 07:05:06 GMT

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

Re: Qlik Replicate - Row size large error

Heinvandenheuvel — Fri, 24 Nov 2023 03:46:34 GMT

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.

Re: Qlik Replicate - Row size large error

DesmondWOO — Fri, 24 Nov 2023 09:23:48 GMT

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

Re: Qlik Replicate - Row size large error

Megha_More — Wed, 29 Nov 2023 04:31:03 GMT

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

Re: Qlik Replicate - Row size large error

Heinvandenheuvel — Wed, 29 Nov 2023 05:45:58 GMT

>> 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.