Jul 16, 2021
2 min read
mySQL Error
MySQL 5.6
To create database with specific character-set and collate,
create database [table name] default character set utf8mb4 collate utf8mb4_unicode_ci;Database
character-set= utf8mb4;
collate=utf8mb4_unicode_ci;
UNIQUE INDEX 생성을 하려고 할 때,
mysql> CREATE UNIQUE INDEX email
-> ON users(email);ERROR 1071 (42000): Specified key was too long; max key length is 767 bytesReason from stackoverflow:767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.
mysql> SET @@global.innodb_large_prefix = 1;
// will increase limit to 3072 bytes.// If I try to create again,
mysql> CREATE UNIQUE INDEX email
-> ON users(email);ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.set global innodb_large_prefix = ON;
set global innodb_file_format_max = BARRACUDA;
set global innodb_file_format = BARRACUDA;// To check the difference
mysql> show variables like 'innodb_lar%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
// If I try again, still gives an error.
mysql> CREATE UNIQUE INDEX email
-> ON users(email);ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.// Need to alter table's row format as dynamic or compressed
alter table [table name] ROW_FORMAT=DYNAMIC;
To check index,
show index from [table name];
To get details,
SHOW FULL COLUMNS FROM [table name];
// can check field, type, collation, null, key, default, extra, privileges, commentSHOW TABLE STATUS FROM [database];
//name, engine, version, row_format, rows, avg_row_length, data_length, max_data_length, index_length, data_free, auto_increment, create_time, update_time, check_time, collation, checksum, create_options, comment
To check character-set,
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+Reference:
