Error innodb unable to lock ibdata1 error 11

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.

Conclusion

Database errors occurs due to a number of reasons. And they end up crippling your servers. Today, we’ve seen the possible causes for ‘InnoDB: Unable to lock ./ibdata1, error: 11’ and how our Support Engineers fix them.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

InnoDB: Unable to lock ./ibdata1, error: 11

На одном сервере часто работают сайты использующие базы данных таблицы которых имеют различный тип: MyISAM и InnoDB. Таблицы InnoDB гораздо более эффективны с точки зрения оптимизации приложений, но работать с ними на уровне сервера баз данных сложнее. В частности, восстановление таблиц при их повреждении является довольно трудозатратной процедурой.

Лог всех данных касающихся работы MySQL пишется в один файл, в нем иногда можно видеть записи следующего типа:

Как исправить ошибку InnoDB: Unable to lock ./ibdata1, error: 11

Часто обозначенные выше ошибки можно увидеть в логе когда сервер баз данных не запускается — часто после перезагрузки или сбоя питания.

Из записи Check that you not already have another mysqld process явно следует, что, вероятно, на сервере по какой-то причине запущены или пытаются запуститься 2 процесса MySQL.

Проверяем так ли это

Если обнаружен какой-либо процесс можно отледить его используя strace или завершить стандартным способом или через kill.

Ошибка InnoDB: Unable to lock /path/to/ibdata1, error: 11 обычно соседствует с первой — ее причина в невреных правах на некоторые InnoDB таблицы в /var/lib/mysql или другом каталоге являющемся docdir для сервера баз данных

Исправить ошибку можно выполнив несколько простых команд.

Останавливаем MySQL — часто он уже остановлен и не запускается.

Перемещаем файл ibdata1, в котором хранятся все данные и индексы InnoDB в сторонний каталог делая бэкап

mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak

Копируем файл обратно восстанавливая права.

cp -a /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1

Также скорректировать права на таблицы можно вручную перейдя в /var/lib/mysql и выполнив chown mysql: на все базы данных и таблицы, для которых установлен другой пользователь (часто это root, такое случается когда разработчики редактируют таблицы от имени root). Таблицы с некорректными правами могут успешно обрабатываться, проблемы обычно дают о себе знать после перезапуска MySQL.

MySQL после корректировки прав должен корректно запуститься, также при этом необходимо проверить логи на предмет поврежденных таблиц, при их наличии — восстановить.

Источник

MySQL InnoDB: Unable to lock ./ibdata1 error: 11 #3057

After upgrading Docker on Debian Buster running on Xen to Docker version 19.03.4, build 9013bf583a the MySQL container fails to start without errors.

I have connected to the container with a view to debugging the issue but I only got as far as stopping MySQL and then the container exits:

Further information (where applicable):

• Output of git diff origin/master , any other changes to the code? If so, please post them.

The output of the requested diff appears to contain hashed passwords? The key change is that one bug fix from a while ago was added manually:

• All third-party firewalls and custom iptables rules are unsupported. Please check the Docker docs about how to use Docker with your own ruleset. Nevertheless, iptabels output can help us to help you: iptables -L -vn , ip6tables -L -vn , iptables -L -vn -t nat and ip6tables -L -vn -t nat

This problem isn’t firewall related.

• Check docker exec -it $(docker ps -qf name=acme-mailcow) dig +short stackoverflow.com @172.22.1.254 (set the IP accordingly, if you changed the internal mailcow network) and docker exec -it $(docker ps -qf name=acme-mailcow) dig +short stackoverflow.com @1.1.1.1 — output? Timeout?

This problem isn’t DNS related.

• Please take a look at the official documentation.

The text was updated successfully, but these errors were encountered:

Источник

How to resolve “InnoDB: Unable to lock ./ibdata1, error: 11”

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:

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.

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.

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.

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Fixya Cloud

Scenario / Questions

I have a simple webserver (Debian 6.0 x86, DirectAdmin with 1 GB of memory and still 10 GB free space, mySQl version 5.5.9), however the mySQL server keeps crashing and I need to kill all mySQL processes to be able to restart it again.

