MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

innodbMySQL

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:

  1. 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.
  2. 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;

Best Answer

According to this, your problem is not with VARCHAR fields. However:

  1. SELECT id, LENGTH(field1)+LENGTH(field2)...+LENGTH(field_n) AS total_length WHERE total_length > 8126
  2. Can be done using 2 queries for each field. (This can be done in a complex query if you want to use INFORMATION_SCHEMA):
    • SELECT MAX(LENGTH(field1)) INTO @max_length1 FROM table_name;
    • SELECT * FROM table_name WHERE LENGTH(field1)=@max_length1;

Note: Increasing innodb_page_size's value may be helpful in your case.

Edit

To generate all the queries with the help of information_schema, run the following queries:

CREATE TABLE queries(field_name varchar(255), query1 varchar(4000), query2 varchar(4000));

INSERT INTO queries SELECT column_name, 
CONCAT('SELECT MAX(LENGTH(',column_name,')) INTO @max_length FROM table_name;'), 
CONCAT('SELECT * FROM table_name WHERE LENGTH(',column_name,')=@max_length;')
FROM information_schema.columns WHERE table_schema='test' and table_name='myisam_table';

SELECT * FROM queries;

Note that you have to run query1 then query2 from the result. DO NOT run all query1 then all query2!

Related Question