Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

Question

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log), i have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).
the DB on S1 was using one data file (ibdata). after dumping the DB to S2 i set innodb_file_per_table=1. this made every table to have its own ibd file. now everything went fine and smoothly.
but after restarting mysql on S2, i faced a problem with getting this error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB and when i try to show engines

show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

innodb is not listed.
in error log i can see this:

InnoDB: Database physically writes the file full: wait...
InnoDB: Cannot initialize created log files because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shut down
InnoDB: normally after that.
111016  8:24:11 [ERROR] Plugin 'InnoDB' init function returned error.
111016  8:24:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
111016  8:24:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=S2-relay-bin' to avoid this problem.

I have tried to delete ib_logfiles but this didn't work as well.
anybody faced such issue before?? any idea is highly appreciated
Thanks

Bloodstream

try youdidwhatwithtsql.com/unknown-table-engine-innodb/760 – Helios

Answer 1

You can delete the InnoDB log files in your mysql data directory called ib_logfile0 and ib_logfile1. Don't delete the InnoDB data file (ibdata1) though.

After that, InnoDB will try to recover after you restart mysqld.

look at main log file:

120413 17:34:47  InnoDB: Initializing buffer pool, size = 64.0M
120413 17:34:47  InnoDB: Completed initialization of buffer pool
120413 17:34:47  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 32 MB
InnoDB: Database physically writes the file full: wait...
120413 17:34:48  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 32 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120413 17:34:49  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.

Scott

Answer 2

Had similar issue after server hanging and restart.

Data is okay - the error message is very misleading.

Stop MySQL service, delete the log files (ib_logfile*) from /var/lib/mysql, and start MySQL service back. Just make 100% sure that MySQL is actually down when deleting log files.

Mistrial

Answer 3

I think configuration of mysql for innodb if you set innodb_buffer_pool_size = 2G, innodb will not work.

Usually it gives an error

"Unknown table engine 'InnoDB".

if you select table with innodb system try innodb_buffer_pool_size = 1G.

Orthoepy

Answer 4

Did you compile mysql with innodb? If you did, you should have gobs of lines referring to it when you run this:

strings `which mysqld` | grep innodb

Jonell

yes i did, and i have gotten a lot of line upon running your command above... also innodb engine was there and working fine until i restarted mysql!! – Bloodstream

Answer 5

I encountered the same issue. Nikl's answer helped me find the issue in the main log file. InnoDB was unable to allocate the required memory at startup. After cleaning some other processes everything started properly.

Logs showed the following:

160219 9:20:23 InnoDB: Error: cannot allocate 12884918272 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 49601872 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds...

Kodak