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?

@Hackerman, can't test anymore, because gave up on MariaDB and went back to MySQL — w.k, Apr 13 '17 at 19:44
FWIW it's not related to MariaDB; MySQL 5.6 has the same issue. It's also not an issue in MariaDB 10.2. — Jeremy Davis, Dec 06 '17 at 05:33
Refer https://stackoverflow.com/a/52778785/2137210 for solution — Pratik, Oct 12 '18 at 12:19

score 32 · Accepted Answer · answered Apr 13 '17 at 23:06

32

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.

answered Apr 13 '17 at 23:06

Rick James

135,179
13
127
222

1

I installed test machine and can now confirm: adding `ROW_FORMAT=DYNAMIC` into `CREATE TABLE`-statement was the missing piece. Thank you! – w.k Apr 18 '17 at 00:04
Instead of `ALTER TABLE`, you can set `innodb_default_row_format=DYNAMIC` before creating it. In any case, I would add all of these global values to the server configuration. Saves remembering all of this the next time something wants to create "large" indices. – Matthias Urlichs Feb 15 '19 at 20:03
Or upgrade to 5.7 and not have to worry about it. – Rick James Feb 15 '19 at 23:18

score 11 · Answer 2 · answered Apr 15 '17 at 09:28

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.

score 5 · Answer 3 · answered Jul 25 '19 at 11:19

After the steps provided by @Rick :

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);

I changed last step to

SET GLOBAL innodb_default_row_format=DYNAMIC;

So far so good.

score 3 · Answer 4 · answered Aug 12 '19 at 16:41

I think solution is answered here for the session but if you restart the MySQL, I don't think these settings will work.

For the permanent solution, you need to enter following code in your My.Ini file-

## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'

Hope it helps reducing rework and scratches on your head :)

score 1 · Answer 5 · answered Jul 22 '20 at 17:06

Adding ROW_FORMAT=DYNAMIC in my CREATE query worked for me.

in your case it would be:

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 ROW_FORMAT=DYNAMIC;

How to enable large index in MariaDB 10?

5 Answers5

Linked