Error unknown unsupported storage engine innodb

How to fix ‘Unknown table engine ‘innodb” error

InnoDB is a storage engine for MySQL. Due to its speedy crash recovery, InnoDB is the preferred engine for high available databases.

But there are times when InnoDB throws error, such as ‘Unknown table engine ‘innodb” . Today, we’ll see when and why this error happens and how to fix it.

What is ‘Unknown table engine ‘innodb” error?

The error message itself clearly says that MySQL service is unable to detect the InnoDB storage engine.

InnoDB engine error manifests itself in different situations:

1. Failure in creating InnoDB tables

Suppose you try to create a new table using the command (“CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;”).

The table creation fails giving the error – ERROR 1286 (42000) at line 1: Unknown table engine ‘innodb’.

Table creation or listing using PhpMyAdmin may also not work. It may not even list the InnoDB storage engine option but instead throw up this error.

2. Using MySQL commands that involve InnoDB

When InnoDB engine is disabled, using certain commands would show this error.

For instance, listing the storage engines using ‘show engines’ command, backing up the databases using mysqldump , etc. are situations where you may end up seeing this error:

mysqldump: Got error: 1286: Unknown table engine ‘InnoDB’ when using LOCK TABLES

3. MySQL server can fail to start

MySQL may not start with InnoDB engine support. The error shown in the log file would be:

4. Error in the websites

All of a sudden, all your database-driven websites can start giving the error along with the database queries:

What causes the error ‘Unknown table engine ‘innodb”

Unknown table engine error happens either because InnoDB storage engine is not enabled in the server or because it got corrupt.

The main reasons that cause the error are:

1. InnoDB not enabled

If the InnoDB engine is disabled in the MySQL database server configuration, it will give error ‘Unknown table engine ‘innodb” whenever you try to create a table with InnoDB support.

It can happen if the MySQL server is not compiled with InnoDB support or if the InnoDB support is disabled via configuration settings.

If the MySQL server was built from source and the built-in InnoDB wasn’t compiled in it, InnoDB engine will not be recognized.

You can check if InnoDB is supported in the server, using the MySQL command:

Check if InnoDB is enabled in server

The value ‘DISABLED’ for ‘have_innodb’ parameter shows that InnoDB is not enabled in that server.

InnoDB engine can also get disabled as a result of a MySQL server upgrade, server migration or manual configuration changes.

If the parameter ‘skip-innodb’ is uncommented in /etc/my.cnf, it will skip loading the InnoDB database engine.

2. Memory shortage

Server not having enough memory to allocate to MySQL, can cause InnoDB to fail. Memory allocation happens based on the parameters such as ‘ innodb_buffer_pool_size’ .

If the value alloted for this pool size is greater than the available memory limit, InnoDB error occurs.

3. Huge or corrupt log files

In the long run, it so happens that the InnoDB log files grow in size of around GBs or they get corrupt due to some reason. The log files are available at ‘ ib_logfile’.

These log files contain the changes to InnoDB data. If they get corrupt, InnoDB will fail to load and ends up giving the error ‘Unknown table engine ‘innodb”.

4. Incorrect permissions for /tmp or socket file

By default, the MySQL server creates its socket file in /tmp or other tmp folder as configured. This folder requires 1777 permissions, for the service to work.

If the /tmp doesn’t have enough permissions to create the socket file, it may end up showing the InnoDB error. InnoDB also fails to run if this folder is full and have no space left.

How to fix the error ‘Unknown table engine ‘innodb”

After identifying the reason for the InnoDB engine error from the error logs, we can apply any of the fixes here, as suited for the situation.

1. Enable InnoDB in the configuration file

If InnoDB support is disabled in the configuration file, here is how to fix it.

Check the ‘/etc/my.cnf’ file. It is the configuration file for MySQL server.

Find the ‘ skip-innodb ‘ parameter and comment it by adding # at the beginning of the line:

Enable InnoDB support in my.cnf

Restart the MySQL server and now InnoDB support will be enabled and you would be able to execute commands without the error.

InnoDB engine support enabled in server

2. Recreate the InnoDB log files

InnoDB usually recovers its log files when the MySQL service is started.

So, if the service does not start due to huge or corrupt log files, then rename them and restart MySQL with these steps:

a. Stop MySQL service:

b. Rename the log files to some other name

c. Start MySQL service:

Now the MySQL server would start fine. It will create the log files from the scratch and the InnoDB tables would be accessible without errors.

3. Edit the configuration settings

Edit the following parameters in ‘/etc/my.cnf’ and set optimal values based on the server settings, such as available memory and CPU.

These are the parameters that allocate memory and CPU for the InnoDB storage engine. There are no default values for these parameters, but the values can change from server to server.

Always verify the permissions of the ‘tmp’ folder and confirm that enough memory and disk space are available for the MySQL server.

At Bobcares, we also examine the server logs and monitor the MySQL performance and fine-tune the values set for each parameter.

4. Setup MySQL server with InnoDB support

