MySQL Bugs: #66738: CREATE TABLE ... LIKE incorrectly handles KEY_BLOCK

Bug #66738	CREATE TABLE ... LIKE incorrectly handles KEY_BLOCK_SIZE
Submitted:	7 Sep 2012 23:11	Modified:	9 Aug 2013 15:39
Reporter:	Justin Tolmer	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.63, 5.5.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When doing a CREATE TABLE ... LIKE where the origin table uses both ROW_FORMAT=COMPRESSED and a KEY_BLOCK_SIZE which is not the default, then the created table claims that it is using the same key block as the origin but in fact uses the default.

SHOW CREATE TABLE will report an incorrect key block size but innochecksum will show that the file on disk is actually using the default.

How to repeat:
Apply this diff and run the new test which it includes:

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index 20617cb..3e23bf4 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -2167,6 +2167,15 @@ sub environment_setup {
   $ENV{'MYSQL_MY_PRINT_DEFAULTS'}= native_path($exe_my_print_defaults);
 
   # ----------------------------------------------------
+  # innochecksum
+  # ----------------------------------------------------
+  my $exe_innochecksum=
+    mtr_exe_exists(vs_config_dirs('extra', 'innochecksum'),
+                  "$path_client_bindir/innochecksum",
+                  "$basedir/extra/innochecksum");
+  $ENV{'MYSQL_INNOCHECKSUM'}= native_path($exe_innochecksum);
+
+  # ----------------------------------------------------
   # Setup env so childs can execute myisampack and myisamchk
   # ----------------------------------------------------
   $ENV{'MYISAMCHK'}= native_path(mtr_exe_exists(
diff --git a/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp-master.opt b/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp-master.opt
new file mode 100644
index 0000000..58cf3f6
--- /dev/null
+++ b/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp-master.opt
@@ -0,0 +1 @@
+--innodb_file_per_table --innodb_file_format=barracuda
diff --git a/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp.test b/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp.test
new file mode 100644
index 0000000..21ab5e2
--- /dev/null
+++ b/mysql-test/suite/innodb_plugin/t/innodb_create_like_comp.test
@@ -0,0 +1,26 @@
+--source include/have_innodb.inc
+--disable_query_log
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+--enable_query_log
+
+# Create a compressed table with non-default key_block_size.
+CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(255))
+ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
+
+# Create a second table like the first.
+CREATE TABLE t2 like t1;
+
+# They both report a key block size of 4 here.
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+# But...
+let $MYSQLD_DATADIR= `select @@datadir`;
+--exec $MYSQL_INNOCHECKSUM -b 4 $MYSQLD_DATADIR/test/t1.ibd
+--exec $MYSQL_INNOCHECKSUM -b 4 $MYSQLD_DATADIR/test/t2.ibd
+
+--disable_query_log
+DROP TABLE t1, t2;
+--enable_query_log

Suggested fix:
diff --git a/sql/table.cc b/sql/table.cc
index acd263a..2c902c7 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2579,6 +2579,7 @@ void update_create_info_from_table(HA_CREATE_INFO *create_info, TABLE *table)
   create_info->table_options= share->db_create_options;
   create_info->avg_row_length= share->avg_row_length;
   create_info->row_type= share->row_type;
+  create_info->key_block_size= share->key_block_size;
   create_info->default_table_charset= share->table_charset;
   create_info->table_charset= 0;
   create_info->comment= share->comment;

Thank you for the report.

I get error:

CURRENT_TEST: innodb_plugin.innodb_create_like_comp
/home/sveta/src/mysql-5.1/extra/innochecksum: invalid option -- 'b'
unrecognized option: -b
mysqltest: At line 21: command "$MYSQL_INNOCHECKSUM -b 4 $MYSQLD_DATADIR/test/t1.ibd" failed

Do you have some special version of innodbchecksum?

You're using the 5.6 build of innochecksum. My test was written for the 5.1 source tree.

Sorry, my fault. Apparently -b was an option we added in our 5.1 branch.

Thank you for the feedback.

Please send us your version of innochecksum (Or diff for -b option)

The diff for the -b option to innochecksum can be found at http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3813 and you can download the source file at that revision at http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/view/3813/extra/innocheck.... Let me know if that isn't sufficient information for you to be able reproduce.

Thank you for the feedback.

Actually this is not replication-related problem: slave replicates all changes just fine. This is issue with how table is created: it is nonsense to have bigint(7986) column.

And this is fixed in version 5.7 already, but since there is no public bug report for 5.5 I set this as "Verified".

Ups! Ignore last comment. Sorry, I updated wrong report.

Thank you for the feedback.

Seems not. I downloaded innochecksum.c, but can not compile it.

Firstly, it fails with 

gcc --pipe -DHAVE_CONFIG_H -I. -I../include -I../include -I../include -I../sql   -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wdeclaration-after-statement -g -DSAFE_MUTEX -DSAFEMALLOC -Wall -Wextra -Wunused -Wwrite-strings -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX   -DUNIV_LINUX -DUNIV_LINUX -MT innochecksum.o -MD -MP -MF .deps/innochecksum.Tpo -c -o innochecksum.o innochecksum.c
cc1: warnings being treated as errors
innochecksum.c: In function ‘main’:
innochecksum.c:798: error: comparison between signed and unsigned integer expressions
make[3]: *** [innochecksum.o] Error 1
make[3]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make: *** [all-recursive] Error 1

But these errors are easy to fix, but then it fails with:

/bin/sh ../libtool --preserve-dup-deps  --tag=CC   --mode=link gcc --pipe -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wdeclaration-after-statement -g -DSAFE_MUTEX -DSAFEMALLOC -Wall -Wextra -Wunused -Wwrite-strings -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS  -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX   -DUNIV_LINUX -DUNIV_LINUX  -rdynamic  -o innochecksum innochecksum.o ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lpthread -lcrypt -lnsl -lm  -lpthread 
libtool: link: gcc --pipe -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -Wdeclaration-after-statement -g -DSAFE_MUTEX -DSAFEMALLOC -Wall -Wextra -Wunused -Wwrite-strings -mtune=native -m64 -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DFORCE_INIT_OF_VARS -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -DSAFE_MUTEX -DUNIV_LINUX -DUNIV_LINUX -rdynamic -o innochecksum innochecksum.o  ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lpthread -lcrypt -lnsl -lm -lpthread
innochecksum.o: In function `buf_calc_page_fast_checksum':
/home/sveta/src/mysql-5.1/extra/innochecksum.c:343: undefined reference to `my_fast_crc32'
/home/sveta/src/mysql-5.1/extra/innochecksum.c:343: undefined reference to `my_fast_crc32'
innochecksum.o: In function `main':
/home/sveta/src/mysql-5.1/extra/innochecksum.c:737: undefined reference to `my_init_cpu_optimizations'
collect2: ld returned 1 exit status
make[3]: *** [innochecksum] Error 1
make[3]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/sveta/src/mysql-5.1/extra'
make: *** [all-recursive] Error 1

Reduced patch to add -b option to innochecksum.

Attachment: innochecksum.patch (application/octet-stream, text), 10.77 KiB.

I'm sorry for the difficulty. We actively develop on our branch and it can be difficult to dis-entangle the diffs we make to the code. I've attached a reduced patch for innochecksum which should allow you to reproduce now.

Thank you for the feedback.

Finally verified as described.

Although I should make few more changes:

Line 302: //  ulint oldcsum, oldcsumfield, csum, csumfield, logseq, logseqfield; /* ulints for checksum storage */

Line 456: if ((ulint)bytes != page_size)

This bug is not reproducable in MySQL-5.5 and later versions.

On what do you base your statement that this does not reproduce? One need only examine the function update_create_info_from_table in the current 5.6.13 source code on launchpad to see that it does not copy the key_block_size over to the create_info and, thus, the bug does still exist.

Justin,

we re-run this test with version 5.1 and 5.5. And here are results:

5.1:

=====mysql-5.1=====
=====bug66738=====
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t1(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
CREATE TABLE t2 LIKE t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

[sveta@delly mysql-5.5]$ ./extra/innochecksum ../mysql-5.1/mysql-test/var/mysqld.1/data/test/t1.ibd 
Table is compressed
Key block size is 4K
[sveta@delly mysql-5.5]$ ./extra/innochecksum ../mysql-5.1/mysql-test/var/mysqld.1/data/test/t2.ibd 
Table is compressed
Key block size is 8K

Bug is still repeatable.

5.5:

=====mysql-5.5=====
=====bug66738=====
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t1(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
CREATE TABLE t2 LIKE t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

[sveta@delly mysql-5.5]$ ./extra/innochecksum mysql-test/var/mysqld.1/data/test/t1.ibd 
Table is compressed
Key block size is 4K
[sveta@delly mysql-5.5]$ ./extra/innochecksum mysql-test/var/mysqld.1/data/test/t2.ibd 
Table is compressed
Key block size is 4K

Bug is not repeatable. 

So bug is fixed in 5.5 and up, and we won't fix it in version 5.1 which is near to its EOL (see p.18 of http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf).