CODEWITHSUNDEEP
mysqlinnodb
rechie
rechie

Reputation: 2209

Unknown table engine 'InnoDB'

Recently, I have found out that I can maximize mysql performance when if I have good hardware. Since I've been using InnoDB I added additional configuration into my.ini

Here is the newly added configurations:

innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120

Then I restart all of the services. But when I used my program, an error occurred "Unknown table engine 'InnoDB'".

What I have tried to solve this problem:

  1. I delete the log file the restart the service but I still got the error.

Upvotes: 25

Views: 81138

Answers (7)

martinszy
martinszy

Reputation: 153

In MariaDB 10.1, there's an ignore-builtin-innodb option that should be disabled to stop fix error.

Upvotes: 0

biniam
biniam

Reputation: 8199

I tried all of those (and many others) but the one method that worked for me is:

  • Stop MySql Server
    /etc/init.d/mysql stop
  • Delete the log files
    rm ib_logfile0 ib_logfile1
  • Rename the InnoDB file (If nothing else works because it will be recreated) mv ibdata1 old_ibdata1

  • I have this configs in /etc/mysql/my.cnf -> Even if you don't specify this, MySql will use the default values.

    [mysqld]
datadir=/data/mysql/data
socket=/var/run/mysqld/mysqld.sock

#Not a must to define the following
innodb_log_file_size=1G
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=1G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_lock_wait_timeout=18000

  • Start MySql Server
    /etc/init.d/mysql start

Upvotes: 5

ChrisN
ChrisN

Reputation: 431

Another option you have if you mangle your my.cnf file completely is to replace it with a default config from the mysql install there . For linux:

You have the following options,

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Here is an example to install it:

#backup original config
mv /etc/my.cnf{,.bak}

#copy new my.cnf from template
cp /usr/share/mysql/my-large.cnf /etc/my.cnf

More information on these options is available at http://dev.mysql.com/doc/mysql/en/option-files.html

Upvotes: 1

aaronbauman
aaronbauman

Reputation: 3737

Other solutions did not fix my problem. InnoDB engine was disabled after adjusting config.

Removing borked ib_* log files in mysql data dir fixed my issue, and allowed me to use 2G buffer pool for InnoDB: http://www.turnkeylinux.org/forum/support/20090111/drupal-6-problem-enable-innodb#comment-131

Upvotes: 21

Nux
Nux

Reputation: 10002

Had this issue when restoring from backup. Problem was I had a bit different settings in my.ini. So in case someone gets this issue just be sure to set the same settings (copy my.ini), stop the MySQL service, then restore whole data folder and then start the MySQL service again.

Upvotes: 0

Sean McCleary
Sean McCleary

Reputation: 3820

I have ran into this problem as well. The problem was that I was allocating more memory to InnoDB than the server had with the variable innodb_buffer_pool_size. MySQL did not complain about not being able to allocate the memory in its logs about this.

Upvotes: 8

rechie
rechie

Reputation: 2209

I just retried deleting the logfile and restarted the services, and it works! But beware of allotting 2G because innodb might not compile, please use 1G if 2G doesn't work.

Upvotes: 11

Related Questions