Questions regarding node starts & table locks

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi everyone,

We’re in the process of transitioning from a standard MySQL setup to Percona XtraDB Cluster (PXC). Our testing has been mostly successful, but we’ve encountered a few issues that we could use some help with. We were on a call with Percona sales and an engineer regarding these questions, but unfortunately, they didn’t have the answers we were looking for and referred us to this forum.

  1. Delayed Node Startup

When a node VM is shut down for a short period (e.g., more than a few minutes) and then brought back online, the MySQL service on that node fails to start on VM boot. If we wait a bit longer (without making any changes), the service starts successfully. Is this behavior expected? Could there be background processes or checks happening that delay the node’s sucessful startup?

  1. Automated HA Recovery

Currently, when a node returns online, MySQL does not start automatically as indicated by the service status. Additionally, in ProxySQL, the node status remains “shunned” until the server list is manually reloaded. Is there a way to fully automate this process? We’re looking for a solution where HA recovery is completely automatic, including MySQL startup and HA status in ProxySQL.

  1. Table Locks Behavior

According to the PXC documentation, table locks should not function correctly or replicate across the cluster, particularly for unsupported queries like LOCK TABLES and UNLOCK TABLES in multi-source setups. However, in our testing, we’ve observed that these locks not only work but also replicate across nodes as expected.

This behavior is probably due to our cluster being in permissive mode. The documentation does not clearly specify this aspect, so we were initially unsure. Could someone confirm if our understanding is correct and if there could be any unexpected side-effects?

Here are the commands we used to test:

    SELECT GET_LOCK('" + temp_lock + "', 1);
    SELECT IS_FREE_LOCK('" + temp_lock + "');
    SELECT RELEASE_LOCK('" + temp_lock + "');

Any insights or advice on these issues would be greatly appreciated. Thanks in advance!

2

Hello @david.lesicnik,

We would need to see the MySQL error logs on this VM to understand why it fails to start. No, this is not expected behavior.

Check to make sure you’ve enabled the service: systemctl enable mysql as that should auto-restart MySQL on failure.

How did you test? Your SQL doesn’t explain much. Check the following:

node1> SELECT GET_LOCK("foobar", 10);   <-- leave this session open
node2> SELECT GET_LOCK("foobar", 10);   <-- in a new session; If this
   succeeds, it means you  have acquired the same lock on 2 different
   nodes, which also means that locks are not replicated. If this fails
   due to timeout, then locks are replicated.
3

Hello Matthew,

Thank you for your answer.

  1. The MySQL error log is empty, which has made troubleshooting challenging. From the systemd journal, I see the following relevant entries:

░░ The job identifier is 852 and the job result is failed.

Aug 01 08:25:45 perconaD2 systemd[1]: Starting Percona XtraDB Cluster...

░░ Subject: A start job for unit mysql.service has begun execution

░░ Defined-By: systemd

░░ Support: http://www.ubuntu.com/support

░░

░░ A start job for unit mysql.service has begun execution.

░░

░░ The job identifier is 937.

Aug 01 08:25:45 perconaD2 mysql-systemd[1500]: WARNING: Node has been rebooted, /mnt/mysql/grastate.dat: seqno = -1, mysql service has not been started aut>

Aug 01 08:25:45 perconaD2 systemd[1]: mysql.service: Control process exited, code=exited, status=1/FAILURE

░░ Subject: Unit process exited

░░ Defined-By: systemd

░░ Support: http://www.ubuntu.com/support

░░

░░ An ExecStartPre= process belonging to unit mysql.service has exited.

░░

░░ The process' exit code is 'exited' and its exit status is 1.

Aug 01 08:25:45 perconaD2 mysql-systemd[1533]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable

Aug 01 08:25:45 perconaD2 mysql-systemd[1533]: WARNING: mysql may be already dead

Aug 01 08:25:45 perconaD2 systemd[1]: mysql.service: Failed with result 'exit-code'.

