MySQL Errors: Diagnostics and Solutions

Introduction

MySQL database errors can bring entire applications to a halt, causing data loss, service outages, and frustrated users. From connection failures and corrupted tables to replication issues and performance problems, MySQL errors require quick diagnosis and resolution to minimize downtime and maintain data integrity.

This comprehensive guide provides database administrators with systematic approaches to diagnosing and resolving MySQL errors. You'll learn to interpret error messages, use diagnostic tools, analyze logs, and implement solutions for common MySQL problems affecting both MariaDB and MySQL installations.

Understanding MySQL error diagnostics is critical for anyone managing database-driven applications. This guide covers everything from basic connection troubleshooting to advanced recovery procedures for corrupted databases, enabling you to quickly restore database functionality.

Understanding MySQL Errors

Common Error Categories

Connection Errors: Can't connect to MySQL server Permission Errors: Access denied for user Data Errors: Duplicate entry, data truncation Table Errors: Table doesn't exist, table is marked as crashed Replication Errors: Slave lag, replication stopped Performance Errors: Too many connections, lock wait timeout Storage Errors: Disk full, tablespace full

MySQL Error Log Location

# Find error log location
mysql -e "SHOW VARIABLES LIKE 'log_error';"

# Common locations
tail -100 /var/log/mysql/error.log        # Debian/Ubuntu
tail -100 /var/log/mysqld.log             # CentOS/RHEL
tail -100 /var/lib/mysql/$(hostname).err  # Default

# Check with systemd
journalctl -u mysql -n 100
journalctl -u mariadb -n 100

Initial MySQL Diagnostics

Quick Status Check

# Check MySQL service
systemctl status mysql
systemctl status mariadb

# Try connecting
mysql -u root -p

# Check if MySQL is listening
ss -tlnp | grep mysql
ss -tlnp | grep 3306

# Process check
ps aux | grep mysqld

# Quick status
mysqladmin -u root -p status
mysqladmin -u root -p ping

# Connection count
mysqladmin -u root -p processlist

# Variables
mysql -e "SHOW VARIABLES LIKE 'version%';"

Step 1: Connection Errors

Error: Can't Connect to MySQL Server

# Error message:
# ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

# Check if MySQL is running
systemctl status mysql

# Start if stopped
systemctl start mysql

# Check socket file
ls -la /var/run/mysqld/mysqld.sock
ls -la /tmp/mysql.sock

# Find socket location
mysql -e "SHOW VARIABLES LIKE 'socket';"

# Check my.cnf
grep socket /etc/mysql/my.cnf
grep socket /etc/my.cnf

# Verify socket permissions
ls -l /var/run/mysqld/

# Fix permissions
chown mysql:mysql /var/run/mysqld
chmod 755 /var/run/mysqld

Error: Access Denied

# ERROR 1045 (28000): Access denied for user 'root'@'localhost'

# Reset root password
systemctl stop mysql

# Start in safe mode
mysqld_safe --skip-grant-tables &

# Connect without password
mysql -u root

# Reset password
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
mysql> exit

# Restart normally
killall mysqld
systemctl start mysql

# Test new password
mysql -u root -p

# Check user permissions
mysql -e "SELECT user, host FROM mysql.user;"
mysql -e "SHOW GRANTS FOR 'username'@'localhost';"

Error: Too Many Connections

# ERROR 1040 (HY000): Too many connections

# Check current connections
mysql -e "SHOW PROCESSLIST;"
mysql -e "SHOW STATUS LIKE 'Threads_connected';"

# Check max connections
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Kill specific connection
mysql -e "KILL connection_id;"

# Increase max_connections temporarily
mysql -e "SET GLOBAL max_connections = 500;"

# Permanent fix - edit my.cnf
[mysqld]
max_connections = 500

# Restart MySQL
systemctl restart mysql

# Find connection-heavy users
mysql -e "SELECT user, COUNT(*) as connections
    FROM information_schema.PROCESSLIST
    GROUP BY user
    ORDER BY connections DESC;"

Step 2: Table Errors

Error: Table is Marked as Crashed

# ERROR 145 (HY000): Table './database/table' is marked as crashed

# Check table
mysql -e "CHECK TABLE database.table;"

# Repair table
mysql -e "REPAIR TABLE database.table;"

# Force repair
mysql -e "REPAIR TABLE database.table EXTENDED;"

# Use myisamchk (for MyISAM tables - service must be stopped)
systemctl stop mysql
myisamchk -r /var/lib/mysql/database/table.MYI
systemctl start mysql

# For InnoDB corruption
mysql -e "SELECT * FROM database.table INTO OUTFILE '/tmp/table_backup.txt';"
mysql -e "DROP TABLE database.table;"
# Restore from backup

# Check all tables
mysqlcheck -u root -p --all-databases
mysqlcheck -u root -p --auto-repair --all-databases

Error: Table Doesn't Exist

# ERROR 1146 (42S02): Table 'database.table' doesn't exist

# List tables
mysql -e "SHOW TABLES FROM database;"

# Check case sensitivity
mysql -e "SHOW VARIABLES LIKE 'lower_case_table_names';"

# Check data directory
ls -la /var/lib/mysql/database/

# Recover from .frm file
mysql database < table.frm

# If .ibd file exists (InnoDB)
# Edit my.cnf
[mysqld]
innodb_force_recovery = 1

systemctl restart mysql

# Export data
mysqldump -u root -p database > backup.sql

# Remove force recovery
# Edit my.cnf, remove innodb_force_recovery
systemctl restart mysql

Step 3: Data Errors

Error: Duplicate Entry

# ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

# Find duplicates
mysql -e "SELECT column, COUNT(*)
    FROM database.table
    GROUP BY column
    HAVING COUNT(*) > 1;"

# Remove duplicates
# Method 1: Keep first occurrence
CREATE TABLE temp_table AS
    SELECT DISTINCT * FROM original_table;

DROP TABLE original_table;
RENAME TABLE temp_table TO original_table;

# Method 2: Delete duplicates
DELETE t1 FROM table t1
INNER JOIN table t2
WHERE t1.id > t2.id
AND t1.duplicate_column = t2.duplicate_column;

# Check constraints
mysql -e "SHOW CREATE TABLE database.table\G"

Error: Data Too Long

# ERROR 1406 (22001): Data too long for column

# Check column definition
mysql -e "DESCRIBE database.table;"

# Modify column size
mysql -e "ALTER TABLE database.table
    MODIFY column_name VARCHAR(500);"

# For TEXT types
mysql -e "ALTER TABLE database.table
    MODIFY column_name MEDIUMTEXT;"

# Check max packet size
mysql -e "SHOW VARIABLES LIKE 'max_allowed_packet';"

# Increase if needed
mysql -e "SET GLOBAL max_allowed_packet = 67108864;"  # 64MB

# Permanent in my.cnf
[mysqld]
max_allowed_packet = 64M

Step 4: Replication Errors

Checking Replication Status

# On slave/replica
mysql -e "SHOW SLAVE STATUS\G"

# Key fields to check:
# - Slave_IO_Running: Should be "Yes"
# - Slave_SQL_Running: Should be "Yes"
# - Last_Error: Shows error message
# - Seconds_Behind_Master: Lag in seconds

# On master
mysql -e "SHOW MASTER STATUS\G"
mysql -e "SHOW BINARY LOGS;"

Fixing Replication

# Skip one error
mysql -e "SET GLOBAL sql_slave_skip_counter = 1;"
mysql -e "START SLAVE;"

# Reset slave
mysql -e "STOP SLAVE;"
mysql -e "RESET SLAVE;"

# Reconfigure slave
mysql -e "CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;"

mysql -e "START SLAVE;"

# Check status
mysql -e "SHOW SLAVE STATUS\G"

# Monitor replication lag
watch -n 1 "mysql -e 'SHOW SLAVE STATUS\G' | grep Seconds_Behind_Master"

Step 5: Performance Errors

Lock Wait Timeout

# ERROR 1205 (HY000): Lock wait timeout exceeded

# Show current locks
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "TRANSACTIONS"

# Find blocking queries
mysql -e "SELECT * FROM information_schema.INNODB_TRX;"
mysql -e "SELECT * FROM information_schema.INNODB_LOCKS;"
mysql -e "SELECT * FROM information_schema.INNODB_LOCK_WAITS;"

# Kill blocking query
mysql -e "KILL query_id;"

# Increase timeout
mysql -e "SET GLOBAL innodb_lock_wait_timeout = 120;"

# In my.cnf
[mysqld]
innodb_lock_wait_timeout = 120

# Prevent long-running transactions
mysql -e "SELECT * FROM information_schema.PROCESSLIST
    WHERE TIME > 60
    ORDER BY TIME DESC;"

Slow Queries

# Enable slow query log
mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 2;"

# Check slow query log location
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

# Analyze slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Find current slow queries
mysql -e "SELECT * FROM information_schema.PROCESSLIST
    WHERE TIME > 5
    ORDER BY TIME DESC;"

# Explain slow query
mysql -e "EXPLAIN SELECT * FROM table WHERE condition;"

# Add indexes
mysql -e "ALTER TABLE table ADD INDEX idx_column (column);"

# Optimize tables
mysql -e "OPTIMIZE TABLE database.table;"

Step 6: Storage Errors

Disk Full Error

# ERROR 3 (HY000): Error writing file (errno: 28 - No space left on device)

# Check disk space
df -h /var/lib/mysql