All the above fixes are relevant on servers that already have InnoDB engine compiled with it. MySQL 5.5 versions and above have InnoDB engine enabled by default.

If your MySQL server doesn’t have InnoDB support compiled in, you need to recompile it with InnoDB. If you are running older versions of MySQL, it is best to upgrade your server.

To install the latest version of MySQL server in Ubuntu, use the command:

This will install the MySQL server 5.6 with InnoDB support, provided all dependencies are installed.

Points to note..

The file ‘ibdata1’ contains the data of the InnoDB databases and should never be deleted.

So, before doing any changes to MySQL server, we always make a backup of these:

a. MySQL data directory (usually at /var/lib/mysql )

b. MySQL configuration file (default at /etc/my.cnf )

Today we saw the major causes for the error ‘Unknown table engine ‘innodb” and how to fix them. In addition to these, some configuration errors can also lead to errors.

Examining the MySQL error logs, permissions, resources, etc. may be vital to resolve InnoDB related errors. In critical situations, restoring from the backups would also be required.

At Bobcares, we perform crash rescue services with minimal downtime, even for servers with fully corrupt data and no backups.

Get a FREE consultation

Do you spend all day answering technical support queries?

Wish you had more time to focus on your business? Let us help you.

We free up your time by taking care of your customers and servers. Our engineers monitor your servers 24/7, and support your customers over help desk, live chat and phone.

Talk to our technical support specialist today to know how we can keep your service top notch!

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Bobcares provides Outsourced Hosting Support and Outsourced Server Management for online businesses. Our services include Hosting Support Services, server support, help desk support, live chat support and phone support.

Источник

Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it:

And this shows in «/var/log/mysql/error.log»:

I’ve checked permissions on all the mysql directories to make sure it had ownership and I also renamed the previou ib_logs so that it could remake them. I’m just getting no where with this issue right now, after looking at google results for 2 hours.

7 Answers 7

After checking the logs I found the following error:

I removed these files:

This resolved my problem after restart.

If you really need skip-innodb (use case: low memory footprint), then of course you don’t have to comment it out. However, if InnoDB is the default storage engine, the server will fail to start until you tell it which storage engine to use instead, e.g. default-storage-engine=myisam for MyISAM.

If you’re using MySQL 5.6+ and want to disable InnoDB, don’t forget «—default-tmp-storage» or it won’t work:

To disable InnoDB, use —innodb=OFF or —skip-innodb. In this case, because the default storage engine is InnoDB, the server will not start unless you also use —default-storage-engine and —default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

Источник

Getting error «Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed» when starting MySQL

I found many similar question on Stackoverflow but didn’t get the exact error solution. My issue is when starting MySQL service on one of the Dedicated Centos 6.5 machine, I am getting error :

Here are free command status: free -m

11 Answers 11

This error also happens when your Database data is corrupt. You may fix this issue by moving your Db data files (ib_logfile0 and ib_logfile1) mentioned below to another location. ib_logfile0 and ib_logfile1 are system tablespace for the InnoDB infrastructure. These files contains several classes for information vital for InnoDB. You may read about these files here.

Before following below steps please keep a copy of files (ib_logfile0 and ib_logfile1) so you may restore your data in case it is lost:

Follow below steps:

• Login to server via SSH with root access.
• Navigate to /var/lib/mysql.
• If you see files like, ib_logfile0 and ib_logfile1, rename or move them to some other folder.
• Stop and start the MySQL service by running sudo service mysql stop and sudo service mysql start

These files will be recreated after you restart the server and the issue will be fixed hopefully.

Источник

MySQL fails to start

Contents

About

MySQL process fails to start.

Looks like this:

Step 0 (reboot)

Restart the server:

Check after restart if mysql is working:

Step 1 (check free HDD space)

Check if there is left free space on disk. If disk is full, remove unnecessary files and start MySQL service again.

Example which shows full (100%) /dev/md2:

Step 2 (check log)

Check if there is any information in log file /var/log/mysqld.log

If there is no NEW information in this log file, do:

and recheck. If still there is no NEW information in this log file, set such permissions with chmod:

• /var/lib/mysql — 755
• files within /var/lib/mysql — 660
• and its subdirectories — 700

Check if you set permissions correctly:

MySQL can start. If not — check log file again, some new info should appear in it (usually error 13)

Step 3 (error 13)

If you see such error in log:

And start MySQL again:

If MySQL still fails to start do:

If MySQL still fails to start, check if there are any information in log.

Step 4 (error 13 /tmp)

If you get this error:

Restart mysql, if fails — check log.

Step 5 (messed up root permissions)

And give all privileges to the root.

Step 6 (crashing)

Log file looks like this:

Step 7

Try to remove ib_logfile0 and ib_logfile1, (with backups):

And start MySQL again:

If MySQL still fails to start, check if there any information in log (/var/log/mysqld.log).

Step 8

Run this command to check more information when MySQL starts:

Источник

Atomic Spin

Atomic Object’s blog on everything we find fascinating.

