MySQL Bugs: #46760: Fast ALTER TABLE no longer works for InnoDB

Bug #46760	Fast ALTER TABLE no longer works for InnoDB
Submitted:	17 Aug 17:37	Modified:	22 Oct 21:46
Reporter:	Dmitri Lenev
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.1.39-bzr, 5.4-bzr	OS:	Any
Assigned to:	Georgi Kodinov	Target Version:	5.1+
Tags:	regression
Triage:	Triaged: D3 (Medium)

Description:
I am running MySQL Server built from latest snapshot of mysql-5.1 tree.
I am trying to execute ALTER TABLE ... ALTER COLUMN ... SET DEFAULT statement for InnoDB
table. It executes correctly, but more close investigation shows that it is not executed
as a "fast" alter table operation, i.e. instead of simply modifying .FRM file it copies
contents from old version of table to the new version, which causes much longer ALTER
TABLE execution time for big tables.

In 5.0 this operation was executed as a "fast" operation i.e. without data copying.

It also works as fast operation for MyISAM tables.

The same applies to ALTER TABLE ... COMMENT= ... .

After preliminary code inspection it seems that in 5.1 and 5.4 "fast"/"online" is broken
for InnoDB tables (i.e. doesn't work as expected).

This bug was discovered while running test coverage for new foreign key implementation
using InnoDB engines.

How to repeat:
Run the following script using mysqltest tool for 5.0, 5.1 and 5.4 versions and compare
results of its execution.

--source include/have_innodb.inc
set storage_engine= InnoDB;

create table t2 (a int);
insert into t2 values (1);
--echo # By using --enable_info and verifying that number of affected
--echo # rows is 0 we check that this ALTER TABLE is really carried
--echo # out as "fast/online" operation, i.e. without full-blown data
--echo # copying.
--echo #
--echo # I.e. info for the below statement should normally look like:
--echo #
--echo # affected rows: 0
--echo # info: Records: 0  Duplicates: 0  Warnings: 0
--enable_info
alter table t2 alter column a set default 10;
--disable_info
show create table t2;

Thank you for the report.

Verified as described.

I've been playing with the latest 5.1-bugteam and Dmitri's test case (table t2 alter
column a set default 10). 
The problem imho is in bool ha_innobase::check_if_incompatible_data() :
8379         /* Check that row format didn't change */
8380         if ((info->used_fields & HA_CREATE_USED_ROW_FORMAT) &&
8381                 get_row_type() != info->row_type) {
8382   
8383                 return COMPATIBLE_DATA_NO;
8384         }

Here get_row_type() returns ROW_TYPE_COMPACT whereas info->row_type is ROW_TYPE_DEFAULT.
Investigating why  HA_CREATE_USED_ROW_FORMAT is set I've stumbled upon the fix for bug
#39200 that's setting it unconditionally.
Looks like regression of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83044

3120 Georgi Kodinov	2009-09-11
      Bug#46760: Fast ALTER TABLE no longer works for InnoDB
      
      Despite copying the value of the old table's row type
      we don't always have to mark row type as being specified.
      Innodb uses this to check if it can do fast ALTER TABLE
      or not.
      Fixed by marking the row as supplied only when it's 
      not the default one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83055

3120 Georgi Kodinov	2009-09-11
      Bug#46760: Fast ALTER TABLE no longer works for InnoDB
      
      Despite copying the value of the old table's row type
      we don't always have to mark row type as being specified.
      Innodb uses this to check if it can do fast ALTER TABLE
      or not.
      Fixed by marking the row as supplied only when it's 
      not the default one.
      Added a test case for 39200.

--ignore-builtin-innodb
--plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
--innodb-file-per-table=1 --innodb_file_format=barracuda

## uses UNIXisms, relies on certain file-sizes, etc.  Example only, do not add to repo!
## needs -master.opt with --innodb-file-per-table=1 --innodb_file_format=barracuda

--source include/have_innodb.inc

select * from information_schema.plugins where plugin_name='InnoDB';

select @@innodb_file_format;

let $MYSQLD_DATADIR= `select @@datadir`;

create table testing (id int unsigned not null auto_increment,
                      name varchar(100) not null,
                      primary key(id))
                     engine=innodb;
# row_format=compressed;
insert into testing (name) values ('foo');
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;

show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

# alter table testing row_format=redundant;
alter table testing row_format=compressed;
show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

optimize table testing;
show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

# --sleep 60
drop table testing;

set global innodb_file_format='Barracuda';
set global innodb_file_format_check='Antelope';

select * from information_schema.plugins where plugin_name='InnoDB';
PLUGIN_NAME	PLUGIN_VERSION	PLUGIN_STATUS	PLUGIN_TYPE	PLUGIN_TYPE_VERSION	PLUGIN_LIBRARY	PLUGIN_LIBRARY_VERSION	PLUGIN_AUTHOR	PLUGIN_DESCRIPTION	PLUGIN_LICENSE
InnoDB	1.0	ACTIVE	STORAGE ENGINE	50139.0	ha_innodb_plugin.so	1.0	Innobase Oy	Supports
transactions, row-level locking, and foreign keys	GPL
select @@innodb_file_format;
@@innodb_file_format
Barracuda
create table testing (id int unsigned not null auto_increment,
name varchar(100) not null,
primary key(id))
engine=innodb;
insert into testing (name) values ('foo');
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
show create table testing;
Table	Create Table
testing	CREATE TABLE `testing` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=latin1
select row_format from information_schema.tables where table_name='testing';
row_format
Compact
total 108
 12 testing.frm
 96 testing.ibd
000
alter table testing row_format=compressed;
show create table testing;
Table	Create Table
testing	CREATE TABLE `testing` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
select row_format from information_schema.tables where table_name='testing';
row_format
Compressed
total 76
 12 testing.frm
 64 testing.ibd
000
optimize table testing;
Table	Op	Msg_type	Msg_text
test.testing	optimize	note	Table does not support optimize, doing recreate + analyze
instead
test.testing	optimize	status	OK
show create table testing;
Table	Create Table
testing	CREATE TABLE `testing` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
select row_format from information_schema.tables where table_name='testing';
row_format
Compressed
total 76
 12 testing.frm
 64 testing.ibd
000
drop table testing;
set global innodb_file_format='Barracuda';
set global innodb_file_format_check='Antelope';

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83713

3120 Georgi Kodinov	2009-09-18
      Bug#46760: Fast ALTER TABLE no longer works for InnoDB
      
      Despite copying the value of the old table's row type
      we don't always have to mark row type as being specified.
      Innodb uses this to check if it can do fast ALTER TABLE
      or not.
      Fixed by marking the row as supplied only when it's 
      not the default one.
      Added a test case for 39200.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83719

3115 Georgi Kodinov	2009-09-18
      Bug#46760: Fast ALTER TABLE no longer works for InnoDB
            
      Despite copying the value of the old table's row type
      we don't always have to mark row type as being specified.
      Innodb uses this to check if it can do fast ALTER TABLE
      or not.
      Fixed by correctly flagging the presence of row_type 
      only when it's actually changed.
      Added a test case for 39200.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version
source revid:joro@sun.com-20090921090422-jz8bdfmapgq48seg) (merge vers: 6.0.14-alpha)
(pib:11)

Noted in 6.0.14 changelog.

For InnoDB tables, an unnecessary table rebuild for ALTER TABLE could
sometimes occur for metadata-only changes. 

Setting report to NDI pending push into 5.1.x.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source
revid:satya.bn@sun.com-20090923121212-4x70dbou02bbq88k) (merge vers: 5.1.40) (pib:11)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version
source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha)
(pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version
source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta)
(pib:13)

Noted in 5.1.40, 5.5.0 changelogs.