# Find large files
du -sh /var/lib/mysql/* | sort -rh | head -20

# Clean binary logs
mysql -e "SHOW BINARY LOGS;"
mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

# Clean relay logs
mysql -e "PURGE RELAY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

# Remove old backups
find /var/backups/mysql -name "*.sql" -mtime +7 -delete

# Configure binary log expiration
# In my.cnf
[mysqld]
expire_logs_days = 7
max_binlog_size = 100M

InnoDB Tablespace Issues

# ERROR 1114 (HY000): The table 'table' is full

# Check tablespace
mysql -e "SELECT tablespace_name, file_name, total_extents
    FROM information_schema.FILES
    WHERE file_type = 'TABLESPACE';"

# Expand tablespace
mysql -e "ALTER TABLESPACE tablespace_name
    ADD DATAFILE '/path/to/datafile.ibd'
    INITIAL_SIZE=1G;"

# Enable per-table tablespaces
# In my.cnf
[mysqld]
innodb_file_per_table = 1

# Check InnoDB file size
du -sh /var/lib/mysql/ibdata1

Step 7: Corruption Recovery

InnoDB Crash Recovery

# InnoDB won't start - enable recovery mode
# Edit /etc/mysql/my.cnf
[mysqld]
innodb_force_recovery = 1

# Recovery levels:
# 1 = Let server run even if detect corrupt page
# 2 = Prevent master thread operations
# 3 = Do not run transaction rollbacks
# 4 = Do not calculate table statistics
# 5 = Do not look at undo logs
# 6 = Do not do forward rollback

# Restart MySQL
systemctl restart mysql

# Dump databases
mysqldump -u root -p --all-databases > all_databases.sql

# Remove recovery mode
# Edit my.cnf, remove innodb_force_recovery
systemctl restart mysql

# Restore if needed
mysql -u root -p < all_databases.sql

General Corruption Checks

# Check all databases
mysqlcheck -u root -p --all-databases

# Check and repair
mysqlcheck -u root -p --auto-repair --all-databases

# Optimize all tables
mysqlcheck -u root -p --optimize --all-databases

# Analyze tables
mysqlcheck -u root -p --analyze --all-databases

Solutions and Prevention

Configuration Optimization

# Recommended my.cnf settings
[mysqld]
# Connection settings
max_connections = 200
max_connect_errors = 1000000

# Buffer settings
innodb_buffer_pool_size = 4G  # 70-80% of RAM
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Query cache (disabled in MySQL 8.0+)
query_cache_size = 0
query_cache_type = 0

# Timeout settings
wait_timeout = 600
interactive_timeout = 600
lock_wait_timeout = 60

# Binary log settings
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1

# Error logging
log_error = /var/log/mysql/error.log
log_warnings = 2

# Slow query logging
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow-query.log

Monitoring Script

cat > /usr/local/bin/mysql-monitor.sh << 'EOF'
#!/bin/bash

LOG="/var/log/mysql-monitor.log"
ALERT_EMAIL="[email protected]"

# Check if MySQL is running
if ! systemctl is-active --quiet mysql; then
    echo "$(date): MySQL is down!" >> "$LOG"
    systemctl start mysql
    echo "MySQL was down and has been restarted" | \
        mail -s "MySQL Alert" "$ALERT_EMAIL"
fi

# Check connections
CONN=$(mysql -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}' | tail -1)
MAX=$(mysql -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}' | tail -1)
PERCENT=$((CONN * 100 / MAX))

if [ $PERCENT -gt 80 ]; then
    echo "$(date): High connections: $CONN/$MAX ($PERCENT%)" >> "$LOG"
fi

# Check slow queries
SLOW=$(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | awk '{print $2}' | tail -1)
if [ $SLOW -gt 100 ]; then
    echo "$(date): High slow queries: $SLOW" >> "$LOG"
fi

# Check replication (if slave)
if mysql -e "SHOW SLAVE STATUS\G" > /dev/null 2>&1; then
    STATUS=$(mysql -e "SHOW SLAVE STATUS\G")
    if ! echo "$STATUS" | grep -q "Slave_IO_Running: Yes"; then
        echo "$(date): Slave IO not running!" >> "$LOG"
        echo "MySQL Replication IO stopped" | \
            mail -s "MySQL Replication Alert" "$ALERT_EMAIL"
    fi
fi
EOF

chmod +x /usr/local/bin/mysql-monitor.sh
echo "*/5 * * * * /usr/local/bin/mysql-monitor.sh" | crontab -

Backup Strategy

# Daily backup script
cat > /usr/local/bin/mysql-backup.sh << 'EOF'
#!/bin/bash

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d-%H%M%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Backup all databases
mysqldump -u root -p"password" --all-databases --single-transaction \
    --routines --triggers --events > "$BACKUP_DIR/all-databases-$DATE.sql"

# Compress backup
gzip "$BACKUP_DIR/all-databases-$DATE.sql"

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "$(date): Backup completed: all-databases-$DATE.sql.gz"
EOF

chmod +x /usr/local/bin/mysql-backup.sh
echo "0 2 * * * /usr/local/bin/mysql-backup.sh" | crontab -

Conclusion

MySQL error diagnosis requires understanding error messages, log analysis, and systematic troubleshooting. Key takeaways:

1. Check logs first: Error log reveals most problems
2. Verify connectivity: Ensure MySQL is running and accessible
3. Monitor performance: Slow queries and locks cause issues
4. Regular maintenance: Check and optimize tables periodically
5. Backup regularly: Corruption requires restore capability
6. Monitor replication: Catch lag before it becomes critical
7. Configure properly: Appropriate settings prevent many errors

Understanding these diagnostic techniques and implementing proactive monitoring ensures reliable MySQL operation and quick recovery when errors occur.