📋

Description

Error 1266 is a warning from MariaDB indicating that it could not use the specified storage engine for a table and instead automatically defaulted to another available engine. This typically occurs during `CREATE TABLE` or `ALTER TABLE` operations when the requested engine is unavailable, incorrectly named, or improperly configured.

💬

Error Message

Using storage engine %s for table '%s'

🔍

Known Causes

3 known causes

⚠️

Requested Engine Unavailable

The storage engine specified in your SQL statement is either not installed, disabled, or not compiled into the current MariaDB server instance.

⚠️

Invalid Engine Name

A typographical error or an incorrect storage engine name was provided in the `CREATE TABLE` or `ALTER TABLE` statement.

⚠️

Engine Dependencies Missing

The desired storage engine may require specific external libraries or system configurations that are not present or correctly set up on the server.

🛠️

Solutions

3 solutions available

1. Explicitly Specify Storage Engine During Table Creation easy

Ensure the desired storage engine is set when creating new tables.

1

When creating a new table, use the `ENGINE` clause to explicitly define the storage engine. For example, to create a table using InnoDB:

CREATE TABLE my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=InnoDB;

2

If you encounter this error on an existing table, you can alter it to the desired engine.

ALTER TABLE my_table ENGINE=InnoDB;

2. Configure Default Storage Engine in `my.cnf` medium

Set a default storage engine for all new tables in your MariaDB configuration.

1

Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.conf.d/50-server.cnf` on Linux systems, or `my.ini` on Windows.

2

Edit the configuration file and add or modify the `default_storage_engine` parameter within the `[mysqld]` section. For example, to set InnoDB as the default:

[mysqld]
default_storage_engine = InnoDB

3

Save the configuration file and restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

3. Analyze and Correct Table Storage Engine Mismatches medium

Identify tables with unexpected storage engines and change them to the intended ones.

1

Query the `information_schema.tables` to find tables and their current storage engines. Filter for tables in your specific database.

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE != 'your_desired_engine';

2

For each table identified in the previous step that uses an unexpected storage engine, use the `ALTER TABLE` statement to change it to the desired engine. For example, to change `my_table` from MyISAM to InnoDB:

ALTER TABLE my_table ENGINE=InnoDB;

3

Repeat this process for all tables that exhibit the error.

🔗

Related Errors

5 related errors

MariaDB Error 1266: Storage Engine Auto-Correction