[MXS-4189] Database can't purge binary logs Created: 2022-06-17  Updated: 2022-07-05  Resolved: 2022-07-05

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: 6.4.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Justin Bennett Assignee: markus makela
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Physical on prem and virtualised, MariaDB 3 node clusters

Attachments: Text File strace_binlog_purge_fails.txt     Text File strace_binlog_purge_works.txt    

 Description   

Hi

We have 2 mariadb 3 node clusters behind maxscale. We are seeing an issue where binary logs aren't removed by PURGE BINARY LOGS TO. This manifests as the PURGE command returning without error but the log is not removed, and expire_logs_days is being ignored (or maybe is being actioned but the logs are removed). Example: 

MariaDB [(none)]> show master logs;
 
+------------------+------------+
 
| Log_name         | File_size  |
 
+------------------+------------+
 
| mysql-bin.003024 | 1073742172 |
 
| mysql-bin.003025 | 1073763450 |
 
| mysql-bin.003026 | 1073752714 |
 
<snip>
 
+------------------+------------+
 
61 rows in set (0.000 sec)
 
 
 
MariaDB [(none)]> purge master logs to 'mysql-bin.003025';
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [(none)]> show master logs;
 
+------------------+------------+
 
| Log_name         | File_size  |
 
+------------------+------------+
 
| mysql-bin.003024 | 1073742172 |
 
| mysql-bin.003025 | 1073763450 |
 
| mysql-bin.003026 | 1073752714 |
 
<snip>
 
+------------------+------------+
 
61 rows in set (0.000 sec)
 
 
 
MariaDB [(none)]>

We haven't made any manual changes to the binlog index file or anything like that. Also, so far we've only noted this behaviour on the node designated as the master by maxscale. No errors are recorded in the database log.

Thanks
Justin.

 Comments   
Comment by Justin Bennett [ 2022-06-17 ]

We originally saw this issue on 10.5.12 but also appears in 10.5.15.

Comment by Justin Bennett [ 2022-06-17 ]

See attached straces for 2 nodes in the same cluster, one from a node where PURGE BINARY LOGS TO works, and one where it doesn't work.

Comment by markus makela [ 2022-07-05 ]

This is expected behavior, SHOW BINARY LOGS is a read-only statement and it's routed to any server capable of processing reads:

2022-07-05 10:06:49   info   : (1) (RW-Split-Router); > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 21, type: QUERY_TYPE_READ, stmt: show binary logs 
2022-07-05 10:06:49   info   : (1) [readwritesplit] (RW-Split-Router); Route query to slave: server3 <
 
2022-07-05 10:06:49   info   : (1) [readwritesplit] (RW-Split-Router); Reply complete from 'server3' (Resultset: 11 rows)

If you want to force the query to be executed on the master, you can do it inside a transaction.

Generated at Thu Feb 08 04:26:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.