| Bug #39200 | optimize table does not recognize ROW_FORMAT=COMPRESSED | ||
|---|---|---|---|
| Submitted: | 2 Sep 2008 22:34 | Modified: | 22 Oct 21:50 |
| Reporter: | Jeff C | ||
| Status: | Closed | ||
| Category: | Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.26 | OS: | Linux |
| Assigned to: | Tatjana A. Nuernberg | Target Version: | 5.1+ |
| Tags: | row_format=compressed, optimize table | ||
| Triage: | Triaged: D2 (Serious) | ||
[2 Sep 2008 22:34]
Jeff C
[3 Sep 2008 8:18]
Sveta Smirnova
Thank you for the report. Verified as described with following test case: $cat bug39200.test --source include/have_innodb.inc 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; --exec ls -la $MYSQL_TEST_DIR/var/master-data/test alter table testing row_format=compressed; --exec ls -la $MYSQL_TEST_DIR/var/master-data/test optimize table testing; --exec ls -la $MYSQL_TEST_DIR/var/master-data/test Result: 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; total 120 drwxr-xr-x 2 ssmirnova ssmirnova 4096 Sep 3 09:17 . drwxr-xr-x 4 ssmirnova ssmirnova 4096 Sep 3 09:17 .. -rw-rw---- 1 ssmirnova ssmirnova 8586 Sep 3 09:17 testing.frm -rw-rw---- 1 ssmirnova ssmirnova 98304 Sep 3 09:17 testing.ibd alter table testing row_format=compressed; total 88 drwxr-xr-x 2 ssmirnova ssmirnova 4096 Sep 3 09:17 . drwxr-xr-x 4 ssmirnova ssmirnova 4096 Sep 3 09:17 .. -rw-rw---- 1 ssmirnova ssmirnova 8586 Sep 3 09:17 testing.frm -rw-rw---- 1 ssmirnova ssmirnova 65536 Sep 3 09:17 testing.ibd optimize table testing; Table Op Msg_type Msg_text test.testing optimize status OK total 120 drwxr-xr-x 2 ssmirnova ssmirnova 4096 Sep 3 09:17 . drwxr-xr-x 4 ssmirnova ssmirnova 4096 Sep 3 09:17 .. -rw-rw---- 1 ssmirnova ssmirnova 8586 Sep 3 09:17 testing.frm -rw-rw---- 1 ssmirnova ssmirnova 98304 Sep 3 09:17 testing.ibd
[24 Sep 2008 8:43]
Marko Mäkelä
When OPTIMIZE falls back to ALTER TABLE, it should preserve all table attributes. Apparently, it does not preserve ROW_FORMAT. Thus, I think that this is a MySQL server bug. The InnoDB plugin could probably work around this bug by implementing the optimize method that is defined in the MySQL 5.1 handler class.
[7 May 11:36]
Tatjana A. 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
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;
[7 May 11:43]
Tatjana A. Nuernberg
... optimize table testing; test.testing optimize note Table does not support optimize, doing recreate + analyze instead show create table testing; CREATE TABLE `testing` ... ( ... ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED select row_format from information_schema.tables where table_name='testing'; row_format Compact total 116 12 testing.frm 104 testing.ibd 03 At this point, INFORMATION_SCHEMA says COMPACT, while SHOW CREATE says COMPRESSED. The .frm says 0x03, which is ROW_TYPE_COMPRESSED, and the .ibd file-size corresponds to COMPACT. Note that all agreed on COMPACT at CREATE-time, and on COMPRESSED after ALTER.
[12 May 11:44]
Tatjana A. Nuernberg
bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list)
{
...
create_info.used_fields |= HA_CREATE_USED_ROW_FORMAT;
It's so obvious in retrospective.
[27 May 18:31]
Tatjana A. Nuernberg
firstpatch fixes bug described herein without obviously affecting maria.
this
--innodb-file-per-table=1 --innodb_file_format=barracuda --debug
CREATE TABLE t1 (a INT, b VARCHAR(100)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
ALTER TABLE t1 AUTO_INCREMENT = 1;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
still gives
CREATE TABLE t1 (a INT, b VARCHAR(100)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
row_format
Compressed
ALTER TABLE t1 AUTO_INCREMENT = 1;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
row_format
Compact
though, which is ... not good.
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc 2009-05-19 04:25:36 +0000
+++ sql/sql_table.cc 2009-05-26 14:23:11 +0000
@@ -6307,7 +6307,10 @@ view_err:
}
if (create_info->row_type == ROW_TYPE_NOT_USED)
+ {
create_info->row_type= table->s->row_type;
+ create_info->used_fields|= HA_CREATE_USED_ROW_FORMAT;
+ }
fixes this issue, but will affect maria3.result
Cleared new behavior with monty, extended tests for 6.0 -- thanks monty, thanks Davi!
[29 May 16:55]
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/75258 2910 Tatiana A. Nurnberg 2009-05-29 Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED When doing ALTER TABLE, we forgot to point out that we actually have ROW_FORMAT information (from the original table), so we dropped to "sensible defaults". This affects both ALTER TABLE and OPTIMIZE TABLE which may fall back on ALTER TABLE for InnoDB. We now flag that we do indeed know the row-type, thereby preserving compression-type etc. No .test in 5.1 since we'd need a reasonable new plugin from InnoDB to show this properly; in higher versions, maria can demonstrate this. @ sql/sql_table.cc In mysql_alter_table() flag that we have row-type info from old table. In compare_tables(), we must explicitly check whether row-type has changed (rather than rely on the flag which will always be set at this point now).
[29 May 17:30]
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/75268 3324 Tatiana A. Nurnberg 2009-05-29 [merge] Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED When doing ALTER TABLE, we forgot to point out that we actually have ROW_FORMAT information (from the original table), so we dropped to "sensible defaults". This affects both ALTER TABLE and OPTIMIZE TABLE which may fall back on ALTER TABLE for InnoDB. We now flag that we do indeed know the row-type, thereby preserving compression-type etc. @ mysql-test/suite/maria/r/maria3.result test updated and extended after clearing with monty. @ mysql-test/suite/maria/t/maria3.test test updated and extended after clearing with monty. @ sql/sql_table.cc In mysql_alter_table() flag that we have row-type info from old table. In compare_tables(), change whether row type has actually changed, rather than rely on the flag (which will always be set now).
[29 May 18:16]
Tatjana A. Nuernberg
queued for 5.1.36, 6.0.12 in bugteam
[16 Jun 13:03]
Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:azundris@mysql.com-20090529164935-xe3dceff53d7pywb) (merge vers: 5.1.36) (pib:6)
[17 Jun 21:27]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:azundris@mysql.com-20090529170733-wxq9j0idmss9rllz) (merge vers: 6.0.12-alpha) (pib:11)
[21 Jul 21:10]
Paul DuBois
Noted in 5.1.36, 5.4.4 changelogs. ALTER TABLE neglected to preserve ROW_FORMAT information from the original table, which could cause subsequent ALTER TABLE and OPTIMIZE TABLE statements to lose the row format for InnoDB tables.
[13 Aug 0:50]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 4:07]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 15:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 15:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 15:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 18:33]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[6 Oct 11:01]
Bugs System
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)
[8 Oct 21:42]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[22 Oct 8:36]
Bugs System
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)
[22 Oct 9:09]
Bugs System
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)
[22 Oct 21:50]
Paul DuBois
Noted in 5.5.0, 6.0.14 changelogs.
