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, comment
SHOW 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:

--

--

--

Consistency achieves everything https://github.com/paigekim29

Love podcasts or audiobooks? Learn on the go with our new app.

5 great content creators to learn iOS development from

Let’s write readable code..does less code mean better performance?

Video Capturing Using Python And OpenCV

{UPDATE} [S.W.A.T] M.I.S.S.I.O.N Hack Free Resources Generator

SUPERALGOS GOVERNANCE Translation Team Proof of Work, January 2022

Tips to Improve MySQL Query Performance

The Most Valuable Elements Of One Test Automation Framework

How to Convert an RTF File to JPG in PHP

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Joo Hee Paige Kim

Joo Hee Paige Kim

Consistency achieves everything https://github.com/paigekim29

More from Medium

Use Spring Framework, React, and PostgreSQL to create a simple To-do Application #1

MongoDB Basic Queries: Explained with examples

MongoDB CRUD operation?

The trade-off between query performance & data consistency when working with connected data in…