Skip to content

stack/wendelin: set MariaDB handshake version on ProxySQL

Titouan Soulard requested to merge tsoulard/slapos:for-mr-snake into master

This is not to be merged as-is, more to discuss.

I had many issues (or say, the same issue many times) with replication on one of our projects: whenever the machine's hypervisor was unexpectedly rebooted, the replica would crash, saying something like:

2026-09-25 14:27:15 6 [ERROR] mysqld: Can't find record in 'message'
2026-09-25 14:27:15 6 [ERROR] Slave SQL: Could not execute Update_rows_v1 event on table erp5.message; Can't find record in 'message', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.001050, end_log_pos 0, Gtid 0-1890510201-34827567, Internal MariaDB error code: 1032

I am no good at debugging the exact cause, but it seems the replica is loosing the correct position in binlog and either re-executes commands or goes too much forward in the file.

In such cases, I would usually rebuild the backup, expecting that ok, maybe such issues can happen. But one day, the backup was not able to rebuild itself, it crashed always on:

2026-09-25 17:17:43 5 [Note] Slave I/O thread: connected to master[…],replication started in log 'FIRST' at position 4
2026-09-25 17:17:44 5 [Note] Slave I/O thread: Falling back to Using_Gtid=No because master does not support GTIDs
2026-09-25 17:19:17 6 [Note] Slave SQL thread exiting, replication stopped in log 'binlog.001050' at position 280141371
2026-09-25 17:19:17 5 [Note] Slave I/O thread exiting, read up to log 'binlog.001050', position 550335623

Falling back to Using_Gtid=No is weird. This means we use the old file/position mechanism which seems less reliable. I expect this issue cannot easily be noticed once the server is running because then either mode can run. @xavier_thompson pointed out that MariaDB checks for version to determine which mode to use, and I noticed that when connecting from the replica to the master, the output of bin/mariadb would give:

Server version: 5.5.30 MariaDB Server

Whereas SELECT VERSION(); returned 11.8.8-MariaDB-log as expected. After searching a bit, it seems to be that the first is version issued in handshake while second is the actual version returned by the server. On a normal MariaDB installation, I could not find a parameter to make these diverge, but our stack has ProxySQL, which does indeed rewrite the handshake value: https://proxysql.com/documentation/global-variables/mysql-variables#mysql-server_version to 5.5.30.

So my hypothesis now is (read this if the rest is too long):

  1. replica determines which of GTID or file position to use based on the handshake version (not checked);
  2. ProxySQL sets this version to 5.5.30, which is before support for GTID, so file position is used (checked);
  3. GTID is hard to match to file position, so we restart very early, earlier than available binlogs (7 days) (not checked);
  4. we cannot restore backup at anytime.

Please note that in some cases we can rebuild the backup (we managed twice, and it failed twice, at different dates obviously). I do not know why it works sometimes, and sometimes not. I expect MariaDB can guess a relatively recent file position, but not so recent that it sometimes fails.

Now I applied the changes suggested here on production master, and the backup was… back up (I had to rebootstrap, but if yours if running I expect useless). So I guess there is some truth in the process, but I am unable to give references for all my claims.

Also, if I am right, I expect using GTIDs would greatly help making MariaDB more resilient to power outages, upgrades and such, the old method is really old, and was kinda "deprecated".

Maybe I am quite unclear, so please do ask questions, I wrote this mainly for myself so I do not forget anything. What I would like is to get some feedbacks from @jerome to know:

  1. I saw that you upgraded ProxySQL recently (d6374275). Maybe this was related? Because more recent ProxySQL advertise a more recent version (8.x), although I expect this version is still not sufficient.
  2. Can you check your prod replicas and grep "Falling back to Using_Gtid=No" in MariaDB error file? I want to know if this issue is widespread. Maybe it is also too late to grep now, but I think you rebuilt one recently.
  3. Do you have an opinion globally? Did you ran into strange issues before that could be explained by what I describe?

I also let @xavier_thompson comment further if he feels like it, I know he had a sidelook at the issue. And of course anyone else is welcome to comment.

Ah, and before I finish, I know hardcoding some version is maybe bad, I am not so interested to discuss it further until we agree on the problem.

Merge request reports