How to resolve row size too large error while loading into MySQL 8 as target DB

[desmondchew]

My source is Oracle 11.0.2.4 while target DB is MySQL 8.0.30. I encountered the following errors on a hand few of tables while loading into the target database. It got error and unable to load. Is there a workaround?

Table 'Schema'.'LOG' (subtask 1 thread 1) is suspended. Failed (retcode -1) to execute statement: 'CREATE TABLE `Schema`.`LOG` ( `ID` DECIMAL(38,0) NOT NULL, `PATH` VARCHAR(4000) NOT NULL, `SUCCESS` DECIMAL(38,0) NOT NULL, `REASON` VARCHAR(4000), `IMPORT_TYPE` VARCHAR(256), `REFDATA` VARCHAR(4000), `TRANS_ID` VARCHAR(4000), `CREATE_DT` DATETIME(6) NOT NULL, `VERSION_FROM` DECIMAL(38,0), `VERSION_TO` DECIMAL(38,0), `ORIG_ID` DECIMAL(38,0), `RES_PATH` VARCHAR(4000), `RES_DT` DATETIME(6), `PARTIAL` DECIMAL(38,0) NOT NULL, `ENRICHED_PATH` VARCHAR(4000) )';

RetCode: SQL_ERROR SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.30]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; execute create table statement failed,
statement CREATE TABLE `Schema`.`LOG` ( `ID` DECIMAL(38,0) NOT NULL, `PATH` VARCHAR(4000) NOT N

[john_wang]

Hello @desmondchew ,

There are many factors may lead the same error and MySQL link shows options for Row Size Limits . Please check and adopt one of the solution.

For your quick moving forward:

1. how about if you run the same SQL manually?

2. how about if you add "ENGINE=MyISAM" in the end of the same SQL and run it manually?

3 how about if you change Replicate task from Batch Apply Mode to Transactional Apply mode.

hope this helps.

Regards,

John.

[desmondchew]

Hi John,

I have manually running the table but unsuccessful.

1. how about if you run the same SQL manually? Error.

2. how about if you add "ENGINE=MyISAM" in the end of the same SQL and run it manually?Error.

0

105

17:39:22

CREATE TABLE xxxx `ENRICHED_PATH` VARCHAR(4000) ) engine=myisam

Error Code: 1118. 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

0.265 sec

3 how about if you change Replicate task from Batch Apply Mode to Transactional Apply mode.

I changed to transactional apply. Still same error. FYI, the table has not been loaded yet. It's zero records in the target DB.

I read that changing the datatype of varchar to TEXT would help. Should I do so?

Desmond

[john_wang · Accepted solution]

Hello @desmondchew ,

Change any one VARCHAR(4000) to TEXT should help. In general change the latest VARCHAR(4000) (no need to "ENGINE=MyISAM").

Let me know if it solve the problem.

thanks,

John.

[Heinvandenheuvel]

I see 5 columns of 4000 varchars which can take up to 4 bytes storage per character to allow for all unicode planes.

Practically, for 'English' like text the actual storage needed is closer to 1 bytes per char, and for Chinese 3 bytes.

Still, the software apparently plays it 'safe' and uses 4 - which would in this example in worst case require a 80K rows.

Maybe you can change to 'ascii' ? Probaly not.

Maybe you can recognize that the 4000 is just a random number pulled out of a hat somewhere. Consider a different number like 3000 and it will work is my guess. Better still run a MAX LENGTH on all the wide source column and see how much is actually used and adapt to that (with 25% buffer?)

You should just runa few CREATE statement test directly to the DB to see when/where it breaks  1 VARCHAR(4000) ok? Fine, try 2, try 3.. 4, 5,...   Now try VARCHAR(3000) one, two, three, four , five of them. (and that 3000 is not special. Feel free to use 2048 or 2345 or whatever.

Hein.