Description:
The value of parameters set using SET PERSIST is not being applied to the thread/sql/replica_{io,sql,worker} threads after restart.
Specifically, after setting SET PERSIST binlog_transaction_compression=ON and restarting, it is expected that binlog_transaction_compression=ON would be enabled across all threads. However, in practice, it has been observed that the setting is OFF in certain threads, despite the global value being ON.
The parsing of some parts of SET PERSIST (attributes with sys_var::PARSE_EARLY) occurs relatively early in the MySQL startup sequence, around line 7340: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L7338-L7341
Parameters that are not marked with PARSE_EARLY are parsed later, at line 8177: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L8176-L8181
The initialization of replication threads occurs between these two points, specifically starting at line 8142, which I believe is the reason for this behavior: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L8142-L8144
How to repeat:
$ sudo yum install -y --enablerepo=apj-platform_ext_rpms-oracle-release mysql-community-server mysql-shell
$ mysqlsh
// create instance on 3310 port
MySQL JS > dba.deploySandboxInstance(3310)
// create instance on 3311 port
MySQL JS > dba.deploySandboxInstance(3311)
// create a replicaset 'mysand'
MySQL JS > shell.connect('root@localhost:3310')
MySQL localhost:3310 ssl JS > dba.createReplicaSet('mysand')
// add 3311 instance as a replica
MySQL localhost:3310 ssl JS > rs = dba.getReplicaSet()
MySQL localhost:3310 ssl JS > rs.addInstance('root@127.0.0.1:3311')
...
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
...
// 3311 instance is a replica of 3310 instance
MySQL localhost:3310 ssl JS > rs.status()
{
"replicaSet": {
"name": "mysand",
"primary": "127.0.0.1:3310",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"127.0.0.1:3310": {
"address": "127.0.0.1:3310",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"127.0.0.1:3311": {
"address": "127.0.0.1:3311",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Waiting for an event from Coordinator",
"applierWorkerThreads": 4,
"receiverStatus": "ON",
"receiverThreadState": "Waiting for source to send event",
"replicationLag": null,
"replicationSsl": "ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2"
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
// SET PERSIST
MySQL localhost:3311 ssl SQL > SET PERSIST binlog_transaction_compression=ON;
Query OK, 0 rows affected (0.0011 sec)
MySQL localhost:3311 ssl SQL > SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME='binlog_transaction_compression';
+--------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------------+----------------+
| binlog_transaction_compression | ON |
+--------------------------------+----------------+
// restart
MySQL localhost:3311 ssl SQL > RESTART;
Query OK, 0 rows affected (0.0003 sec)
// global value is ON
MySQL localhost:3311 ssl SQL > show global variables like 'binlog_transaction_compression';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| binlog_transaction_compression | ON |
+--------------------------------+-------+
// session value of replica_{io,sql,worker1,worker2} is OFF
MySQL localhost:3311 ssl SQL > select v.THREAD_ID, t.NAME, v.VARIABLE_NAME, v.VARIABLE_VALUE from performance_schema.variables_by_thread v JOIN performance_schema.threads t ON v.THREAD_ID = t.THREAD_ID where v.VARIABLE_NAME='binlog_transaction_compression';
+-----------+---------------------------+--------------------------------+----------------+
| THREAD_ID | NAME | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------------------+--------------------------------+----------------+
| 42 | thread/sql/replica_io | binlog_transaction_compression | OFF |
| 43 | thread/sql/replica_sql | binlog_transaction_compression | OFF |
| 44 | thread/sql/replica_worker | binlog_transaction_compression | OFF |
| 47 | thread/sql/replica_worker | binlog_transaction_compression | OFF |
| 48 | thread/sql/replica_worker | binlog_transaction_compression | ON |
| 52 | thread/sql/replica_worker | binlog_transaction_compression | ON |
| 53 | thread/sql/one_connection | binlog_transaction_compression | ON |
+-----------+---------------------------+--------------------------------+----------------+
Suggested fix:
It is expected that the values set by SET PERSIST should also be applied to all replication threads.
I would appreciate it if a fix could be implemented to ensure this behavior.
Description: The value of parameters set using SET PERSIST is not being applied to the thread/sql/replica_{io,sql,worker} threads after restart. Specifically, after setting SET PERSIST binlog_transaction_compression=ON and restarting, it is expected that binlog_transaction_compression=ON would be enabled across all threads. However, in practice, it has been observed that the setting is OFF in certain threads, despite the global value being ON. The parsing of some parts of SET PERSIST (attributes with sys_var::PARSE_EARLY) occurs relatively early in the MySQL startup sequence, around line 7340: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L7338-L7341 Parameters that are not marked with PARSE_EARLY are parsed later, at line 8177: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L8176-L8181 The initialization of replication threads occurs between these two points, specifically starting at line 8142, which I believe is the reason for this behavior: https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/mysqld.cc#L8142-L8144 How to repeat: $ sudo yum install -y --enablerepo=apj-platform_ext_rpms-oracle-release mysql-community-server mysql-shell $ mysqlsh // create instance on 3310 port MySQL JS > dba.deploySandboxInstance(3310) // create instance on 3311 port MySQL JS > dba.deploySandboxInstance(3311) // create a replicaset 'mysand' MySQL JS > shell.connect('root@localhost:3310') MySQL localhost:3310 ssl JS > dba.createReplicaSet('mysand') // add 3311 instance as a replica MySQL localhost:3310 ssl JS > rs = dba.getReplicaSet() MySQL localhost:3310 ssl JS > rs.addInstance('root@127.0.0.1:3311') ... Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C ... // 3311 instance is a replica of 3310 instance MySQL localhost:3310 ssl JS > rs.status() { "replicaSet": { "name": "mysand", "primary": "127.0.0.1:3310", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "127.0.0.1:3311": { "address": "127.0.0.1:3311", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Waiting for an event from Coordinator", "applierWorkerThreads": 4, "receiverStatus": "ON", "receiverThreadState": "Waiting for source to send event", "replicationLag": null, "replicationSsl": "ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2" }, "status": "ONLINE" } }, "type": "ASYNC" } } // SET PERSIST MySQL localhost:3311 ssl SQL > SET PERSIST binlog_transaction_compression=ON; Query OK, 0 rows affected (0.0011 sec) MySQL localhost:3311 ssl SQL > SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME='binlog_transaction_compression'; +--------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------------------+----------------+ | binlog_transaction_compression | ON | +--------------------------------+----------------+ // restart MySQL localhost:3311 ssl SQL > RESTART; Query OK, 0 rows affected (0.0003 sec) // global value is ON MySQL localhost:3311 ssl SQL > show global variables like 'binlog_transaction_compression'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | binlog_transaction_compression | ON | +--------------------------------+-------+ // session value of replica_{io,sql,worker1,worker2} is OFF MySQL localhost:3311 ssl SQL > select v.THREAD_ID, t.NAME, v.VARIABLE_NAME, v.VARIABLE_VALUE from performance_schema.variables_by_thread v JOIN performance_schema.threads t ON v.THREAD_ID = t.THREAD_ID where v.VARIABLE_NAME='binlog_transaction_compression'; +-----------+---------------------------+--------------------------------+----------------+ | THREAD_ID | NAME | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------------------+--------------------------------+----------------+ | 42 | thread/sql/replica_io | binlog_transaction_compression | OFF | | 43 | thread/sql/replica_sql | binlog_transaction_compression | OFF | | 44 | thread/sql/replica_worker | binlog_transaction_compression | OFF | | 47 | thread/sql/replica_worker | binlog_transaction_compression | OFF | | 48 | thread/sql/replica_worker | binlog_transaction_compression | ON | | 52 | thread/sql/replica_worker | binlog_transaction_compression | ON | | 53 | thread/sql/one_connection | binlog_transaction_compression | ON | +-----------+---------------------------+--------------------------------+----------------+ Suggested fix: It is expected that the values set by SET PERSIST should also be applied to all replication threads. I would appreciate it if a fix could be implemented to ensure this behavior.