Description:
With InnoDB I'm getting a warning when using key_block_size=... in a situation where it isn't supported:
mysql> create table t1 (id int primary key, i int, key(i))
engine=innodb key_block_size=4;
Query OK, 0 rows affected, 2 warnings (0.85 sec)
mysql> show warnings;
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: ignoring
With MyISAM (or actually any non-InnoDB engine?) there is no such warning:
mysql> create table t2 (id int primary key, i int, key(i)) engine=myisam key_block_size=4;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t3 (id int primary key, i int, key(i)) engine=memory key_block_size=4;
Query OK, 0 rows affected (0.62 sec)
With engine=memory the table default and per-index values are preserved, but probably not used in any meaningful way.
With engine=MyISAM the per-index value defaults to 1024 (1MB?) instead:
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) KEY_BLOCK_SIZE=1024,
KEY `i` (`i`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
And even when trying to explicitly set a different value key_block_size will stay as 1024, without any error or warning:
mysql> alter table t2 add column j int, add key (j) key_block_size=4;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) KEY_BLOCK_SIZE=1024,
KEY `i` (`i`) KEY_BLOCK_SIZE=1024,
KEY `j` (`j`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
How to repeat:
See embedded statements and results above
Suggested fix:
Raise "key_block_size ignored" warnings when used with engines that don't actually support it ...
Description: With InnoDB I'm getting a warning when using key_block_size=... in a situation where it isn't supported: mysql> create table t1 (id int primary key, i int, key(i)) engine=innodb key_block_size=4; Query OK, 0 rows affected, 2 warnings (0.85 sec) mysql> show warnings; | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: ignoring With MyISAM (or actually any non-InnoDB engine?) there is no such warning: mysql> create table t2 (id int primary key, i int, key(i)) engine=myisam key_block_size=4; Query OK, 0 rows affected (0.05 sec) mysql> create table t3 (id int primary key, i int, key(i)) engine=memory key_block_size=4; Query OK, 0 rows affected (0.62 sec) With engine=memory the table default and per-index values are preserved, but probably not used in any meaningful way. With engine=MyISAM the per-index value defaults to 1024 (1MB?) instead: mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `i` int(11) DEFAULT NULL, PRIMARY KEY (`id`) KEY_BLOCK_SIZE=1024, KEY `i` (`i`) KEY_BLOCK_SIZE=1024 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 1 row in set (0.00 sec) And even when trying to explicitly set a different value key_block_size will stay as 1024, without any error or warning: mysql> alter table t2 add column j int, add key (j) key_block_size=4; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `i` int(11) DEFAULT NULL, `j` int(11) DEFAULT NULL, PRIMARY KEY (`id`) KEY_BLOCK_SIZE=1024, KEY `i` (`i`) KEY_BLOCK_SIZE=1024, KEY `j` (`j`) KEY_BLOCK_SIZE=1024 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4 1 row in set (0.00 sec) How to repeat: See embedded statements and results above Suggested fix: Raise "key_block_size ignored" warnings when used with engines that don't actually support it ...