pt-archiver SELECT query fails because of primary key

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?

Regards

Could you share your create table statement to try to reproduce it?

Hi Carlos,

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”

Regards
Chris

The problem is the Perl DBI library is returning the (13) as part of the column name:

colnames => ‘id,start,end,user_id(13),interval’,

I’ve created bug report for this issue: https://bugs.launchpad.net/percona-toolkit/+bug/1691630.
I’ll let you know when it is fixed.

Thanks,

The fix will be released in version 3.0.4 but you can download the fixed program at:

https://raw.githubusercontent.com/percona/percona-toolkit/PT-143/bin/pt-archiver

Regards

Thank you Carlos,

I’ll let you know how I get on.

Cheers
Chris

Hi Carlos,

It’s been a while since I was able to work with this. The issue has been fixed.

Thanks for your time.

Cheers
Chris