Error 1286 42000 unknown storage engine federated

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:

[ERROR] Unknown/unsupported storage engine: InnoDB

4. Error in the websites

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

Notice: Error: Unknown table engine 'InnoDB'
Error No: 1286

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:

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:

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

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:

/etc/init.d/mysql stop

b. Rename the log files to some other name

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

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

c. Start MySQL service:

/etc/init.d/mysql start

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.

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
innodb_thread_concurrency

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:

apt-get install mysql-server-5.6

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.

Read: Database crash rescue – How we re-built an InnoDB MySQL database when ibdata1 file was corrupted

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.

linux notes

среда, 6 августа 2014 г.

MySQL error 1286: Unknown table engine ‘InnoDB’

Во время тюнинга MySQL на одном из серверов, поймал такую ошибку — MySQL error 1286: Unknown table engine ‘InnoDB’. Оказалось, что виноваты мои кривые руки.

Собственно посмотрим, какие движки MySQL нам доступны:
mysql> SHOW ENGINES;
+————+———+—————————————————————-+—————+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+—————+——+————+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+————+———+—————————————————————-+—————+——+————+

Источник

1286 — Unknown storage engine ‘InnoDB’

I am trying to use roundcube and it recently just broke. I don’t know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a table:

Ideas as to how to fix? It used to work just fine.

6 Answers 6

OBSERVATIONS

• I see there is nothing Innodb-related in the my.cnf .
• Look for Innodb-related error messages expressing why mysqld failed to start in the error log.

My first guess would be look at the ib_logfile0 and ib_logfile1 in /var/lib/mysql .

If they have been set to size different from the default, that probably will prevent mysqld’s startup. The default for innodb_log_file_size is 5M.

SUGGESTION #1

If RoundCube’s installation overwrote the my.cnf , see if you have a physical backup of the previous my.cnf . Put that back in place and run

SUGGESTION #2

Take the filesize of ib_logfile0 and divide by 1048576. Let’s say it is 12M.

Put that in my.cnf

Just delete ib_logfileX files and restart mysql service.

You changed the innodb-log-file-size var to size more bigger than already set it in your mysql daemon.

Petre already wrote it, but perhaps to clarify:

It seems that the InnoDB Engine is not loaded when MySQL cannot write to the temp directory (usually /tmp). This might be caused by either a full temp partition, or incorrect permissions on the temp directory.

As Petre has mentioned chmod 1777 /tmp should resolve this issue. Don’t forget to restart MySQL afterwards.

Some of the things that are mentioned in the article: 1. Deleting innodb log files. 2. Changing innodb_buffer_pool_size to lower then 2G

When you have disable innodb, dont’ forget to remove all innodb-related tables from Dbs. For example «mysql» database has 5 of them:

I use roundcube, apache2, mysql, dovecot, postfix.

After a harddisk crash and reinstall of the same system on a new HDD I cannot connect to the mail server by web application (roundcube). The message was «Unknown storage engine ‘InnoDB’».

After about 2 weeks of searching the configuration fault without results and by a miracle, today, I saw that some of the linux components cannot write in /tmp.

That’s all that I did . put chmod 1777 /tmp .
Now it’s running ok!

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.11.43147

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

«SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine ‘InnoDB’ «

JKDuck

New Pleskian

Thus I returned to Parallels Plesk Panel v. 11.0.9 by just using the full recovery function, just rolling back to the server’s status it had the day before. However, the error message quoted above sustained. Not only that, in one (of three) WordPress-Installations I am running on this V-Server, the same message appears, an the website is not running at all!

What went wrong, what can I do to get the InnoDB running again?
Thank You.
Hoping for your help!

UFHH01

Guest

Please post your «my.cnf» from «/etc/mysql» for further investigations, if the other suggestions don’t already help.

Did you try the two «all-time» suggestions:

As well, you might want to see, if you actual MySQL — version supports different engines and if this is the case, which one is set to be the default with these commands:

( Login to MySQL: ) mysql -uadmin -p`cat /etc/psa/.psa.shadow`
( Show the supported engines: ) SHOW ENGINES;

If you would like to change the default MySQL — engine, you might want to edit the «my.cnf» — file and adjust the line:

JKDuck

New Pleskian

but that returned a «ERROR 1045 (28000): Access denied for user ‘admin’@’localhost’ (using password: YES)»; I know user name and password, but not how to apply it when I am using putty.

/.my.cnf» to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with —help to get a list of available options and with
# —print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain «#» chars.
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

# innodb_buffer_pool_size=2M
# innodb_additional_mem_pool_size=500K
# innodb_log_buffer_size=500K
# innodb_thread_concurrency=2
[mysqld]
local-infile=0
#
# * Basic Settings
#

#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
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 = 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 = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * 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

# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#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 = include_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 I recommend the OpenSSL GUI «tinyca».
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# innodb_buffer_pool_size=2M
# innodb_additional_mem_pool_size=500K
# innodb_log_buffer_size=500K
# innodb_thread_concurrency=2
[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

Источник

SQLSTATE[42000]: Syntax error or access violation: 1286 #3243

Comments

RausV commented Aug 11, 2018 •

Hello, I hope someone can help me. Have already searched here but found nothing suitable that would have helped. It is not so easy for me because my english is not as good I would like .
Please give me start help, thanks!

What steps will reproduce the problem?

Start install: homepage.com/humhub/index.php?r=installer%2Findex

What is the expected result?

What do you get instead?

Database Exception – yiidbException
SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine ‘InnoDB’
The SQL being executed was: CREATE TABLE queue (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
channel varchar(255) NOT NULL,
job blob NOT NULL,
created_at int(11) NOT NULL,
started_at int(11),
finished_at int(11)
) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB
Error Info: Array
(
[0] => 42000
[1] => 1286
[2] => Unknown storage engine ‘InnoDB’
)
↵
Caused by: PDOException
SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine ‘InnoDB’
in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/db/Command.php at line 1258

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/db/Schema.php at line 664
655656657658659660661662663664665666667668669670671672673
$exceptionClass = ‘yiidbException’;
foreach ($this->exceptionMap as $error => $class) <
if (strpos($e->getMessage(), $error) !== false) <
$exceptionClass = $class;
>
>
$message = $e->getMessage() . «nThe SQL being executed was: $rawSql»;
$errorInfo = $e instanceof PDOException ? $e->errorInfo : null;
return new $exceptionClass($message, $errorInfo, (int) $e->getCode(), $e);
>

• Returns a value indicating whether a SQL statement is for read purpose.
• @param string $sql the SQL statement
• @return bool whether a SQL statement is for read purpose.
  */
  public function isReadQuery($sql)
  <

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/db/Command.php at line 1263 – yiidbSchema::convertException(PDOException, ‘CREATE TABLE queue ( id int. ‘)

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/db/Command.php at line 1075 – yiidbCommand::internalExecute(‘CREATE TABLE queue ( id int. ‘)

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/db/Migration.php at line 323 – yiidbCommand::execute()

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/humhub/modules/queue/migrations/m170118_111823_queuetable.php at line 20 – yiidbMigration::createTable(‘<<%queue>>’, [‘id’ => yiidbmysqlColumnSchemaBuilder, ‘channel’ => yiidbmysqlColumnSchemaBuilder, ‘job’ => yiidbmysqlColumnSchemaBuilder, ‘created_at’ => yiidbmysqlColumnSchemaBuilder, . ], ‘CHARACTER SET utf8 COLLATE utf8_. ‘)
14151617181920212223242526 ‘id’ => $this->primaryKey(),
‘channel’ => $this->string()->notNull(),
‘job’ => $this->binary()->notNull(),
‘created_at’ => $this->integer()->notNull(),
‘started_at’ => $this->integer(),
‘finished_at’ => $this->integer(),
], $this->tableOptions);

public function down()

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php at line 725 – m170118_111823_queuetable::up()

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php at line 199 – yiiconsolecontrollersBaseMigrateController::migrateUp(‘m170118_111823_queuetable’)

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/InlineAction.php at line 57 – call_user_func_array([humhubcommandsMigrateController, ‘actionUp’], [0])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/Controller.php at line 157 – yiibaseInlineAction::runWithParams([])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/console/Controller.php at line 148 – yiibaseController::runAction(‘up’, [])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/humhub/commands/MigrateController.php at line 184 – yiiconsoleController::runAction(‘up’)
178179180181182183184185186187188189190 ob_start();
$controller = new self(‘migrate’, Yii::$app);
$controller->db = Yii::$app->db;
$controller->interactive = false;
$controller->includeModuleMigrations = true;
$controller->color = false;
$controller->runAction(‘up’);

• Executes migrations in a specific folder

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/humhub/modules/installer/controllers/SetupController.php at line 127 – humhubcommandsMigrateController::webMigrateAll()
121122123124125126127128129130131132133 Yii::$app->cache->flush();

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/InlineAction.php at line 57 – call_user_func_array([humhubmodulesinstallercontrollersSetupController, ‘actionInit’], [])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/Controller.php at line 157 – yiibaseInlineAction::runWithParams([‘r’ => ‘installer/setup/init’])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/Module.php at line 528 – yiibaseController::runAction(‘init’, [‘r’ => ‘installer/setup/init’])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/web/Application.php at line 103 – yiibaseModule::runAction(‘installer/setup/init’, [‘r’ => ‘installer/setup/init’])

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/protected/vendor/yiisoft/yii2/base/Application.php at line 386 – yiiwebApplication::handleRequest(humhubcomponentsRequest)

in /home/.sites/938/site9988536/web/4uns.at/sub/humhub/index.php at line 25 – yiibaseApplication::run()
19202122232425 require(DIR . ‘/protected/humhub/config/web.php’),
(is_readable(DIR . ‘/protected/config/dynamic.php’)) ? require(DIR . ‘/protected/config/dynamic.php’) : [],
require(DIR . ‘/protected/config/common.php’),
require(DIR . ‘/protected/config/web.php’)
);

$_GET = [
‘r’ => ‘installer/setup/init’,
];

$_COOKIE = [
‘wordpress_logged_in_06875cac1cdbe3d6111e5b6d2589b42f’ => ‘wordpressadmin|1534340595|QBn5L4lStwPhsoCKe6xmAuCUQcYrTQcmyfEJCBpKetj|f00db0e5c52115016df7a2e1a59baf4e4e790aca73def50e391c97b841337b0b’,
‘wp-settings-1’ => ‘libraryContent=browse&imgsize=thumbnail’,
‘wp-settings-time-1’ => ‘1533130996’,
‘_csrf’ => ‘b401eb3dbc7bbbadd474d45631e8c88e21debab11d842983747841778bf63aa5a:2:‘,
];
Yii Framework
2018-08-11, 16:27:28

Additional info

Note: Please use the community site for any questions on customizing HumHub.
Note: Also provide the full error stacks from Administration —> Information —> Logging and errors from your browser console if possible.

Thanks a lot for your help .

The text was updated successfully, but these errors were encountered:

Источник

Во время тюнинга MySQL на одном из серверов, поймал такую ошибку — MySQL error 1286: Unknown table engine ‘InnoDB’. Оказалось, что виноваты мои кривые руки.

Собственно посмотрим, какие движки MySQL нам доступны:
mysql> SHOW ENGINES;
+————+———+—————————————————————-+—————+——+————+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+————+———+—————————————————————-+—————+——+————+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+————+———+—————————————————————-+—————+——+————+