[Solved] MYSQL5.6 Error: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

 

mysql> CREATE TABLE permissions (
    ->     role varchar(50) NOT NULL,
    ->     resource varchar(512) NOT NULL,
    ->     action varchar(8) NOT NULL,
    ->     constraint uk_role_permission UNIQUE (role,resource,action)
    -> );
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Steps

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix='on';
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format='Barracuda';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE permissions (
    ->     role varchar(50) NOT NULL,
    ->     resource varchar(512) NOT NULL,
    ->     action varchar(8) NOT NULL,
    ->     constraint uk_role_permission UNIQUE (role,resource,action)
    -> )  CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Similar Posts: