13

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).

Emanuel Ones
  • 461
  • 1
  • 3
  • 8
  • 2
    Add stored calculated field with the value value = hash(title), where 'hash' is any built-in chechsum function (https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html). Then create unique index by user_id and this calculated field. Of course, collision is possible, but its probability is too low (dependent by hash length). – Akina Mar 04 '19 at 11:55
  • This has 5 workarounds. – Rick James Mar 09 '19 at 23:40

1 Answers1

23

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

Adam McGurk
  • 147
  • 7
Emanuel Ones
  • 461
  • 1
  • 3
  • 8
  • 1
    On Linux, you most probably will have to add the above lines to /etc/mysql/mariadb.conf.d/50-server.cnf under the [mariadb] section header. See here: https://keawe.de/mariadb-innodb-keycloak – Stephan Richter Jun 19 '20 at 10:40
  • 1
    for Ubuntu - created /etc/mysql/mariadb.conf.d/60-fix-key-length.cnf with these entries under [mysqld]. Works like a charm. – edgars Oct 29 '20 at 09:45
  • 2
    I had to remove out line innodb_default_row_format = dynamic else MariaDB would't start on CentOs. But even without this option I got rid of the error. – kovinet Dec 31 '20 at 06:54
  • In the latest version of MariaDB this does not work. The innodb_file_format_max = Barracuda makes MariaDB crash when starting. – Goldug Jul 29 '22 at 16:14