Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
desmondchew
Creator III
Creator III

How to trouble shoot target apply error in a task

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)

Labels (1)
7 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
desmondchew
Creator III
Creator III
Author

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

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

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,

Heinvandenheuvel
Specialist III
Specialist III

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.

 

 

 

desmondchew
Creator III
Creator III
Author

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

 

john_wang
Support
Support

Thanks for your great support Desmond.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!