Bug #93809 lost a whole file's gtid
Submitted: 4 Jan 2:55 Modified: 15 Feb 11:30
Reporter: phoenix Zhang Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.24 OS:Any
Assigned to: Bogdan Kecman CPU Architecture:Any

[4 Jan 2:55] phoenix Zhang
Description:
We test MySQL failover by kill -9, after a long time test, when check the gtid,  the gtid in mysql-bin.000004 all lost.

In mysql-bin.000004, we can see the gtid below, the previous_gtids is 3817c69d-0f19-11e9-9200-0242c0a8300f:1-1180, and next gtid is 3817c69d-0f19-11e9-9200-0242c0a8300f:1181
mysql> show binlog events in 'mysql-bin.000004' limit 12;
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                 |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.24-log, Binlog ver: 4                                |
| mysql-bin.000004 |  123 | Previous_gtids |         1 |         194 | 3817c69d-0f19-11e9-9200-0242c0a8300f:1-1180                          |
| mysql-bin.000004 |  194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1181' |
| mysql-bin.000004 |  259 | Query          |         1 |         345 | BEGIN                                                                |
| mysql-bin.000004 |  345 | Table_map      |         1 |         415 | table_id: 111 (flashback_test.6)                                     |
| mysql-bin.000004 |  415 | Update_rows    |         1 |         905 | table_id: 111 flags: STMT_END_F                                      |
| mysql-bin.000004 |  905 | Xid            |         1 |         936 | COMMIT /* xid=8780 */                                                |
| mysql-bin.000004 |  936 | Gtid           |         1 |        1001 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1182' |
| mysql-bin.000004 | 1001 | Query          |         1 |        1087 | BEGIN                                                                |
| mysql-bin.000004 | 1087 | Table_map      |         1 |        1157 | table_id: 111 (flashback_test.6)                                     |
| mysql-bin.000004 | 1157 | Update_rows    |         1 |        2111 | table_id: 111 flags: STMT_END_F                                      |
| mysql-bin.000004 | 2111 | Xid            |         1 |        2142 | COMMIT /* xid=8788 */                                                |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------+
12 rows in set (0.00 sec)

In mysql-bin.000005, there no previous_gtids event at all, maybe this time, while writing this event, MySQL is kill again.  The file size is 123, equal the End_log_pos.
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000005 |   4 | Format_desc |         1 |         123 | Server ver: 5.7.24-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

At in mysql-bin.000006, the previous_gtids is empty, the next gtid is 3817c69d-0f19-11e9-9200-0242c0a8300f:1181 now, all gtids in mysql-bin.000004 is missing.
mysql> show binlog events in 'mysql-bin.000006' limit 10;
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name         | Pos   | Event_type     | Server_id | End_log_pos | Info                                                                 |
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000006 |     4 | Format_desc    |         1 |         123 | Server ver: 5.7.24-log, Binlog ver: 4                                |
| mysql-bin.000006 |   123 | Previous_gtids |         1 |         154 |                                                                      |
| mysql-bin.000006 |   154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= '3817c69d-0f19-11e9-9200-0242c0a8300f:1181' |
| mysql-bin.000006 |   219 | Query          |         1 |         305 | BEGIN                                                                |
| mysql-bin.000006 |   305 | Table_map      |         1 |         375 | table_id: 110 (flashback_test.6)                                     |
| mysql-bin.000006 |   375 | Update_rows    |         1 |        8445 | table_id: 110                                                        |
| mysql-bin.000006 |  8445 | Update_rows    |         1 |       15845 | table_id: 110                                                        |
| mysql-bin.000006 | 15845 | Update_rows    |         1 |       23991 | table_id: 110                                                        |
| mysql-bin.000006 | 23991 | Update_rows    |         1 |       31987 | table_id: 110                                                        |
| mysql-bin.000006 | 31987 | Update_rows    |         1 |       40175 | table_id: 110                                                        |
+------------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------+
10 rows in set (0.00 sec)

The my.cnf is:
[mysqld]
binlog-format=row
binlog-ignore-db=dbscale_tmp
enforce-gtid-consistency
general_log=1
gtid-mode=on
innodb_buffer_pool_size=32M
innodb_lock_wait_timeout=31560000
log-slave-updates
log_bin=mysql-bin
log_error=mysql-error
log_timestamps=system
lower_case_table_names=1
max_allowed_packet=16M
max_connections=1000
port=3306
skip-slave-start
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
user=mysql

How to repeat:
none
[15 Jan 11:30] Bogdan Kecman
Hi,

I'm not sure what are you doing exactly. This is a regular master/slave replication and not a group replication / innodb cluster setup right?

Can you give me the config from both servers?
What type of failover is happening here? 

all best
Bogdan
[16 Feb 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".