| Bug #72028 | inconsistent key_block_size | ||
|---|---|---|---|
| Submitted: | 13 Mar 2014 5:28 | Modified: | 17 Feb 2016 11:55 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Mar 2014 7:49]
MySQL Verification Team
Hello Zhai, Thank you for the report and test case. Verified as described. Thanks, Umesh
[13 Mar 2014 9:04]
Hartmut Holzgraefe
As a table option this only changes the default for new indexes added after this point, not the key block size for already existing indexes. So it behaves the same way as ALTER TABLE t1 [DEFAULT] CHARACTER SET ...; Maybe it would be more clear if an optional DEFAULT keyword was also supported when setting KEY_BLOCK_SIZE on the table level as it is with CHARACTER SET ...
[13 Mar 2014 9:41]
zhai weixiang
Actually the table was rebuilded if executing 'ALTER TABLE .. key_block_size=..'. And all indexes will use the new key_block_size (no matter it's an old index or new one)
root@yf 05:39:48>show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`) KEY_BLOCK_SIZE=4,
KEY `b` (`b`) KEY_BLOCK_SIZE=4,
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=gbk ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1
1 row in set (0.00 sec)
Breakpoint 8, page_zip_compress (page_zip=0x2b5062825390, page=0x2b506cfa4000 "", index=0x2b58e4088018, level=6, mtr=0x2b58cfca5680)
at /u01/project/trunk/mysql-5.6.16/storage/innobase/page/page0zip.cc:1208
1208 {
(gdb) p index->name
$92 = 0x2b58e4088178 "b"
(gdb) p page_zip->ssize
$93 = 1
(gdb) c
Continuing.
Breakpoint 8, page_zip_compress (page_zip=0x2b5062825510, page=0x2b506cfa8000 "", index=0x2b58e4088788, level=6, mtr=0x2b58cfca5680)
at /u01/project/trunk/mysql-5.6.16/storage/innobase/page/page0zip.cc:1208
1208 {
(gdb) p index->name
$94 = 0x2b58e40888e8 "c"
(gdb) p page_zip->ssize
$95 = 1
(gdb) c
Continuing.
Breakpoint 8, page_zip_compress (page_zip=0x2b5062825210, page=0x2b506cfa0000 "", index=0x2b58e4066908, level=6, mtr=0x2b58cfca5460)
at /u01/project/trunk/mysql-5.6.16/storage/innobase/page/page0zip.cc:1208
1208 {
(gdb) p index->name
$96 = 0x2b58e4066a68 "PRIMARY"
(gdb) p page_zip->ssize
$97 = 1
(gdb) c
[13 Mar 2014 11:20]
zhai weixiang
It seems key_block_size have a different meaning when myisam is used... I am not familiar with myisam ... :(
[13 Mar 2014 12:22]
Hartmut Holzgraefe
"And all indexes will use the new key_block_size
(no matter it's an old index or new one)"
Where do you see that? Below i created the table with key block size 4 and one index, then changed table default block size to 8, created another index, then changed table default to 16. See how the two indexes show sizes of 4 and 8 in the end (the key_block_size defaults in effect at the time of their creation):
mysql> create table t1 (id int primary key, i int, key(i)) key_block_size=4;
Query OK, 0 rows affected (0.52 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
mysql> alter table t1 key_block_size=8;
Query OK, 0 rows affected (0.70 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4,
KEY `i` (`i`) KEY_BLOCK_SIZE=4
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
mysql> alter table t1 add column j int, add index (j);
Query OK, 0 rows affected (1.74 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4,
KEY `i` (`i`) KEY_BLOCK_SIZE=4,
KEY `j` (`j`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
mysql> alter table t1 key_block_size=16;
Query OK, 0 rows affected (0.63 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4,
KEY `i` (`i`) KEY_BLOCK_SIZE=4,
KEY `j` (`j`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=16
1 row in set (0.00 sec)
[13 Mar 2014 12:30]
Hartmut Holzgraefe
configurable key_block_size is a InnoDB-only feature according to https://dev.mysql.com/doc/refman/5.5/en/create-table.html With MyISAM a SHOW CREATE will always show key_block_size=1024 even if the table default is changed. Problems here: * no warnings are given on CREATE or ALTER when trying to set a different size on an index with MyISAM * even after setting a different value on an index the actual value remains 1024 * the 1024 default given by MyISAM doesn't really make sense as the value is supposed to have a unit of KB, so 1024 would equal 1MB? All of this is material for a different bug report (or several) though ...
[13 Mar 2014 13:06]
zhai weixiang
Hi, Hartmut Holzgraefe
you can query INNODB_CMP ( or INNODB_CMP_RESET) table to confirm it
root@test 08:54:52>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
KEY `a` (`a`) KEY_BLOCK_SIZE=4,
KEY `b` (`b`) KEY_BLOCK_SIZE=4,
KEY `c` (`c`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)
root@test 08:54:57>select * from information_schema.INNODB_CMP_RESET;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)
root@test 08:55:00>INSERT INTO t1 values (rand()*100000, rand()*10000, rand()*10000);\
Query OK, 1 row affected (0.00 sec)
root@test 08:55:14>INSERT INTO t1 select rand()*100000, rand()*10000, rand()*10000 from t1 a, t1 b;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
root@test 08:55:55>INSERT INTO t1 select rand()*100000, rand()*10000, rand()*10000 from t1 a, t1 b;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@test 08:55:59>INSERT INTO t1 select rand()*100000, rand()*10000, rand()*10000 from t1 a, t1 b;
Query OK, 36 rows affected (0.00 sec)
Records: 36 Duplicates: 0 Warnings: 0
root@test 08:56:00>INSERT INTO t1 select rand()*100000, rand()*10000, rand()*10000 from t1 a, t1 b,t1 c;
Query OK, 74088 rows affected (10.85 sec)
Records: 74088 Duplicates: 0 Warnings: 0
root@test 08:56:20>select * from information_schema.INNODB_CMP_RESET;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 21810 | 17829 | 7 | 3981 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
[13 Mar 2014 14:52]
Hartmut Holzgraefe
Ah, so it is a per-table setting after all, not per-index as the SHOW CREATE output implies? That would be a bug indeed ...
[13 Mar 2014 14:53]
Hartmut Holzgraefe
PS: regarding MyISAM see http://bugs.mysql.com/72033 now
[17 Feb 2016 11:55]
Erlend Dahl
Posted by developer: [6 Jan 2016 1:22] Abhishek Ar Ranjan This is a legacy issue of mysql. Any specific table option meant for a specific SE used in CREATE TABLE statement is stored. SHOW CREATE TABLE statement shows that options even when another SE is in use. For example, Connection option is used for Federated engine but when this option is used in CREATE TABLE statement, it shows in SHOW CREATE TABLE statement. This behaviour is not to change for now to support backward compatibility.
Description: root@test 01:20:56>create table t1 (a int primary key, b int, c int , key (b)); Query OK, 0 rows affected (0.02 sec) root@test 01:21:29>drop table t1; Query OK, 0 rows affected (0.00 sec) root@test 01:21:39>create table t1 (a int primary key, b int, c int , key (b)) key_block_size = 8; Query OK, 0 rows affected (0.02 sec) root@test 01:21:48>create table t2 like t1; Query OK, 0 rows affected (0.01 sec) root@test 01:21:55>alter table t2 key_block_size = 4; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 root@test 01:22:06>show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) KEY_BLOCK_SIZE=8, KEY `b` (`b`) KEY_BLOCK_SIZE=8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 KEY_BLOCK_SIZE=4 1 row in set (0.00 sec) so people may be confused which key_block_size was used exactly..... How to repeat: N/A Suggested fix: I don't know if this is the expected behavior.. :(