0

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

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!

  • Ref. 1 this table has many columns, is there a way to pull column names from INFORMATION_SCHEMA in order to sum their length? – HTF Apr 15 '15 at 21:46
  • Ref. 2 could you please give me some hints how to construct this query using INFORMATION_SCHEMA – HTF Apr 15 '15 at 22:00
  • I added that to the answer. However, it is not recommended to have huge number of VARCHAR fields in one table. – Jehad Keriaki Apr 16 '15 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.