How to resolve the specified key was too long; max key length is 767 bytes issue in MySQL?
Error 1071 - "Specified key was too long; max key length is 767 bytes" is a common error that can occur in MySQL versions 5.6 (and prior versions) when you try to create an index on a column or a combination of columns with a total length that exceeds the maximum key length.
Why does this error occur?
767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).
How to resolve the issue in InnoDB?
If your existing database engine is set to InnoDB, you can do the following to resolve the issue.
- Open your MySQL configuration file (usually named my.cnf or my.ini).
- Add the following line under the [mysqld] section:
innodb_large_prefix=1 - Save and close the file.
- Restart the MySQL server for the changes to take effect.
Alternatively, you can enable innodb_large_prefix at runtime by executing the following SQL statement:
SET GLOBAL innodb_large_prefix = 1;How to change your existing database engine to InnoDB?
- Connect to your MySQL server using a client such as MySQL Workbench or the command-line client.
- Select the database that you want to modify using the
USEstatement. For example:
USE your_database_name;
ALTER DATABASE statement with the DEFAULT option to change the default storage engine for the current database. For example:ALTER DATABASE your_database_name DEFAULTCHARACTERSET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;This statement will set the default storage engine for the current database to InnoDB.
SELECT @@default_storage_engine;
This statement will display the current default storage engine for the MySQL server.
How to create a new database with the InnoDB engine?
To create a new database with the InnoDB engine, you can use the following steps:
- Connect to MySQL server using a MySQL client such as MySQL Workbench or the MySQL command-line client.
- Use the following command to create a new database:
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Replace "database_name" with the name of the database that you want to create.
3. Use the following command to set the default storage engine for the new database to InnoDB:
ALTER DATABASE database_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
4. Verify that the new database has been created with the InnoDB engine by using the following command:
SHOW CREATE DATABASE database_name;
This command will display information about the new database, including the default storage engine.
