Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.3, 10.3.14
Description
Problem found during RQG testing involving row based replication.
./mysql-test-run.pl --mem rpl_t_s_4 | tee prt
|
...
|
main.rpl_t_s_4 'innodb,mix' [ skipped ] Not ROW format
|
include/master-slave.inc
|
[connection master]
|
CREATE TABLE t1 (col1 INT, col2 INT, col_c CHAR(20), KEY idx2 (col_c)) ENGINE = InnoDB ;
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 1 , 1, '1');
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 2 , 2, '2');
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 3 , 3, '3');
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 4 , 4, '3333333333');
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 5 , 5, '3333333333');
|
UPDATE t1 SET col2 = 453 WHERE col1 IN (5,4);
|
UPDATE t1 SET col2 = 130 WHERE col1 IN (5,4);
|
UPDATE t1 SET col2 = 99 WHERE col1 IN (5,4);
|
UPDATE t1 SET col2 = 202 WHERE col1 IN (5,4);
|
UPDATE t1 SET col2 = 464 WHERE col1 IN (5,4);
|
UPDATE t1 SET col2 = 430 WHERE col1 IN (5,4);
|
INSERT INTO t1 (col1, col2, col_c) VALUES ( 6 , 6, '6');
|
ALTER TABLE t1 ADD UNIQUE KEY uidx2 ( col1, col_c );
|
UPDATE t1 SET col2 = 213 WHERE col1 = 5;
|
main.rpl_t_s_4 'innodb,row' [ fail ]
|
...
|
=== SHOW SLAVE STATUS ===
|
...
|
Slave_IO_Running Yes
|
Slave_SQL_Running No
|
...
|
Last_Errno 1062
|
Last_Error Could not execute Update_rows_v1 event on table test.t1; Duplicate entry '5-3333333333' for key 'uidx2', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.000001, end_log_pos 4228
|
|
|
Given the fact that using
|
ALTER TABLE t1 ADD UNIQUE KEY uidx2 ( col1, col_c ), ALGORITHM = COPY;
|
or
|
Engine = MyISAM instead of InnoDB
|
makes the problem disappear I guess its more an InnoDB than a replication bug.
|
|
|
10.3 09bd2138522787a4e0b015695c462903f4a9e728 2019-02-22
|
10.4 5b4d6595d26aaaf0bf8bb3c9171cf1da96306a7c 2019-02-21
|
Attachments
Issue Links
- relates to
-
MDEV-19103 [perf] #57 Preferred to chose index with auto_increment column when slave apply binlog
-
- Open
-
-
-
- Open
-
-
-
- Confirmed
-
Activity
It all looks the row-based formatting issue. When a query is reconstructed from the rows-event image it passes
UPDATE `test`.`t1`
SET
col1=5 /* INT meta=0 nullable=1 is_null=0 */,
col2=213 /* INT meta=0 nullable=1 is_null=0 */,
col_c='3333333333' /* STRING(20) meta=65044 nullable=1 is_null=0 */
WHERE
col1=5 /* INT meta=0 nullable=1 is_null=0 */ and
col2=430 /* INT meta=0 nullable=1 is_null=0 */ and
col_c='3333333333' /* STRING(20) meta=65044 nullable=1 is_null=0 */;
to OK.
I am investigating further.
Now it's confirmed to be RBR issue, and specifically a wrong index is chosen for locating the being updated record, which happens to be not unique.
More specifically there's an issue in Rows_log_event::find_key() that did not check whether the actual key value
is composed of any NULL. When that's not the case the unique key must be selected.
The problem does occur with LOCK=SHARED, but ALGORITHM=FORCE (rebuilding the table natively inside InnoDB) makes the problem disappear.
I do not understand why we would possibly get the duplicate key error for (col1=5,col_c='3333333333'). I do not see any update of col1. And why should the updates of col2 affect this at all? I suspect that this is a problem with replication, not with InnoDB.
Elkin, I would suggest you to check what gets passed to ha_innobase::write_row() and ha_innobase::update_row() on the slave. If you think that it is an InnoDB problem, then please provide some log of statements or preferrably a test case that does not involve replication.