MySQL Bugs: #56632: ALTER TABLE implicitly changes ROW

Bug #56632	ALTER TABLE implicitly changes ROW_FORMAT to COMPRESSED
Submitted:	7 Sep 2010 21:39	Modified:	14 Dec 2010 23:41
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.50, 5.5.6-m3, 5.6.0-m4	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
If table create options contain a valid value of KEY_BLOCK_SIZE, ALTER on the table implicitly converts current ROW_FORMAT into compressed, without any warnings.

In the provided test case, CREATE TABLE produces a warning 'ignoring KEY_BLOCK_SIZE=1 unless ROW_FORMAT=COMPRESSED', and ROW_FORMAT of the created table is Compact. The following ALTER TABLE does not produce any warnings, but ROW_FORMAT switches to Compressed.

How to repeat:
SET innodb_strict_mode=0;
DROP TABLE IF EXISTS t;
CREATE TABLE t ( i INT ) ENGINE=InnoDB 
   ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=1;
SHOW WARNINGS;
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS 
   FROM information_schema.tables WHERE TABLE_NAME='t';
ALTER TABLE t ADD COLUMN f1 INT;
SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS 
   FROM information_schema.tables WHERE TABLE_NAME='t';

Suggested fix:
Do not perform implicit format conversion.

The warning code related to KEY_BLOCK_SIZE and ROW_FORMAT needs to be improved in order to take into account whether the KEY_BLOCK_SIZE is specified on the current ALTER statement or the previous CREATE statement.

Note that a similar bug is Bug#56628 which can be fixed with a separate patch.

Patch pushed to mysql-5.5-innodb

Attachment: Bug56632.patch (application/octet-stream, text), 24.47 KiB.

Patch approved by Jimmy yang on Review Board
Most of the review comments were about getting the code looking like Innodb code.
The testcase uses about 12 different tests to make sure that the warnings are based on CREATE OPTIONS actually added to the ALTER and not those being carried over from the original CREATE statement.

For clarity,  This patch was merged to mysql-5.1-innodb, committed, and null-merged with mysql-5.5-innodb.  Then pushed to both branches on bk-internal.
The change affected code in mysql-5.1-innodb/storage/innodb_plugin but not mysql-5.1-innodb/storage/innobase

After further review, the previous fix is not correct.

The previous fix used this rule; "If KEY_BLOCK_SIZE was specified on this statement and ROW_FORMAT was not, automatically change ROW_FORMAT to COMPRESSED." In this way, the following series of statements would make a COMPRESSED table with the second statement and convert it back to a COMPACT table on the third when in non-strict mode;

CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPACT;
ALTER TABLE t1 KEY_BLOCK_SIZE=1;
ALTER TABLE t1 ADD COLUMN f1 INT;

On the third statement, Innodb has no way of knowing which create option came last, ROW_FORMAT or KEY_BLOCK_SIZE, so it has to choose one to be priority over the other. If they were on the same statement, ROW_FORMAT would be used. The third ALTER should not change the ROW_FORMAT like that, which would be unexpected.

In order to correct this and make the behavior consistent. Innodb should automatically convert a table to COMPRESSED only if there is a non-zero KEY_BLOCK_SIZE and the current ROW_FORMAT is DEFAULT (or unspecified). With this rule, the second ALTER with fail in strict mode and ignore the KEY_BLOCK_SIZE in non-strict mode. And since the ROW_FORMAT was explicitly set and not explicitly changed, it stays COMPACT throughout.

So the new rule could be summed up this way; A table that has been explicitly set to a non-COMPRESSED row format cannot be implicitly changed to COMPRESSED by specifying a KEY_BLOCK_SIZE by itself. In this case, The KEY_BLOCK_SIZE must be accompanied by ROW_FORMAT=COMPRESSED.

A new patch and testcase is forthcoming.

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

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

This new 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.

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)

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)

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

An existing InnoDB could be switched to ROW_FORMAT=COMPRESSED
implicitly by a KEY_BLOCK_SIZE clause in an ALTER TABLE statement.
Now, the row format is only switched to compressed if there is an
explicit ROW_FORMAT=COMPRESSED clause. on the ALTER TABLE statement.

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 on either the
CREATE TABLE or ALTER TABLE statement, or is specified as DEFAULT. In
case of a conflict between KEY_BLOCK_SIZE and ROW_FORMAT clauses, the
KEY_BLOCK_SIZE is ignored if innodb_strict_mode is off, and the
statement causes an error if innodb_strict_mode is on.

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)