Bug #90448 INSERT IGNORE not ignored on SQL thread
Submitted: 16 Apr 12:09 Modified: 20 Apr 5:46
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.21 OS:Any
Assigned to: CPU Architecture:Any

[16 Apr 12:09] Simon Mudd
This is with SBR, so binlog_format    = STATEMENT

A table is defined as:

running 5.7.21

root@myhost [db]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `col1` varchar(50) NOT NULL,
  `col2` varchar(50) NOT NULL,
  `col3` varchar(50) DEFAULT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` varchar(50) DEFAULT NULL,
  `col6` varchar(50) DEFAULT NULL,
  `col7` text CHARACTER SET utf8mb4,
  `col8` smallint(6) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `last_changed` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`col1`,`col2`,`last_changed`),
  KEY `created` (`created`),
  KEY `last_changed` (`last_changed`),
  KEY `col5` (`col5`)
/*!50100 PARTITION BY RANGE (last_changed)
(PARTITION day_20180131 VALUES LESS THAN (1517439600) ENGINE = InnoDB,
 PARTITION day_20180201 VALUES LESS THAN (1517526000) ENGINE = InnoDB,
 PARTITION day_20180202 VALUES LESS THAN (1517612400) ENGINE = InnoDB,
 PARTITION day_20180403 VALUES LESS THAN (1522792800) ENGINE = InnoDB,
 PARTITION day_20180404 VALUES LESS THAN (1522879200) ENGINE = InnoDB) */
1 row in set (0.02 sec)

by mistake partitions were not added after 2018-04-04.

Data was inserted successfully on the with a date out of bounds of the partition, and this succeeded on the upstream master but replication breaks on the downstream slave.


    1567 | Error 'Incorrect partition name' on query. Default database: 'db'. Query: 'INSERT IGNORE INTO t1 (col2,col5,col1,col7,last_changed,created,col4,col3,col6) VALUES
('xxxxxxxxxxxxxxx','1521130048021_485871','1521130048021_485871_r_000000_0','xxxxxxxxxxxxxxx','1523867555','1523867549','1521130048021_485871_r_000000','xxxxxxxxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),('xxxxxxxxxxxxxxx','1521130048021_485871','1521130048021_485871_m_000000_0','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx','1523867527','1523867519','1521130048021_485871_m_000000','xxxxxxxxx','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),('xxxxxxxxxxxxxxx','1521130048021_485871','1521130048021_485871_m_000001_0','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx','1523867525','1523867519','1521130048021_485871_m_000001','SUCCEEDED','xxxxxxxx.... [ output truncated by show slave status ]

Expected behaviour is given the INSERT IGNORE ... that the statement is accepted (despite the partition not existing) but this doesn't happen.

How to repeat:
see above.

Suggested fix:
If the statement is accepted and any errors on the master (which had the same table structure ) then it should also be accepted on the slave.
[18 Apr 16:03] Simon Mudd
One comment: I had 1024 partitions. not sure if that's relevant. At the time of creating this table it was the limit on the number of possible partitions. I'll upload the data requested.
[19 Apr 6:37] Umesh Shastry
Thank you, Simon for the requested details.I'll give it a try and come back to you on this. I agree with your last note and better to open the issue.

[19 Apr 12:43] Shane Bester
The error was non-fatal in that I could simply STOP SLAVE;START SLAVE; to recover.
1. Setup replication with SBR ..
2. On mysql connection paste this:  Note the \r means reconnect in client.

-- ----------
set sql_mode='STRICT_TRANS_TABLES';
use test;
drop table if exists t;
create table t(a int, primary key(a))engine=innodb 
partition by range(a) (
 partition p1 values less than (10),
 partition p2 values less than (20),
 partition p3 values less than (30)
alter table t truncate partition all;
delete from t ;
insert ignore into t(a) values (31) ;
-- ------

On client it says:
mysql> \r
Connection id:    6
Current database: test

mysql> insert ignore into t(a) values (31) ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

In error log on slave it says:

 [Note] Slave I/O thread for channel '': connected to master 'root@',replication started in log 'i7-bin.000001' at position 154
 [ERROR] Slave SQL for channel '': Error 'Incorrect partition name' on query. Default database: 'test'. Query: 'insert ignore into t(a) values (31)', Error_code: 1567
 [Warning] Slave: Incorrect partition name Error_code: 1567
 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'i7-bin.000001' position 1106
[19 Apr 12:50] Shane Bester
Bug also occurs on:
 Version: '5.7.23-debug'  socket: ''  port: 3307  Built on 2018/04/15
[20 Apr 5:37] Shane Bester
Does not appear to affect 8.0.11...
[20 Apr 5:46] Simon Mudd
Thanks for further follow up on this. The issue is not "serious" for me as the affected system is somewhat unusual. I do not usually have servers with such a high number of partitions.

Good to see that 8.0 is not affected.