Mysql – Unable to start thesql slave due to the error: Unknown table engine ‘InnoDB’

MySQL

I had to install my mysql slave server due to storage issue. I have synced my master disk to the slave disk. Now i'm not able to start my slave server (4GB RAM) due to the error:
Unknown table engine 'InnoDB'

I can see that:

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.00 sec)

I went over some similar questions, and tried to do as recommended. Nothing helped so far. Here is what i've tried:

Delete/rename/move ib_logfile
Check if skip-innodb is enabled (was disabled).
Edit innodb configuration settings

Here is my current settings.

cat my.cnf | grep inno
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so
#skip-innodb
#innodb_io_capacity=400
#innodb_support_xa=0
innodb_additional_mem_pool_size = 50M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:200G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:10G;ibdata8:10G;ibdata9:20G;ibdata10:10G;ibdata11:10G;ibdata12:10G;ibdata13:5G;ibdata14:5G;ibdata15:20G;ibdata16:10G;ibdata17:20G;ibdata18:20G;ibdata19:20G:autoextend
innodb_autoextend_increment = 1000
innodb_data_home_dir = /data/mysql/
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 500M
innodb_log_files_in_group = 2
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

Here is my data folder:

ll /data
-rw-r--r--  1 mysql mysql    0 Dec 27  2013 i_am_new_data
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile0.bak
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile1.bak
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile2.bak
-rw-r--r--  1 mysql mysql  22K Mar 22 03:53 my.cnf
drwxr-xr-x 13 mysql mysql 4.0K Mar 22 03:56 mysql
drwxr-xr-x  2 mysql mysql 4.0K Dec 27  2013 recovering
drwxr-xr-x  2 mysql mysql    6 Mar 22 03:56 tmp

Here is my /data/mysql folder:

   drwx------ 2 mysql mysql   19 Dec 28  2013 default
    -rw-rw---- 1 mysql mysql  12G Mar 22 01:04 ibdata1
    -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile0
    -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile1
    -rw-rw---- 1 mysql mysql   76 Mar 22 04:16 master.info
    drwx------ 2 mysql mysql 4.0K Dec 28  2013 mysql
    -rw-rw---- 1 mysql mysql 1.1G Mar 12 07:19 mysql-bin.000314
    -rw-rw---- 1 mysql mysql 1.1G Mar 14 17:56 mysql-bin.000315
    -rw-rw---- 1 mysql mysql 1.1G Mar 17 09:53 mysql-bin.000316
    -rw-rw---- 1 mysql mysql 602M Mar 19 12:34 mysql-bin.000317
    -rw-rw---- 1 mysql mysql  18M Mar 19 13:18 mysql-bin.000318
    -rw-rw---- 1 mysql mysql  125 Mar 19 13:18 mysql-bin.000319
    -rw-rw---- 1 mysql mysql 313M Mar 20 08:45 mysql-bin.000320
    -rw-rw---- 1 mysql mysql 3.8M Mar 20 08:58 mysql-bin.000321
    -rw-rw---- 1 mysql mysql  125 Mar 20 08:58 mysql-bin.000322
    -rw-rw---- 1 mysql mysql 765M Mar 22 01:04 mysql-bin.000323
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:20 mysql-bin.000324
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:21 mysql-bin.000325
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:33 mysql-bin.000326
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-bin.000327
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-bin.000328
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:10 mysql-bin.000329
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:13 mysql-bin.000330
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:15 mysql-bin.000331
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:47 mysql-bin.000332
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:53 mysql-bin.000333
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:56 mysql-bin.000334
    -rw-rw---- 1 mysql mysql  106 Mar 22 03:56 mysql-bin.000335
    -rw-rw---- 1 mysql mysql  399 Mar 22 03:56 mysql-bin.index
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:18 mysql-relay-bin.000001
    -rw-rw---- 1 mysql mysql 173K Mar 22 02:18 mysql-relay-bin.000002
    -rw-rw---- 1 mysql mysql 5.8M Mar 22 02:20 mysql-relay-bin.000003
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:20 mysql-relay-bin.000004
    -rw-rw---- 1 mysql mysql  49K Mar 22 02:21 mysql-relay-bin.000005
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:22 mysql-relay-bin.000006
    -rw-rw---- 1 mysql mysql 564K Mar 22 02:33 mysql-relay-bin.000007
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:35 mysql-relay-bin.000008
    -rw-rw---- 1 mysql mysql 116K Mar 22 02:36 mysql-relay-bin.000009
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-relay-bin.000010
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:10 mysql-relay-bin.000011
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:10 mysql-relay-bin.000012
    -rw-rw---- 1 mysql mysql 1.7M Mar 22 03:13 mysql-relay-bin.000013
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:14 mysql-relay-bin.000014
    -rw-rw---- 1 mysql mysql  85K Mar 22 03:15 mysql-relay-bin.000015
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:46 mysql-relay-bin.000016
    -rw-rw---- 1 mysql mysql 2.5M Mar 22 03:47 mysql-relay-bin.000017
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:47 mysql-relay-bin.000018
    -rw-rw---- 1 mysql mysql 762K Mar 22 03:53 mysql-relay-bin.000019
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:54 mysql-relay-bin.000020
    -rw-rw---- 1 mysql mysql 203K Mar 22 03:56 mysql-relay-bin.000021
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:56 mysql-relay-bin.000022
    -rw-rw---- 1 mysql mysql 1.7M Mar 22 04:16 mysql-relay-bin.000023
    -rw-rw---- 1 mysql mysql  575 Mar 22 03:56 mysql-relay-bin.index
    srwxrwxrwx 1 mysql mysql    0 Mar 22 03:56 mysql.sock
    -rw-r--r-- 1 mysql mysql    6 Dec 28  2013 mysql_upgrade_info
    drwx------ 2 mysql mysql   59 Jan 21  2016 percona
    -rw-rw---- 1 mysql mysql   56 Mar 22 03:56 relay-log.info
    drwx------ 2 mysql mysql   48 Dec 28  2013 test
    drwx------ 2 mysql mysql   57 Dec 28  2013 test2
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb0
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb1
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb2
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb3
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb4
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb5
    -rw-rw---- 1 mysql mysql 3.9K Dec 27  2013 mydb5_percona.test.co.il.err
    -rw-rw---- 1 mysql mysql 117K Mar 22 01:04 mydb5_repair.test.co.il.err
    -rw-r----- 1 mysql root   28K Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.err
    -rw-rw---- 1 mysql mysql    5 Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.pid

