Bug #46760 Fast ALTER TABLE no longer works for InnoDB

• Submitted: 17 Aug 2009 15:37
• Modified: 18 Dec 2009 13:11
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.39-bzr, 5.4-bzr
• OS: Any
• Assigned to: Georgi Kodinov
• CPU Architecture: Any
• Tags: regression

[17 Aug 2009 15:37] Dmitry Lenev

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;

[17 Aug 2009 16:05] Sveta Smirnova

Thank you for the report.

Verified as described.

[11 Sep 2009 10:02] Georgi Kodinov

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.

[11 Sep 2009 11:44] Bugs System

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.

[11 Sep 2009 13:18] Bugs System

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.

[14 Sep 2009 23:19] Tatiana Azundris Nuernberg

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

[14 Sep 2009 23:19] Tatiana Azundris Nuernberg

## 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';

[14 Sep 2009 23:24] Tatiana Azundris Nuernberg

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';

[18 Sep 2009 11:32] Bugs System

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.

[18 Sep 2009 13:01] Bugs System

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.

[30 Sep 2009 8:17] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20090929093622-1mooerbh12e97zux) (version source revid:[e-mail]-20090921090422-jz8bdfmapgq48seg) (merge vers: 6.0.14-alpha) (pib:11)

[30 Sep 2009 20:12] Paul DuBois

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.

[6 Oct 2009 9:01] Bugs System

Pushed into 5.1.40 (revid:[e-mail]-20091006073316-lea2cpijh9r6on7c) (version source revid:[e-mail]-20090923121212-4x70dbou02bbq88k) (merge vers: 5.1.40) (pib:11)

[22 Oct 2009 6:37] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091022063126-l0qzirh9xyhp0bpc) (version source revid:[e-mail]-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

[22 Oct 2009 7:09] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091022060553-znkmxm0g0gm6ckvw) (version source revid:[e-mail]-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

[22 Oct 2009 19:46] Paul DuBois

Noted in 5.1.40, 5.5.0 changelogs.

[18 Dec 2009 10:40] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:56] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:10] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:24] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[18 Dec 2009 13:11] MC Brown

Already documented in 5.1.41

[22 Nov 2010 4:37] Jimmy Yang

alter table rename column would still use table copy option (to avoid out of sync between InnoDB and MySQL dictionary), please see bug #47621 for detail