Description:
1) Adding an auto-increment column and multiple key to the table, the slave can not find the record when update it.
2）Adding an auto-increment column and primary key to the table, the slave updated the wrong record. It is updated by the primary key. If slave_rows_search_algorithms is set to HASH SCAN, it also cannot find the record.
How to repeat:
case1: MTR test
--source include/master-slave.inc
--source include/have_binlog_format_row.inc
--connection master
--connect(conn_1,localhost,root,,)
--connect(conn_2,localhost,root,,)
--connection master
CREATE TABLE t1(id INT);
--source include/sync_slave_sql_with_master.inc
SELECT @@slave_rows_search_algorithms;
--connection master
--connection conn_1
START TRANSACTION;
INSERT INTO t1(id) VALUES(1);
--connection conn_2
START TRANSACTION;
INSERT INTO t1(id) VALUES(2);
COMMIT;
--connection conn_1
COMMIT;
--connection master
ALTER TABLE t1 ADD pid INT NOT NULL AUTO_INCREMENT, ADD KEY(pid);
#ALTER TABLE t1 ADD pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
--echo "master info1"
SELECT * FROM t1 ORDER BY id;
--source include/sync_slave_sql_with_master.inc
SELECT @@slave_rows_search_algorithms;
--echo "slave info1"
SELECT * FROM t1 ORDER BY id;
--connection master
--echo "master info2"
DELETE FROM t1 WHERE id=1;
SELECT * FROM t1 ORDER BY id;
--source include/sync_slave_sql_with_master.inc
--echo "slave info2"
SELECT * FROM t1 ORDER BY id;
--connection master
UPDATE t1 SET id=100 WHERE id=2;
SELECT * FROM t1 ORDER BY id;
--source include/sync_slave_sql_with_master.inc
--echo "slave info2"
SELECT * FROM t1 ORDER BY id;
--connection master
DROP TABLE t1;
--source include/rpl_end.inc
result:
mysqltest: At line 158: Error in sync_with_master.inc
In included file ./include/sync_slave_sql.inc: 159
included from ./include/sync_slave_sql_with_master.inc: 79
included from /sda/zhijun/80/mysql-8.0.26/mysql-test/t/implicit_01.test: 41
The result from queries just before the failure was:
SELECT * FROM mysql.slave_master_info;
Number_of_lines Master_log_name Master_log_pos Host User_name User_password Port Connect_retry Enabled_ssl Ssl_ca Ssl_capath Ssl_cert Ssl_cipher Ssl_key Ssl_verify_server_cert Heartbeat Bind Ignored_server_ids Uuid Retry_count Ssl_crl Ssl_crlpath Enabled_auto_position Channel_name Tls_version Public_key_path Get_public_key Network_namespace Master_compression_algorithm Master_zstd_compression_level Tls_ciphersuites Source_connection_auto_failover
**** mysql.gtid_executed on server_1 ****
SELECT * FROM mysql.gtid_executed;
source_uuid interval_start interval_end
rpl_topology= 1->2
rand_seed: '' _rand_state: ''
extra debug info if any: ''
rpl_topology=1->2
connection slave;
ERROR: sync_slave_sql.inc failed on connection 'slave'
ERROR: use_gtids='0'
ERROR: _saved_gtids=''
ERROR: _saved_file='master-bin.000001'
ERROR: _saved_pos='1397'
ERROR: _saved_channel_name=
ERROR: timeout='300'
ERROR: result='-2'
ERROR: error type: The slave SQL thread was not started, the slave's master information was not initialized, the arguments were incorrect, or an error occurred.
LAST_ERROR_MESSAGE Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 1366
