Bug #105310 slave stops with HA_ERR_KEY_NOT_FOUND when update the auto_increment column
Submitted: 25 Oct 2021 2:48 Modified: 25 Oct 2021 13:38
Reporter: zhijun long Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.*/8.0.*, 5.7.36, 8.0.27 OS:Any
Assigned to: CPU Architecture:Any

[25 Oct 2021 2:48] zhijun long
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
[25 Oct 2021 13:38] MySQL Verification Team
Hello zhijun long,

Thank you for the report and test case.

regards,
Umesh