pt-archiver SELECT query fails because of primary key

[Carlos Salguero]

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`,`start`,`end`,`user_id`(13),`interval`),
KEY `uid_start_end` (`user_id`(13),`start`,`end`)

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`,`end`,`user_id`(13),`interval` LIMIT 1000;

I'm running pt-archiver via a bash script ...
pt-archiver --source "D=${SHARD_OLD},p=XXXXXX,t=$table" --dest "D=${SHARD_NEW},p=XXXXXX,t=$table" --where "some_id = ${SOME_ID}" --limit 1000 --no-ascend --no-delete --replace --low-priority-insert --statistics --progress 1000

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`,`end`,`user_id`(13),`interval` LIMIT 1000
"] at /usr/bin/pt-archiver line 5991.

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`,`start`,`end`,`user_id`(13),`interval`),
KEY `cid_start_end` (`user_id`(13),`start`,`end`)
) 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`,`start`,`sum_value`,`user_id`,`interval `,`mean`,`max`,`min` FROM test.stats_r FORCE INDEX(`PRIMARY`) WHERE (id = 12345) ORDER BY `id`,`start`,`end`,`user_id`,`interval` LIMIT 1000;

SELECT /*!40001 SQL_NO_CACHE */ `id`,`end`,`start`,`sum_value`,`user_id`,`interval `,`mean`,`max`,`min` FROM test.stats_r FORCE INDEX(`PRIMARY`) WHERE (id = 12345) ORDER BY `id`,`start`,`end`,`user_id`(13),`interval` LIMIT 1000;

The former completes without an error; the latter failes with "ERROR 1305 (42000): FUNCTION test.user_id does not exist"

[Carlos Salguero (carlos-salguero)]

When there is a table having a key like:
PRIMARY KEY (id,start,end,user_id(13),interval),
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

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-734