Healthwatch Grafana UI Login Failure due to Galera Flow Control Stall

Products

• VMware Tanzu Platform Core
• VMware Tanzu Application Platform
• VMware Tanzu Application Service
• VMware Tanzu Platform - Cloud Foundry

Issue/Introduction

After performing a minor upgrade or certificate rotation on a Healthwatch/TAS Foundation with a PXC (Percona XtraDB Cluster) backing store, users may find that the Grafana monitoring UI becomes inaccessible. Login attempts hang for approximately 50 seconds before returning an HTTP 500 Internal Server Error, regardless of whether the user authenticates via LDAP or a local admin account.

Symptoms:

1) Grafana login page loads successfully but login button spins and eventually fails

2) Both LDAP and local admin users are affected equally

3) Grafana log (grafana.log) shows repeated errors such as:

  logger=user.sync level=error msg="Failed to update user"
  error="Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"

  logger=authn.service level=error msg="Failed to run post auth hook"
  error="[user.sync.internal] unable to update user"

  logger=context level=error msg="Request Completed"
  method=POST path=/login status=500 duration=50.927s

Cause

The root cause is a Galera cluster-wide write stall triggered by one MySQL PXC node entering a frozen wsrep apply state following an upgrade or cert rotation.

Resolution

1) You can ssh into the pxc nodes and then connect to the DB using the below steps:

• Use bosh ssh to log into the 'pxc' VM.
• Run 'sudo su -'.
• Run 'mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf' to connect to the mysql DB server and get a DB prompt.
• At the DB prompt,  run 'use grafana;' to use the grafana DB.

2)  Confirm Grafana DB is the issue

Check grafana.log for Error 1205 on login:

grep -i "user.sync\|1205\|lock wait" /var/vcap/sys/log/grafana/grafana.log

3)  Identify the lagging Galera node

Run on the pxc nodes, if you have more than one, run it on all the nodes after connecting to the DB as mentioned in step 1. 

SHOW STATUS LIKE 'wsrep_local_recv_queue';
SHOW STATUS LIKE 'wsrep_flow_control_sent';
SHOW STATUS LIKE 'wsrep_last_committed';
SHOW STATUS LIKE 'wsrep_local_state_comment';

The problematic node will show:

- `wsrep_local_recv_queue` — significantly elevated 
- `wsrep_flow_control_sent` — `1` or higher
- `wsrep_last_committed` — `-1`

4) Kill stale threads on the primary write node

On the node with `wsrep_flow_control_active = true`, run the below steps:

SHOW FULL PROCESSLIST;

Identify grafana application threads stuck for a long time in state `wsrep: replicating and certifying write set` or `waiting for handler commit` and terminate it. 

KILL <thread_id>;

-- Repeat for each stale grafana thread

-- Note: system user threads (IDs 1, 2, 10) cannot be killed manually — skip them

5) Restart MySQL on the lagging node 

Once you find the problematic node where `wsrep_last_committed` has a value of `-1` as mentioned in step 3, you can restart the process on that node using the below command 

bosh -d healthwatch-uuid restart pxc/id --no-converge

6) Confirm flow control has lifted on the write node

SHOW STATUS LIKE 'wsrep_local_send_queue';
SHOW STATUS LIKE 'wsrep_flow_control_active';

Expected values post-recovery:

- `wsrep_local_send_queue` → `0`

- `wsrep_flow_control_active` → `false`

 

7) Restart Grafana 

bosh -d <healthwatch-deployment> restart grafana

You can now check the login for both admin user and also the LDAP users.