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%'; +…

Accepted Answer

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.

How to enable large index in MariaDB 10?

Solution

Problem

Related problems