This guide will help you investigate and resolve common MySQL database issues. While this list covers many frequent problems, if you encounter something not listed here, please reach out to our support team.

Understanding MySQL server errors

When a MySQL server encounters a problem, it provides error information that can help diagnose the issue. For instance, if you try to query a table that doesn’t exist using the mysql client, you might see an error like this:

shell> use test_database;
shell> SELECT * FROM test_table;

ERROR 1146 (42S02): Table 'test_database.test_table' doesn't exist

This error message contains three key pieces of information:

Numeric Error Code: (e.g., 1146). This code is specific to MySQL and may not correspond directly to error codes in other database systems.
SQLSTATE Value: (e.g., 42S02). This is a five-character code, standardized by ANSI SQL and ODBC, making it more portable across different database systems.
Descriptive Message: (e.g., “Table ‘test_database.test_table’ doesn’t exist”). This text provides a human-readable explanation of the error, often pointing to the specific database or table involved.

Info: Not all MySQL error numbers are mapped to SQLSTATE error codes. In such cases, the value HY000 (general error) is often used for unmapped errors. For a comprehensive list of MariaDB/MySQL error codes and their meanings, refer to the MariaDB Error Codes documentation.

Common MySQL issues

These are some of the most frequently encountered MySQL problems.

Database corruption

If you suspect database corruption, running a repair operation can often resolve the problem. However, if the corruption persists, restoring from a backup is the recommended supported solution.

Before attempting any repairs, always back up your database:

mysqldump database_name > database_name.sql

Replace database_name with the actual name of your database.

To check and repair an entire database:

Use mysqlcheck to check and attempt to repair all tables in a database:

mysqlcheck -r database_name

Again, replace database_name with your database’s name. The -r flag tells mysqlcheck to repair any corrupted tables it finds.

To repair a specific table:

If you know which table is corrupted, you can target it directly:

mysqlcheck -r database_name table_name

Replace database_name and table_name with the relevant names.

If repair fails:

If mysqlcheck cannot repair the database, you will need to restore it from the backup you created:

mysql database_name < database_name.sql

Error establishing a database connection

This generic error message can stem from various underlying causes. Here’s how to troubleshoot:

Check MySQL Service Status: Ensure that the MySQL (or MariaDB) service is running on your server.
- For systems using systemd (like CentOS 7, AlmaLinux, Ubuntu 16.04+): systemctl status mysql # or mariadb
- For older systems using init.d: service mysql status # or mariadb status
Examine MySQL Error Logs: The MySQL error log (commonly found at /var/log/mysqld.log, /var/log/mysql/error.log, or a similar path specified in your MySQL configuration) often contains specific details about why the connection failed or why the service might not be running. Info: If the logs indicate database corruption, refer to the Database Corruption section above.
Verify Connection Credentials: If there’s no indication of corruption and the service is running, check the website or application’s configuration file (e.g., wp-config.php for WordPress, configuration.php for Joomla, or environment files for custom applications). Ensure the database name, username, and password are correct.
Check User Privileges: Confirm that the database user has the necessary permissions (grants) to access the specified database from the host they are connecting from (usually localhost for web applications on the same server). You can check grants using a MySQL client: SQL SHOW GRANTS FOR 'username'@'hostname';

Issues with remote MySQL connections

Connecting to MySQL from a remote machine requires specific configurations:

Remote IP Address: You’ll need the public IP address of the machine that will be connecting to your MySQL server.
MySQL User Configuration:
- The MySQL user account must be configured to allow connections from the remote IP address (or from % for any IP, though this is less secure).
- For example, to grant privileges to user_name from remote_ip_address: SQL GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'remote_ip_address' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
- If using a control panel like cPanel, there’s usually an interface (e.g., “Remote MySQL”) to authorize remote IPs for specific users or all users under an account.
Firewall Configuration: The server’s firewall must allow incoming connections on MySQL’s port (default is 3306) from the remote IP address.

Important: It is not recommended to open port 3306 globally in your firewall. Instead, specifically allow the remote IP address access to port 3306. Use tools like csf.allow (for ConfigServer Security & Firewall), firewalld commands, or iptables rules.
Example using firewalld:
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="your_remote_ip" port port="3306" protocol="tcp" accept'
firewall-cmd --reload

Datadir disk full

MySQL’s datadir is the directory where it stores all its database files. By default, this is often /var/lib/mysql/.

You can verify your server’s datadir location with:

mysql -e "SHOW VARIABLES LIKE '%datadir%';"

Output will look similar to:

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

If the partition or disk where this directory resides becomes full, MySQL will encounter errors and may stop functioning correctly.

Info: Before taking action, always verify the actual location of MySQL’s datadir. Check if it’s a symbolic link to another location.
ls -ld /var/lib/mysql
If the output starts with a d, it’s a directory:
drwxr-x--x 14 mysql mysql 4096 Oct  6 11:38 /var/lib/mysql
If it starts with an l, it’s a symbolic link:
lrwxrwxrwx 1 mysql mysql 20 Oct  6 11:38 /var/lib/mysql -> /mnt/larger_disk/mysql

If the datadir is full, you may need to free up space on that partition or move the datadir to a location with more available space.

Tmpdir disk full

MySQL uses a temporary directory (tmpdir) for certain operations, such as creating temporary tables during complex queries, ALTER TABLE operations, or CREATE INDEX on InnoDB tables. If this temporary directory runs out of space, these operations can fail.

This issue most commonly causes mysqlcheck to stall indefinitely. You might also see errors in the MySQL error log similar to:

[Warning] mysqld: Disk is full writing '/tmp/#sql_*****.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

You can verify the MySQL tmpdir setting(s) with:

mysql -e "SHOW VARIABLES LIKE '%tmpdir%';"

