Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.0.13
-
None
-
Windows, Unix
Description
Set up a replication setup with 10.0.13 as a slave (master doesn't really matter).
On master, run:
drop schema if exists repl1; |
create schema repl1; |
create table repl1.t1 (id1 bigint(20), id2 varchar(2048), primary key (id1)) engine=innodb; |
alter table repl1.t1 add index idx1 (id1, id2); |
This completes, provides a warning, and creates the index, albeit truncated:
mysql> alter table repl1.t1 add index idx1 (id1, id2); |
Query OK, 0 rows affected, 2 warnings (0.02 sec) |
Records: 0 Duplicates: 0 Warnings: 2
|
mysql> show warnings;
|
+---------+------+---------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+---------------------------------------------------------+ |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes | |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes | |
+---------+------+---------------------------------------------------------+ |
 |
mysql> show create table repl1.t1\G |
*************************** 1. row ***************************
|
Table: t1 |
Create Table: CREATE TABLE `t1` ( |
`id1` bigint(20) NOT NULL DEFAULT '0', |
`id2` varchar(2048) DEFAULT NULL, |
PRIMARY KEY (`id1`), |
KEY `idx1` (`id1`,`id2`(767)) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
However, this same warning is transformed to an error on the 10.0.13 slave, and breaks replication, and does not create the index.
mysql> show slave status\G
|
...
|
Last_Error: Error 'Specified key was too long; max key length is 767 bytes' on query. Default database: ''. Query: 'alter table repl1.t1 add index idx1 (id1, id2)' |
...
|
BUT, if you then run the exact same command on the slave directly, it completes, issues a warning, and creates the index, as on the master:
mysql> alter table repl1.t1 add index idx1 (id1, id2); |
Query OK, 0 rows affected, 1 warning (0.04 sec) |
Records: 0 Duplicates: 0 Warnings: 1
|
mysql> show warnings;
|
+---------+------+---------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+---------------------------------------------------------+ |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes | |
+---------+------+---------------------------------------------------------+ |
1 row in set (0.00 sec) |
 |
mysql> show create table repl1.t1\G |
*************************** 1. row ***************************
|
Table: t1 |
Create Table: CREATE TABLE `t1` ( |
`id1` bigint(20) NOT NULL DEFAULT '0', |
`id2` varchar(2048) DEFAULT NULL, |
PRIMARY KEY (`id1`), |
KEY `idx1` (`id1`,`id2`(767)) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec) |
MySQL Bugs DB has this similar/related bug:
http://bugs.mysql.com/bug.php?id=68453
However, there is a slight difference in behavior from MySQL and MariaDB. MySQL 5.6 does not allow this index to be created, unless you have innodb_large_prefix=ON and innodb_file_per_table=ON and barracuda with DYNAMIC or COMPRESSED format (non-defaults). MariaDB 10.0 allows these indexes to be created, just not when it comes from the slave thread.
The slave thread should act the same way as the command line thread. There should be no differences.
And note my sql_mode='' on all instances, both at the session and global level. Also, I do not have innodb_large_prefix ON at all, so that variable is not involved.