Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Cannot Reproduce
-
Affects Version/s: 10.0.13
-
Fix Version/s: N/A
-
Component/s: Replication
-
Labels:None
-
Environment: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Closing for now as "Cannot reproduce", please comment to re-open if you have additional information.
Hi Chris,
Could you please attach your cnf files for master and slave, and still specify which exactly version your master is running (even if it appears to be unimportant)?
I am not getting the problem you described:
Master:
MariaDB [test]> 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)Slave:
MariaDB [test]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: wheezy-64-bin.000001 Read_Master_Log_Pos: 904 Relay_Log_File: wheezy-64-relay-bin.000002 Relay_Log_Pos: 1195 Relay_Master_Log_File: wheezy-64-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 904 Relay_Log_Space: 1496 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)MariaDB [test]> 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)