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

MySQL & MariaDB
1

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 - Stack Overflow),

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

2

Hi @ilake_Chang , welcome to the Percona Forums!

You’re probably using InnoDB Row Format of COMPACT or REDUNDANT
The REDUNDANT and COMPACT row formats support a maximum index key prefix length of 767 bytes whereas DYNAMIC and COMPRESSED row formats support an index key prefix length of 3072 bytes.

I might add PTDEBUG=1 to better understand the error message in general, but otherwise you shouldn’t need special flags in order to accomplish this UK index addition operation

you can attempt to add the UK but specify a prefix length definition when creating the index:

You can reduce this to:
ADD UNIQUE INDEX index_membership_customers_on_email_and_hotel_brand_id (email(200), hotel_brand_id)

1 Like