Skip to main content

MySQL

This page collects the MySQL problems reported most often on FPT Database Engine, with the symptoms that identify each one and the steps that resolve it. Use it to diagnose an incident yourself, or to gather the detail FPT Support needs.

Each entry follows the same shape: symptoms, cause, impact, then resolution.

Known issues​

Backup fails requesting OPTIMIZE TABLE​

Symptoms​

A MySQL backup job fails and sends an email notification like this:

cluster_id : abcxyz11
cluster_name : clustername
vdc_name : ABCXYZ_VCD
org_name : ABCXYZ-ORG
start_time : 10/23/2055 00:30:02
backup_type : diff
backup_size : NoneG
backup_log : ERROR: Please run OPTIMIZE TABLE on all listed tables to fix this issue. Tables found: db/transactions...
backup_state : failed
created_at : 10/23/2055 00:31:01

The cause is a bug in Percona XtraBackup, the software FPT Cloud uses to back up FPT Database Engine clusters.

Cause​

From MySQL 8.0.29 onward, InnoDB supports INSTANT ADD/DROP COLUMN. An INSTANT operation does not copy the table and does not rebuild it. It only writes metadata into the InnoDB dictionary, which shows up as TOTAL_ROW_VERSIONS > 0.

XtraBackup is not fully compatible with tables carrying that metadata, so it cannot process any table where INSTANT ADD/DROP COLUMN has been used. The backup job stops and asks you to run OPTIMIZE TABLE.

Impact​

  • Query performance drops, because the data is no longer organized optimally.
  • System load rises, consuming resources and memory.
  • INSERT and UPDATE operations take longer.
  • Fragmented tables slow down backup and recovery.

Resolution​

Rebuild the affected table to clear the INSTANT metadata:

OPTIMIZE TABLE db.transactions;

After it completes, the table is fully rebuilt, the INSTANT column version metadata is gone, TOTAL_ROW_VERSIONS returns to 0, and backups run normally again.

warning

OPTIMIZE TABLE rebuilds the whole table and can take a WRITE lock. On large tables the rebuild runs long, so schedule it during off-peak hours and confirm you have enough temporary storage for it first.

MySQL crashes on a composite index over a JSON column​

Symptoms​

A MySQL node crashes when a query uses a composite index built over a JSON column:

22:20:45 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
...
Query (407ad76b1830): SELECT `fort_knox_funds_flows`.* FROM `fort_knox_funds_flows`
WHERE (25830440 MEMBER OF(`fort_knox_funds_flows`.`money_movements` ->> "$[*].to_user_id")
OR 25830440 MEMBER OF(`fort_knox_funds_flows`.`money_movements` ->> "$[*].from_user_id"))
ORDER BY `fort_knox_funds_flows`.`created_at` DESC LIMIT 20

This is an upstream MySQL bug. See MySQL bug 109542 for the full report.

Cause​

From MySQL 8.0.2x onward, a table whose INDEX definition references fields inside a JSON column can crash the server. MySQL fails to create and maintain indexes over JSON columns reliably:

  • It cannot handle JSON objects correctly inside a composite index, which produces memory errors or asynchronous processing faults.
  • It cannot optimize how JSON data is stored and retrieved within a composite index.
  • Storage features such as Full Disk Encryption can make the fault more severe.

Impact​

MySQL crashes or restarts without warning. In some cases the database cannot recover its data afterward, which affects availability and reliability directly in production.

Resolution​

  • Use single-column indexes rather than composite indexes when a JSON column is involved.
  • Avoid indexing a JSON column directly. Where you need one, create a generated column from the JSON value and index that instead.
  • Upgrade to a newer MySQL release such as 8.0.42, where this bug is fixed.

Metadata lock storm on slave nodes​

Symptoms​

On a MySQL HA cluster, the master node reads and writes normally, but replication lag on the two slave nodes climbs sharply, reaching around two hours. Many threads on the slave nodes sit in Waiting for table metadata lock:

1073  admin  10.225.65.36:25680  fpt  Query  178  Waiting for table metadata lock  SELECT COUNT(1) AS `cnt` FROM `user_notifications` ...
1075  admin  10.225.65.36:25694  fpt  Query  178  Waiting for table metadata lock  SELECT COUNT(1) AS `cnt` FROM `user_notifications` ...
...

This follows a DDL command run against the table, which pushes the slave nodes into a metadata lock storm.

Cause​

MySQL uses metadata locks (MDL) to protect table structure at schema and table level while DDL and DML statements run.

In a MySQL InnoDB Cluster, replicated DDL transactions such as ALTER TABLE, CREATE INDEX, and DROP TABLE are applied sequentially on the slave nodes by the applier thread. If a user session is still holding an MDL lock, because of a long-running query or an uncommitted DML statement, the applier thread waits for it.

When the applier thread cannot take the lock in time, the remaining transactions in the Global Replication Queue block behind it. The applier thread stalls, every thread touching that table stalls with it, and the slave node becomes effectively unreachable for the application.

Impact​

Queries and transactions block, data access is disrupted, and system latency rises. Replication transactions stall, which produces the replication lag and degrades performance across the cluster.

Resolution​

  1. Pause the applications and services touching the table the DDL statement targets. This stops new queries from taking or queuing on metadata locks.
  2. Restart the slave nodes to release the threads holding locks. Restarting ends the stuck sessions so the DDL can apply.
tip

Before running DDL against a high-QPS table, disconnect the applications using the tables and indexes that statement affects. This avoids the lock storm entirely and keeps the change from disrupting the cluster.

Next steps​