Change limit for “Mysql Row size too large”

by

The question has been asked on serverfault too.

You may want to take a look at this article which explains a lot
about MySQL row sizes. It’s important to note that even if you use
TEXT or BLOB fields, your row size could still be over 8K (limit for
InnoDB) because it stores the first 768 bytes for each field inline in
the page.

The simplest way to fix this is to use the Barracuda file format
with InnoDB. This basically gets rid of the problem altogether by
only storing the 20 byte pointer to the text data instead of storing
the first 768 bytes.

The method that worked for the OP there was:

  1. Add the following to the my.cnf file under [mysqld] section.

    innodb_file_per_table=1
innodb_file_format = Barracuda

  2. ALTER the table to use ROW_FORMAT=COMPRESSED.

    ALTER TABLE nombre_tabla
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED 
    KEY_BLOCK_SIZE=8;

There is a possibility that the above still does not resolve your issues. It is a known (and verified) bug with the InnoDB engine, and a temporary fix for now is to fallback to MyISAM engine as temporary storage. So, in your my.cnf file:

internal_tmp_disk_storage_engine=MyISAM

More Related Contents:

  1. how to get result form mysql query in specific ids [closed]
  2. How to do a regular expression replace in MySQL?
  3. How to solve “Error: MySQL shutdown unexpectedly”?
  4. Count the number of occurrences of a string in a VARCHAR field?
  5. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  6. Unknown Column In Where Clause
  7. Left Join not returning all rows
  8. MySQL match() against() – order by relevance and column?
  9. Maximum length for MySQL type text
  10. How to count words in MySQL / regular expression replacer?
  11. gem install: Failed to build gem native extension (can’t find header files)
  12. Entity-Attribute-Value Table Design
  13. Filter by COUNT(*)?
  14. MySQL concatenation operator
  15. SQL Group By with an Order By
  16. How to Convert UTC Date To Local time Zone in MySql Select Query
  17. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  18. MySql – Way to update portion of a string?
  19. Using reserved words in column names
  20. MySQL INNER JOIN select only one row from second table
  21. How to select only date from a DATETIME field in MySQL?
  22. UPDATE all records with a random number from a range
  23. mysql sorting of version numbers
  24. Get Insert Statement for existing row in MySQL
  25. Defining Composite Key with Auto Increment in MySQL
  26. mysql Failed to read auto-increment value from storage engine
  27. Alter table if exists or create if doesn’t
  28. How to use Table output from stored MYSQL Procedure
  29. Array data type, split string,
  30. How can I make a table in MySQL called “order”?

Leave a Comment