Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

Question

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it:

╰$ sudo service mysql restart
stop: Unknown instance:
start: Job failed to start

And this shows in "/var/log/mysql/error.log":

120415 23:01:09 [Note] Plugin 'InnoDB' is disabled.
120415 23:01:09 [Note] Plugin 'FEDERATED' is disabled.
120415 23:01:09 [ERROR] Unknown/unsupported storage engine: InnoDB
120415 23:01:09 [ERROR] Aborting

120415 23:01:09 [Note] /usr/sbin/mysqld: Shutdown complete

I've checked permissions on all the mysql directories to make sure it had ownership and I also renamed the previou ib_logs so that it could remake them. I'm just getting no where with this issue right now, after looking at google results for 2 hours.

Incidentally, I did get mysql to run by commenting out the "skip_innodb" entry in my.cnf. But I'm fairly certain that I wasn't supposed to solve this that way. — Garrett, Apr 16 '12 at 01:17
Please show all variables from my.cnf that are innodb settings — RolandoMySQLDBA, Apr 24 '12 at 21:26
The actual cause is listed in the few log entries before the ones you displayed. — danblack, Nov 14 '22 at 23:38

score 72 · Answer 1 · edited Aug 21 '17 at 13:08

72

After checking the logs I found the following error:

[ERROR] Unknown/unsupported storage engine: InnoDB

I removed these files:

rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1

at /var/lib/mysql

This resolved my problem after restart.

edited Aug 21 '17 at 13:08

Apache

294
6
19

answered May 21 '14 at 05:18

Vinay

749
5
2

7

the `rm` command is used to Remove files rather than rename them as far as i know... – Itai Ganot May 21 '14 at 07:06
6

Those files contain the *actual data* for your database. You don't want to delete them. – Stefan Lasiewski Jun 02 '14 at 20:26
3

No, the `ibdata` file contains the data (unless you have file-per-table). The ib_logfile files are the replay logs that contain the data for database-altering transactions that may have been in process when/if the database crashed. If you were able to shutdown the server successfully, deleting these log files won't hurt you. If it crashed, then you need them. But this is a valid answer. If your my.cnf file changes the innodb_log_file_size option and it no longer matches those two files, you will get the error message that the OP states. Deleting/moving so new ones can be made fixes it. – Safado Jun 30 '14 at 15:28
2

Why edits can only be made for 5 minutes is beyond me... I should have stated it is a *potential* fix for the above mentioned error. As stated by the OP, it was not the fix he needed. – Safado Jun 30 '14 at 15:40
I could not "ignore or skip" innodb as I had at hand, over 10 years of data from a customer in that ibdata file. The only option was to keep "innodb" engine selected and make that data work with the mysql server. – Ram on Rails Sep 29 '15 at 10:06
2

This solution also worked for me. No data damaged. – TCB13 Oct 31 '15 at 16:40
1

This solution worked for me as well. +1 – Paul Carlton Jan 19 '16 at 22:32
1

Got this error after running out of free disk space during mysql (re)install. Finding & removing truncated files helped. – Dallaylaen Jan 25 '16 at 20:42
2

You can always rename them, e.g. `mv ib_logfile0 ib_logfile0.bak && mv ib_logfile1 ib_logfile1.bak`. – kenorb Apr 14 '16 at 13:58
1

This works for some people and I appreciate that. However, the lack of an explanation as to how deleting those files fixes the problem has me worried. – Peter Chaula Jan 24 '21 at 18:45
I found "[ERROR] InnoDB: Missing FILE_CHECKPOINT at 45142 between the checkpoint 45142 and the end 4115531." in the log not far before the "Unknown/unsupported storage engine" error. Deleting the /var/lib/mysql/ib_logfile0 (there was no ib_logfile1) indeed allowed me to run "service mysql start" without failing. But... Data was damaged. It was only only a local dev WordPress running but that needed a reinstall. Not sure if db corruption this was the cause of the error or the result of the file deletion :/ – RavanH Jan 11 '22 at 09:27
works like a charm. – Alex May 03 '23 at 19:49
Yup, simply running out of disk space can result in the same error, if you delete these two files you might free up a little disk space (enough for the server to be able to start again) but will quickly run into the same thing. This happened to me, a simple df -h will tell you what the situation is like. – John Hunt Jul 17 '23 at 20:49

score 22 · Answer 2 · answered Apr 24 '12 at 19:03

22

If you really need skip-innodb (use case: low memory footprint), then of course you don't have to comment it out. However, if InnoDB is the default storage engine, the server will fail to start until you tell it which storage engine to use instead, e.g. default-storage-engine=myisam for MyISAM.

So, try this:

$ sudo -u mysql mysqld --skip-innodb --default-storage-engine=myisam

answered Apr 24 '12 at 19:03

Andy Mikhailenko

329
1
4

6

Try to add "default-storage-engine=myisam" to /etc/mysql/my.cnf in the _[mysqld]_ section if you want to have "skip_innodb". – pincoded Jul 30 '12 at 20:10
Works beautifully. Cheers! – Boann Nov 12 '12 at 08:11
4

