| Bug #90448 | INSERT IGNORE not ignored on SQL thread | ||
|---|---|---|---|
| Submitted: | 16 Apr 2018 12:09 | Modified: | 20 Apr 2018 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 | |
[18 Apr 2018 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 2018 6:37]
MySQL Verification Team
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. Regards, Umesh
[19 Apr 2018 12:43]
MySQL Verification Team
The error was non-fatal in that I could simply STOP SLAVE;START SLAVE; to recover. -------- TESTCASE -------- 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 ; \r insert ignore into t(a) values (31) ; -- ------ On client it says: <cut> 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@127.0.0.1:3306',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 2018 12:50]
MySQL Verification Team
Bug also occurs on: Version: '5.7.23-debug' socket: '' port: 3307 Built on 2018/04/15
[20 Apr 2018 5:37]
MySQL Verification Team
Does not appear to affect 8.0.11...
[20 Apr 2018 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.
Description: 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`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 /*!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. SHOW SLAVE STATUS shows: 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.