log file:

170322 03:46:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql
170322  3:46:22 [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
170322  3:46:22  InnoDB: Initializing buffer pool, size = 3.0G
170322  3:46:22  InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /data/mysql/ibdata1 is of a different size
InnoDB: 770688 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 13107200 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
170322  3:46:22 [ERROR] Plugin 'InnoDB' init function returned error.
170322  3:46:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170322  3:46:23 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-rel14.11-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), 14.11, Revision 603
170322  3:46:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251
170322  3:46:36 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail. Error_code: 1286
170322  3:46:36 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286
170322  3:46:36 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782
170322  3:46:36 [Note] Slave I/O thread: connected to master 'repl@192.118.71.35:3306',replication started in log 'mysql-bin.000324' at position 8573354
170322  3:47:33 [Note] /usr/sbin/mysqld: Normal shutdown

170322  3:47:33 [Note] Slave I/O thread killed while reading event
170322  3:47:33 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000324', position 11116963
170322  3:47:33 [Note] /usr/sbin/mysqld: Shutdown complete

170322 03:47:33 mysqld_safe mysqld from pid file /data/mysql/mydb5_slave.test.co.il.test.co.il.pid ended
170322 03:47:39 mysqld_safe Starting mysqld daemon with databases from /data/mysql
170322  3:47:39 [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
170322  3:47:39  InnoDB: Initializing buffer pool, size = 3.0G
170322  3:47:40  InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /data/mysql/ibdata1 is of a different size
InnoDB: 770688 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 13107200 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
170322  3:47:40 [ERROR] Plugin 'InnoDB' init function returned error.
170322  3:47:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170322  3:47:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-rel14.11-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), 14.11, Revision 603
170322  3:47:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251
170322  3:47:51 [Note] Slave I/O thread: connected to master 'repl@192.118.71.35:3306',replication started in log 'mysql-bin.000324' at position 11116963
170322  3:47:51 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail, Error_code: 1286
170322  3:47:51 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286
170322  3:47:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782

Please save me. I feel lost.

Best Answer

The main clue is here:

170322  3:46:22  InnoDB: Initializing buffer pool, size = 3.0G
170322  3:46:22  InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /data/mysql/ibdata1 is of a different size
InnoDB: 770688 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 13107200 pages!

It probably means that this disagrees with what it found on disk:

innodb_data_file_path = ibdata1:200G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:10G;ibdata8:10G;ibdata9:20G;ibdata10:10G;ibdata11:10G;ibdata12:10G;ibdata13:5G;ibdata14:5G;ibdata15:20G;ibdata16:10G;ibdata17:20G;ibdata18:20G;ibdata19:20G:autoextend

When you cloned the disk, were there no ibdata* files? And/or were they not the sizes above?

Addenda

In the old days, when there were 2GB or 4GB limits on files, and when InnoDB was in its infancy, the ibdata1, etc, kludge was invented. These days, nearly everyone says ibdata1:10G:autoextend and never has to worry about it. (The 10G can be any value.)

In certain situations (in the old days), with multiple ibdata* files listed, it became tricky to copy files over. If the last ibdata file was not there, and autoextend, then one had to exactly match the sizes.

You have gone back to the old days. I think you must change my.cnf to say

ibdata1:12345678900:autoextend

ibdata1:12345678900,ibdata2:20G,...

But you must (I think) put exactly the current size of ibdata1 in place of 12345678900.

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Thesql replication deadlock slave stopped

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;

Best Answer

Related Solutions

Error ‘Unknown Table Engine ‘InnoDB” After Restarting MySQL

I have very bad news for you.

I also have very good news for you along with some bad news.

UPDATE 2011-10-17 06:19 EDT

UPDATE 2011-10-17 11:07 EDT

Thesql replication deadlock slave stopped

Related Question