0

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.

0

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

Or

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

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

3
  • When i cloned the disk, Everything was the overwrite so it's doesn't matter what was there before. This is a copy of the master server data. As you can is in the mysql data above, i have one ibdata of 12GB.
    – Omri
    Mar 22 '17 at 7:45
  • should i change innodb_data_file_path to innodb_data_file_path = ibdata:12GB?
    – Omri
    Mar 22 '17 at 8:14
  • Back to the old days... see my addenda.
    – Rick James
    Mar 22 '17 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.