MariaDB – Specified Key Was Too Long; Max Key Length is 767 Bytes

indexmariadbMySQL

I have a table that has 3 columns:

id(int 11)  | user_id(int 4) | title(varchar 512) 
____________|________________|___________________
     1      |        3       | Thing X
     2      |        3       | Something Else
     3      |        5       | Thing X

And i need to make a unique combination between user_id and title. For that i'm using this simple query:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`)

The charset for the title column is utf8mb4_unicode_ci. The database was created using the charset utf8mb4:

CREATE DATABASE learning_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And the table type is InnoDB.

I've tried to set a few globals:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

But i'm getting the same error 1071:

Specified key was too long; max key length is 767 bytes

Is there anything else that i might be able to do?

P.S: if i set to index only the first 191 characters of the title i don't get that error:

ALTER TABLE posts ADD UNIQUE `unique_post`(`user_id`, `title`(191))

…but i have titles related to the same user that might be different only at the very last letter(aka last 4 bytes considering my charset)

P.S.S: i'm on localhost (using xampp).

Best Answer

Ok so after a weekend long of research the answer is very simple and i'm going to leave it here for others facing this same problem.

This answer is tested on MariaDB 10.1.38, db charset utf8mb4, table type InnoDB and table charset utf8mb4_unicode_ci:

Open my.ini and add this lines(if they already exist just edit everything after =) right after [mysqld]:

innodb_file_format = Barracuda
innodb_file_per_table = on
innodb_default_row_format = dynamic
innodb_large_prefix = 1
innodb_file_format_max = Barracuda

You can also set them trough the cmd/terminal:

On windows - if your XAMPP is located at c:\xampp\:

Open up a cmd and navigate to mysql's bin - in this case:

cd c:\xampp\mysql\bin

Autenticate:

mysql -h localhost -u root

Once you're authenticated run this queries(one at a time):

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = on;
SET GLOBAL innodb_default_row_format = dynamic;
SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_format_max = Barracuda;

But i recommend sticking to editing the my.ini(or .cnf) since if you are going for queries your options might by rewritten every time you restart your Apache.

You can read more on the InnoDB storage format: here

Also this article is very interesting in helping you to optimize your varchars brefore creating the unique keys: here

Related Question