Pt-online-schema-change - failed with "Cannot determine the key_len of the chunk index"

Polyglot Projects Percona Toolkit
,
1

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

2

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.

3

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 (56.2 KB)

4

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:

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
5

I created your table in my test and the EXPLAIN looks correct. You must have something odd in your configuration.

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 |
+----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
6

Yes @matthewb , I see all different for “rows”

+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| 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 |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
7

I have run “ANALYZE TABLE” also, I see it is still the same @matthewb

+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| table-name.                 | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+

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.
8

Can you do SELECT WHERE order_id = 0? Do you only have 1 row?

9

I have executed direct 'ALTER TABLE ’ on this one and it worked without issues.

10

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.

1 Like