░░ Subject: Unit failed

░░ Defined-By: systemd

░░ Support: http://www.ubuntu.com/support

░░

░░ The unit mysql.service has entered the 'failed' state with result 'exit-code'.

Aug 01 08:25:45 perconaD2 systemd[1]: Failed to start Percona XtraDB Cluster.

And here’s the CLI output, going from failed to running after a few minutes, with nothing done in between


mvd@perconaW:~$ sudo service mysql start

Job for mysql.service failed because the control process exited with error code.

See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.

mvd@perconaW:~$ sudo service mysql start

For today’s testing, I encountered additional issues getting this node up; it only started successfully on the fourth attempt, approximately 10 minutes after the VM started.

  1. The systemd journal for the mysql service includes the following warning after a reboot:

WARNING: Node has been rebooted, /mnt/mysql/grastate.dat: seqno = -1, mysql service has not been started automatically

This is what’s made me believe that this is intended behaviour.

4

A grastate.dat file with -1 as the seqno indicates either A) a currently running mysqld process, or B) a previously crashed mysqld process.

/var/log/mysqld/error.log is the default error log. You should have contents here. Please check your my.cnf file and ensure the error log file location is specified. Please include your full configuration file here.

5

I’ve also been testing a new XtraDB cluster and experienced David’s first bullet point. I found that mysql-systemd only checks the sequence number (and prevents service startup) for the first five minutes of server uptime.

This step fails on service start right after boot: ExecStartPre=/usr/bin/mysql-systemd check-grastate

From /usr/bin/mysql-systemd:

check_grastate_dat() {
  local seqno=-1
  local uptime=$(awk '{print int($1/60)}' /proc/uptime)
  if [ $uptime -lt 5 ]; then
    if [ -f $grastate_loc ]; then
      seqno=$(grep 'seqno:' $grastate_loc | cut -d: -f2 | tr -d ' ')
      if [ $seqno -eq -1 ]; then
        log_warning_msg "Node has been rebooted, $grastate_loc: seqno = $seqno, mysql service has not been started automatically"
        exit 1
      fi
    else
      log_failure_msg "$grastate_loc is missing after reboot, mysql service has not been started automatically"
      exit 1
    fi
  fi
}

This seems to conflict with how pc.recovery should work. If pc.recovery is enabled, shouldn’t the server be allowed to start regardless of the sequence number so that it can negotiate with the other nodes?

6

So, a VM crashing (without a normal mysql service shutdown) would produce -1, I assume? So this means that in case of a VM crash, the process will never autostart?

After turning the mysql service off normally, then shutting the VM down, the service comes back normally after a start up, so it indeed only seems to happen when the service has an unclean shutdown.

The file is in /var/log/mysql/error.log for my setup (default Ubuntu), but when this issue pops up, it logs nothing into the file.

Thanks,
David

7

Correct. If the server isn’t shutdown correctly, /mnt/mysql/grastate.dat will contain seqno = -1.

When the service tries to restart, /usr/bin/mysql-systemd check-grastate runs. If the VM or server has been running for less than 5 minutes it will check seqno and prevent startup if it’s -1, or allow startup if it’s not -1. If the server has been up for more than 5 minutes, it will not check seqno and will start regardless. If this step prevents startup, you will not see any logging in in the mysql error log. I saw the information about check-grastate in journalctl and systemctl.

As a workaround, I modified the mysql service config (/lib/systemd/system/mysql.service) to Restart=always and RestartPreventExitStatus=SIGTERM SIGINT. This will allow the service to retry startup over and over. It will continue to fail for those first 5 minutes, but it will eventually start up.

This isn’t ideal, since it takes 5 extra minutes to come back online, but it’s better than requiring manual intervention. I was hoping someone might chime in with whether or not this is a bad idea.

Alternatively, you could remove the check-grastate step from the service, avoiding the 5-minute delay entirely. I just haven’t been bold enough to do this, yet.