Index column size too large. The maximum column size is 767 bytes. on import existing database in Laravel (Read carefully not duplicate)

Question

Please read carefully. This is not a duplicate on the topic: #1071 - Specified key was too long; max key length is 767 bytes and How to fix 1709 - Index column size too large. The maximum column size is 767 bytes. in mysql in XAMPP

I already have an existing base. And I get this error. My question is how to correct it and I tried everything.

First I set in my database.php this code

        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',

After I set:

         Schema::defaultStringLength(191);

Always i got error message:

Index column size too large. The maximum column size is 767 bytes.

The first time my import burst here:

 CREATE TABLE `admins` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `admins_email_unique` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 
 COLLATE=utf8mb4_unicode_ci

This is 5 tables in a row, I have 40 more. For that reason I can't change existing table.

The question, how to fix this? I use mariaDB and xampp. The difference between my question and those above is that I import an existing table and I can not change the data. I also work in Laravel

Answer 1

This is an XY problem

Why? Because the issue is not the amount of data not fitting the index, that's the symptom.

Real problem

Real problem is how to stop duplicate email entries.

Attempted solution

Attempted solution is to create a UNIQUE index on the email column. It's a great attempt at solving the issue except - emails can be unusually large, your index length will vary. Sometimes it might be a 10 bytes, sometimes 30, sometimes 50.. and sometimes 255 - that's not good.

Back to the drawing board

What if all emails had fixed length? That's a much easier problem to tackle. You don't have to worry about index size limitation, all you need to make sure is that it's below the default limit of 767 bytes.

Better solution

Let's not index the email field. Let's create another column, email_unique and let's store the hash of the email there. Then, make the hash a UNIQUE index.

Benefits: - always fixed with - always falls within default index length of 767 bytes - no worrying about utf8

How to do it to waste as little space as possible

• choose a hashing algorithm. sha1 is completely fine, although you can go for sha256. I'll use 256-bit version of SHA-2 algorithm
• create a binary(32 field). It will hold the raw value of our hashing function. It will always be fixed with for any kind of email
• I'll use triggers, before create and before update to maintain the value of the hash so I don't have to worry about it in my language's logic.

Add the binary column

ALTER TABLE admins ADD email_hash BINARY(32) AFTER email;

Add the before insert trigger

DELIMITER $$

CREATE TRIGGER `admins_before_insert` BEFORE INSERT 
ON admins 
FOR EACH ROW BEGIN
    SET NEW.email_hash = UNHEX(SHA2(NEW.email, 256)); -- this creates a binary representation of a sha-256 hashed email column

END$$

DELIMITER ;

Add before update trigger

DELIMITER $$

CREATE TRIGGER `admins_before_insert` BEFORE UPDATE
ON admins 
FOR EACH ROW BEGIN
    SET NEW.email_hash = UNHEX(SHA2(NEW.email, 256)); -- this creates a binary representation of a sha-256 hashed email column

END$$

DELIMITER ;

Final words

I added trigger example code but I didn't test it. The idea is to be able to add and update emails and have MySQL tell you if there's a duplicate. Some people don't like using triggers. That's why the step with triggers is optional and an example how to achieve the effect if you prefer that route.

You can, of course, increase the amount of bytes MySQL will accept for indexing. However, that's not the optimal solution as you can quickly fill up your memory and basically waste resources. Down the line, you might exceed newly set limit.

Answer 2

You can chenge the innodb_large_prefix in your config file to ON. That will set your index key prefixes up to 3072 bytes as the mysql doc says.

[mysqld]
innodb_large_prefix = 1