32

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
Paulo Boaventura
  • 1,365
  • 1
  • 9
  • 29
Ritesh Kumar
  • 2,183
  • 2
  • 20
  • 32
  • 2
    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
  • If your run `perror 28`, you don't need to guess. `perror` is part of mysql/mariadb for translating error codes to messages. – danblack Nov 15 '22 at 03:28

13 Answers13

42

This error also happens when your Database data is corrupt. You may fix this issue by moving your Db data files (ib_logfile0 and ib_logfile1) mentioned below to another location. ib_logfile0 and ib_logfile1 are system tablespace for the InnoDB infrastructure. These files contains several classes for information vital for InnoDB. You may read about these files here.

Before following below steps please keep a copy of files (ib_logfile0 and ib_logfile1) so you may restore your data in case it is lost:

Follow below steps:

  • Login to server via SSH with root access.
  • Navigate to /var/lib/mysql.
  • If you see 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

These files will be recreated after you restart the server and the issue will be fixed hopefully.

Thanks

Naeem Ul Wahhab
  • 2,465
  • 4
  • 32
  • 59
Minhaj Javed
  • 913
  • 8
  • 20
  • 3
    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
  • I'll buy you a beer man – Manchineel Aug 14 '22 at 13:56
  • Those files get regeneated of restarting mysql, so I don't think so its a reliable solution. – Imran Zahoor Sep 16 '22 at 16:38
  • 1
    This is the most dangerous upvoted answer I've seen. I guess the users that [lose their data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991) by following bad answers like this and ASSUME every Innodb error is corruption don't come back here to comment/down vote. Read the error message people. Even if you don't, copy the log files and don't remove them so at least you can go back when you realise how broken this answer is. Error 28, `perror 28` - no space on define isn't fixed by deleting InnoDB redo logs, its by creating more space. – danblack Nov 14 '22 at 23:00
  • Thanks! My database failed with the same error message, and renaming ib_logfile0 and ib_logfile1 fixed the issue. I didn't notice any data loss (at least the data required by the application to work is still in the database files). Still, I wouldn't change those files without a good backup. – freeAR Jan 07 '23 at 04:46
  • Thanks! Reading the comments though, it seems I took a risky move but I haven't experienced any data loss. I backed up (copied the files and pasted them somewhere) then renamed the files as ib_logfile0_old and ib_logfile1_old and it resolved the issue upon restarting xampp. – allan Jul 18 '23 at 10:35
21

I have the same problems, this my solution:

  1. Add more RAM to the server

  2. 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
xuri
  • 840
  • 7
  • 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
  • Changing the RAM amount doesn't fix "OS error code 28: No space left on device", which is the disk storage ran out of space. – danblack Nov 14 '22 at 23:01
10

This is frequently occurred issue. Do following -

  1. 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".
  2. 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

Mike Q
  • 6,716
  • 5
  • 55
  • 62
Avinash Thombre
  • 194
  • 1
  • 5
  • 4
    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
  • The error of the OP is a out of space error. Deleting data files isn't a solution to this. It would be most helpful if this answer was deleted to avoid users taking action that destroys their data in a case where its easily recoverable. – danblack Nov 14 '22 at 23:04
6

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)

Petr Nagy
  • 490
  • 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
  • There probably was data loss or you where exceptionally luck. The error was an out of space error. Changing `innodb_buffer_pool_size` only affects memory. `innodb_log_file_size` doesn't downsize the file size. Users are [losing their data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991) by following this advice. Please remove the answer. – danblack Nov 14 '22 at 23:14
  • @danblack I think you have misread my answer. – Petr Nagy Nov 16 '22 at 03:44
2

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

  1. Added more RAM to sever
  2. Decrease the value of innodb-buffer-pool size
  3. Set innodb_log_file_size
  4. Restart mysql

Example of addition to my.cnf:

innodb_buffer_pool_size = 10M
innodb_log_file_size = 1000M
calvinf
  • 3,754
  • 3
  • 28
  • 41
Ritesh Kumar
  • 2,183
  • 2
  • 20
  • 32
  • 1
    @Walter your config file in `/etc/mysql/my.cnf` – Anatole Lucet Aug 17 '19 at 13:44
  • Changing the ram amount or log file size wouldn't help the out of space issue you had 4 years prior to this answer. I'm assuming the error message prior to this was different. – danblack Nov 14 '22 at 23:16
2

After few unsuccess hours, i checked the disk space... and was full...

user3781113
  • 51
  • 3
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32254754) – toptecshare Jul 21 '22 at 01:35
  • 1
    This is actually the only correct reading of "InnoDB: Error: unable to create temporary file; errno: 28". Upvoting because of that (and the other broken answers here). But do provide more analysis next time. – danblack Nov 14 '22 at 23:23
1

In my case, I needed to restart mysql using innodb_force_recovery as described here: https://serverfault.com/a/592797/519751

mcaleaa
  • 451
  • 5
  • 9
0

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

Community
  • 1
  • 1
user3871474
  • 17
  • 2
  • This "mmap(137363456 bytes) failed; errno 12" an out of memory. It should of been a new question as it doesn't answer the one asked. – danblack Nov 14 '22 at 23:17
  • 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 <<<<--------------it seems that space was full – user3871474 Nov 16 '22 at 00:13
  • Yes, that was the question. Why did you answer with your own different log message? – danblack Nov 16 '22 at 01:53
  • yeah, it seems different errors, the ones that been asked was regarding full space and mine was " Fatal error: cannot allocate memory for the buffer pool". i just want to share it once the space has been fixed and "cannot allocate memory" come up later. – user3871474 Nov 17 '22 at 03:21
0

I got this error:

[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[Note] InnoDB: Starting shutdown...
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

After restoring a file backup of a crashed server and trying to start MySQL/MariaDB. Embarrassingly, it was just because I forgot to change the owner and group of the restored folder to mysql from root. So check file permissions and owners/groups.

georgiecasey
  • 21,793
  • 11
  • 65
  • 74
-1

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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
frustrated.
  • 1
  • 2
-1

for me the solution was to change the config to add

innodb_use_native_aio = 0

in mysql config

Skiv
  • 11
  • 2
-1

Just move this log file (ib_logfile0) to some other place for safer side and start the mysql services it worked for me.

Rahul Sharma
  • 1
  • The error here, "InnoDB: Error: unable to create temporary file; errno: 28", is an out of space error. Moving one file an risking breaking your database isn't a good tradeoff. A restart would create a ib_logfile0 of the same size, so doesn't solve the space issue. Please delete this misleading answer. Users are [losing their data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991) by following this advice rather than reading the error message. – danblack Nov 14 '22 at 23:26
-1

I also met the same issue when restore a backup set made via innobackupex to a new instance. Finally ,the root cause is innodb_log_file_size doesn't match original instance that take the backup, and fix steps by steps like below:

  1. Get innodb_log_file_size value from oragin instance via below command:

    mysql uroot -ppasswd -NBe "show global variables like 'innodb_log_file_size';"

  2. Modify the /etc/my.cnf of new instance with last get value:

    vim /etc/my.cnf

  3. Restart mysqld via :

    systemctl restart mysqld

blylei
  • 1
  • 3