On the local server, this error occurs when trying to change the encoding and mapping of the table to utf8mb4 , and on the remote one - no. I know the cause of the error, I wonder why the behavior is different. The local mysql is 5.5.45, the remote is 5.5.55.
MaxU
52.2k 6 18 50
Mik mik
1.609 6 22
|
1 answer
Judging by the answers to a similar question in the English version of SO in the INNODB engine (up to MySQL version 5.5.14), there are the following restrictions on the length of the field with a unique key:
INNODB utf8 VARCHAR(255)INNODB utf8mb4 VARCHAR(191)
compare the sysvar_innodb_large_prefix parameter in both databases
Prefixes prefixes longer than 767 bytes (up to 3072 bytes) for the InnoDB tables that use DYNAMIC or COMPRESSED row format. (Creating such tables also requires the option values innodb_file_format = barracuda and innodb_file_per_table = true.)
It is not necessary to use the prefix length code. If you want to make a new one, it will be more than 3072 for a REDUNDANT or COMPACT table for an ER_INDEX_COLUMN_TOO_LONG error.
Community spirit ♦
one
MaxU MaxU
52.2k 6 18 50
- Yes, it differs - innodb_large_prefix on the local database ON, on the remote database - OFF. But in this case, the error should appear on the remote, is not it? And then what is fraught with is that I use ALTER TABLE on a remote database without first preparing such fields? - Mik
- @Mik are you sure? The idea should be the opposite ... And the data is the same in both databases? I can't check now ... - MaxU
- Yes, quite sure. On all databases where the change of encoding has passed without error, innodb_large_prefix = OFF. - Mik
- As it turned out, it does not work on localhost even after
set global innodb_large_prefix = 'OFF'- Mik - @Mik, specify the script to create this column:
CREATE TABLE ...- MaxU
|