CODEWITHSUNDEEP
mysqlinnodb
Markum
Markum

Reputation: 4059

InnoDB missing from MySQL

I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL server. I recently upgraded it from the dotdeb repository, then installed mysql-server.

There is no mention of InnoDB in my my.cnf except some comments which explain InnoDB is enabled by default, which I don't understand. There is also no mention of InnoDB in SHOW ENGINES.

Is there something I'm missing here?

If it matters, my MySQL server version is: 5.5.24-1~dotdeb.1 (Debian).

EDIT: SHOW ENGINES:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Upvotes: 1

Views: 10523

Answers (4)

Marcos Paulino Chahin
Marcos Paulino Chahin

Reputation: 1

Check if you have enough space on disk and where mysql.sock is stored.

  1. Stop MYSQL
  2. Edit my.cnf and increase:

innodb_buffer_pool_size=100M (May vary per case)

  1. Add:

    [mysqld] innodb_force_recovery = 1

  2. Execute the following

    mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak

    mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

  3. Start MySQL and take backups just in case.

  4. Log in to mysql and: show engines; - Check to see that InnoDB is listed and SUPPORT = YES.

  5. If all is good up until 6, exit and edit my.cnf setting this back:

[mysqld] innodb_force_recovery = 0

  1. Restart MySQL

  2. Go to your websites, check that all works, and good luck!

PS - You may want to check what caused this, perhaps working on your production server, or restarting caused your log files to get corrupted. You're in the clear for now, so have a look around and make sure all else looks good, especially free disk space and offsite backups.

Upvotes: 0

Swayok
Swayok

Reputation: 462

I've got this problem with Debian 7 server with preinstalled mysql 5.5. There was no InnoDB engine after SHOW ENGINES

As severin mentioned before run this:

  1. sudo /etc/init.d/mysql restart
  2. sudo tail -n 1000 /var/log/syslog

I've got this one:

InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.

And solution on other line:

InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf

After adding innodb_use_native_aio = 0 to my.cnf InnodDB appeared in SHOW ENGINES

Upvotes: 0

severin
severin

Reputation: 10268

The problem is most probably a non-matching log file size: mysql expects the innodb log files to be exactly the size that is specified in the config file. To check whether this is really the issue, do the following:

  1. sudo /etc/init.d/mysql restart
  2. sudo tail -n 1000 /var/log/syslog

(I'm assuming you are on Debian)

If you see some errors reported there regarding innodb and log file size (Sorry, I can't remember the exact wording of the message), then the fix is easy:

  1. locate the logfiles (probably /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1)
  2. stop the mysql server
  3. rename the log files: sudo mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak etc.
  4. start the mysql server
  5. check in /var/log/syslog whether the errors are no longer happening
  6. connect to mysql and check via SHOW ENGINES; whether InnoDB is available now...

Hope this helps!

Upvotes: 4

Ike Walker
Ike Walker

Reputation: 65587

The first thing to do is to run SHOW ENGINES at the MySQL prompt to confirm if Innodb is disabled.

If it is, check the error log for the MySQL server. It will have details on why InnoDB was disabled. There are several reasons MySQL might disable InnoDB on startup. For example, if the innodb log file size specified in my.cnf does not match the size of the existing log file(s) on disk.

Upvotes: 1

Related Questions