SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB'

3
votes

Some of you may say "there's a lot of answers in other threads" or "Google it". But actually I didn't find the answer.

I am developing a PHP / MySQL project using symfony and for an unknown reason (meaning "I wasn't writing any php line"), I got this error "SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB'".

I already tried these solutions:

  1. Restart MySQL
  2. Stop MySQL, delete ib_logfile0 and ib_logfile1, Start Mysql
  3. Do a "Show engines". This command returns no instance of "InnoDB". Not even a "NOT ACTIVE" (weird, right ?).
  4. (even weirder) PHPMyAdmin displays all tables except that the ones using InnoDB display "used" instead of "InnoDB". No problem with MyISAM tables.

Some people suggested to dump database, recompile MySQL and load the sql file into the recompiled database but... Let say I'm not in the mood for that (that would take a LOT of time).

2
mysqlinnodb
How did you install mysql initially? Premade package for your os (xamp/wamp, os-package?). It would be VERY unusual to get a premade package with innodb disabled/not included. – Marc B
No, it's a debian-squeeze-6.0. It was installed on the server many months ago and the project worked well until this afternoon. – MaximeBernard
I was thinking about a log file too big or something like that but it doesn't seem so. – MaximeBernard
Is this question better suited for serverfault instead of SO? – xQbert

2 Answers

3
votes

Show engines tells that InnoDB failed to start on whatever reason. You have to open the error log (check SHOW VARIABLES LIKE 'error_log'):

mysql> show variables like 'log_error';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| log_error     | /var/log/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.00 sec)

and find a reason why InnoDB failed to start. Then fix it.

0
votes

First check your MySQL version, if have support to innoDB, second check the mysql config file (/etc/my.cnf or /etc/mysql/my.cnf) on linux, (Windows i don't know) if innodb is disabled with "skip-innodb" parameter. Some configuration disabled the innoDB support because use most RAM,