I've seen many threads about this error, but the solutions I've found don't seem to be applicable in my case.
I've received a rather large (~150Go) dump file from an Oracle database. I converted it to a MySQL one, using OraDump. However, when I try to import it in my MySQL server, I get the infamous error :
ERROR 111 (42000) at line 162936 : Row size too large. The maximum row size for the used table, not counting BLOBs, is 65535.
This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.
I tried increasing the innodb_log_file_size parameter, removing the strict mode, switching from ImmoDB to myISAM, nothing worked.
In my last attempt, I tried to add the -f parameter to the dump importation, in hope to just squeeze past the error, but now it just seems stuck.
I don't think I can change the table schemas, since they are created within the 150Go dump file, and I don't even know which tables/columns are at fault.
Is there any way around it ?
CodePudding user response：
The way to figure out which table is at fault is to dump and restore one table at a time. That will identify which table(s) are at fault. Then if necessary, dump and restore smaller subsets of rows from the table that causes the error. Continue subdividing the problem until you have narrowed it down to the culprit.
This process might take several tries.
Don't change the storage engine. This particular error is about MySQL in general, not any specific storage engine.
This error has nothing to do with the innodb_log_file_size. (Once you get past this error, you might get a subsequent error regarding innodb_log_file_size — it must be 10x the size of the largest individual BLOB or TEXT value).
Don't try to squeeze past the error by "forcing" it or disabling strict mode. If it did allow this, it would just corrupt your data.
The error message tells you exactly what you need to do:
You have to change some columns to TEXT or BLOBs.
This means change the table definition in MySQL. You probably don't have to change it in Oracle.
If you can't change the table definition, then sorry, you can't import this data into MySQL.
If you want any suggestion for how to fix this, then please post the DDL for the original table from Oracle, and the DDL for the table you're trying to import into in MySQL. Please use DBMS_METADATA.GET_DDL() in Oracle and SHOW CREATE TABLE in MySQL to get the DDL in text — not a screenshot image.
CodePudding user response：
Seeing the CREATE TABLE for the offending table may suffice for figuring out what to do next.
If the loading loaded some, but not all, of the tables, can you figure out which table was "last" (and not fully reloaded) or "next" to be reloaded?
The error talked about "row size" the talk about BLOB, etc may be not that relevant, since there is a datatype for a column. In particular, changing columns in an Engine=InnoDB table from VARCHAR to TEXT will not help.
The problem may be in having too many columns and/or a "row format" that is less forgiving of lots of columns.
More on limits: http://mysql.rjweb.org/doc.php/limits