MySQL Bugs: #56628: ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error

Bug #56628	ALTER TABLE .. KEY_BLOCK_SIZE=0 produces untrue warning or unnecessary error
Submitted:	7 Sep 2010 19:46	Modified:	14 Dec 2010 22:43
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.51, 5.5.6-m3, 5.6.0-m4	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
KEY_BLOCK_SIZE=0 clause for an InnoDB table causes a warning 'InnoDB: ignoring KEY_BLOCK_SIZE=0.' This is true for CREATE TABLE or ALTER on a table with default KEY_BLOCK_SIZE, but wrong for ALTER TABLE if KEY_BLOCK_SIZE was previously set: in this case, despite the warning, KEY_BLOCK_SIZE is removed from the table create options.

If innodb_strict_mode=ON, instead of producing the warning the statement fails.

Setting KEY_BLOCK_SIZE=0 is the only way I've found so far to get rid of KEY_BLOCK_SIZE in table parameters. If it is indeed so, it should not be treated as an error. 

How to repeat:
--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t;
--enable_warnings

CREATE TABLE t ( i INT ) 
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS 
   FROM information_schema.tables WHERE TABLE_NAME='t';

SET innodb_strict_mode=1;
--echo # With strict mode ON, attempt to remove KEY_BLOCK_SIZE causes an error:
--error ER_CANT_CREATE_TABLE
ALTER TABLE t KEY_BLOCK_SIZE=0;

SET innodb_strict_mode=0;
--echo # With strict mode OFF, we are getting a warning
ALTER TABLE t KEY_BLOCK_SIZE=0;
--echo # ... but the warning is wrong, in fact KEY_BLOCK_SIZE=0 was acted upon:
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS 
  FROM information_schema.tables WHERE TABLE_NAME='t';

# Cleanup
DROP TABLE t;
--exit

Suggested fix:
Disable the warning if ALTER TABLE ... KEY_BLOCK_SIZE=0 is issued for a table with previously set KEY_BLOCK_SIZE; or, provide another way to remove KEY_BLOCK_SIZE from table creation options, and change the ALTER behavior so that KEY_BLOCK_SIZE=0 is really ignored (does not change anything).

After an initial fix and some more analysis, I think this bug needs some more description as to just how to fix it.  

The main issue is how to handle KEY_BLOCK_SIZE=0.  In strict mode, KEY_BLOCK_SIZE=0 is always rejected straightaway with this warning and error;
| Level   | Code | Message
+---------+------+-----------------------------------------------------+
| Warning | 1478 | InnoDB: invalid KEY_BLOCK_SIZE = 0. Valid values are [1, 2, 4, 8, 16]
| Error   | 1005 | Can't create table 'test.bug56628' (errno: 1478)
+---------+------+-----------------------------------------------------+

That seems to be appropriate behavior according to this bug.  So that means that KEY_BLOCK_SIZE=0 is NOT a valid value.  But this bug request seems to want to institutionalize it as a way to get rid of the "KEY_BLOCK_SIZE=n" text in the CREATE_OPTIONS field of INFORMATION_SCHEMA.TABLES.  Currently, InnoDB gives this warning in non-strict mode;
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=0.

Then the KEY_BLOCK_SIZE is removed from the CREATE_OPTIONS field.  But Innodb is actually still using a KEY_BLOCK_SIZE because the table is still compressed.  That default KEY_BLOCK_SIZE is 8 and is always used for ROW_FORMAT-COMPRESSED if the KEY_BLOCK_SIZE is not given explicitly.  

There is no hint there that setting KEY_BLOCK_SIZE=0 should turn OFF compression even though KEY_BLOCK_SIZE=1 will turn compression ON if a ROW_FORMAT was not specified.  If KEY_BLOCK_SIZE=0 really is a bad value according to strict mode, then we need to continue to use a warning about it in non-strict mode.  Since it has the affect of changing the previously used KEY_BLOCK_SIZE to the default level, maybe a new warning should be created that says something like this;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=0.      |
| Warning | 1478 | InnoDB: Using default KEY_BLOCK_SIZE=8. |
+---------+------+-----------------------------------------+

