Fixing Database Write errors

Database Write Issues

Symptoms:

The database build phase fails with ER_LOCK_WAIT_TIMEOUT
Restore step 2 reports MySQL server has gone away
max_allowed_packet too small
Got a packet bigger than 'max_allowed_packet' bytes

Fix 1: max_allowed_packet

Restoring a large SQL dump needs a large enough max_allowed_packet.

Raise this value in the MySQL configuration. On shared hosting, contact support to have it raised. On a VPS, edit my.cnf or mariadb.conf:

[mysqld]
max_allowed_packet = 256M

Then restart MySQL/MariaDB.

Fix 2: MySQL user is missing privileges

The restore user needs ALL PRIVILEGES on the database (including CREATE, DROP, INSERT, UPDATE, DELETE, INDEX, REFERENCES, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, TRIGGER, EVENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE).

GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';
FLUSH PRIVILEGES;

Fix 3: Disk full

The database server cannot write when the disk is full. Check the free disk space on the server and delete old logs and unneeded binary logs (PURGE BINARY LOGS).

Fix 4: MySQL crashed / connection drop

Restart the MySQL service. Check the MySQL/MariaDB error log to find the cause of the crash.

Fix 5: innodb_lock_wait_timeout

A large restore needs a longer timeout:

[mysqld]
innodb_lock_wait_timeout = 600

Fix 6: utf8mb4 collation mismatch

The error Unknown collation: 'utf8mb4_0900_ai_ci' when restoring a database from MySQL 8 to an older MariaDB.

Fix 1: replace the collation in the SQL file before importing:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

Fix 2: create the destination database with a compatible collation:

CREATE DATABASE example_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;