Bug #120198 Lost gtid after crash recovery

• Submitted: 1 Apr 7:45
• Modified: 16 Apr 14:19
• Reporter: yl deng
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[1 Apr 7:45] yl deng

Description:
As in MySQL 5.7, GTIDs may be lost after crash recovery. I have created a test case, and the result is as follows.

```
Mysqltest client output from logfile
----------- MYSQLTEST OUTPUT START -----------

include/gtid_utils.inc
CREATE TABLE t1(c1 int primary key, c2 int);
insert into t1 values(1, 1);
insert into t1 values(2, 2);
insert into t1 values(3, 3);
flush logs;
select GTID_COUNT(@@GLOBAL.GTID_EXECUTED);
GTID_COUNT(@@GLOBAL.GTID_EXECUTED)
24
select GTID_COUNT(@@GLOBAL.GTID_PURGED);
GTID_COUNT(@@GLOBAL.GTID_PURGED)
0
select interval_start, interval_end from mysql.gtid_executed;
interval_start  interval_end
1       24
select * from t1;
c1      c2
1       1
2       2
3       3
set session debug="+d,ib_trx_commit_crash_before_trx_commit_start";
insert into t1 values(4, 4);
[restart mysqld and crash before write prev_gtids to newest binlog]
# restart:
select * from t1;
c1      c2
1       1
2       2
3       3
4       4
select GTID_COUNT(@@GLOBAL.GTID_EXECUTED);
GTID_COUNT(@@GLOBAL.GTID_EXECUTED)
24
select GTID_COUNT(@@GLOBAL.GTID_PURGED);
GTID_COUNT(@@GLOBAL.GTID_PURGED)
24
select interval_start, interval_end from mysql.gtid_executed;
interval_start  interval_end
1       24
drop table t1;
include/gtid_utils_end.inc

```

How to repeat:
1.Insert a debug crash point before init_gtid_sets, and rebuild with -DWITH_DEBUG=1.

```
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index adcaf2e..eb51507 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -7928,6 +7928,8 @@ int mysqld_main(int argc, char **argv)
     Gtid_set gtids_in_binlog(global_sid_map, global_sid_lock);
     Gtid_set gtids_in_binlog_not_in_table(global_sid_map, global_sid_lock);

+    DBUG_EXECUTE_IF("crash_before_write_prev_gtids", DBUG_SUICIDE(););
+
     if (mysql_bin_log.init_gtid_sets(
             &gtids_in_binlog, &purged_gtids_from_binlog,
             opt_source_verify_checksum, true /*true=need lock*/,
```
2. create mysql-server\mysql-test\suite\binlog\t\lost_gtid.test
--source include/have_log_bin.inc
--source include/have_debug.inc
--source include/gtid_utils.inc

let $DDIR= `select @@datadir`;
--let $_server_id= `SELECT @@server_id`
--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect

CREATE TABLE t1(c1 int primary key, c2 int);
insert into t1 values(1, 1);
insert into t1 values(2, 2);
insert into t1 values(3, 3);
flush logs;
select GTID_COUNT(@@GLOBAL.GTID_EXECUTED);
select GTID_COUNT(@@GLOBAL.GTID_PURGED);
select interval_start, interval_end from mysql.gtid_executed;
select * from t1;

set session debug="+d,ib_trx_commit_crash_before_trx_commit_start";
--source include/expect_crash.inc
--error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
insert into t1 values(4, 4);

--echo [restart mysqld and crash before write prev_gtids to newest binlog]
--exec echo "wait" > $_expect_file_name
--error 1,137
--exec $MYSQLD_CMD --debug=d,crash_before_write_prev_gtids --gtid_mode=on --enforce_gtid_consistency=on --datadir=$DDIR >$MYSQLTEST_VARDIR/log/mysqld_out_err.log 2>&1
--source include/wait_until_disconnected.inc

--let $restart_parameters = restart:
--source include/start_mysqld.inc

select * from t1;
select GTID_COUNT(@@GLOBAL.GTID_EXECUTED);
select GTID_COUNT(@@GLOBAL.GTID_PURGED);
select interval_start, interval_end from mysql.gtid_executed;

#cleanup
drop table t1;

--source include/gtid_utils_end.inc

```
3. ./mtr --mem binlog.gtid_lost --mysqld=--gtid_mode=on --mysqld=--enforce_gtid_consistency

```

[1 Apr 12:10] Jean-François Gagné

> As in MySQL 5.7 [...]

The 5.7 bug is Bug#As in MySQL 5.7.

[16 Apr 14:30] Jean-François Gagné

My previous comment had a bad cut-and paste, below is the comment without mistake.

> As in MySQL 5.7 [...]

The 5.7 bug is Bug#120177.

Seeing that this is now Verified (at least in 8.0.45), I am curious if 8.4 and 9.x are affected.