Sample Header Ad - 728x90

pt-online-schema-change failed when creating new tablle, specified key was too long; max key length is 767 bytes issue

0 votes
1 answer
279 views
The version, mysql is InnoDB 5.6.10-log, pt-online-schema-change 3.1.0. table info is
email varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
uid varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
hotel_brand_id int(11) DEFAULT NULL,

UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id),
My purpose is to add a unique index,
pt-online-schema-change --recursion-method=none --nocheck-unique-key-change h=$DATABASE_HOST,D=$DATABASE,t=$TABLE,u=$DATABASE_USER,p=$DATABASE_PASSWORD   --alter "ADD UNIQUE INDEX index_membership_customers_on_email_and_hotel_brand_id (email, hotel_brand_id)"   --$RUN --alter-foreign-keys-method=auto
then I met the unique index length issue(https://stackoverflow.com/questions/1814532/mysql-error-1071-specified-key-was-too-long-max-key-length-is-767-bytes) ,
Creating new table...
Error creating new table: DBD::mysql::db do failed: Specified key was too long; max key length is 767 bytes [for Statement "CREATE TABLE umami_prod._membership_customers_new (
It seems this was the cause
uid varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id)
so I want to resize the columns or change its character set, but whatever I got to do, it failed because pt-online-schema-change try to create a new table first, then it failed because of this index length issue in the beginning. I couldn't change anything So my question is 1. How does the current table support this utfm8 varchar(255), How does it possible?
UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id)
2. Maybe there are table settings I missed for pt-online-schema-change 3. Anything I couldn't do, next, it is a 4M rows size table, and since pt-online-schema-change couldn't work because of the above issue, my only option is alter, to lock whole table? Thanks for your reply, any hint might help, thanks
Asked by Ilake Chang (101 rep)
Jun 25, 2022, 10:58 PM
Last activity: Jun 26, 2022, 04:05 AM