I have found a topic on the mySQL website here however there’s no solution for it.

Any ideas anyone?

Find below all possible solutions or suggestions for the above questions..

Suggestion: 1:

another approach from one comment in the same blog:

Then kill it (the process number)

e.g. kill -9 13498

Then try to restart MySQL again.

Suggestion: 2:

with ubuntu 14.04. I’m experiencing this problem when I try to restart via

Suggestion: 3:

The most common cause of this problem is trying to start MySQL when it is already running.

To resolve it, kill off any running instances of MySQL and then restart it using your normal startup scripts, e.g. service mysql start .

Don’t attempt to start MySQL manually when using distribution-packaged versions unless you are prepared for a world of hurt.

Suggestion: 4:

Solution

make a copy of the original files (ibdata1, ib_logfile0, ib_logfile1…).

Suggestion: 5:

This helped me to solve it:

Delete all ibdata files and let mysql create them.

go to mysql library:

move innodb files somewhere in case you need it:

Suggestion: 6:

Came here from googling of the same repeating error but with error code 13 ( InnoDB: Unable to lock ./ibdata1, error: 13 ). After trying lot of solutions around the internet, invented one that helped me (apparmor!)

Add these lines to the config /etc/apparmor.d/usr.sbin.mysqld (and reload apparmor and mysql of course):

The main differences between often solutions: two rules (for dir itself and for all files inside, note the double ** ) and k option to allow mysql to lock files.

Источник

Vesta Control Panel — Forum

Reboot and Mysql Problem

Reboot and Mysql Problem

Post by muhammed332 » Mon Jan 19, 2015 10:09 am

When I was reboot vps after mysql problem

VPS Spec.
512MB Ram
20GB SSD Disk
Amsterdam 3
Ubuntu 14.04 x64

Re: Reboot and Mysql Problem

Post by joem » Wed Jan 21, 2015 6:33 am

muhammed332 wrote: When I was reboot vps after mysql problem

VPS Spec.
512MB Ram
20GB SSD Disk
Amsterdam 3
Ubuntu 14.04 x64

Re: Reboot and Mysql Problem

Post by muhammed332 » Wed Jan 21, 2015 6:44 am

muhammed332 wrote: When I was reboot vps after mysql problem

VPS Spec.
512MB Ram
20GB SSD Disk
Amsterdam 3
Ubuntu 14.04 x64

Re: Reboot and Mysql Problem

Post by melkerman » Wed Jan 21, 2015 3:14 pm

I have the exact same problem. After reboot, MySQL is trying to start when it is already running. I have to manual stop and start mysql from terminal. And then everything works again.

VPS DigitalOcean
2 GB Ram
40GB SSD Disk
Ubuntu 14.04 x64 + Vesta Install, nothing else.

Re: Reboot and Mysql Problem

Post by skurudo » Wed Jan 21, 2015 3:55 pm

1. stop satan-oracle-sql machine

Re: Reboot and Mysql Problem

Post by muhammed332 » Wed Jan 21, 2015 5:46 pm

skurudo wrote: Guys, try this:

1. stop satan-oracle-sql machine

Re: Reboot and Mysql Problem

Post by muhammed332 » Wed Jan 21, 2015 5:50 pm

Im try it on ubuntu 12.04 x64.
No any problem on ubuntu 12.04 x64

But 14.04 x64 Mysql Problem
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

Any ideas for this issue?

Re: Reboot and Mysql Problem

Post by skurudo » Thu Jan 22, 2015 5:18 pm

Re: Reboot and Mysql Problem

Post by skurudo » Thu Jan 22, 2015 5:23 pm

Re: Reboot and Mysql Problem

Post by skurudo » Thu Jan 22, 2015 6:33 pm

Источник

• I understand that not following below instructions might result in immediate closing and deletion of my issue.
• I have understood that answers are voluntary and community-driven, and not commercial support.
• I have verified that my issue has not been already answered in the past. I also checked previous issues.

