MySQL Bugs: #82194: innodb_large_prefix does not honor UNIQUEness past 255

Bug #82194	innodb_large_prefix does not honor UNIQUEness past 255
Submitted:	12 Jul 2016 5:47	Modified:	12 Jul 2016 18:11
Reporter:	Rick James	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.12, 5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, large_prefix

Description:
innodb_file_format = Barracuda
innodb_large_prefix = ON
innodb_file_per_table = ON
ROW_FORMAT = DYNAMIC

Yet...
VARCHAR(500) UNIQUE can get "duplicate key" error.

Forum discussion:
http://stackoverflow.com/questions/38311323/mysql-5-7-13-innodb-unique-column-varchar500

How to repeat:
mysql> CREATE TABLE so38311323 (v500 VARCHAR(500) NOT NULL, UNIQUE(v500)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC ;

mysql> insert into so38311323 (v500) VALUES (repeat('x',300));

mysql> insert into so38311323 (v500) VALUES (repeat('x',301));

mysql> insert into so38311323 (v500) VALUES (repeat('x',301));
ERROR 1062 (23000): Duplicate entry 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' for key 'v500'
mysql> SHOW CREATE TABLE so38311323\G
*************************** 1. row ***************************
       Table: so38311323
Create Table: CREATE TABLE `so38311323` (
  `v500` varchar(500) NOT NULL,
  UNIQUE KEY `v500` (`v500`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

mysql> SELECT * FROM information_schema.`INNODB_SYS_TABLES` WHERE name = 'try/so38311323'\G
*************************** 1. row ***************************
     TABLE_ID: 7445
         NAME: try/so38311323
         FLAG: 33
       N_COLS: 4
        SPACE: 5449
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

mysql> SHOW TABLE STATUS LIKE 'so38311323'\G
*************************** 1. row ***************************
           Name: so38311323
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-07-11 22:19:25
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+

Suggested fix:
Please fix the code, not the documentation.

Hello Rick James,

Thank you for the report.
Imho this seems to be working as expected as I don't see any issues:

- 
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'innodb_large_prefix' OR Variable_name LIKE 'innodb_file_format' OR Variable_name LIKE 'innodb_file_per_table' OR Variable_name LIKE '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_file_format      | Barracuda                                               |
| innodb_file_per_table   | ON                                                      |
| innodb_large_prefix     | ON                                                      |
| innodb_version          | 5.7.13                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                   |
| version                 | 5.7.13-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
11 rows in set (0.01 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists r;
Query OK, 0 rows affected (0.01 sec)

mysql> create table r(a varchar(500), unique(a))engine=innodb charset=utf8 row_format=dynamic;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into r values(concat(repeat('a',499),'b'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into r values(concat(repeat('a',499),'c'));
Query OK, 1 row affected (0.00 sec)

mysql> select distinct(a) from r\G
*************************** 1. row ***************************
a: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab
*************************** 2. row ***************************
a: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaac
2 rows in set (0.00 sec)

mysql>
mysql> SHOW TABLE STATUS LIKE 'r'\G
*************************** 1. row ***************************
           Name: r
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-07-12 09:37:53
    Update_time: 2016-07-12 09:37:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE r\G
*************************** 1. row ***************************
       Table: r
Create Table: CREATE TABLE `r` (
  `a` varchar(500) DEFAULT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Thanks,
Umesh

-- 
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists r;
Query OK, 0 rows affected (0.00 sec)

mysql> create table r(a varchar(500), unique(a))engine=innodb row_format=dynamic;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into r values(concat(repeat('a',499),'b'));
Query OK, 1 row affected (0.00 sec)

mysql> insert into r values(concat(repeat('a',499),'c'));
Query OK, 1 row affected (0.00 sec)

mysql> select distinct(a) from r\G
*************************** 1. row ***************************
a: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab
*************************** 2. row ***************************
a: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaac
2 rows in set (0.00 sec)
mysql>
mysql> SHOW TABLE STATUS LIKE 'r'\G
*************************** 1. row ***************************
           Name: r
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-07-12 09:38:33
    Update_time: 2016-07-12 09:38:33
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

mysql> show create table r\G
*************************** 1. row ***************************
       Table: r
Create Table: CREATE TABLE `r` (
  `a` varchar(500) DEFAULT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

(Bad test case; closing until I get it fixed.)

Sorry for the false alarm.  I goofed.

Thank you Rick for confirming.

Regards,
Umesh