# Pt-online-schema-change - failed with "Cannot determine the key\_len of the chunk index" **URL:** \ **Category:** Percona Toolkit\ **Tags:** mysql, percona\ **Created:** [August 21, 2024, 10:55am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487 "2024-08-21T10:55:03Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![virin\_t](https://avatars.discourse-cdn.com/v4/letter/v/b38774/32.png) [@virin\_t](https://forums.percona.com/u/virin_t)\ **Post date:** [August 21, 2024, 10:55am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/1 "2024-08-21T10:55:03Z")
Hi I am trying to run pt-online-schema-change on a table like below pt-online-schema-change –alter=“CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci” –chunk-size=100000 –chunk-time=1 –max-load threads\_running=500 –max-lag=30 –set-vars lock\_wait\_timeout=1,foreign\_key\_checks=0,sql\_log\_bin=0 –no-check-replication-filters –no-drop-old-table –execute \ for one table I am getting the below error Cannot determine the key\_len of the chunk index because MySQL chose no index instead of the PRIMARY index for the first lower boundary statement. See --[no]check-plan in the documentation for more information. table already have a primary index, please suggest ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 21, 2024, 3:37pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/2 "2024-08-21T15:37:54Z")
Hello @virin_t, Please remove `--chunk-size` and `--chunk-time` and try again. These may be causing unexpected internal issues with EXPLAIN. If that does not help, please provide SHOW CREATE TABLE. You can also do `PTDEBUG=1 pt-online-schema-change ...` to see what the query is that pt-osc is trying to explain. ---
**Author:** ![virin\_t](https://avatars.discourse-cdn.com/v4/letter/v/b38774/32.png) [@virin\_t](https://forums.percona.com/u/virin_t)\ **Post date:** [August 22, 2024, 9:38am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/3 "2024-08-22T09:38:54Z")
Hi @matthewb thank you for the reply, I tried without those two and it is still the same. here is the PTDEBUG result attached. [pt-debug.txt](https://forums.percona.com/uploads/short-url/56rgMpmkjFBw7i6XLGSSjFh0zfx.txt) (56.2 KB) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 22, 2024, 1:10pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/4 "2024-08-22T13:10:04Z")
Can you run ‘ANALYZE TABLE’ on this table? Looking at the debug, line starting at 843, you can see it is indeed doing a full table scan. Are there really 40M rows in this table? I don’t really see anything wrong with this schema: ```auto CREATE TABLE `test`.`_table-name_new` ( `order_id` int unsigned NOT NULL DEFAULT '0', `quotes_id` bigint unsigned DEFAULT NULL, `is_persisted` tinyint unsigned NOT NULL DEFAULT '0', `data` mediumblob, `info` blob, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPRESSED ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 22, 2024, 1:11pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/5 "2024-08-22T13:11:45Z")
I created your table in my test and the EXPLAIN looks correct. You must have something odd in your configuration. ```auto mysql [localhost:8035] {msandbox} (test) > EXPLAIN SELECT order_id FROM `_table-name_new`; +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | _table-name_new | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ ``` ---
**Author:** ![virin\_t](https://avatars.discourse-cdn.com/v4/letter/v/b38774/32.png) [@virin\_t](https://forums.percona.com/u/virin_t)\ **Post date:** [August 23, 2024, 5:22am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/6 "2024-08-23T05:22:06Z")
> [@matthewb](#): > > `EXPLAIN SELECT order_id FROM` Yes @matthewb , I see all different for “rows” ```auto +----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | table-name | NULL | index | NULL | PRIMARY | 4 | NULL | 40745969 | 100.00 | Using index | +----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+ ``` ---
**Author:** ![virin\_t](https://avatars.discourse-cdn.com/v4/letter/v/b38774/32.png) [@virin\_t](https://forums.percona.com/u/virin_t)\ **Post date:** [August 23, 2024, 6:23am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/7 "2024-08-23T06:23:39Z")
I have run “ANALYZE TABLE” also, I see it is still the same @matthewb ```auto +-----------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+---------+----------+----------+ | table-name. | analyze | status | OK | +-----------------------------+---------+----------+----------+ ``` ```auto pt-online-schema-change \ > --alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" \ > --max-load threads_running=500 \ > --max-lag=30 \ > --set-vars lock_wait_timeout=1,foreign_key_checks=0,sql_log_bin=0 \ > --no-check-replication-filters \ > --no-drop-old-table \ > --execute \ 2024-08-22T23:05:05 Cannot determine the key_len of the chunk index because MySQL chose no index instead of the PRIMARY index for the first lower boundary statement. See --[no]check-plan in the documentation for more information. ``` ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 23, 2024, 7:03pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/8 "2024-08-23T19:03:37Z")
Can you do SELECT WHERE order\_id = 0? Do you only have 1 row? ---
**Author:** ![virin\_t](https://avatars.discourse-cdn.com/v4/letter/v/b38774/32.png) [@virin\_t](https://forums.percona.com/u/virin_t)\ **Post date:** [August 26, 2024, 10:46am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/9 "2024-08-26T10:46:04Z")
I have executed direct 'ALTER TABLE ’ on this one and it worked without issues. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 26, 2024, 2:36pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/10 "2024-08-26T14:36:20Z")
Great. Just for extra, I ran into this same situation with a client a few days ago. I’m still trying to understand what’s happening. If it is a bug, I’ll file it and hopefully it’ll get fixed. Glad you found a workaround solution. ---
**Author:** ![coyzhang](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@coyzhang](https://forums.percona.com/u/coyzhang)\ **Post date:** [February 19, 2025, 9:53am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/11 "2025-02-19T09:53:29Z")
hi, @matthewb I’ve encountered the same issue. Has the cause of this problem been identified? My client discovered this issue, but I can’t reproduce it using the table schema he provided. I hope you can reply to me. Thank you. ---
**Author:** ![coyzhang](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@coyzhang](https://forums.percona.com/u/coyzhang)\ **Post date:** [February 19, 2025, 10:14am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/12 "2025-02-19T10:14:09Z")
Additional information: The error I got is “cannot determine the key\_len of the chunk index because mysql chose no index instead of the primary index for the first lower boundary statement. see --[no]check - plan in the documentation for more information. at /usr/bin/pt - online - schema - change line 9549.\n\n”,“Progress”:100}]". The SQL statement I executed is “alter table asset\_unit drop key idx\_creator;”. The table schema is: CREATE TABLE `asset_unit` ( `id` bigint unsigned NOT NULL AUTO\_INCREMENT COMMENT ‘ID’, `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Name’, `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Description’, `posterId` int DEFAULT NULL COMMENT ‘Cover Image ID’, `assetTypeKey` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Asset Type’, `subAssetType` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Sub - Asset Type’, `teamId` bigint unsigned DEFAULT NULL COMMENT ‘Group ID’, `productTypeId` int DEFAULT NULL COMMENT ‘Product Type ID’, `selectorCarId` bigint unsigned DEFAULT NULL COMMENT ‘Vehicle Information ID’, `createdAt` datetime NOT NULL COMMENT ‘Creation Time’, `updatedAt` datetime NOT NULL COMMENT ‘Update Time’, `deletedAt` datetime DEFAULT NULL COMMENT ‘Deletion Time’, `creationUserId` int DEFAULT NULL COMMENT ‘Creation User ID’, `updatedUserId` int DEFAULT NULL COMMENT ‘Update User ID’, `deletionUserId` int DEFAULT NULL COMMENT ‘Deletion User ID’, `fileBatchId` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Current Original Resource ID’, `productTypeKey` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Product Type Key Value’, `creationUsername` varchar(128) COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Creating User’, `updatedUsername` varchar(128) COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Updated User Username’, `deletionUsername` varchar(128) COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Deleting User Name’, `teamKey` varchar(128) COLLATE utf8mb4\_general\_ci DEFAULT NULL COMMENT ‘Team Key’, `produced_by` varchar(255) COLLATE utf8mb4\_general\_ci DEFAULT ‘’ COMMENT ‘Asset Source, such as api - mago, etc.’, `status` tinyint unsigned DEFAULT ‘0’ COMMENT ‘Status, values from 0 - 10 are reserved and commonly used across business lines’, PRIMARY KEY (`id`), KEY `idx_asset_unit_created_at` (`createdAt`), KEY `idx_asset_unit_team_key` (`teamKey`), KEY `idx_team_key` (`deletedAt`,`teamKey`), KEY `idx_asset_type_key` (`deletedAt`,`assetTypeKey`), KEY `idx_creator` (`creationUsername`), KEY `idx_status` (`status`), KEY `idx_updated_at` (`updatedAt`) ) ENGINE=InnoDB AUTO\_INCREMENT=9599279 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_general\_ci COMMENT=‘Asset Unit’ ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [February 19, 2025, 4:25pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/13 "2025-02-19T16:25:20Z")
@coyzhang If you are just dropping an index, you don’t need PTOSC. Dropping indexes is already a background, non-blocking, online operation (has been since 5.6 IIRC). ---
**Author:** ![coyzhang](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@coyzhang](https://forums.percona.com/u/coyzhang)\ **Post date:** [February 20, 2025, 2:46am UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/14 "2025-02-20T02:46:19Z")
However, this table still requires the execution of some other DDLs, such as creating indexes, altering columns, and so on. There may be the same problem, and I want to figure out the root cause of the problem more clearly. Thanks I’d like to consult whether directly executing “drop index” will cause master-slave delay in the scenario of 8 million data. And will the same happen if executing ptosc? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [February 20, 2025, 3:47pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/15 "2025-02-20T15:47:25Z")
@coyzhang > [@matthewb](#): > > `PTDEBUG=1 pt-online-schema-change ...` to see what the query is that pt-osc is trying to explain. > [@coyzhang](#): > > I’d like to consult whether directly executing “drop index” will cause master-slave delay in the scenario of 8 million data. Dropping an index via direct ALTER should not cause lag because the table isn’t being altered. Only the segment (internal disk structure) is being removed. > [@coyzhang](#): > > And will the same happen if executing ptosc? pt-osc rebuilds the entire table for all ALTER including drop index. This is why you don’t need ptosc when doing certain ALTERs that are already background operations. Yes, lag can happen with ptosc because of how much data is being copied. ---
**Author:** ![coyzhang](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@coyzhang](https://forums.percona.com/u/coyzhang)\ **Post date:** [February 25, 2025, 3:14pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/16 "2025-02-25T15:14:42Z")
Hi @matthewb Combining the ptosc code and the PDBUG information, I have already found the cause of this issue. Also, I checked @virin_t ptbug.log and found that it was due to the same reason. When there is a field value of the auto-increment primary key with id = 0 in the MySQL table, this issue will occur, which leads to the failure of obtaining the index information when getting the execution plan. As for why it’s related to the auto-increment primary key, I still need to continue to locate the root cause. Thank you for your help. EXPLAIN SELECT /\*!40001 SQL\_NO\_CACHE \*/ \* FROM `db`.`table` FORCE INDEX (`PRIMARY`) WHERE `` \>= ? /_key\_len_/ params: 0 IndexLength:6884 36665 Range scan: $VAR1 = { extra =\> undef, filtered =\> ‘9829120’, id =\> ‘1’, key =\> undef, key\_len =\> undef, partitions =\> undef, possible\_keys =\> undef, ref =\> undef, rows =\> ‘40731096’, select\_type =\> ‘SIMPLE’, table =\> ‘table’, type =\> ‘ALL’ }; ---
**Author:** ![coyzhang](https://avatars.discourse-cdn.com/v4/letter/c/0ea827/32.png) [@coyzhang](https://forums.percona.com/u/coyzhang)\ **Post date:** [March 10, 2025, 3:45pm UTC](https://forums.percona.com/t/pt-online-schema-change-failed-with-cannot-determine-the-key-len-of-the-chunk-index/32487/17 "2025-03-10T15:45:13Z")
The root cause of this problem is that when using `unsigned int` to define the primary key column `id` in MySQL and performing a query of `id >= 0` , it will cause a full table scan. Even if `force index(primary)` is used, this is still the case. Therefore, in the result of the query plan, the index key is `null` , which leads to the failure of `pt` when checking the execution plan index. I think that pt should be able to perform some internal logical processing to avoid the occurrence of this problem. thanks @matthewb