key was too long in mariadb, but same script with same encoding works on mysql

Question

I've been reading several variations of this question and related articles, and although I did solve my problem in the end (ended up doing s/utf8mb4/utf8/g on my sql script), I am still at a loss why this works on MySQL as it is, but had to the search replace in MariaDB.

The setup:

Machine 1: Mysql 5.7.19
Machine 2: MariaDb 10.1.25

On both machines I created the database like:

CREATE DATABASE `test` COLLATE 'utf8mb4_general_ci'

The input script is a 16MB SQL file, with around 300 tables, all of them declared as ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED

When I try to import this file on MariaDb, I get:

ERROR 1071 (42000) at line 602: Specified key was too long; max key length is 767 bytes

But I import the same file witout changes on MySQL, and all tables are created just fine.

To be able to do the same on the MariaDB machine, I need to do the aforementioned s/utf8mb4/utf8/g before running the script.

The gist of the question is: Is there a way I can setup MariaDB so I can import the file in the same format than as in MySQL?

Regarding the duplicate rasied by @Cbroe, it fails in a couple of ways:

1 - It explains the difference in index size between MyIsam and InnoDB (and I'm using InnoDB in both cases, and while it works in MySQL it fails in MariaDB).

2 - It doesn't explain how to make MariaDB behave as MySQL, if it is possible. Since it is possible to have this script work with this engine and this encoding work on MySQL, is it possible to do the same in MariaDB? If not, why not?

Furthermore, we already have a working answer that is distinct from the ones in the alleged duplicate.

the size 767 bytes is the same form mysql so be sure you have the same chararcter set and collation in your mariadb .. be sure you are not using different default for this — ScaisEdge, Aug 22 '17 at 16:24
Possible duplicate of [What is the index key size limit in MariaDB?](https://stackoverflow.com/questions/22700395/what-is-the-index-key-size-limit-in-mariadb) — CBroe, Aug 22 '17 at 16:24
An explanation for why the character set has an effect on this here, https://stackoverflow.com/a/22515986/1427878 — CBroe, Aug 22 '17 at 16:24
@CBroe, I'm not convinced the duplicate applies, for the reasons I posited in my update. Regarding the explanation, in your second comment, I find that a bit more apropos, but still fails on the count of: is there a workaround to make MariaDB work as MySQL works, so I can keep utf8mb4 and innodb? — yivi, Aug 22 '17 at 17:03
Have you first of all verified whether MySQL actually created an index of the necessary length ... and did not just silently swallow its complaints due to lax configuration? — CBroe, Aug 22 '17 at 17:09
@CBroe no, I haven't. Configuration is as lax as a brand new ubuntu installation can be. Which I guess is a lot? Maybe that could be the answer: "Mysql executes the script but fails at creating the index, I need to relax MariaDb's config if I want to the same behaviour". I'm going to setup the tests again. — yivi, Aug 22 '17 at 17:18
How come you didn't try to question whether the issue might be in the wrong data type for the index? I mean, you're obviously indexing a textual field of some sort, and if it's used for searching purposes - why not a fulltext index instead of regular one? Just because MariaDB complained at import, that doesn't mean that the original system with MySQL was error-free. The real solution lies in identifying the problem. Key length restriction is just a symptom of the real problem. To cure the disease, you fix its cause, not its symptom. — N.B., Aug 22 '17 at 17:48
@N.B. I guess that I assumed that since MySQL didn't complain, it worked? It is not such a weird assumption to make, IMO. Anyway, with Solarflare [answer](https://stackoverflow.com/a/45823616/1426539) the problem is gone. Thanks for your helpful advice, though. Peace. — yivi, Aug 22 '17 at 18:01

score 9 · Accepted Answer · answered Aug 22 '17 at 17:23

To enable support for large keys, you have to enable the system variable innodb_large_prefix:

When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) are allowed for InnoDB tables that use DYNAMIC or COMPRESSED row format. See Section 14.8.1.7, “Limits on InnoDB Tables” for maximums associated with index key prefixes under various settings.

With that in mind:

Default values for Mysql:

Permitted Values (<= 5.7.6)   Type    boolean  
                              Default OFF
Permitted Values (>= 5.7.7)   Type    boolean
                              Default ON

Default values for MariaDB:

Data Type: boolean
Default Value:
     ON  (>= MariaDB 10.2.2)
     OFF (<= MariaDB 10.2.1)

So set that value in your MariaDB configuration (you will also need to use the correct row_format, innodb_file_format and enable innodb_file_per_table).

score 2 · Answer 2 · answered Aug 24 '17 at 03:35

There are 5 workarounds for the "767" problem:

⚈  Upgrade to 5.7.7 (MariaDB 10.2.x) for 3072 byte limit -- your host provider may not provide this; 
⚈  Change 255 to 191 on the VARCHAR -- you lose any keys longer than 191 characters (unlikely?); 
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese; 
⚈  Use a "prefix" index -- you lose some of the performance benefits; 
⚈  Reconfigure (if staying with 5.6.3 (10.1.x) - 5.7.6) -- 4 things to change: Barracuda + innodb_file_per_table + innodb_large_prefix + dynamic or compressed.

score 1 · Answer 3 · answered Aug 22 '17 at 18:21

1

To complement @Solarflare answer:

Besides enabling innodb_large_prefix, it was necessary to set innodb_file_format to BARRACUDA.

This article explains what format MariaDB uses for InnoDB, and has more details about versions and compatibilities.

answered Aug 22 '17 at 18:21

yivi

42,438
18
116
138

key was too long in mariadb, but same script with same encoding works on mysql

3 Answers3