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

**URL:** <https://forums.percona.com/t/pt-online-schema-change-failed-when-creating-new-tablle-specified-key-was-too-long-max-key-length-is-767-bytes-issue/16328>\
**Category:** MySQL & MariaDB\
**Tags:** percona\
**Created:** [June 27, 2022, 1:39pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-when-creating-new-tablle-specified-key-was-too-long-max-key-length-is-767-bytes-issue/16328 "2022-06-27T13:39:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ilake\_Chang](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ilake_chang/32/6827_2.png) [@ilake\_Chang](https://forums.percona.com/u/ilake_Chang)\
**Post date:** [June 27, 2022, 1:39pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-when-creating-new-tablle-specified-key-was-too-long-max-key-length-is-767-bytes-issue/16328/1 "2022-06-27T13:39:57Z")

</div>

The version, mysql is InnoDB 5.6.10-log, pt-online-schema-change 3.1.0. table info is

```auto
`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,

```auto
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](https://stackoverflow.com/questions/1814532/mysql-error-1071-specified-key-was-too-long-max-key-length-is-767-bytes)),

```auto
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

```auto
`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?

```auto
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

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [June 27, 2022, 5:09pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-when-creating-new-tablle-specified-key-was-too-long-max-key-length-is-767-bytes-issue/16328/2 "2022-06-27T17:09:09Z")

</div>

Hi @ilake_Chang , welcome to the Percona Forums!

> [@ilake\_Chang](#):
>
> How does the current table support this utfm8 varchar(255), How does it possible?

You’re probably using [InnoDB Row Format](https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-detrmining) 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._

> [@ilake\_Chang](#):
>
> - Maybe there are table settings I missed for `pt-online-schema-change`

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

> [@ilake\_Chang](#):
>
> 1. 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?

you can attempt to add the UK but specify a [prefix length](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique) definition when creating the index:

> [@ilake\_Chang](#):
>
> `ADD UNIQUE INDEX index_membership_customers_on_email_and_hotel_brand_id (email, hotel_brand_id)`

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