Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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, I get the following:

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. if i delete ib_logfiles and ibdata file, innodb will return back normally, but i can't access my innodb tables, ie. after deleting ibdata1 and restarting mysql

desc article;
ERROR 1146 (42S02): Table 'MyDb.article' doesn't exist

My innodb configuration in my.cnf is as follows:

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

although the table is there!!

Has anybody faced such issue before??

Any idea is highly appreciated

Thanks

share|improve this question
    
I had the same error message but in the context of replication. The slave stopped with that error message. I solved by checking on master and slave the innodb_log_file_size option. It should be the same. Then restart the slave mysql and it should work. in my case: innodb_log_file_size was 2000M. –  Rodo Oct 7 '13 at 11:44

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
Thanks Rolando, I have re-imported my DB again, although it is a very large DB (About 100G). now i have rebuilt master-slave as well. the tables are fine and i can access everything. my problem is: the last time i did the same and got the same but upon first restart to mysql on the slave server, i lost innodb engine!! do you have any idea?? –  Alaa Oct 17 '11 at 6:28
    
I updated my answer to explain why InnoDB went away. –  RolandoMySQLDBA Oct 17 '11 at 10:18
    
I updated my answer again to explain why the error message was misleading –  RolandoMySQLDBA Oct 17 '11 at 15:06
    
innodb_buffer_pool_size is in there twice as well. Nice catch! +1 –  Derek Downey Oct 17 '11 at 15:27

In my case, using innodb_force_recovery=1 didn't work. I then discovered that there was a second instance of mysql running which wouldn't accept any connections and killing with -1 didn't work, so I opted to reboot the server.

After it rebooted I ran start slave and everything got back to normal on its own.

share|improve this answer

The error log indicates you have some data corruption. When that happens, InnoDB does not become available in SHOW ENGINES. You can try to force recover by doing the following steps:

  • shutdown mysql on S2
  • add innodb_force_recovery=1 to your my.cnf file
  • start mysql and see if you have your data listed.

Hopefully it's there and you can redump your data and reload it on S2.

share|improve this answer
    
+1 for suggesting recovery of data as the first course of action !!! –  RolandoMySQLDBA Oct 17 '11 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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