Bug #87333 gtid_executed_compression_period=1 do not work correctly

• Submitted: 7 Aug 2017 2:24
• Modified: 7 Aug 2017 8:43
• Reporter: ashe sun (OCA)
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: mysql-5.7.19
• OS: Linux
• CPU Architecture: Any
• Tags: gtid_compression

[7 Aug 2017 2:24] ashe sun

Description:
mysql> show global variables like '%gtid%'
    -> ;
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | b530069d-78df-11e7-832d-000c29c0110c:1-14 |
| gtid_executed_compression_period | 1                                         |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> #gtid_executed_compression_period == 1
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000006 |     1428 |              |                  | b530069d-78df-11e7-832d-000c29c0110c:1-14 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b530069d-78df-11e7-832d-000c29c0110c |              1 |           10 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b530069d-78df-11e7-832d-000c29c0110c |              1 |           14 |
+--------------------------------------+----------------+--------------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> insert into ashe.test select 1;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000007 |      499 |              |                  | b530069d-78df-11e7-832d-000c29c0110c:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select *from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b530069d-78df-11e7-832d-000c29c0110c |              1 |           14 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> insert into ashe.test select 1;                                                                                                                                                       
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select *from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| b530069d-78df-11e7-832d-000c29c0110c |              1 |           14 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000007 |      804 |              |                  | b530069d-78df-11e7-832d-000c29c0110c:1-16 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> show global variables like '%gtid%'
    -> ;
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | b530069d-78df-11e7-832d-000c29c0110c:1-16 |
| gtid_executed_compression_period | 1                                         |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

mysql> 
mysql> show global variables like '%read_ony%'
    -> \c
mysql> show global variables like '%read_only%'
    -> ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | OFF   |
| super_read_only  | OFF   |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.01 sec)

mysql> 

How to repeat:
same as Description

[7 Aug 2017 4:56] MySQL Verification Team

was it set dynamically or from my.cnf?  if dynamically, check

https://bugs.mysql.com/bug.php?id=86692
(dynamically changing gtid_executed_compression_period prevents gtid compression!)

[7 Aug 2017 8:43] ashe sun

sorry,
When binary logging is enabled, the value of
executed_gtids_compression_period is not used and the
mysql.gtid_executed table is compressed on each binary log rotation.

I have closed this.