Why didn't MySQL keep the innodb_file_format after loading a mysqldump

Question

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?

Answer 1

According to the MySQL Documentation on

innodb_file_format

The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

innodb_file_format_max

At server startup, InnoDB sets the value of this variable to the file format tag in the system tablespace (for example, Antelope or Barracuda). If the server creates or opens a table with a “higher” file format, it sets the value of innodb_file_format_max to that format.

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:

echo "SET GLOBAL innodb_file_format = 'Barracuda';" > mynewdump.sql
cat mydump.sql >> mynewdump.sql

Now, just load the dump

mysql -uroot -p < mynewdump.sql

TECHNIQUE #2 : Set the Barracuda Option Before Loading the Dump

Login to mysql as root@localhost and run this from the mysql prompt

mysql> SET GLOBAL innodb_file_format = 'Barracuda';
mysql> source mynewdump.sql

WARNING

TEST THE SUGGESTIONS ON A DEV AND STAGING SERVER, PLEASE

Give it a Try !!!