In this way, it will be clear that an alter statement that uses KEY_BLOCK_SIZE=0 on a compressed file in non-strict mode will still result result in a compressed file and even though the KEY_BLOCK_SIZE is no longer seen in I_S.TABLES, the warning will show that it is set implicitly.

Hi Kevin,

When I was talking about KEY_BLOCK_SIZE=0 being the only way to "remove KEY_BLOCK_SIZE from table creation options", I did not mean switching off compression -- I literally meant removing the setting from table description. 

The use case below leads to the problem that I encounter in tests often, and I suppose more users might start running onto it when they explore InnoDB modes and options.

- server is running with innodb_file_per_table=OFF, innodb_file_format=Antelope, innodb_strict_mode=OFF 

- I create a table:
CREATE TABLE t ( i INT ) ENGINE = InnoDB KEY_BLOCK_SIZE=4
It might be not the smartest thing to do, but nothing bad happens, I just get warnings
+---------+------+--------------------------------------------------------------
| Level   | Code | Message                                                      
+---------+------+--------------------------------------------------------------
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.       
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4.                           
+---------+------+--------------------------------------------------------------
which is fine with me. 

- Next time, server is started with innodb_strict_mode=ON -- after all, InnoDB manual suggests it as a good thing to do (http://dev.mysql.com/doc/innodb-plugin/1.1/en/innodb-other-changes-strict-mode.html)

- Now, I cannot ALTER my table -- whatever I do with it, causes an error:
ALTER TABLE t ADD COLUMN f INT;
ERROR 1005 (HY000): Can't create table 'test.#sql-2d36_c' (errno: 1478)

As a standard user, the only (and not very obvious) way I now have to deal with the problem is to set my session value of innodb_strict_mode to OFF, and then run ALTER TABLE ... KEY_BLOCK_SIZE=0, which will cause warnings but will remove the offending clause from the table description. Unfortunately, it will also cause replication failure if server is running with replication and slave also has innodb_strict_mode=ON, because the ALTER will pass on master, but fail on slave.

That's why I thought that there should be a way to 'fix' the table without dropping and re-creating it from scratch.

In regard to warnings and their text in non-strict mode, it is not a huge problem, although indeed if we do want the warning to appear, its description could be more relevant.

> In email, Kevin Lewis wrote:
>  Great explanation Elena.  The extra details really helps to explain what really needs to be fixed.  I am going back into debugging mode to figure out why after ignoring a bad value, the KEY_BLOCK_SIZE setting still shows up in the CREATE_OPTIONS.  If it did not show up after not being used, then there would be no need to use KEY_BLOCK_SIZE=0 in the first place, right?

Elena wrote;
I would say so if the behavior related to ROW_FORMAT/KEY_BLOCK_SIZE was consistent, but unfortunately it does not always look this way.
Please consider another example.

- Server is running with

innodb_file_format = Barracuda;
innodb_file_per_table = ON;
innodb_strict_mode = ON;

I create 3 tables:
CREATE TABLE t1 ( i INT ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
CREATE TABLE t2 ( i INT ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
CREATE TABLE t3 ( i INT ) ENGINE=InnoDB KEY_BLOCK_SIZE=8;

All three table definitions are entirely correct and pass without errors and warnings.
Technically, all tables are identical -- they all have row format compressed and key block size 8, although some parameters are set implicitly.

Then, server is restarted with
innodb_file_per_table = OFF
(or it is just set globally).
It is not unthinkable, as the parameter is performance-related and the user might want to switch it off during the tuning process.

Now, the tables start behaving differently.

I can alter the first table even although i get a couple of warnings:

ALTER TABLE t1 ADD COLUMN f INT;
Records: 0  Duplicates: 0  Warnings: 2

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                          |
+---------+------+---------------------------------------------------------------+

Moreover, I can easily fix the table so it does not produce warnings anymore:

ALTER TABLE t1 ROW_FORMAT=COMPACT;
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE t1 ADD COLUMN f1 INT;
Records: 0  Duplicates: 0  Warnings: 0

However, I cannot do the same with the other two tables:

ALTER TABLE t2 ADD COLUMN f INT;
ERROR 1005 (HY000): Can't create table 'test.#sql-1ec7_311' (errno: 1478)

ALTER TABLE t2 ROW_FORMAT=COMPACT;
ERROR 1005 (HY000): Can't create table 'test.#sql-1ec7_311' (errno: 1478)
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.           |
| Warning | 1478 | InnoDB: cannot specify ROW_FORMAT = COMPACT with KEY_BLOCK_SIZE. |
| Error   | 1005 | Can't create table 'test.#sql-1ec7_311' (errno: 1478)            |
+---------+------+------------------------------------------------------------------+

In this case, the solution with ignoring the value upon table creation would not work, as the value was valid at that point.

OK, so valid values for KEY_BLOCK_SIZE should remain in the CREATE OPTIONS even after they cannot be used because of conflicts with innodb_file_per_table or inodb_file_format.  But if a bad value for KEY_BLOCK_SIZE is given like 7, The 'ignored' warning occurs and KEY_BLOCK_SIZE=7 should be erased from the CREATE_OPTIONS, right?  Likewise, if there is a natural conflict between CREATE_OPTIONS like; ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4,  the 'ignored' warning should happen and the ignored setting should be erased from the CREATE_OPTIONS.  Do you agree?

On a related subject, in email Peter Gulutzan made this comment;
"I don't see that setting KEY_BLOCK_SIZE=0 could (or should)
change ROW_FORMAT. It should mean "key_block_size=default".
It should be legal always."

I tend to agree.  So this means that if KEY_BLOCK_SIZE=0 is used to set it to the default value (as it currently does) then it has some documented usefulness
and should not be rejected in strict mode with an error.

A patch for mysql-5.5-innodb with comprehensive fixes for Bug56632 and Bug#56628

Attachment: Bug56632andBug56628.patch (application/octet-stream, text), 112.94 KiB.

This patch is against mysql-5.5.innodb but a version for mysql-5.1-innodb will also be created.

This code change modifies several behaviors that were previously not expected or expected differently or just did not work right. The following rules for create options are now enforced and tested for.

# Rules for interpreting CREATE_OPTIONS
# 1) Create options on an ALTER are added to the options on the
# previous CREATE or ALTER statements.
# 2) KEY_BLOCK_SIZE=0 is considered a default or unspecified value.
# If the current ROW_FORMAT has explicitly been set to COMPRESSED,
# Innodb will use a default value of 8. Otherwise KEY_BLOCK_SIZE
# will not be used.
# 3) ROW_FORMAT=DEFAULT allows Innodb to choose its own default, COMPACT.
# 4) ROW_FORMAT=DEFAULT and KEY_BLOCK_SIZE=0 can be used at any time to
# unset or erase the values persisted in the MySQL dictionary and
# by SHOW CREATE TABLE.
# 5) When incompatible values for ROW_FORMAT and KEY_BLOCK_SIZE are
# both explicitly given, the ROW_FORMAT is always used in non-strict
# mode.
# 6) Innodb will automatically convert a table to COMPRESSED only if a
# valid non-zero KEY_BLOCK_SIZE has been given and ROW_FORMAT=DEFAULT
# or has not been used on a previous CREATE TABLE or ALTER TABLE.
# 7) Innodb strict mode is designed to prevent incompatible create
# options from being used together.
# 8) The 'non-strict' behavior is intended to permit you to import a
# mysqldump file into a database that does not support compressed
# tables, even if the source database contained compressed tables.
# All invalid values and/or incompatible combinations of ROW_FORMAT
# and KEY_BLOCK_SIZE are automatically corrected
#
# *** innodb_strict_mode=ON ***
# 1) KEY_BLOCK_SIZE=0 can be used to set it to 'unspecified'.
# 2) A valid KEY_BLOCK_SIZE is incompatible with COMPACT, DYNAMIC &
# REDUNDANT.
# 3) KEY_BLOCK_SIZE=1,2,4,8, or 16 as well as ROW_FORMAT=COMPRESSED and
# ROW_FORMAT=DYNAMIC are incompatible with innodb_file_format=Antelope
# and innodb_file_per_table=OFF
# 4) KEY_BLOCK_SIZE on an ALTER must occur with ROW_FORMAT=COMPRESSED
# or ROW_FORMAT=DEFAULT if the ROW_FORMAT was previously specified
# as COMPACT, DYNAMIC or REDUNDANT.
# 5) KEY_BLOCK_SIZE on an ALTER can occur without a ROW_FORMAT if the
# previous ROW_FORMAT was DEFAULT, COMPRESSED, or unspecified.
#
# *** innodb_strict_mode=OFF ***
# 1. Ignore a bad KEY_BLOCK_SIZE, defaulting it to 8.
# 2. Ignore a bad ROW_FORMAT, defaulting to COMPACT.
# 3. Ignore a valid KEY_BLOCK_SIZE when an incompatible but valid
# ROW_FORMAT is specified.
# 4. If innodb_file_format=Antelope or innodb_file_per_table=OFF
# it will ignore ROW_FORMAT=COMPRESSED or DYNAMIC and it will
# ignore all KEY_BLOCK_SIZEs.
#
# See InnoDB documentation page "SQL Compression Syntax Warnings and Errors"

In this patch, existing tests innodb_bug54679.test and innodb_bug56632.test are
modified to use these rules and a new exaustive test is created to replace them.
A future patch can delete these tests.

The differences from previous behavior are;
1) KEY_BLOCK_SIZE=0 is allowed in both strict and non-strict mode
with no errors or warnings. (Bug#56628)
2) An explicit valid non-DEFAULT ROW_FORMAT always takes priority
over a valid KEY_BLOCK_SIZE. (bug#56632)
3) Automatic use of COMPRESSED row format is only done if the ROW_FORMAT
is DEFAULT or unspecified.
4) ROW_FORMAT=FIXED is prevented in strict mode.

patch for mysql-5.5-innodb with better comments and replaced tests are removed.

Attachment: Bug56628and56632.patch (application/octet-stream, text), 105.09 KiB.

Patch pushed to mysql-5.1-innodb

Attachment: Bug56628and56632.5.1.patch (application/octet-stream, text), 110.66 KiB.

Patch pushed to mysql-5.5-innodb

Attachment: Bug56628and56632.5.5.patch (application/octet-stream, text), 110.45 KiB.

patch for the mysql-next-mr branch

Attachment: Bug56628and55632.next.mr.patch (application/octet-stream, text), 2.12 KiB.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

Added to change log:

The clause KEY_BLOCK_SIZE=0 is now allowed on CREATE TABLE and ALTER
TABLE statements for InnoDB tables, regardless of the setting of
innodb_strict_mode. The zero value has the effect of resetting the
KEY_BLOCK_SIZE table parameter to its default value, depending on the
ROW_FORMAT parameter. Any valid, non-default ROW_FORMAT parameter
takes precedence over KEY_BLOCK_SIZE when both are specified.
KEY_BLOCK_SIZE only enables ROW_FORMAT=COMPRESSED if ROW_FORMAT is
not specified or is specified as DEFAULT.

As a consequence of this
fix, ROW_FORMAT=FIXED is not allowed when the innodb_strict_mode is
enabled.

Rearranged the changelog entries between this bug and bug#56628 somewhat. The changelog entry for this bug is now:

The clause KEY_BLOCK_SIZE=0 is now allowed on CREATE TABLE and ALTER
TABLE statements for InnoDB tables, regardless of the setting of
innodb_strict_mode. The zero value has the effect of resetting the
KEY_BLOCK_SIZE table parameter to its default value, depending on the
ROW_FORMAT parameter, as if it had not been specified. That default
is 8 if ROW_FORMAT=COMPRESSED. Otherwise, KEY_BLOCK_SIZE is not used
or stored with the table parameters.

As a consequence of this fix,
ROW_FORMAT=FIXED is not allowed when the innodb_strict_mode is
enabled.

Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)

When a table is altered from compressed to non-compressed (ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0), the KEY_BLOCK_SIZE is no longer mentioned on the table level of SHOW CREATE TABLE, but it is still mentioned with each secondary index.

I think that is wrong; the KEY_BLOCK_SIZE=0 should also clean up the index details.