I was loading a mysql dump today when I ran into
ERROR 1118 (42000) at line 279: Row size too large (> 8126)
Which I thought was weird because I know I had turned on row compression and was using Barracuda on that server. I also knew I wasn't running into that problem on the production database.
I checked on the production database and sure enough
show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
But on the server that loads the dump the file_format variable is set to:
mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+-----------+
Here's what my MySQL dump looks like
sudo mysqldump -u mysqlbackups --databases limbo | gzip > /local_backup_directory'+%m-%d-%Y'.sql.gz
Why wasn't the innodb_file_format and format_max variables dumped? How can I tell mysqldump to dump the file_format and format_max variables?
Best Answer
According to the MySQL Documentation on
innodb_file_format
innodb_file_format_max
Since each of these belong to
my.cnf
and not the data, the format of InnoDB is agnostic as far as the mysqldump goes. If you want to shift everything to Barracuda on the fly, there are two ways (TEST WHAT I AM ABOUT TO SUGGEST ON A DEV AND STAGING SERVER, PLEASE)TECHNIQUE #1 : Prepend the Barracuda Option to the mysqldump
Suppose the mysqldump file is called
mydump.sql
. Do this:Now, just load the dump
TECHNIQUE #2 : Set the Barracuda Option Before Loading the Dump
Login to mysql as
root@localhost
and run this from the mysql promptWARNING
TEST THE SUGGESTIONS ON A DEV AND STAGING SERVER, PLEASE
Give it a Try !!!