2

Recently changed servers and went from CentOS 5.8 to CentOS 6.4. I installed MySQL 5.1.69 on my new server, and manually swapped /var/lib/mysql. I am not sure what version of MySQL was on my old server, but would expect it to be the same.

When I try to query a table, I get the following:

ERROR 1286 (42000): Unknown table engine 'InnoDB'

Tables which don't use InnoDB work fine. Looks like I don't have InnoDB loaded. I installed MySQL using yum install mysql mysql-server. Why is InnoDB not loaded, and how should I load it?

EDIT:

Maybe I should manually change ib_logfile0 to have the file size the same?

mysql> SHOW ENGINES;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                   | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)

mysql>

/var/log/mysqld.log:

130622 17:05:19 [Note] /usr/libexec/mysqld: Shutdown complete

130622 17:05:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130622 17:06:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130622 17:06:09  InnoDB: Initializing buffer pool, size = 8.0M
130622 17:06:09  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
130622 17:06:09 [ERROR] Plugin 'InnoDB' init function returned error.
130622 17:06:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130622 17:06:09 [Note] Event Scheduler: Loaded 0 events
130622 17:06:09 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
user1032531
  • 568
  • 2
  • 11
  • 26

2 Answers2

0

Seems like this is a good answer. Which basically states that you should remove/rebuild the ib_logfile0 yourself. Just move the file to someplace safe—not in the MySQL directory that is looking for it—and restart the server.

The caveat is: Do you actually have DBs that require InnoDB? If so, you can manually do a mydump of each DB to get a backup, then drop the DBs from the MySQL database, shut down MySQL, move/delete the ib_logfile0—I also suggest moving/deleting the ibdata1 and ib_logfile1 as well—and just restarting MySQL.

Then check your logs to see if InnoDB has started. I bet it will. At that point recreate the DBs you go rid of & reimport the data. New ibdata1, ib_logfile0 and ib_logfile1 files will be created and all should be good.

Giacomo1968
  • 3,542
  • 27
  • 38
  • Don't delete ibdata1!!!!!!!!!!! Did so, and get: SQL show index from `tracker` failed : Table 'mydb.tracker' doesn't exist – user1032531 Jun 23 '13 at 13:10
  • You can safely `ibdata1`, `ib_logfile0` & `ib_logfile1` as long as you have backups of the DBs & safely delete all DBs in the database. The only way to solve problems like this is to rebuild from scratch. This is recommended by Oracle & the MySQL team themselves. – Giacomo1968 Jun 25 '13 at 15:26
-1

Did you copy the ib_logfile0 file over from the old server? If so, try moving it out of that directory (or rename it) and restart mysqld.

Safado
  • 4,786
  • 7
  • 37
  • 54