pt-archiver SELECT query fails because of primary key
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Carlos Salguero |
Bug Description
Hi
I'm using pt-archiver Ver 2.2.12 and mysql Ver 14.14 Distrib 5.6.21-70.1.
I have a table where one of the key components (user_id) is of type varchar(100); the table has the following keys:
PRIMARY KEY (`id`,`
KEY `uid_start_end` (`user_
The select query generated by pt-archiver is as follows:
SELECT /*!40001 SQL_NO_CACHE */ `id`,`end`,`start`, ...
FROM `dbX`.`tableY` FORCE INDEX(`PRIMARY`)
WHERE (id = 12345)
ORDER BY `id`,`start`
I'm running pt-archiver via a bash script ...
pt-archiver --source "D=${SHARD_
I get the following exception
DBD::mysql::st execute failed: FUNCTION dbX.user_id does not exist [for Statement "
SELECT /*!40001 SQL_NO_CACHE */ ...
FROM `dbX`.`tableY` FORCE INDEX(`PRIMARY`)
WHERE (id = 12345) ORDER BY `id`,`start`
"] at /usr/bin/
I'm not liberty to change the primary key (I know the select query works when I replace "user_id(13)" with "user_id").
Is there a way I can resolve this without creating (then dropping) an alternative index?
The create statement below excludes several fields for brevity and some name have been changed for the same reason. That said, I am able to reproduce the issue.
CREATE TABLE `stats_r` (
`id` int(10) unsigned NOT NULL,
`end` datetime NOT NULL,
`start` datetime NOT NULL,
`sum_value` float DEFAULT NULL,
`user_id` varchar(100) NOT NULL DEFAULT '',
`interval` int(10) unsigned NOT NULL DEFAULT '0',
`mean` float DEFAULT NULL,
`max` float DEFAULT NULL,
`min` float DEFAULT NULL,
PRIMARY KEY (`id`,`
KEY `cid_start_end` (`user_
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I've tested by creating the table in a test database and running the following queries:
SELECT /*!40001 SQL_NO_CACHE */ `id`,`end`
SELECT /*!40001 SQL_NO_CACHE */ `id`,`end`
The former completes without an error; the latter failes with "ERROR 1305 (42000): FUNCTION test.user_id does not exist"
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
tags: | added: pt143 |
Changed in percona-toolkit: | |
status: | Triaged → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
When there is a table having a key like: end,user_ id(13), interval) ,
PRIMARY KEY (id,start,
DBI driver is taking (13) as part of the field name.
Fixed the way pt-archiver composes the key using DBI cols field
instead of the colnames field since the cols field has the correct
field names for the key.
https:/ /github. com/percona/ percona- toolkit/ pull/224