Getting error "Plugin 'InnoDB' registration as a STORAGE ENGINE failed" when starting MySQL

Question

I found many similar question on Stackoverflow but didn't get the exact error solution. My issue is when starting MySQL service on one of the Dedicated Centos 6.5 machine, I am getting error :

141018 05:13:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141018  5:13:47 [Warning] Can't create test file /var/lib/mysql/ip-184-168-73-83.lower-test
141018  5:13:47 [Warning] Can't create test file /var/lib/mysql/ip-184-168-73-83.lower-test
/usr/libexec/mysqld: Can't create/write to file '/tmp/ibkTWnhE' (Errcode: 28)
141018  5:13:48  InnoDB: Error: unable to create temporary file; errno: 28
141018  5:13:48 [ERROR] Plugin 'InnoDB' init function returned error.
141018  5:13:48 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
141018  5:13:48 [ERROR] Can't start server : Bind on unix socket: No space left on device
141018  5:13:48 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
141018  5:13:48 [ERROR] Aborting

141018  5:13:48 [Note] /usr/libexec/mysqld: Shutdown complete

141018 05:13:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Here are free command status: free -m

             total       used       free     shared    buffers     cached
Mem:          3743       3631        111          0       2705         21
-/+ buffers/cache:        905       2838
Swap:         2047          0       2047

I know its late, but I think someone may face this problem as you have. Here I guess you have file permission error. Mysql run by its own user called "mysql" which have to permission in "/var/lib/mysql" directory. Please check that directory that has write and execute permission for the user called "mysql" otherwise you have to change default user of mysql — Shahadat Hossain Khan, Feb 03 '15 at 10:36

xuri · Answer 1 · 2020-09-29T02:22:27.667

19

I have the same problems, this my solution:

Add more RAM to the server
Decrease the value of innodb-buffer-pool size in the config file:

sudo nano /etc/mysql/my.cnf
```
innodb_buffer_pool_size = 10M
```

After save /etc/mysql/my.cnf.

Restart mysql service:

sudo service mysql restart

exit

edited Sep 29 '20 at 02:22

answered Apr 10 '15 at 08:14

xuri

792
6
18

Add swap partitions on small memory machines https://xuri.me/2016/10/25/add-swap-on-ubuntu.html – xuri May 10 '18 at 08:41

score 19 · Answer 2 · answered Apr 13 '20 at 12:23

19

I fixed this issue by following:

Login to server via SSH with root access.
Navigate to /var/lib/mysql.
If you see log files like, ib_logfile0 and ib_logfile1, rename or move them to some other folder.
Stop and start the MySQL service by running sudo service mysql stop and sudo service mysql start

I hope this helps. Thanks

answered Apr 13 '20 at 12:23

Minhaj Javed

595
5
18

1

Thanks for the reply! this solved all the problems. so can you explain why the log file is crashing the server like this so it doesn't happen again in the future? That way we can prevent it. – Fazıl Akbulut Nov 27 '21 at 09:05

score 11 · Answer 3 · edited Feb 24 '22 at 17:20

11

This is frequently occurred issue. Do following -

delete/move out these "aria_log_contro, ib_logfile0, ib_logfile1, ib_data1" files from location "..\xampp\mysql\data" and also from "..\xampp\mysql\backup".
stop and start apache server and mysql form xampp control panel

This should fix the issue; actually it worked for me.

NOTE: THIS IS GOING TO RESET THE DB IN MANY CASES BE VERY CAREFUL

edited Feb 24 '22 at 17:20

Mike Q

5,800
4
48
56

answered Nov 03 '17 at 14:22

Avinash Thombre

186
1
5

3

I would like to add a warning to this method for people who don't know what these files contain. Removing these files will probably seriously corrupt your database and you won't be able to recover it. ib_logfile contains redo logs (up to my knowledge) which are essential for innodb tables. – papa zulu Jan 17 '20 at 17:32

score 7 · Answer 4 · answered May 12 '20 at 04:53

7

Changing the values of innodb_buffer_pool_size and innodb_log_file_size didn't work for me.

Moving ib_logfile0 and ib_logfile1 files didn't help either.

What did help was:

> service mysql stop

Edit my.cfg and add innodb_force_recovery = 1

> service mysql start
> service mysql stop

Comment the innodb_force_recovery = 1 line.

> service mysql start

And voilá. (I should note that I have no idea if this involves any data loss or not)

answered May 12 '20 at 04:53

Petr Nagy

505
3
15

where is my.cnf located? – Pangamma Apr 15 '22 at 18:32
1

@Pangamma https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location – Petr Nagy Apr 18 '22 at 04:45

