mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 09:55:33 +01:00
284c72eacf
Problem:-
When mysql executes INSERT ON DUPLICATE KEY INSERT, the storage engine checks
if the inserted row would generate a duplicate key error. If yes, it returns
the existing row to mysql, mysql updates it and sends it back to the storage
engine.When the table has more than one unique or primary key, this statement
is sensitive to the order in which the storage engines checks the keys.
Depending on this order, the storage engine may determine different rows
to mysql, and hence mysql can update different rows.The order that the
storage engine checks keys is not deterministic. For example, InnoDB checks
keys in an order that depends on the order in which indexes were added to
the table. The first added index is checked first. So if master and slave
have added indexes in different orders, then slave may go out of sync.
Solution:-
Make INSERT...ON DUPLICATE KEY UPDATE unsafe while using stmt or mixed format
When there is more then one unique key.
Although there is two exception.
1. Auto Increment key is not counted because Innodb will get gap lock for
failed Insert and concurrent insert will get a next increment value. But if
user supplies auto inc value it can be unsafe.
2. Count only unique keys for which insertion is performed.
So this patch also addresses the bug id #72921
76 lines
2.3 KiB
Text
76 lines
2.3 KiB
Text
# Test to see if slave can detect certain known bugs present
|
|
# on the master, and appropriately decides to stop
|
|
# (assuming the bug is fixed in the slave, slave cannot of course
|
|
# imitate the bug, so it has to stop).
|
|
|
|
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
|
|
|
|
source include/have_debug.inc;
|
|
# because of pretend_version_50034_in_binlog the test can't run with checksum
|
|
source include/have_binlog_checksum_off.inc;
|
|
|
|
# Currently only statement-based-specific bugs are here
|
|
-- source include/have_binlog_format_statement.inc
|
|
|
|
source include/master-slave.inc;
|
|
|
|
# testcase with INSERT SELECT
|
|
connection master;
|
|
CREATE TABLE t1 (
|
|
id bigint(20) unsigned NOT NULL auto_increment,
|
|
field_1 int(10) unsigned NOT NULL,
|
|
field_2 varchar(255) NOT NULL,
|
|
field_3 varchar(255) NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY field_1 (field_1, field_2)
|
|
);
|
|
CREATE TABLE t2 (
|
|
field_a int(10) unsigned NOT NULL,
|
|
field_b varchar(255) NOT NULL,
|
|
field_c varchar(255) NOT NULL
|
|
);
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
|
|
sync_slave_with_master;
|
|
connection master;
|
|
# Updating table t1 based on values from table t2
|
|
INSERT INTO t1 (field_1, field_2, field_3)
|
|
SELECT t2.field_a, t2.field_b, t2.field_c
|
|
FROM t2
|
|
ON DUPLICATE KEY UPDATE
|
|
t1.field_3 = t2.field_c;
|
|
# Inserting new record into t2
|
|
INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
|
|
# Updating t1 again
|
|
INSERT INTO t1 (field_1, field_2, field_3)
|
|
SELECT t2.field_a, t2.field_b, t2.field_c
|
|
FROM t2
|
|
ON DUPLICATE KEY UPDATE
|
|
t1.field_3 = t2.field_c;
|
|
SELECT * FROM t1;
|
|
connection slave;
|
|
|
|
# show the error message
|
|
#1105 = ER_UNKNOWN_ERROR
|
|
--let $slave_sql_errno= 1105
|
|
--let $show_slave_sql_error= 1
|
|
--source include/wait_for_slave_sql_error.inc
|
|
|
|
# show that it was not replicated
|
|
SELECT * FROM t1;
|
|
connection master;
|
|
|
|
# clean up
|
|
drop table t1, t2;
|
|
connection slave;
|
|
drop table t1, t2;
|
|
# clear error message in sql thread
|
|
--source include/stop_slave_io.inc
|
|
RESET SLAVE;
|
|
|
|
# End of 5.0 tests
|
|
--let $rpl_only_running_threads= 1
|
|
--source include/rpl_end.inc
|