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
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.
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)
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
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 |
+----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
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 |
+----+-------------+--------------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
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.
Can you do SELECT WHERE order_id = 0? Do you only have 1 row?
I have executed direct 'ALTER TABLE ’ on this one and it worked without issues.
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
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.
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’
@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).
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?
@coyzhang
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.
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.