Output typically looks like:

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_tmpdir     |       |  (If blank, uses MySQL's general tmpdir)
| slave_load_tmpdir | /tmp  |
| tmpdir            | /tmp  |
+-------------------+-------+

By default, this is usually /tmp.

Note: If /tmp cannot be cleared sufficiently, or if MySQL requires more temporary space than /tmp allows, you might need to configure MySQL to use a different temporary directory with more space.

Multiple instances of MySQL running

Occasionally, particularly due to monitoring services like tailwatchd (cPanel), multiple mysqld processes might attempt to run simultaneously. This can lead to a situation where different instances control different resources (e.g., one has the lock on the datadir, another on the PID file, another on the port, and another on the socket). This will prevent MySQL from starting or operating correctly.

You can check for multiple mysqld processes using:

ps faux | grep mysqld

If you find multiple instances, you’ll need to stop them all and then attempt a clean start of the MySQL service. Ensure only one legitimate mysqld process is running.

Other potential MySQL issues

These issues may be less common but are still important to recognize.

Host ‘Host_Name’ is blocked

If a host attempts to connect to the MySQL server too many times with incorrect credentials or other connection errors, MySQL may block that host to prevent abuse. The error message will typically look like:

ERROR 1129 (HY000): Host 'host_name' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

To resolve this, you can flush the host cache:

mysqladmin flush-hosts

You may need to provide MySQL admin credentials if a password is set:

mysqladmin -u root -p flush-hosts

/usr/sbin/mysqld: Can’t open file: ‘./somedatabase/sometable.frm’ (errno: 24)

This error, (errno: 24), signifies “Too many open files.” It occurs when the MySQL process (mysqld) has reached its limit for the number of file descriptors it can have open simultaneously.

Diagnostic Commands:

Check MySQL’s configured open_files_limit: SQL SHOW VARIABLES LIKE 'open_files_limit';
Check the process limits for mysqld: pgrep -f /usr/sbin/mysqld | xargs -L1 -I {} cat /proc/{}/limits | grep "Max open files"
Check the current number of files open by mysqld: lsof -u mysql | wc -l (Ensure mysql is the correct user MySQL runs as on your system).

If the current number of open files is at or near the “Max open files” limit, you need to increase it.

Solution:

The method to increase the open files limit depends on your system:

General Linux (edit /etc/security/limits.conf): Add the following lines: mysql soft nofile 65535 mysql hard nofile 65535 A reboot or re-login session might be required for these system-wide limits to apply to new service startups.
CentOS 7/8, AlmaLinux, RHEL 7+ (and other systemd systems): Systemd services have their own limit configurations.
1. For MariaDB: Create the directory if it doesn’t exist: mkdir -p /etc/systemd/system/mariadb.service.d/ Create or edit /etc/systemd/system/mariadb.service.d/limits.conf and add: [Service] LimitNOFILE=65535
2. For MySQL: Create the directory if it doesn’t exist: mkdir -p /etc/systemd/system/mysql.service.d/ # or mysqld.service.d Create or edit /etc/systemd/system/mysql.service.d/limits.conf (or mysqld.service.d/limits.conf) and add: [Service] LimitNOFILE=65535
3. Then, reload systemd and restart the service: systemctl daemon-reload systemctl restart mariadb # or mysql
Configure MySQL’s open_files_limit (in /etc/my.cnf or /etc/mysql/my.cnf): Add or modify the open_files_limit variable under the [mysqld] section: [mysqld] open_files_limit = 65535 Restart MySQL after making this change.
cPanel/WHM Specific: In WHM’s “Tweak Settings” (under Server Configuration), ensure the option “Let cPanel determine the best value for your MySQL open_files_limit configuration?” is disabled if you are setting this manually, to prevent cPanel from overriding your custom settings.

After applying changes, restart MySQL and monitor.

MySQL showing 0 size In cPanel

If cPanel reports a database size as 0MB when you know it contains data, the database size cache might be outdated or corrupted. You can try to update it:

/scripts/update_db_cache

This script may output errors if it encounters problems. Troubleshoot any errors reported by the script.

InnoDB disabled or not functioning

If you experience issues specifically with InnoDB tables (e.g., they seem to be missing, or mysqlcheck crashes when checking them), InnoDB support might be disabled or experiencing problems.

Check InnoDB Status: Execute the following in the MySQL client:

SHOW ENGINES;

Look for the InnoDB engine. The Support column should say DEFAULT or YES. If it says NO or DISABLED, InnoDB is not active.

If mysqlcheck on an InnoDB table returns an error like: Error : Incorrect information in file: './database_name/table_name.frm' Error : Corrupt …and the table is known to be InnoDB, this might indicate an issue with the InnoDB engine itself, rather than simple table file corruption.

Lots of locked tables

Table locking is a mechanism databases use to ensure data consistency. However, excessive or long-held locks can cause performance bottlenecks. This is primarily an issue with older storage engines like MyISAM.

MyISAM: MyISAM uses table-level locking. When a query modifies a MyISAM table (e.g., INSERT, UPDATE, DELETE), the entire table is locked, preventing other queries from accessing it until the first one completes. On busy sites with many concurrent writes, this can cause queries to queue up (“sleep”), leading to slow site performance and potentially high server load.
InnoDB: InnoDB uses row-level locking (for the most part), which is much more efficient for concurrent access, as only the specific rows being modified are locked, not the entire table.

Identifying Locked Tables: To see currently running MySQL processes and their status (including “Locked” or “Waiting for table metadata lock”):

mysqladmin processlist
# or, for more detail if you have access:
# mysql -e "SHOW FULL PROCESSLIST;"

Look for queries in a Locked state or a high number of queries in a Sleep state waiting for locks.

Solution: Convert to InnoDB If frequent table locking on MyISAM tables is an issue, the best long-term solution is to convert the affected tables (or entire database) to the InnoDB storage engine.

Command to convert all MyISAM tables in a specific database to InnoDB:

DB_NAME="your_database_name"
mysql -BN -e "SELECT CONCAT('ALTER TABLE \`', TABLE_SCHEMA, '\`.\`', TABLE_NAME, '\` ENGINE=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA = '$DB_NAME';" | mysql - $DB_NAME

Important: Replace your_database_name with the actual name of the database. Always back up your database before performing such operations.

Client does not support authentication protocol

If you see an error message like: Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client

This typically means there’s a mismatch between the authentication method used by the MySQL server (often related to password hashing) and what the MySQL client (e.g., a PHP script, a command-line tool) supports. This commonly occurs when connecting an older client to a newer MySQL server that uses a more secure password hashing method (like caching_sha2_password in MySQL 8+).

Check Versions:

MySQL Client Version (example for command-line client): mysql --version # or for PHP, you can use phpinfo() to check the “Client API library version” under the mysqlnd or mysqli section.
MySQL Server Version: mysqladmin version # or connect to mysql and run: SELECT VERSION();

Possible Solutions:

Upgrade the MySQL Client: This is often the best solution. Ensure your application connectors (PHP, Python, Java, etc.) and command-line tools are up-to-date.
Change User Authentication Method (Server-Side): If upgrading the client isn’t immediately possible, you can alter the MySQL user on the server to use an older, compatible authentication method (e.g., mysql_native_password). For a user 'username'@'hostname': SQL ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'new_password'; FLUSH PRIVILEGES; Caution: This reduces security compared to newer authentication methods.

MySQL conflicts/dependency errors during cPanel updates

During a cPanel update (/scripts/upcp), you might encounter errors related to MySQL package conflicts or dependencies. The log might show something like:

[20230508.133519]   error: Failed dependencies:
[20230508.133519]    MySQL conflicts with mysql-5.0.77-4.el5_5.3.i386
[20230508.133519] W Exit Code: 254
[20230508.133519] ***** FATAL: Test install failed: error: Failed dependencies:
[20230508.133519]    MySQL conflicts with mysql-5.0.77-4.el5_5.3.i386
[20230508.133519]   The Administrator will be notified to review this output when this script completes
[20230508.133519] ***** FATAL: Error testing if the RPMs will install: Died at /usr/local/cpanel/scripts/updatenow.static line 12527.

This usually indicates that the currently installed version of MySQL is incompatible with the version cPanel is trying to manage or update to. The solution is typically to upgrade MySQL to a version supported by your cPanel installation. This can often be done via WHM’s “MySQL/MariaDB Upgrade” interface.

Advanced MySQL server errors

Can’t create/write to file (Errcode: 13 “Permission denied”)

You might encounter this error when MySQL tries to create a temporary file, especially if tmpdir is set to a non-standard location. For example, with tmpdir = /home/mysqltmp in /etc/my.cnf, MariaDB 10.1+ might fail to start with:

2018-07-30  0:23:27 139945490954496 [ERROR] mysqld: Can't create/write to file '/home/mysqltmp/ibZjwUqN' (Errcode: 13 "Permission denied")

This can happen even if filesystem permissions for /home/mysqltmp seem correct for the mysql user. The cause can be systemd’s security features like ProtectHome=true.

Diagnosis (Systemd): Check the service unit file for ProtectHome:

Bash

awk '/ProtectHome/' /usr/lib/systemd/system/mariadb.service # or mysql.service

If ProtectHome=true is set, it makes /home, /root, and /run/user largely inaccessible to the service.

Solution (Systemd): Override the ProtectHome setting for the MySQL/MariaDB service.

Create an override file. For MariaDB: mkdir -p /etc/systemd/system/mariadb.service.d/ echo -e '[Service]\nProtectHome=false' > /etc/systemd/system/mariadb.service.d/protecthome.conf For MySQL, adjust the path: /etc/systemd/system/mysql.service.d/protecthome.conf.
Reload systemd and restart the service: systemctl daemon-reload systemctl restart mariadb # or mysql

See also the MariaDB JIRA issue MDEV-10399 for more context.

Specified key was too long (ERROR 1071)

You might see an error like this when creating or altering tables, particularly with InnoDB and UTF8 character sets:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

InnoDB has limits on index key prefix lengths. For COMPACT or REDUNDANT row formats with utf8mb3 (aliased as utf8), the maximum is 767 bytes. For utf8mb4, each character can take up to 4 bytes, making it easier to hit this limit.

Solution: To support longer keys, you need to use the DYNAMIC or COMPRESSED row format for InnoDB tables, which requires specific MySQL settings.

Ensure the following settings are in your /etc/my.cnf (or equivalent) under the [mysqld] section: [mysqld] innodb_file_per_table = 1 innodb_file_format = Barracuda # (Barracuda is default in MySQL 5.7+) innodb_large_prefix = 1 # (Default is ON in MySQL 5.7.7+) Restart MySQL after adding/changing these. Note: innodb_file_format and innodb_large_prefix are deprecated/obsolete in MySQL 8.0+ as their improved behaviors are standard.
Alter the table to use the DYNAMIC row format: SQL ALTER TABLE `database_name`.`table_name` ROW_FORMAT=DYNAMIC; Replace database_name and table_name accordingly. This will allow for index key prefixes up to 3072 bytes.

For more details, refer to the MySQL documentation on InnoDB row formats (adjust version for your server).

This error can occur when starting MySQL manually (e.g., for recovery, password resets) if it cannot locate its language and error message files.

[ERROR] Can't find messagefile '/share/mysql/errmsg.sys' (path may vary slightly)

The typical fix is to explicitly tell mysqld_safe or mysqld where the base MySQL installation directory is, so it can find the share directory correctly (usually located at /usr/share/mysql or similar).

Solution: Add the --basedir=/usr (or the correct base path for your installation) parameter to your manual startup command. For example:

Bash

mysqld_safe --user=mysql --basedir=/usr &
# or
# mysqld --user=mysql --basedir=/usr &

Table is crashed and last repair failed (Error 144)

This error indicates a table is marked as crashed, and a previous repair attempt was unsuccessful. It can prevent MySQL/MariaDB from starting if the crashed table is a critical system table (e.g., in the mysql database).

Example error log:

2023-09-12 12:00:27 0 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/db'
2023-09-12 12:00:27 0 [ERROR] Fatal error: Can't open and lock privilege tables: Got error '144 "Table is crashed and last repair failed"' for './mysql/db'
2023-09-12 12:00:27 0 [ERROR] Aborting

Troubleshooting Steps (Offline Repair):

If MySQL/MariaDB won’t start, you can’t use mysqlcheck or REPAIR TABLE. You’ll need to use an offline repair tool.

Identify the Table Engine:
- If InnoDB, try InnoDB recovery proceduresFor MyISAM or Aria tables, you’ll use myisamchk or aria_chk respectively.To determine the engine if MySQL is down, look at the table file extensions in the database directory (e.g., /var/lib/mysql/database_name/).
  - MyISAM: .MYI, .MYD, .frmAria: .MAI, .MAD, .frm
In the example error, the table is ./mysql/db. Check its files: ls -lh /var/lib/mysql/mysql/db.* Output like -rw-rw---- ... db.MAD and -rw-rw---- ... db.MAI indicates the Aria engine.
STOP THE MYSQL/MARIADB SERVICE COMPLETELY: This is critical. Offline repair tools must not be run on a live database. systemctl stop mariadb # or mysql systemctl disable mariadb # To prevent accidental restart systemctl mask mariadb # To further prevent restart, verify no mysqld or mariadbd process is running: pgrep -a -f 'mysqld|mariadbd'
Run the Appropriate Repair Tool:
- For Aria tables (like mysql.db in the example): Navigate to the directory containing the table files (e.g., /var/lib/mysql/mysql/ for system tables). cd /var/lib/mysql/mysql aria_chk -r db # -r means “recover” You might see: aria_chk: Got error 'Can't find file' when trying to use aria control file './aria_log_control'. This is often normal during recovery if the log is also affected; the tool will proceed.
- For MyISAM tables: myisamchk -r /path/to/datadir/database_name/table_name.MYI # Or, if in the directory: # myisamchk -r table_name
Attempt to Restart MySQL/MariaDB: systemctl unmask mariadb # or mysql systemctl start mariadb # or mysql
If Startup Fails with Another Crashed Table: Repeat the process. The log might point to another crashed table (e.g., mysql.global_priv). Stop the service again and repair that table.
Post-Repair Checks: Once MySQL/MariaDB starts, run a full check of all databases and tables: mysqlcheck -Asc # Check All databases, Search for errors, Check tables If it reports further errors, you can try an online repair for specific databases or tables: mysqlcheck -r database_name # Repair specific database mysqlcheck -r database_name table_name # Repair specific table

Messages often mistaken for errors (“Non-Errors”)

Some log messages or startup behaviors might seem like errors but are actually indicators of other states or require looking elsewhere for the true problem.

Starting MySQL.. ERROR! Manager Of Pid-file quit without updating file

This message, often seen when trying to start MySQL from the command line (e.g., service mysql start), is not the actual error. It simply means that the MySQL startup script launched the MySQL server process (mysqld), but that process exited (failed to start properly) before it could update its Process ID (PID) file.

The actual reason for the startup failure will be logged in the MySQL error log. Check the log file (e.g., /var/log/mysqld.log, /var/log/mysql/error.log) for the specific error messages that occurred during the startup attempt.

IP Address ‘192.168.1.201’ could not be resolved: temporary failure In name resolution

This is a warning, not a fatal error. It means that when a client connected from an IP address (e.g., 192.168.1.201), the MySQL server attempted to perform a reverse DNS (rDNS) lookup to find the hostname associated with that IP, and the lookup failed or timed out.

You can prevent MySQL from performing these rDNS lookups by adding the following directives under the [mysqld] section of your MySQL configuration file (/etc/my.cnf or similar):

[mysqld]
skip-host-cache
skip-name-resolve

After adding these, restart the MySQL service.

Warning: Adding skip-name-resolve has implications:

Grant Hostnames: You will no longer be able to use hostnames in MySQL GRANT statements (e.g., GRANT ALL ON db.* TO 'user'@'myhost.example.com'). All grants must use IP addresses or '%'.
cPanel Restores: cPanel/WHM’s restorepkg functionality may rely on hostname resolution for database user grants. Disabling name resolution might interfere with proper database restoration during account transfers or backups.
It is generally recommended not to add skip-name-resolve unless necessary. Instead, ensure your server’s DNS resolution is working correctly, or if you must use this option, connect applications via IP address and update grants to use IP addresses. You can also add problematic client hostnames to the server’s /etc/hosts file.

MySQL gone away / too many sleeping processes

Errors like “MySQL server has gone away” or observing a large number of “sleeping” connections in SHOW PROCESSLIST; can indicate that client connections are being terminated prematurely or are being held open longer than necessary.

One common cause can be MySQL’s timeout variables:

wait_timeout: The number of seconds the server waits for activity on a non-interactive connection before closing it.
interactive_timeout: The number of seconds the server waits for activity on an interactive connection (like mysql client) before closing it.

If these values are too low, connections might be dropped before an application is finished with them. If they are too high, connections might remain open unnecessarily, consuming resources.

Sometimes, applications open connections and don’t close them properly, leading to many sleeping processes. Lowering wait_timeout (e.g., to 60-300 seconds) can help prune these idle connections. However, this is a workaround, not a fix for the application’s behavior.

Info: While adjusting these timeouts can sometimes alleviate symptoms, it’s often not the root cause of “MySQL server has gone away” errors, which can also be due to sending queries that are too large (max_allowed_packet), network issues, or MySQL crashing. The underlying issue with excessive or long-running queries should ideally be addressed by a database administrator or SQL developer by optimizing queries and application logic.

Was this article helpful?

Thank you for your input.

Thank you for your feedback.