I'm trying to convert/copy MyISAM to InnoDB table and I'm getting the following error message:
mysql> INSERT INTO test.innodb_table
SELECT * FROM test.myisam_table
ERROR 1118 (42000): Row size too large (> 8126).
Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC
or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline.
The MyISAM table has many VARCHAR(255) columns so it looks like it's reaching the maximum row size limit for InnoDB table.
Questions:
- How can I check on which row(s) the query above is actually failing so I can potentially remove it? I tried to use where clause and I was able to insert ~5000 rows this way but the orignal table has over 2 million rows.
- I would like to limit the size of these variable length columns. How I can check the max character length for all columns? Pseudocode:
if column type equal to VARCH (255), show rows with max character length
UPDATE: Thu Apr 16 15:58:19 BST 2015:
I constructed the following query to get a list of rows that exceed the max row length:
mysql> SET SESSION group_concat_max_len = 102400;
SELECT CONCAT('SELECT id, ',
GROUP_CONCAT('LENGTH(', 'IFNULL(', COLUMN_NAME, ',
0)', ')' SEPARATOR '+'), ' AS total_length
FROM myisam_table HAVING total_length > 8126;') AS 'Query'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = (SELECT DATABASE())
AND TABLE_NAME = 'myisam_table'\G
When I execute a query from the output of the query above, I receive a list of rows with a size greater than 8126 bytes:
+---------+--------------+
| id | total_length |
+---------+--------------+
| 489 | 10938 |
| 5244 | 9141 |
| 5869 | 9680 |
| 6083 | 21284 |
| 6654 | 8644 |
...
I can then successfully insert this query so I don't understand how this exactly works and where is this size limit imposed:
mysql> INSERT INTO test.innodb_table SELECT * FROM test.myisam_table WHERE id = 489;
