0

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`)
  1. Maybe there are table settings I missed for pt-online-schema-change
  2. 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

1 Answer 1

1

Can you live with 191 instead of 225? If not, your 5 options (including that one) are here:

http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

1
  • Ya, I am ok 191 instead of 225, I couldn't, because the current problem is, that it would fail when executing pt-online-schema-change, so it seems I need to ask for maintenance to lock the table. Jun 27 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.