topic Re: How to trouble shoot target apply error in a task in Qlik Replicate

How to trouble shoot target apply error in a task

desmondchew — Thu, 22 Aug 2024 13:21:37 GMT

We are replicating from Oracle 11g source into MySQL 8 target endpoint on AWS RDS. We are seeing the following error in logs. How can we troubleshoot this? I could not find the table attrep_changesE2B34BEB3F920C7F in RDS.

00010100: 2024-08-22T09:17:46 [TARGET_APPLY ]I: Failed (retcode -1) to execute statement: CREATE TEMPORARY TABLE `db`.`attrep_changesE2B34BEB3F920C7F` ( `seq` INTEGER NOT NULL, `col1` VARCHAR(4000), `col2` VARCHAR(4000), `col3` VARCHAR(4000), `col4` VARCHAR(255), `col5` VARCHAR(255), `col6` VARCHAR(120), `col7` VARCHAR(120), `col8` VARCHAR(120), `col9` VARCHAR(120), `col10` VARCHAR(120), `col11` VARCHAR(120), `col12` VARCHAR(100), `col13` VARCHAR(100), `col14` VARCHAR(100), `col15` VARCHAR(100), `col16` VARCHAR(100), `col17` VARCHAR(100), `col18` VARCHAR(100), `col19` VARCHAR(100), `col20` VARCHAR(100), `col21` VARCHAR(100), `col22` VARCHAR(100), `col23` VARCHAR(100), `col24` VARCHAR(100), `col25` VARCHAR(100), `col26` VARCHAR(100), `col27` VARCHAR(80), `col28` VARCHAR(80), `col29` VARCHAR(80), `col30` VARCHAR(80), `col31` VARCHAR(80), `col32` VARCHAR(80), `col33` VARCHAR(80), `col34` VARCHAR(60), `col35` VARCHAR(60), `col36` VARCHAR(60), `col37` VARCHAR(60), `col38` VARCHAR(50), `col39` VARCHAR(50), `col40` VARCHAR(50), `col41` VARCHAR(50), `col42` VARCHAR(50), `col43` VARCHAR(50), `col44` VARCHAR(50), `col45` VARCHAR(50), `col46` VARCHAR(50), `col47` VARCHAR(50), `col48` VARCHAR(50), `col49` VARCHAR(50), `col50` VARCHAR(50), `col51` VARCHAR(41), `col52` VARCHAR(41), `col53` VARCHAR(41), `col54` VARCHAR(41), `col55` VARCHAR(41), `col56` VARCHAR(37), `col57` VARCHAR(37), `col58` VARCHAR(37), `col59` VARCHAR(37), `col60` VARCHAR(37), `col61` VARCHAR(37), `col62` VARCHAR(37), `col63` VARCHAR(37), `col64` VARCHAR(37), `col65` VARCHAR(37), `col66` VARCHAR(37), `col67` VARCHAR(37), `col68` VARCHAR(37), `col69` VARCHAR(37), `col70` VARCHAR(37), `col71` VARCHAR(37), `col72` VARCHAR(37), `col73` VARCHAR(37), `col74` VARCHAR(37), `col75` VARCHAR(37), `col76` VARCHAR(37), `col77` VARCHAR(37), `col78` VARCHAR(37), `col79` VARCHAR(37), `col80` VARCHAR(37), `col81` VARCHAR(37), `col82` VARCHAR(37), `col83` VARCHAR(37), `col84` VARCHAR(37), `col85` VARCHAR(37), `col86` VARCHAR(37), `col87` VARCHAR(37), `col88` VARCHAR(37), `col89` VARCHAR(37), `col90` VARCHAR(37), `col91` VARCHAR(37), `col92` VARCHAR(37), `col93` VARCHAR(37), `col94` VARCHAR(37), `col95` VARCHAR(37), `col96` VARCHAR(37), `col97` VARCHAR(37), `col98` VARCHAR(37), `col99` VARCHAR(37), `col100` VARCHAR(37), `col101` VARCHAR(37), `col102` VARCHAR(37), `col103` VARCHAR(37), `col104` VARCHAR(37), `col105` VARCHAR(37), `col106` VARCHAR(37), `col107` VARCHAR(37), `col108` VARCHAR(37), `col109` VARCHAR(37), `col110` VARCHAR(37), `col111` VARCHAR(37), `col112` VARCHAR(37), `col113` VARCHAR(37), `col114` VARCHAR(37), `col115` VARCHAR(37), `col116` VARCHAR(37), `col117` VARCHAR(37), `col118` VARCHAR(37), `col119` VARCHAR(37), `col120` VARCHAR(37), `col121` VARCHAR(37), `col122` VARCHAR(37), `col123` VARCHAR(37), `col124` VARCHAR(37), `col125` VARCHAR(37), `col126` VARCHAR(37), `col127` VARCHAR(37), `col128` VARCHAR(37), `col129` VARCHAR(37), `col130` VARCHAR(37), `col131` VARCHAR(37), `col132` VARCHAR(37), `col133` VARCHAR(37), `col134` VARCHAR(37), `col135` VARCHAR(37), `col136` VARCHAR(37), `col137` VARCHAR(37), `col138` VARCHAR(34), `col139` VARCHAR(34), `col1... [1022502] (ar_odbc_stmt.c:5054)
00010100: 2024-08-22T09:17:46 [TARGET_APPLY ]I: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.37]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 [1022502] (ar_odbc_stmt.c:5062)
00010100: 2024-08-22T09:17:46 [TARGET_APPLY ]I: Execute create net changes table statement failed. CREATE TEMPORARY TABLE `db`.`attrep_changesE2B34BEB3F920C7F` ( `seq` INTEGER NOT NULL, `col1` VARCHAR(4000), `col2` VARCHAR(4000), `col3` VARCHAR(4000), `col4` VARCHAR(255), `col5` VARCHAR(255), `col6` VARCHAR(120), `col7` VARCHAR(120), `col8` VARCHAR(120), `col9` VARCHAR(120), `col10` VARCHAR(120), `col11` VARCHAR(120), `col12` VARCHAR(100), `col13` VARCHAR(100), `col14` VARCHAR(100), `col15` VARCHAR(100), `col16` VARCHAR(100), `col17` VARCHAR(100), `col18` VARCHAR(100), `col19` VARCHAR(100), `col20` VARCHAR(100), `col21` VARCHAR(100), `col22` VARCHAR(100), `col23` VARCHAR(100), `col24` VARCHAR(100), `col25` VARCHAR(100), `col26` VARCHAR(100), `col27` VARCHAR(80), `col28` VARCHAR(80), `col29` VARCHAR(80), `col30` VARCHAR(80), `col31` VARCHAR(80), `col32` VARCHAR(80), `col33` VARCHAR(80), `col34` VARCHAR(60), `col35` VARCHAR(60), `col36` VARCHAR(60), `col37` VARCHAR(60), `col38` VARCHAR(50), `col39` VARCHAR(50), `col40` VARCHAR(50), `col41` VARCHAR(50), `col42` VARCHAR(50), `col43` VARCHAR(50), `col44` VARCHAR(50), `col45` VARCHAR(50), `col46` VARCHAR(50), `col47` VARCHAR(50), `col48` VARCHAR(50), `col49` VARCHAR(50), `col50` VARCHAR(50), `col51` VARCHAR(41), `col52` VARCHAR(41), `col53` VARCHAR(41), `col54` VARCHAR(41), `col55` VARCHAR(41), `col56` VARCHAR(37), `col57` VARCHAR(37), `col58` VARCHAR(37), `col59` VARCHAR(37), `col60` VARCHAR(37), `col61` VARCHAR(37), `col62` VARCHAR(37), `col63` VARCHAR(37), `col64` VARCHAR(37), `col65` VARCHAR(37), `col66` VARCHAR(37), `col67` VARCHAR(37), `col68` VARCHAR(37), `col69` VARCHAR(37), `col70` VARCHAR(37), `col71` VARCHAR(37), `col72` VARCHAR(37), `col73` VARCHAR(37), `col74` VARCHAR(37), `col75` VARCHAR(37), `col76` VARCHAR(37), `col77` VARCHAR(37), `col78` VARCHAR(37), `col79` VARCHAR(37), `col80` VARCHAR(37), `col81` VARCHAR(37), `col82` VARCHAR(37), `col83` VARCHAR(37), `col84` VARCHAR(37), `col85` VARCHAR(37), `col86` VARCHAR(37), `col87` VARCHAR(37), `col88` VARCHAR(37), `col89` VARCHAR(37), `col90` VARCHAR(37), `col91` VARCHAR(37), `col92` VARCHAR(37), `col93` VARCHAR(37), `col94` VARCHAR(37), `col95` VARCHAR(37), `col96` VARCHAR(37), `col97` VARCHAR(37), `col98` VARCHAR(37), `col99` VARCHAR(37), `col100` VARCHAR(37), `col101` VARCHAR(37), `col102` VARCHAR(37), `col103` VARCHAR(37), `col104` VARCHAR(37), `col105` VARCHAR(37), `col106` VARCHAR(37), `col107` VARCHAR(37), `col108` VARCHAR(37), `col109` VARCHAR(37), `col110` VARCHAR(37), `col111` VARCHAR(37), `col112` VARCHAR(37), `col113` VARCHAR(37), `col114` VARCHAR(37), `col115` VARCHAR(37), `col116` VARCHAR(37), `col117` VARCHAR(37), `col118` VARCHAR(37), `col119` VARCHAR(37), `col120` VARCHAR(37), `col121` VARCHAR(37), `col122` VARCHAR(37), `col123` VARCHAR(37), `col124` VARCHAR(37), `col125` VARCHAR(37), `col126` VARCHAR(37), `col127` VARCHAR(37), `col128` VARCHAR(37), `col129` VARCHAR(37), `col130` VARCHAR(37), `col131` VARCHAR(37), `col132` VARCHAR(37), `col133` VARCHAR(37), `col134` VARCHAR(37), `col135` VARCHAR(37), `col136` VARCHAR(37), `col137` VARCHAR(37), `col138` VARCHAR(34), `col139` VARCHAR(34), `col140` VARCHAR(30), `col141` VARCHAR(28), `col142` VARCHAR(28), `col143` VARCHAR(20), `col144` VARCHAR(20), `col145` VARCHAR(20), `col146` VARCHAR(20), `col147` VARCHAR(20), `col148` VARCHAR(20), `col149` VARCHAR(20), `col150` VARCHAR(20), `col151` VARCHAR(20), `col152` VARCHAR(20), `col153` VARCHAR(20), `col154` VARCHAR(20), `col155` VARCHAR(20), `col156` VARCHAR(20), `col157` VARCHAR(20), `col158` VARCHAR(20), `col159` VARCHAR(20), `col160` VARCHAR(20), `col161` VARCHAR(20), `col162` VARCHAR(20), `col163` VARCHAR(20), `col164` VARCHAR(20), `col165` VARCHAR(20), `col166` VARCHAR(20), `col167` VARCHAR(20), `col168` VARCHAR(20), `col169` VARCHAR(20), `col170` VARCHAR(20), `col171` VARCHAR(20), `col172` VARCHAR(20), `col173` VARCHAR(20), `col174` VARCHAR(20), `col175` VARCHAR(20), `col176` VARCHAR(20), `col177` VARCHAR(20), `col178` VARCHAR(20), `col179` VARCHAR(20), `col180` VARCHAR(20), `col181` VARCHAR(20), `col182` VARCHAR(20), `col183` VARCHAR(20), `col184` VARCHAR(20), `col185` VARCHAR(20), `seg1` VARCHAR(41), `seg2` VARCHAR(80), `seg3` VARCHAR(41) ) CHARSET=utf8mb4 [1022502] (odbc_bulk.c:723)

Re: How to trouble shoot target apply error in a task

john_wang — Thu, 22 Aug 2024 14:06:59 GMT

Hello @desmondchew ,

Please check out the article: Qlik Replicate: How to resolve row size too large error while loading into MySQL as target DB

Hope this helps.

John.

Re: How to trouble shoot target apply error in a task

desmondchew — Thu, 22 Aug 2024 14:11:10 GMT

Hi John,

The log file is showing error related to the table "attrep_changesE2B34BEB3F920C7F". It says "CREATE TEMPORARY TABLE `db`.`attrep_changesE2B34BEB3F920C7F` ( `seq` INTEGER"

This differs from permanent table as it's a Qlik tables. How can we fix this as I have little control of the table and I am unable to find it in the target database?

Thank you.
Desmond

Re: How to trouble shoot target apply error in a task

john_wang — Thu, 22 Aug 2024 14:17:38 GMT

Hello Desmond, @desmondchew

The net change table columns definition comes from the target table columns. So if the target table columns were changed, then the net change table will be defined with different columns accordingly.

In your error messages, there are 3 columns:

`col1` VARCHAR(4000), `col2` VARCHAR(4000), `col3` VARCHAR(4000)

You need to change them to TEXT. Let me know if it works for you.

Thanks,

John.

Re: How to trouble shoot target apply error in a task

Heinvandenheuvel — Thu, 22 Aug 2024 15:59:35 GMT

This is not really a Replicate error issues, but a design constraint issue. Read the error message, generated by MySQL, carefully:

[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.37]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 [1022502] (ar_odbc_stmt.c:5062)

Sometimes there is simply too much. Sometimes surprise issues like UTF characters potentially needing 4 bytes versus expect single byte cause this too happen.

That's most likely the case since the simple total is 20K, not 64K

perl -ne "if (/VARCHAR\((\d+)/) { $n++, $t += $1 } } { print qq($n\t$t)" < tmp.txt
188 20595

Go count!

Hein,

Re: How to trouble shoot target apply error in a task

Heinvandenheuvel — Thu, 22 Aug 2024 18:15:24 GMT

A little extra...

>> I could not find the table attrep_changesE2B34BEB3F920C7F in RDS.

Of course not, because it could not be created.

VARCHAR(41) ) CHARSET=utf8mb4 [1022502] (odbc_bulk.c:723) ---> https://dev.mysql.com/doc/refman/8.4/en/charset-unicode-utf8mb4.html

The artificially named columns are the minimal superset to catch all tables being replicated. You'll find some table with 3 VARCHAR(4000) columns, and you'll find some table with 188 columns which may well be two different tables. Find the table(s) with those 3 extra-wide column and for testing/development purposes try to remove one of them for replication. Now does it work? What does the create statement now look like after the removal? It may well become VARCHAR(4000) , VARCHAR(4000) , VARCHAR(1500) meaning there is an other table with 3 wide columns but the 3rd widest was 1500 UTF chars and was slated to be replicated using one of the 4000 character wide helper table columns which was needed anyway for an other table.

Do you see how that might work?

You may be able to influence the target DB character set also to get this to work.

Good luck,

Hein.

Re: How to trouble shoot target apply error in a task

desmondchew — Fri, 23 Aug 2024 03:10:34 GMT

We are aware of the error as it's a limitation of using MySQL as a target endpoint with the "row size too large" constraint. It's not a replicate error. But the DB doesn't allow to replicate, and the challenge was to identify which table was the cause in this task as we have many tables, and the log file doesn't tell us the table.

I have since isolate them and found the culprit and able to work on it.

Thank you.

Desmond

Re: How to trouble shoot target apply error in a task

john_wang — Fri, 23 Aug 2024 03:27:34 GMT

Thanks for your great support Desmond.