How to Debug MariaDB Galera

Connect to the DB

Start a shell in one of the Galera cluster Pods and use the mysql client to connect to the DB.

kubectl exec -n $INSTANCE_ID mariadb-2 \
    -it -c mariadb-galera \
    -- bash -c "mysql -uroot --password=\$MARIADB_ROOT_PASSWORD"

When connected to the Galera cluster these SQL commands may be useful to debug what’s wrong: Using Status Variables.

Show MariaDB logs

To see the logs of a single node:

kubectl -n $INSTANCE_ID logs -c mariadb-galera mariadb-0 -f

To see the logs of all nodes:

kubectl -n $INSTANCE_ID logs -c mariadb-galera \
    -l app.kubernetes.io/name=mariadb-galera \
    --prefix -f

Crossplane can’t reconsilidate users or grants

It might happen that crossplane can’t reconcile users or grants on some clusters. The logs of the mariadb pod will log something like this:

2022-12-15 15:52:14 473 [Warning] WSREP: Ignoring error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'GRANT ALL ON `49d8e185-8cc0-413e-9d28-2a3bab081300`.* TO 'c89343c6-6367-4a89-9de9-1bfd72643d91'@'%'', Error_code: 1133

This is most likely caused by a bug in MariaDB and the workaround is to increase the table_definition_cache of the affected instance.

kubectl exec mariadb-0 -c mariadb-galera -it -- bash

mysql -u root -p$MARIADB_ROOT_PASSWORD
SET GLOBAL table_definition_cache=4000

Determine the cluster status

Put the following in a file called mariadb_state in your $PATH:

#!/usr/bin/env bash

query() {
  echo -e "\n===> \033[0;33mmariadb-$1\033[0m"
  CMD='mysql -uroot --password="$MARIADB_ROOT_PASSWORD"'
  QUERY='SHOW STATUS WHERE Variable_name IN ("wsrep_cluster_size", "wsrep_cluster_status", "wsrep_connected", "wsrep_evs_state", "wsrep_local_state_comment", "wsrep_local_state_uuid", "wsrep_cluster_state_uuid");'
  kubectl exec "mariadb-$1" -it -c mariadb-galera -- bash -c "$CMD -e '$QUERY'"
}

if [ -z "$1" ]; then
  query 0
  query 1
  query 2
else
  query "$1"
fi

This gives you an overview of the individual nodes' view of the cluster:

===> mariadb-0
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| wsrep_local_state_uuid    | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_local_state_comment | Synced                               |
| wsrep_evs_state           | OPERATIONAL                          |
| wsrep_cluster_size        | 3                                    |
| wsrep_cluster_state_uuid  | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_cluster_status      | Primary                              |
| wsrep_connected           | ON                                   |
+---------------------------+--------------------------------------+

===> mariadb-1
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| wsrep_local_state_uuid    | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_local_state_comment | Synced                               |
| wsrep_evs_state           | OPERATIONAL                          |
| wsrep_cluster_size        | 3                                    |
| wsrep_cluster_state_uuid  | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_cluster_status      | Primary                              |
| wsrep_connected           | ON                                   |
+---------------------------+--------------------------------------+

===> mariadb-2
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| wsrep_local_state_uuid    | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_local_state_comment | Synced                               |
| wsrep_evs_state           | OPERATIONAL                          |
| wsrep_cluster_size        | 3                                    |
| wsrep_cluster_state_uuid  | b72a7f60-9615-11ec-b43a-8a29d1ec8cf3 |
| wsrep_cluster_status      | Primary                              |
| wsrep_connected           | ON                                   |
+---------------------------+--------------------------------------+