MySQL: Failed Registration of InnoDB as a Storage Engine

It happens countless times for many reasons. You attempt to start or restart your “MySQL server”:http://dev.mysql.com/ after a small configuration change and MySQL stubbornly refuses to start. Or, if it does start, some important functionality, such as “InnoDB”:http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html support, is missing.

Most Linux users will be familiar with the following pleasant response:

In the case of a configuration change, MySQL clearly fails to start because of the change, but why? Some changes, such as the size of a log file, seem trivial.

Sometimes the cause can be difficult to track down, especially if you don’t know where to look for clues.

h2. MySQL Logging

MySQL has “many types of logs…”:http://dev.mysql.com/doc/refman/5.0/en/server-logs.html a general query log, a connection log, an error log, a binary log, a slow query log, etc. And then there are the Linux system logs.

In the case of a failed MySQL start due to a configuration error or MySQL problem, details are usually output to the “error log”:http://dev.mysql.com/doc/refman/5.0/en/error-log.html. The default location of the error log is the “data directory”:http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html (usually /var/lib/mysql ). The log is normally named based on the hostname of the server. For example, database.example.com.err .

If this file doesn’t seem to exist in your MySQL data directory, MySQL has probably been configured to log elsewhere. Check your my.cnf file, which is normally at /etc/my.cnf . The my.cnf file can contain a log-error option in the [mysqld] section which specifies a different location of the log. If no such option exists and you can’t find the log, try specifying a location for the log yourself, such as log-error = /var/lib/mysq/mysql_error.log

h2. InnoDB Log Files

A common configuration change which can prevent MySQL from starting involves the InnoDB log file size. The “InnoDB log file”:http://www.pythian.com/news/1242/innodb-logfiles/ is the ‘redo’ or recovery log for the InnoDB storage engine. It contains transactions which have been committed to a MySQL table, but which have not yet been written to disk. Should MySQL crash and lose the contents of its buffer pool, the log file can be used to recover any data changes which were in the buffer pool. These recovered data changes can then be written to disk.

The my.cnf file contains a few options which affect the InnoDB log files. In this instance, the key one is innodb_log_file_size . This option sets the size of the InnoDB log file.

h2. Failed Registration of InnoDB as a Storage Engine

Frequently individuals decide to make changes to the innodb_log_file_size option. Generally, they attempt to increase the value, and restart the MySQL server. Unless the “proper procedure”:http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html is followed, MySQL will fail to start (or start without support for InnoDB). The individual may be confused about why MySQL suddenly decided not to start. A quick check of the MySQL error log will usually reveal the cause.

In the case of changing the innodb_log_file_size , one will often find an error similar to the following:

110509 12:04:27 InnoDB: Initializing buffer pool, size = 384.0M 110509 12:04:27 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 157286400 bytes! 110509 12:04:27 [ERROR] Plugin ‘InnoDB’ init function returned error. 110509 12:04:27 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed. 110509 12:04:27 [ERROR] Unknown/unsupported table type: innodb 110509 12:04:27 [ERROR] Aborting 110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

While the cause of MySQL’s failure to start is obvious from the log file, this is often overlooked.

There are two solutions to this particular problem:

* Restore the my.cnf file to its original state, with an innodb_log_file_size equal to the actual size of the existing InnoDB log files.

* Rename or move both the ./ib_logfile0 and ./ib_logfile1 files, and then start the MySQL server.

The ./ib_logfile0 and ./ib_logfile1 files are located in the InnoDB data directory (usually /var/lib/mysql ). Both files must be moved or renamed for the above procedure to work. When starting MySQL, new InnoDB log files of the appropriate size will be created.

The original InnoDB log files only need to be kept as long as they may be needed for data recovery. If the MySQL server successfully starts after the above procedure, and all data is intact, the original InnoDB log files can be discarded.

Источник

I’ve a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I’ve done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables:

2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: The InnoDB memory heap is disabled
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Compressed tables use zlib 1.2.8
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Using Linux native AIO
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Using SSE crc32 instructions
2020-10-17  0:51:18 140430430813568 [Note] InnoDB: Initializing buffer pool, size = 500.0M
InnoDB: mmap(549126144 bytes) failed; errno 12
2020-10-17  0:51:18 140430430813568 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-10-17  0:51:18 140430430813568 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-17  0:51:18 140430430813568 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-17  0:51:18 140430430813568 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-17  0:51:18 140430430813568 [ERROR] **Unknown/unsupported storage engine: InnoDB**
2020-10-17  0:51:18 140430430813568 [ERROR] Aborting

---

---

My full DB conf:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 500M
max_heap_table_size     = 25M
tmp_table_size          = 25M
#log_slow_queries        = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

#
# * Query Cache Configuration
#
query_cache_limit   = 2M
query_cache_size        = 50M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit    = 1000
#log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = exclude_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
# ssl=on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

Here my first page of htop

Could not export/copy htop result.

I would be happy if you could help me out!!

Thanks a lot
M