MySQL Bugs: #60737: innodb_file_format=Barracuda still creates tables in Antelope format

Bug #60737	innodb_file_format=Barracuda still creates tables in Antelope format
Submitted:	2 Apr 2011 23:44	Modified:	20 May 2013 19:38
Reporter:	Sean Jenkins	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.10, 5.5.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Barracuda, File_format, innodb

Description:
Despite documentation indicating that setting innodb_file_format=Barracuda will cause all newly created tables utilizing the Innodb engine to be created using the Barracuda file format, tables are still created with the row_format of Compact, which is the Antelope file_format

How to repeat:
Make sure the following are set:

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        |
+--------------------------+-----------+

Using even an existing database issue the following:

`CREATE TABLE test (id int) ENGINE=INNODB`

Perform a `SHOW TABLE STATUS`:

*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-04-02 17:33:52
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

Note, we're still on Compact, not Dynamic or Compressed (Barracuda file format)

Suggested fix:
Ideally, create a variable to override the default row_format on system startup.  It's my belief, although I haven't been able to confirm it, that the problem is that the default row_format is Compact and all new Innodb tables are inheriting this.  Regardless of setting the innodb_file_format=Barracuda, you're inheriting the row_format default value of Compact, and applying that.  If the innodb_file_format=Antelope or is not overridden, then innodb_row_format=Compact, otherwise, if innodb_file_format=Barracuda, then innodb_row_format=Dynamic.

As mentioned, I think although this needs to be tunable at the users discretion, there should be a default row_format applied based on the chosen innodb_file_format.  The exception being when you're attempting to mix formats.  In those cases, the innodb_row_format should win.

Verified just as described:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.11-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb%format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE test (id int) ENGINE=INNODB;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-04-03 10:58:23
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format) says:

"The file format to use for new InnoDB tables."

Any plans to fix this? Having to specify file format explicitly when creating tables gets a bit annoying if you still want to keep your application compatible with older InnoDB-versions.

Please fix this bug or remove the bogus documentation stating the default innodb format is tunable.

I found this with version 5.6.10 (Workbench version 5.2.47CE), and I am such newbie (with MySQL) that I can not find a good workaround. Please rise priority.

The innodb_file_format is more like the token to enable a particular format. To
use the new Barracuda formats, it requires the following conditions:

1) innodb_file_format set to Barracuda
2) innodb_file_per_table set to true
3) use row_format for compress or dynamic

Without these 3 conditions, the default table format will be Antelope with COMPACT displayed in the SHOW TABLE STATUS details.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Really..., Two years and 1 major release to fix a "documentation bug"?
And do you really expect everybody that would like to benefit from row_format=dynamic or row_format=compressed to alter their applications to explicitly specify it a table creation time?
I relly do not find this a documentation bug. If innodb_file_format=Barracuda is really just ment as an enabler for the Barracuda format and not a default-setting, I wonder why the setting was implemented in the first place.
I'm really struggling to find a situation where you couldn't live with innodb_file_format=Barracuda even if you want all your tables to be created with row_format=Compact. That is unless innodb_file_format=Barracuda has a noticeable performance penalty for Antelope-only setups. In that case, please document such a performance-penalty.

I have to agree with Kristian, this isn't a documentation bug, this is a broken feature.  What's the point of setting:

1) innodb_file_format set to Barracuda
2) innodb_file_per_table set to true

if they're absolutely useless because the real "trick" is to apply the row_format on table creation time.  So, documentation bug, no, bad implementation, yes.  Chalk it up for what it is please, but don't call it something it isn't.  If you don't intend to fix it properly, please state as much.