ERROR (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
This is a commonly seen error in database driven sites. The same error can show up when a connection or restart for MySQL server is attempted via commandline.
A quick check of the log files may reveal that the actual error faced by MySQL server is “InnoDB: Unable to lock ./ibdata1, error: 11”.
In our role as Outsourced Tech Support for web hosting providers, database errors are one major category of issues we resolve for the customers.
What causes “InnoDB: Unable to lock ./ibdata1, error: 11”
We’ve come across various reasons why the error “InnoDB: Unable to lock ./ibdata1, error: 11” shows up in MySQL error logs.
1. Duplicate process
Most often, an improper MySQL service restart is the main culprit. If a previously running process was not properly shut down and a process restart is attempted, you get this error.
A running MySQL process locks the MySQL socket and data files, and the error logs show the message:
InnoDB: Unable to lock ./ibdata1, error: 11 InnoDB: Check that you do not already have another mysqld process
2. Corrupt data file
ibdata1 file in the MySQL data directory contains the shared tablespace that stores InnoDB’s internal data. The data stored in this file includes:
a. data dictionary (metadata of tables)
b. change buffer
c. doublewrite buffer
d. undo logs
In very busy database servers, over time the ibdata1 grows into huge size. This can lead to service crash or data corruption, causing MySQL errors.
InnoDB: Unable to open the first data file InnoDB: Error in opening ./ibdata1 InnoDB: Operating system error number 11 in a file operation.
3. Lack of disk space
MySQL uses tmp directories to store intermittent data while processing queries. When this tmp folder or disk space gets full, or inode usage exceeds limit, MySQL throws errors.
4. File system errors
File system errors such as I/O errors or read-only file system, can cause errors to pop up in various services, including MySQL.
5. Permission and ownership
The MySQL data directory and socket file require specific permission and ownership, for the service to work fine. If there is any mismatch in these parameters, MySQL would be unable to access the data files.
6. AppArmor configuration
AppArmor is a security module to bring restrictions on services by configuring custom profiles. Any corruption or misconfiguration in these profiles or module can lead to InnoDB error 11.
7. Slow queries
Database driven website software usually involves a lot of MySQL queries. Many of these queries can be complex or slow, clogging the resources allocated to MySQL server.
Such resource locks can lead to incomplete MySQL server restarts and inability to allocate resources for the other queries or processes.
InnoDB: Unable to open the first data file InnoDB: Error in opening ./ibdata1 InnoDB: Operating system error number 11 in a file operation. InnoDB: Error number 11 means 'Resource temporarily unavailable'.
How to fix “InnoDB: Unable to lock ./ibdata1, error: 11”
When we notice a MySQL error in a website or server, the first thing we do is to examine the error logs. The ‘Error’ entries in the log during that time period is checked.
The lines that follow the Error “InnoDB: Unable to lock ./ibdata1, error: 11” gives us hints about the issue. We then debug the issue in these ways.
1. Remove hung processes
When the error is obtained during MySQL service restart, we first check if there is an already running process. At times there may be some scripts which keep on restarting MySQL.
An unexpected server restart can also cause hung or stuck process in the server. The stuck process is identified and killed. Then the MySQL server is restarted and confirmed to be running fine.
2. Restore data directory
In cases where the data directory or ibdata1 file is corrupt, we recreate and restore them. The permissions and ownership of these files and folders are updated to the desired parameters.
3. Filesystem checks
The server logs are investigated and filesystem checks are performed to identify errors related to bad sectors or disk space issues.
With our periodic server audits, we proactively detect such file system issues in the servers we manage. This enables us to take prompt actions to prevent a server downtime.
4. Fix AppArmor configuration
In cases where the issue is caused due to AppArmor profiles being corrupt, we recreate the profiles and fix the related configuration issues.
5. Debug slow queries
To detect the queries that are taking up the MySQL resources, we configure slow query logs in the servers we manage. These logs are examined during our periodic audits.
From these logs, it is easy to pinpoint the queries that are clogging the resources. Fixing these slow query logs helps to avoid overheads and related errors in MySQL service.
0 Comments