How to enable large index in MariaDB 10?

Question

In Debian Jessie I installed MariaDB server 10.0.30 and I try to increase max key length. AFAIU it depends of the config parameter innodb_large_prefix being enabled. According to the docs, it also requires barracuda file format and innodb_file_per_table. After setting them in config and restarting server I see in client, that those parameters are set correctly:

> SHOW GLOBAL VARIABLES LIKE 'innodb_large%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | OFF       |
| innodb_file_format_max   | Antelope  |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

> SHOW GLOBAL VARIABLES LIKE 'innodb_page%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

I am not sure, why innodb_file_format_max is set Antelope, but while innodb_file_format_check is OFF, it should not matter. Actually, even if I had it also set Barracuda, it did not made difference.

If i try now create table with large index like:

CREATE TABLE `some_table` (
  `some_tableID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `column` varchar(750) COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`some_tableID`),
  KEY `column` (`column`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci;

I get error:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

On Ubuntu 16.04 with mysql server 5.7.17 are all related settings same (by default) and there is no problem with large index (for utf8mb4 it is 750*4 = 3000).

What is wrong with my MariaDB setup?

Answer 1

It requires more than just those two settings...

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

Perhaps all you need is to add ROW_FORMAT=... to your CREATE TABLE.

These instructions are needed for 5.6.3 up to 5.7.7. Beginning with 5.7.7, the system defaults correctly to handle larger fields.

Alternatively, you could use a "prefix" index:

INDEX(column(191))

(But prefix indexing is flawed in many ways.)

"If the server later creates a higher table format, innodb_file_format_max is set to that value" implies that that setting is not an issue.

Answer 2

innodb_large_prefix only applies to COMPRESSED and DYNAMIC row formats.

MariaDB 10.0 and 10.1 have InnoDB 5.6, which by default creates tables with ROW_FORMAT=Compact (even if innodb_file_format is set to Barracuda). So, to use large prefixes, you need to specify the row format explicitly. Same is true for MySQL 5.6.

InnoDB 5.7 by default creates the table with ROW_FORMAT=DYNAMIC, which is why the same CREATE relying on innodb_large_prefix works in MySQL 5.7 and MariaDB 10.2 without any additional clauses.