[MDEV-6645] Make Slave Thread and Regular Thread handle error/warning 1071 the same Created: 2014-08-26  Updated: 2014-10-14  Due: 2014-09-30  Resolved: 2014-10-14

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Chris Calender (Inactive) Assignee: Elena Stepanova
Resolution: Cannot Reproduce Votes: 0
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.

 Comments   
Comment by Elena Stepanova [ 2014-08-30 ]

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]> drop schema if exists repl1;
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> create schema repl1;
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [test]> create table repl1.t1 (id1 bigint(20), id2 varchar(2048), primary key (id1)) engine=innodb;
 
Query OK, 0 rows affected (1.11 sec)
 
 
 
MariaDB [test]> alter table repl1.t1 add index idx1 (id1, id2);
 
Query OK, 0 rows affected, 1 warning (0.93 sec)
 
Records: 0  Duplicates: 0  Warnings: 1
 
 
 
MariaDB [test]> 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)
 

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)
 

MariaDB [test]> show binlog events;
 
+----------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+
 
| Log_name             | Pos | Event_type        | Server_id | End_log_pos | Info                                                                                                   |
 
+----------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+
 
| wheezy-64-bin.000001 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.13-MariaDB-log, Binlog ver: 4                                                         |
 
| wheezy-64-bin.000001 | 248 | Gtid_list         |         1 |         273 | []                                                                                                     |
 
| wheezy-64-bin.000001 | 273 | Binlog_checkpoint |         1 |         316 | wheezy-64-bin.000001                                                                                   |
 
| wheezy-64-bin.000001 | 316 | Gtid              |         1 |         354 | GTID 0-1-1                                                                                             |
 
| wheezy-64-bin.000001 | 354 | Query             |         1 |         445 | drop schema if exists repl1                                                                            |
 
| wheezy-64-bin.000001 | 445 | Gtid              |         1 |         483 | GTID 0-1-2                                                                                             |
 
| wheezy-64-bin.000001 | 483 | Query             |         1 |         566 | create schema repl1                                                                                    |
 
| wheezy-64-bin.000001 | 566 | Gtid              |         1 |         604 | GTID 0-1-3                                                                                             |
 
| wheezy-64-bin.000001 | 604 | Query             |         1 |         757 | use `test`; create table repl1.t1 (id1 bigint(20), id2 varchar(2048), primary key (id1)) engine=innodb |
 
| wheezy-64-bin.000001 | 757 | Gtid              |         1 |         795 | GTID 0-1-4                                                                                             |
 
| wheezy-64-bin.000001 | 795 | Query             |         1 |         904 | use `test`; alter table repl1.t1 add index idx1 (id1, id2)                                             |
 
+----------------------+-----+-------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+
 
11 rows in set (0.00 sec)
 

MariaDB [test]> select @@innodb_large_prefix;
 
+-----------------------+
 
| @@innodb_large_prefix |
 
+-----------------------+
 
|                     0 |
 
+-----------------------+
 
 
 
MariaDB [test]> select @@sql_mode;
 
+------------+
 
| @@sql_mode |
 
+------------+
 
|            |
 
+------------+
 
 
 
MariaDB [test]> select @@version;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.0.13-MariaDB-log |
 
+---------------------+

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)
 

MariaDB [test]> select @@innodb_large_prefix;
 
+-----------------------+
 
| @@innodb_large_prefix |
 
+-----------------------+
 
|                     0 |
 
+-----------------------+
 
 
 
MariaDB [test]> select @@sql_mode;
 
+------------+
 
| @@sql_mode |
 
+------------+
 
|            |
 
+------------+
 
 
 
MariaDB [test]> select @@version;
 
+---------------------+
 
| @@version           |
 
+---------------------+
 
| 10.0.13-MariaDB-log |
 
+---------------------+
 
1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2014-10-14 ]

Closing for now as "Cannot reproduce", please comment to re-open if you have additional information.

Generated at Thu Feb 08 07:13:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.