MariaDB Character Encoding

Question

I have just started to port an older MySQL/Spring/Eclipselink project to MariaDB. I am encountering an issue with table creation that can be demonstrated as follows:

MariaDB [spasm]> CREATE TABLE Configuration (ID BIGINT NOT NULL, Attribute VARCHAR(190) NOT NULL UNIQUE, Value VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.07 sec)

MariaDB [spasm]> drop table Configuration;
Query OK, 0 rows affected (0.06 sec)

MariaDB [spasm]> CREATE TABLE Configuration (ID BIGINT NOT NULL, Attribute VARCHAR(255) NOT NULL UNIQUE, Value VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [spasm]> 

MariaDB [spasm]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

I understand that this is related to character encoding, however I don't know how to manage/correct it?

If you have to have a requirement for a unique constraint on the full 255 multibyte characters, and if you are using InnoDB storage engine, then consider enabling **`innodb_large_prefix`**. Reference: https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_large_prefix — spencer7593, Oct 06 '17 at 23:02
I don't think it is the unique constraint causing the issue - If I have two varchar(255) (not keys) then the error occurs? — skyman, Oct 06 '17 at 23:21
The issue is with INDEXes that have to grow real large to handle UNICODE (up to 4 bytes) - so it goes above the limit. So a `PRIMARY KEY` will try to create an `UNIQUE INDEX` and fail because of space issues. :-( been bitten many times — Jacques Amar, Oct 07 '17 at 01:26
Thank you, but what I don't understand is that the primary key is an INT and the issue seems to be that if I have a VARCHAR of 190 and 255 the create works and if I have two VARCHAR's of 255 it fails - is their an assumption of an index on the VARCHAR's?. — skyman, Oct 07 '17 at 02:03
That little token `UNIQUE` on the `Attribute` column is telling MySQL to create a unique index. 255 characters times 4 bytes per character is 1020 bytes, and that exceeds the InnoDB key length limit. Changing the column size to 190 "works", because 190*4=760 which is less than 767 limit. Remove that keyword token `UNIQUE` from the `Attribute` column, and the error will disappear. Or change the characterset on the column to utf8, which uses a maximum of 3 bytes per character 255*3<767. If you actually need a unique constraint on the full 255 utf8mb4 characters, then see my first comment above — spencer7593, Oct 09 '17 at 13:37

score 5 · Accepted Answer · answered Oct 08 '17 at 23:56

The new default CHARACTER SET is utf8mb4. It is complaining about the UNIQUE index:

Attribute VARCHAR(255) NOT NULL UNIQUE

If you are hitting the limit because of trying to use CHARACTER SET utf8mb4. Then do one of the following (each has a drawback) to avoid the error:

⚈  Upgrade to 5.7.7 (MariaDB 10.2.2?) for 3072 byte limit -- your cloud may not provide this; 
⚈  Change 255 to 191 on the VARCHAR -- you lose any values 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.

Or... Stay with 5.6/5.5/10.1 but perform 4 steps to raise the limit to 3072 bytes:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)

MariaDB Character Encoding

1 Answers1