| Bug #96516 | the mysql.gtid_executed table updates in real time when binlog is enable | ||
|---|---|---|---|
| Submitted: | 13 Aug 2019 9:41 | Modified: | 6 Nov 2019 21:47 |
| Reporter: | Jinghua Lin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 | OS: | CentOS |
| Assigned to: | CPU Architecture: | x86 | |
[13 Aug 2019 9:41]
Jinghua Lin
[16 Aug 2019 12:25]
MySQL Verification Team
Hello John, Thank you for the report. I'm not seeing any differences on how it behaves in 8.0.16 and 8.0.17 at least while following - https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html Could you please share exact scenario where you are observing the change, along with exact configuration of the server? Thank you. regards, Umesh
[19 Aug 2019 7:13]
Jinghua Lin
This is my configuration: [mysqld] server-id=1 port=3306 gtid_mode=ON enforce_gtid_consistency=ON mysqlx=off datadir=/opt/mysql/data/3306 log_bin=/opt/mysql/log/binlog/3306/mysql-bin log_error=/opt/mysql/data/3306/mysql-error.log tmpdir=/opt/mysql/tmp/3306 socket=/opt/mysql/tmp/3306/mysql.sock innodb_log_group_home_dir=/opt/mysql/log/redolog/3306 In 8.0.17: mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000002 | 659 | | | 64ac7514-c249-11e9-9454-02000aba413b:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 64ac7514-c249-11e9-9454-02000aba413b | 1 | 1 | | 64ac7514-c249-11e9-9454-02000aba413b | 2 | 2 | +--------------------------------------+----------------+--------------+ 2 rows in set (0.00 sec) In 8.0.16: mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000002 | 662 | | | daf70aaa-c24a-11e9-b5bf-02000aba413b:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.gtid_executed; Empty set (0.00 sec) The documentation says that when binary logging is enabled, the mysql.gtid_executed table does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable. We can see that 8.0.16 is the same as described in the documentation, but 8.0.17 is different. I want to know what makes it different. Is that a bug?
[21 Aug 2019 6:20]
MySQL Verification Team
Hello John, Thank you for the feedback and requested details. This doesn't look like a code bug to me but documentation request to update the statement "When binary logging is enabled, the mysql.gtid_executed table does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable". For now, keeping the bug category as is. regards, Umesh
[21 Aug 2019 6:20]
MySQL Verification Team
- 8.0.16 rm -rf 96516/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96516 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96516 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --gtid_mode=ON --server-id=1 --enforce_gtid_consistency=ON --log-error=$PWD/96516/log.err --mysqlx=off --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 155 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> select @@GLOBAL.gtid_executed; +------------------------+ | @@GLOBAL.gtid_executed | +------------------------+ | | +------------------------+ 1 row in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> show master status; +---------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+----------------------------------------+ | binlog.000001 | 340 | | | d1165c2b-c3da-11e9-9fae-0010e05f3e06:1 | +---------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.gtid_executed; Empty set (0.00 sec) mysql> select @@GLOBAL.gtid_executed; +----------------------------------------+ | @@GLOBAL.gtid_executed | +----------------------------------------+ | d1165c2b-c3da-11e9-9fae-0010e05f3e06:1 | +----------------------------------------+ 1 row in set (0.00 sec) mysql> - 8.0.17 bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 155 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+----------------------------------------+ | binlog.000001 | 340 | | | ee8ffd76-c3d9-11e9-811b-0010e05f3e06:1 | +---------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | ee8ffd76-c3d9-11e9-811b-0010e05f3e06 | 1 | 1 | +--------------------------------------+----------------+--------------+ 1 row in set (0.00 sec) mysql> select @@GLOBAL.gtid_executed; +----------------------------------------+ | @@GLOBAL.gtid_executed | +----------------------------------------+ | 1baa61f4-c3da-11e9-8eec-0010e05f3e06:1 | +----------------------------------------+ 1 row in set (0.00 sec) -- 5.7 (same as 8.0.16) rm -rf 96516/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96516 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96516 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-bin --gtid_mode=ON --server-id=1 --enforce_gtid_consistency=ON --log-error=$PWD/96516/log.err --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | hod03-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> select @@GLOBAL.gtid_executed; +------------------------+ | @@GLOBAL.gtid_executed | +------------------------+ | | +------------------------+ 1 row in set (0.00 sec) mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | hod03-bin.000001 | 313 | | | 27647a4b-c3db-11e9-a711-0010e05f3e06:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.gtid_executed; Empty set (0.00 sec) mysql> select @@GLOBAL.gtid_executed; +----------------------------------------+ | @@GLOBAL.gtid_executed | +----------------------------------------+ | 27647a4b-c3db-11e9-a711-0010e05f3e06:1 | +----------------------------------------+ 1 row in set (0.00 sec)
[21 Aug 2019 7:57]
Jinghua Lin
OK, you mean maybe need to update the documentation? But I do not see any change about this in the latest release notes. I truly want to know what change the processing logic. Looking forward to your answer. Thank you!
[28 Aug 2019 14:22]
Erlend Dahl
Posted by developer - Pedro Figueiredo Hi! This change in behaviour was introduced by the work around Clone. The introduction of clone allow for us to store the GTID in InnoDB's undo-log and update the `mysql.gtid_executed` table upon commit - even with the binlog on. This work is no only important for Clone - it ensures GTID are always correctly recovered even if the binlog get corrupted, somehow - but is also an important step in having binlogless and GTID-enabled slaves that are crash-safe. Documentation should be updated with the proper changes. Thank you all for your time.
[29 Aug 2019 2:25]
Jinghua Lin
Oh, thank you for your answer. I have one more question about that. In 8.0.17, it didn't compress the mysql.gtid_executed table after i restarted the mysql. Is that normal?
[6 Nov 2019 21:47]
Margaret Fisher
Posted by developer: Thanks very much for pointing this out. I have updated the explanation to the version shown below. I don't know the situation with the compression, sorry. I have made a note to check with the replication team, but if you would prefer to raise another bug please go ahead. New explanation: GTIDs are stored in the mysql.gtid_executed table only when gtid_mode is ON or ON_PERMISSIVE. If binary logging is disabled (log_bin is OFF), or if log_slave_updates is disabled, the server stores the GTID belonging to each transaction together with the transaction in the mysql.gtid_executed table at transaction commit time. In addition, the table is compressed periodically at a user-configurable rate, as described in mysql.gtid_executed Table Compression. If binary logging is enabled (log_bin is ON), from MySQL 8.0.17, the server updates the mysql.gtid_executed table in the same way as when binary logging or slave update logging is disabled, storing the GTID for each transaction at transaction commit time. However, in releases before MySQL 8.0.17, the server only updates the mysql.gtid_executed table when the binary log is rotated or the server is shut down. At these times, the server writes GTIDs for all transactions that were written into the previous binary log into the mysql.gtid_executed table. This situation applies on a replication master prior to MySQL 8.0.17, or on a replication slave prior to MySQL 8.0.17 where binary logging is enabled, and it has the following consequences: - In the event of the server stopping unexpectedly, the set of GTIDs from the current binary log file is not saved in the mysql.gtid_executed table. These GTIDs are added to the table from the binary log file during recovery so that replication can continue. The exception to this is if you disable binary logging when the server is restarted (using --skip-log-bin or --disable-log-bin). In that case, the server cannot access the binary log file to recover the GTIDs, so replication cannot be started. - The mysql.gtid_executed table does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable. In releases before MySQL 8.0.17, always use @@GLOBAL.gtid_executed, which is updated after every commit, to represent the GTID state for the MySQL server, instead of querying the mysql.gtid_executed table.
[12 Nov 2019 12:43]
Margaret Fisher
Posted by developer: Correction: updated to state the new behavior is with InnoDB as the storage engine only.
[14 Nov 2019 9:45]
Margaret Fisher
Posted by developer: Development advise that the lack of compression at startup should be investigated as a bug, so I've raised Bug #30541799 MYSQL.GTID_EXECUTED TABLE NOT COMPRESSED ON STARTUP FROM 8.0.17 (MySQL Bug: 97631).