What if you want innodb-support? – Nilzor Jun 05 '13 at 09:35
1

Mariadb on Centos 7 added `default-storage-engine=myisam` on file /etc/my.cnf under `[mysqld]`. That did the trick. Thx – Damodar Bashyal May 03 '17 at 05:58
Then all the db using innodb couldn't be loaded `Unknown storage engine 'InnoDB'` – TomSawyer Feb 03 '23 at 16:26

score 11 · Answer 3 · answered Apr 23 '13 at 21:13

If you're using MySQL 5.6+ and want to disable InnoDB, don't forget "--default-tmp-storage" or it won't work:

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb

You can add this to your my.cnf:

[mysqld] 
innodb=OFF 
ignore-builtin-innodb 
skip-innodb
default-storage-engine=myisam 
default-tmp-storage-engine=myisam

just to make sure it'll work.

+1, Never notice the official document. it should set `default-storage-engine` and `default-tmp-storage-engine`, thanks. — Giberno, Nov 07 '14 at 08:48

score 9 · Answer 4 · answered May 18 '16 at 16:27

Check your mysql error log.

tail -100 /var/log/mysql/error.log

If your log says (like mine did):

InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
[ERROR] InnoDB: Cannot allocate memory for the buffer pool

You don't have enough memory to use the default buffer size of 128M

Edit the config file /etc/mysql/my.cnf adding a line to specify a smaller innodb_buffer_pool_size.

# make the buffer pool smaller than 128M since we only have 1 GB of total RAM
innodb_buffer_pool_size = 16M

Save the config file, and start mysql

sudo service mysql start

Is spot on here. But could also be hanging processes of the webserver or something eating up the RAM, do check that, too. — sjas, May 08 '17 at 14:17

score 0 · Answer 5 · answered Oct 07 '22 at 16:33

I got this error on a modern MariaDB 10.5 system, because I forgot to units on the innodb control lines;

innodb_buffer_pool_size = 4096
innodb_log_file_size    = 1024

instead of

innodb_buffer_pool_size = 4096MB
innodb_log_file_size    = 1024MB

So, if you get this one, check there

score 0 · Answer 6 · answered Mar 09 '23 at 19:29

0

Check that you have enough free space on the disk.

Run this command to view available free space

df -h

I had this same error message but, once I provisioned more space mysql service was able to start normally

answered Mar 09 '23 at 19:29

elijahbee

1
1

score 0 · Answer 7 · answered Aug 30 '23 at 15:28

I had this problem, when reloading the /var/lib/mysql/ folder into a fresh install of Debian 12.

I had to remove the logfile with

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0-bak

and create a new empty file with

echo ""> /var/lib/mysql/ib_logfile0

Now the service starts, but you see a lot of errors like

Aug 30 15:15:31 test mariadbd[40673]: 2023-08-30 15:15:31 0 [ERROR] InnoDB: Page [page id: space=9154, page number=3] log sequence number 258766592934 is in the future! Current system log sequence number 254569578017.
Aug 30 15:15:31 test mariadbd[40673]: 2023-08-30 15:15:31 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

I use 10.11.3-MariaDB, so I guess, I have to set in /etc/mysql/mariadb.conf.d/50-server.cnf

[mariadb]
innodb_force_recovery=4

and restart with

systemctl restart mysql

score 0 · Answer 8 · answered Aug 14 '16 at 18:02

0

I got this error when I deleted the location I use for tmpdir. If you've recently changed your tmpdir, you might want to check that it's a valid, writable location.

answered Aug 14 '16 at 18:02

Jeff

119
7

johnshen64 · Answer 9 · 2012-04-16T04:22:02.367

-1

Try 2 more things. 1. Lower the innodb buffer pool size. 2. Edit mysql initial script and add --innodb option.

I wonder also if your package is buggy. Could you try a different minor version?

Also, I assume your mysql server got upgraded as well? Maybe that version is broken? Precise is not final yet.

edited Apr 16 '12 at 04:22

answered Apr 16 '12 at 03:17

johnshen64

5,865
24
17

Already did that, to no effect. – Garrett Apr 16 '12 at 03:53
Sorry, edited it with some things to try, more like a bad package. Try to use the mysql.com version or percona which are usually more stable. – johnshen64 Apr 16 '12 at 04:25
Also make sure the old package is completely purged, when the new is installed. – johnshen64 Apr 16 '12 at 05:07
Would purging the package completely result in lost database information? – Garrett Apr 16 '12 at 06:25
Should not, but to be safe you should always backup your DBS. – johnshen64 Apr 16 '12 at 14:25
1. Helped for me - I was running at AWS EC2 Micro instance and 128MB was simply too much. "innodb_buffer_pool_size = 16M" under the [mysqld] section of my.cnf did the trick – Nilzor Jun 05 '13 at 09:41

Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

9 Answers9

Linked