Aamir · Answer 5 · 2022-03-13T12:12:47.387

4

After hours and hours of trying different solutions, I finally came across the solution here .

Which itself gets solution from the official MariaDB forum.

And the solution is to delete ib_logfile0 from /var/lib/mysql

Worked for MariaDB in my case.

EDIT:

Don't forget to restart Mysql with systemctl restart mysqld.service or systemctl restart mariadb.service (incase of using MariaDB).

edited Mar 13 '22 at 12:12

answered Nov 10 '21 at 22:25

Aamir

51
4

1

Same solution worked for me – aslamdoctor Jan 25 '22 at 03:11
I am glad that it did :) – Aamir Feb 07 '22 at 16:24

score 3 · Answer 6 · edited Jan 29 '19 at 02:52

3

Although late but putting answer here so that solution that helped me can help someone. I took following steps:

Added more RAM to sever
Decrease the value of innodb-buffer-pool size
Set innodb_log_file_size
Restart mysql

Example of addition to my.cnf:

innodb_buffer_pool_size = 10M
innodb_log_file_size = 1000M

edited Jan 29 '19 at 02:52

calvinf

3,645
3
26
39

answered Feb 02 '18 at 07:20

Ritesh Kumar

2,095
2
19
32

1

@Walter your config file in `/etc/mysql/my.cnf` – Anatole Lucet Aug 17 '19 at 13:44

score 2 · Answer 7 · edited Apr 13 '17 at 12:42

I was getting below mysql error log:-

[Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using Linux native AIO
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
InnoDB: Completed initialization of buffer pool
InnoDB: Fatal error: cannot allocate memory for the buffer pool
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
[Note] /usr/libexec/mysqld: Shutdown complete

I found out there are two solutions which are:-

1)Set innodb_log_file_size equal to the actual size of the existing InnoDB log files. To see what size of innoDB log allocated, login mysql and enter following cmd:-

SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';

Expected result example:- 5242880

After that, insert that value in my.cnf:- vi /etc/my.cnf

 innodb_log_file_size =5242880

2)Rename or move both the ./ib_logfile0 and ./ib_logfile1 files, and then start the MySQL server.This normally will be located at /var/lib/mysql. After start mysql, it create new innoDB log file and restore possible half-written data from the file of .ibd.

The expexted mysql log example:-

InnoDB: Database physically writes the file full: wait...
161216  9:58:54  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
161216  9:58:54 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
161216  9:58:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
161216  9:58:54  InnoDB: Waiting for the background threads to start
161216  9:58:55 InnoDB: 5.5.50 started; log sequence number 1589772
161216  9:58:55 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
161216  9:58:55 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
161216  9:58:55 [Note] Server socket created on IP: '0.0.0.0'.
161216  9:58:55 [Note] Event Scheduler: Loaded 0 events
161216  9:58:55 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.50'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi

References:- JUSTIN KULESZA (2011). MySQL: Failed Registration of InnoDB as a Storage Engine. Available at: https://spin.atomicobject.com/2011/05/09/mysql-failed-registration-of-innodb-as-a-storage-engine/.

RolandoMySQLDBA (2014). MySQL my.cnf: innodb_log_file_size is missing. Available at: https://dba.stackexchange.com/questions/75688/mysql-my-cnf-innodb-log-file-size-is-missing/158325#158325

Changing the Number or Size of InnoDB Redo Log Files. Available at: http://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html

score 0 · Answer 8 · edited Nov 05 '20 at 23:24

Nothing was working with reinstalls, removes, and others (I had no data to keep, not a fix; more of a data destruction process, big caveat there):

1005  mysql_install_db
1007  /usr/bin/mysqld_safe --datadir='/var/lib/mysql
1008  /usr/bin/mysqld_safe --datadir='/var/lib/mysql' (^z)
1009  bg
1010  mysql
1011  mysql_secure_installation
1012  mysql
1013  mysql -p

And viola; actually usable database.

score 0 · Answer 9 · answered Jul 29 '21 at 15:47

0

for me the solution was to change the config to add

innodb_use_native_aio = 0

in mysql config

answered Jul 29 '21 at 15:47

Skiv

1
1

score -1 · Answer 10 · answered May 03 '22 at 18:28

-1

If you are using MyIsam instead of Innodb

You can change the my.ini from

default-storage-engine=Innodb

to

default-storage-engine=MyIsam

answered May 03 '22 at 18:28

Guillermo

111
2

Getting error "Plugin 'InnoDB' registration as a STORAGE ENGINE failed" when starting MySQL

10 Answers10

Linked