After upgrading Docker on Debian Buster running on Xen to Docker version 19.03.4, build 9013bf583a the MySQL container fails to start without errors.

mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11                                                                                                                 
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB d                                                                   ata or log files.                                                                                                                                                                                               
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [Note] InnoDB: Unable to open the first data file                                                                                                                  
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Operating system error number 11 in a file operation.                                                                                              
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Error number 11 means 'Resource temporarily unavailable'                                                                                           
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/li                                                                   brary/operating-system-error-codes/                                                                                                                                                                             
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Cannot open datafile './ibdata1'                                                                                                                   
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data fil                                                                   es to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new                                                                    ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be                                                                    careful: do not remove old data files which contain your precious data!                                                                                                                                         
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file                                                                                        
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [Note] InnoDB: Starting shutdown...                                                                                                                                
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] Plugin 'InnoDB' init function returned error.                                                                                                              
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.                                                                                                   
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] Unknown/unsupported storage engine: InnoDB                                                                                                                 
mysql-mailcow_1      | 2019-10-19  7:51:18 0 [ERROR] Aborting 

I have connected to the container with a view to debugging the issue but I only got as far as stopping MySQL and then the container exits:

 docker-compose exec mysql-mailcow /bin/bash
# service mysql stop
 * Stopping MariaDB database server mysqld       

Further information (where applicable):

Further notes:

• Output of git diff origin/master, any other changes to the code? If so, please post them.

The output of the requested diff appears to contain hashed passwords? The key change is that one bug fix from a while ago was added manually:

 git status
On branch master
Your branch is ahead of 'origin/master' by 63 commits.
  (use "git push" to publish your local commits)

Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git checkout -- <file>..." to discard changes in working directory)

        modified:   data/web/inc/functions.inc.php

Untracked files:
  (use "git add <file>..." to include in what will be committed)

        .swp

no changes added to commit (use "git add" and/or "git commit -a")

git diff data/web/inc/functions.inc.php
diff --git a/data/web/inc/functions.inc.php b/data/web/inc/functions.inc.php
index 93b8d968..2c1a38dc 100644
--- a/data/web/inc/functions.inc.php
+++ b/data/web/inc/functions.inc.php
@@ -714,9 +714,11 @@ function is_valid_domain_name($domain_name) {
                return false;
        }
        $domain_name = idn_to_ascii($domain_name, 0, INTL_IDNA_VARIANT_UTS46);
-       return (preg_match("/^([a-zd](-*[a-zd])*).(([a-zd](-*[a-zd])*))*$/i", $domain_name)
+       //return (preg_match("/^([a-zd](-*[a-zd])*).(([a-zd](-*[a-zd])*))*$/i", $domain_name)^M
+       return (preg_match("/^([a-zd](-*[a-zd])*)(.([a-zd](-*[a-zd])*))*$/i", $domain_name)^M
                   && preg_match("/^.{1,253}$/", $domain_name)
-                  && preg_match("/^[^.]{1,63}.([^.]{1,63})*$/", $domain_name));
+                  //&& preg_match("/^[^.]{1,63}.([^.]{1,63})*$/", $domain_name));^M
+                  && preg_match("/^[^.]{1,63}(.[^.]{1,63})*$/", $domain_name));^M
 }
 function set_tfa($_data) {

• All third-party firewalls and custom iptables rules are unsupported. Please check the Docker docs about how to use Docker with your own ruleset. Nevertheless, iptabels output can help us to help you: iptables -L -vn, ip6tables -L -vn, iptables -L -vn -t nat and ip6tables -L -vn -t nat

This problem isn’t firewall related.

• Check docker exec -it $(docker ps -qf name=acme-mailcow) dig +short stackoverflow.com @172.22.1.254 (set the IP accordingly, if you changed the internal mailcow network) and docker exec -it $(docker ps -qf name=acme-mailcow) dig +short stackoverflow.com @1.1.1.1 — output? Timeout?

This problem isn’t DNS related.

General logs:

• Please take a look at the official documentation.