--- title: "How to Diagnose and Fix Common MySQL Errors on Ubuntu 24.04" description: "Fix the most common MySQL 8.0 errors on Ubuntu 24.04: failed connections, access denied, too many connections, lock timeouts, full disks and crashed tables." category: "Troubleshooting Guide" language: en source: https://cubepath.com/docs/troubleshooting-guide/mysql-errors-diagnostics-and-solutions updated: 2026-09-25 --- # How to Diagnose and Fix Common MySQL Errors on Ubuntu 24.04 MySQL error messages are short, but they almost always tell you which layer failed: the server process, the socket or network, authentication, a lock, or the storage underneath. In this tutorial you will learn where MySQL records its errors on Ubuntu 24.04 and work through the errors you are most likely to meet in production, each with a way to confirm the cause and a fix. ## Prerequisites To follow this tutorial you need: - A server running Ubuntu 24.04 LTS with MySQL 8.0 installed from the Ubuntu repositories (`mysql-server` package), for example a CubePath VPS. - A non-root user with `sudo` privileges. - A recent backup of your databases before you try any of the repair steps in Step 7. On Ubuntu, the MySQL `root` account authenticates with the `auth_socket` plugin by default, so you log in as root with `sudo mysql` and no password. The commands below use that method. MariaDB uses the same error codes for most of these cases, but its service is called `mariadb` and its paths differ slightly. ## Step 1 - Checking the service and the error log Most serious problems are recorded in the MySQL error log before users notice them. Check whether the service is running first: ```bash sudo systemctl status mysql ``` ```output ● mysql.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled) Active: active (running) since Fri 2026-09-25 08:12:40 UTC; 3h ago ``` On Ubuntu, the error log is `/var/log/mysql/error.log`. Confirm the path and read the most recent entries: ```bash sudo mysql -e "SELECT @@log_error;" sudo tail -n 50 /var/log/mysql/error.log ``` If the service fails to start, the journal usually shows the reason together with the error log: ```bash sudo journalctl -u mysql -n 50 --no-pager ``` Keep the error log open in a second terminal with `sudo tail -f /var/log/mysql/error.log` while you work through the following steps. ## Step 2 - Fixing connection errors ### ERROR 2002: Can't connect through socket ```text ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ``` The client connects to `localhost` through a Unix socket, and the socket does not exist. In almost every case the server is not running. Start it and check whether it stays up: ```bash sudo systemctl start mysql sudo systemctl status mysql ``` If it fails again, the reason is in the error log (Step 1). Frequent causes are a full disk (Step 6), a typo in a configuration file, or a memory setting larger than the server's RAM. To find configuration mistakes, validate the configuration without starting the server: ```bash sudo -u mysql mysqld --validate-config ``` If the server is running but the error persists, the client is looking for the socket in the wrong place. Compare the server's socket with the one in your application's configuration: ```bash sudo mysql -e "SELECT @@socket;" ``` ### ERROR 2003: Can't connect to MySQL server on a remote host ```text ERROR 2003 (HY000): Can't connect to MySQL server on '203.0.113.10:3306' (111) ``` Error 111 means connection refused. By default MySQL on Ubuntu listens only on `127.0.0.1`. Check where it listens: ```bash sudo ss -ltnp | grep 3306 ``` ```output LISTEN 0 151 127.0.0.1:3306 0.0.0.0:* users:(("mysqld",pid=1182,fd=23)) ``` If a remote application server really needs access, open `/etc/mysql/mysql.conf.d/mysqld.cnf`, set `bind-address` to the server's private IP, restart MySQL with `sudo systemctl restart mysql`, and allow only that client in the firewall: ```bash sudo ufw allow from 10.0.0.5 to any port 3306 proto tcp ``` Replace `10.0.0.5` with your application server's IP. Never expose port 3306 to the whole Internet. Error `(110)` instead of `(111)` means the connection timed out, which points to a firewall dropping the packets. ## Step 3 - Fixing authentication errors ### ERROR 1698: Access denied for root ```text ERROR 1698 (28000): Access denied for user 'root'@'localhost' ``` This appears when you run `mysql -u root` without `sudo` on Ubuntu. The `root` account uses `auth_socket`, which only accepts the operating system's root user. Use `sudo mysql` instead. For applications, create a dedicated user rather than changing how root authenticates. ### ERROR 1045: Access denied (using password: YES) ```text ERROR 1045 (28000): Access denied for user 'app'@'localhost' (using password: YES) ``` Either the password is wrong, or no account matches the user and host combination. MySQL identifies accounts by both, so `'app'@'localhost'` and `'app'@'%'` are different accounts with different passwords. List the accounts that exist: ```bash sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user = 'app';" ``` ```output +------+-----------+-----------------------+ | user | host | plugin | +------+-----------+-----------------------+ | app | 10.0.0.% | caching_sha2_password | +------+-----------+-----------------------+ ``` Here the account only exists for the `10.0.0.%` network, so a connection from `localhost` is refused. Create the missing account, or reset the password of the existing one: ```bash sudo mysql ``` ```sql ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'your_strong_password'; ``` Replace `your_strong_password` with a strong, unique password and update it in the application's configuration. Then check that the account has privileges on the database it needs: ```sql SHOW GRANTS FOR 'app'@'10.0.0.%'; ``` ### Resetting a lost root password If someone changed `root` to password authentication and the password is lost, `sudo mysql` no longer works. Start MySQL temporarily without privilege checks and with networking disabled so nobody else can connect: ```bash sudo systemctl stop mysql sudo install -d -o mysql -g mysql /run/mysqld sudo -u mysql mysqld --skip-grant-tables --skip-networking & ``` Connect and restore Ubuntu's default socket authentication for root. `FLUSH PRIVILEGES` reloads the grant tables so that `ALTER USER` works: ```bash mysql -u root ``` ```sql FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket; EXIT; ``` Shut down the temporary instance and start the service normally: ```bash sudo mysqladmin -u root shutdown sudo systemctl start mysql sudo mysql -e "SELECT CURRENT_USER();" ``` ```output +----------------+ | CURRENT_USER() | +----------------+ | root@localhost | +----------------+ ``` ## Step 4 - Fixing ERROR 1040: Too many connections ```text ERROR 1040 (HY000): Too many connections ``` The server reached `max_connections` (151 by default). MySQL keeps one extra connection for administrative accounts, so `sudo mysql` usually still works. Check the limit, current usage, and the historical peak: ```bash sudo mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Max_used_connections');" ``` Then see who holds the connections and what they are doing: ```bash sudo mysql -e "SELECT user, host, db, command, time, state FROM information_schema.processlist ORDER BY time DESC LIMIT 20;" ``` Two patterns are common: - Hundreds of connections in `Sleep` state: the application opens connections and does not close them, or its connection pool is larger than it needs to be. Fix the pool size in the application. Lowering `wait_timeout` also closes idle connections sooner. - Many connections running or waiting on the same query: the database is slow, requests pile up, and each one holds a connection. Raising the limit only makes the pile bigger. Find the slow query or the lock (Step 5). If you have confirmed that the load is legitimate and the server has memory to spare, raise the limit. `SET PERSIST` applies immediately and survives restarts: ```bash sudo mysql -e "SET PERSIST max_connections = 300;" ``` ## Step 5 - Fixing lock wait timeouts and deadlocks ```text ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ``` A query waited longer than `innodb_lock_wait_timeout` (50 seconds by default) for a row locked by another transaction. The other transaction is usually a long-running one, or one the application opened and never committed. While the problem is happening, find the blocking session with the `sys` schema: ```bash sudo mysql -e "SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, sql_kill_blocking_connection FROM sys.innodb_lock_waits\G" ``` ```output *************************** 1. row *************************** waiting_pid: 3121 waiting_query: UPDATE orders SET status = 'paid' WHERE id = 5512 blocking_pid: 3087 blocking_query: NULL sql_kill_blocking_connection: KILL 3087 ``` A `blocking_query` of `NULL` means the blocking session is idle inside an open transaction, a typical application bug. List transactions that have been open for more than a minute: ```bash sudo mysql -e "SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query FROM information_schema.innodb_trx WHERE trx_started < NOW() - INTERVAL 1 MINUTE;" ``` To release the lock immediately, run the `KILL` statement from the output. The killed session's transaction is rolled back: ```bash sudo mysql -e "KILL 3087;" ``` For a lasting fix, keep transactions short and make sure the application commits or rolls back on every code path. ```text ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ``` A deadlock is different: two transactions each waited for the other, and InnoDB rolled one back immediately. The application should retry the transaction. Inspect the most recent deadlock in the `LATEST DETECTED DEADLOCK` section of: ```bash sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | less ``` Frequent deadlocks usually come from transactions that update the same rows in different orders, or from updates that scan many rows because of a missing index. ## Step 6 - Fixing storage errors ### Disk full ```text ERROR 3 (HY000): Error writing file '/tmp/MYfd=12' (OS errno 28 - No space left on device) ``` Errno 28 means the filesystem is full. Check both space and inodes, then find what is using space in the data directory: ```bash df -h /var/lib/mysql /tmp sudo du -sh /var/lib/mysql/* | sort -rh | head ``` MySQL 8.0 enables binary logging by default, and binary logs (`binlog.000123` files) are often the largest item. List them: ```bash sudo mysql -e "SHOW BINARY LOGS;" ``` If the server has no replicas, or all replicas have already read past them, purge old logs. Never delete binary log files with `rm`, which leaves MySQL's index out of sync: ```bash sudo mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;" ``` To keep them from growing again, set a shorter retention period (in seconds; the default is 30 days): ```bash sudo mysql -e "SET PERSIST binlog_expire_logs_seconds = 604800;" ``` ### Packet too large ```text ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes ``` A single query or row, often an import with large `INSERT` statements or a big BLOB, exceeds `max_allowed_packet` (64 MB by default in MySQL 8.0). Raise it on the server, and on the client when importing: ```bash sudo mysql -e "SET PERSIST max_allowed_packet = 268435456;" mysql --max-allowed-packet=256M -u app -p your_database < dump.sql ``` ## Step 7 - Checking and recovering damaged tables InnoDB repairs itself automatically after a crash by replaying its redo log, so most crashes need no action. Check tables when the error log reports corruption, or when a MyISAM table reports: ```text ERROR 145 (HY000): Table './your_database/sessions' is marked as crashed and should be repaired ``` Check all tables with `mysqlcheck`: ```bash sudo mysqlcheck --all-databases --check ``` For MyISAM tables that show errors, repair them: ```bash sudo mysqlcheck --repair your_database sessions ``` `REPAIR` does not apply to InnoDB. If InnoDB is damaged so badly that MySQL crashes on startup, you can start it in forced recovery mode only to dump the data. Open the configuration file: ```bash sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` Add this line under `[mysqld]`: ```ini innodb_force_recovery = 1 ``` Start MySQL and dump everything to a file: ```bash sudo systemctl start mysql sudo mysqldump --all-databases --single-transaction --routines --events > /root/recovery-dump.sql ``` If MySQL still does not start, raise the value one step at a time up to 3. Values 4 to 6 can permanently damage data files, so use them only after copying `/var/lib/mysql` somewhere safe. Once you have a dump, remove the `innodb_force_recovery` line, and restore the data into a clean data directory or a new server. Restoring from your latest backup is often faster and more reliable. > Warning: While `innodb_force_recovery` is set, the data may be inconsistent (from value 4 upward InnoDB is also read-only). Keep applications disconnected until you have restored the data. ## Step 8 - Checking replication errors On a replica, errors stop replication silently while the server itself keeps running. Check its status: ```bash sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E 'Replica_IO_Running|Replica_SQL_Running|Last_IO_Error|Last_SQL_Error|Seconds_Behind_Source' ``` ```output Replica_IO_Running: Yes Replica_SQL_Running: No Last_IO_Error: Last_SQL_Error: Could not execute Write_rows event on table shop.orders; Duplicate entry '5512' for key 'orders.PRIMARY' Seconds_Behind_Source: NULL ``` Both threads must say `Yes`. An IO thread error is about connectivity or credentials to the source. An SQL thread error like this one means the replica's data has drifted from the source, usually because something wrote directly to the replica. Skipping the failing event hides the drift rather than fixing it. The reliable fix is to rebuild the replica from a fresh backup of the source, and to set `super_read_only = ON` on replicas so it cannot happen again. ## Conclusion You now know where MySQL logs its errors on Ubuntu 24.04 and how to handle the ones that cause most outages: a stopped server, host and password mismatches, connection exhaustion, lock contention, full disks and damaged tables. In most cases the error code plus the error log points directly at the cause. As next steps, enable the slow query log to catch performance problems early, and schedule automated `mysqldump` or physical backups that you test restoring regularly.