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(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
- 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`)
- Maybe there are table settings I missed for
pt-online-schema-change - Anything I couldn't do, next, it is a 4M rows size table, and since
pt-online-schema-changecouldn'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
