Description:
MySQL's rpl.rpl_insert_id test has started to fail sporadically after mysql-9.5.0 made the --gtid-mode=ON default.
The problem is caused by the fact that in case of statements on MyISAM tables (which are used in this test) update of @@gtid_executed variable happens before the tables are unlocked. As result there is a short window during which INSERT statement is considered executed by code which checks if replica caught up with source in include/rpl/sync_to_replica.inc script (as it relies on GTID state if GTIDs are on), while results of the INSERT are not yet visible to SELECT in other connections, provided that concurrent inserts are allowed for MyISAM (@@concurrent_insert = 1) (which is again the case for replica (but not source!) for this test).
This is not the case for case when we rely on binlog file name and position for tracking replica state as those are updated after tables are unlocked (and this is why we didn't see failures before GTID mode became default).
How to repeat:
Run MySQL's rpl.rpl_insert_id test many times on a busy server.
The test will fail sporadically due to difference in results looking like:
CURRENT_TEST: rpl.rpl_insert_id
--- mysql-test/suite/rpl/r/rpl_insert_id.result 2025-10-29 23:00:08.483307286 +0300
+++ mysql-server/bld_debug/mysql-test/var/log/rpl_insert_id.reject 2025-11-26 12:46:29.625227411 +0300
@@ -50,7 +49,6 @@
select * from t2 ORDER BY b;
b c
5 0
-6 11
#
# check if INSERT SELECT in auto_increment is well replicated (bug #490)
#
Alternatively, one can apply the following patch to mysql-9.5.0 source which simulates the busy server situation by introducing delay before unlocking the table used in test, build the server and observe failures in repeatable fashion:
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 5055efb947d..f7c48b13a0f 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4985,6 +4985,10 @@ finish:
lex->cleanup(true);
+ if (thd->slave_thread && thd->lex->sql_command == SQLCOM_INSERT && thd->lex->query_tables &&
+ strcmp(thd->lex->query_tables->table_name, "t2") == 0)
+ my_sleep(3000000);
+
/* Free tables */
THD_STAGE_INFO(thd, stage_closing_tables);
close_thread_tables(thd);
./mtr --mem rpl_insert_id
Logging: ../mysql-server/mysql-test/mysql-test-run.pl --mem rpl_insert_id
MySQL Version 9.5.0
Checking supported features
- Binaries are debug compiled
Using 'all' suites
Collecting tests
- Adding combinations for rpl
Removing old var directory
Creating var directory '...mysql-server/bld_debug/mysql-test/var'
- symlinking 'var' to '/dev/shm/var_auto_NbNV'
Installing system database
Using parallel: 1
ports_per_thread:30
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 25%] rpl.rpl_insert_id 'mix' [ fail ]
Test ended at 2025-11-26 10:46:30
CURRENT_TEST: rpl.rpl_insert_id
--- .../mysql-server/mysql-test/suite/rpl/r/rpl_insert_id.result 2025-10-29 23:00:08.483307286 +0300
+++ .../mysql-server/bld_debug/mysql-test/var/log/rpl_insert_id.reject 2025-11-26 12:46:29.625227411 +0300
@@ -29,7 +29,6 @@
4
select * from t2 ORDER BY b;
b c
-1 4
drop table t1;
drop table t2;
create table t1(a int auto_increment, key(a)) engine=myisam;
@@ -50,7 +49,6 @@
select * from t2 ORDER BY b;
b c
5 0
-6 11
#
# check if INSERT SELECT in auto_increment is well replicated (bug #490)
#
...
Suggested fix:
Turn the GTID mode OFF for particular test.
Or set @@concurrent_insert to 0 on replica in it.
Mention that GTIDs + MyISAM + @@concurrent_insert=1 can be problematic in documentation.
© 2025, Oracle Corporation and/or its affiliates