pt-archiver SELECT query fails because of primary key

Polyglot Projects Percona Toolkit
1

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

2

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

3

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

4

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: [url]https://bugs.launchpad.net/percona-toolkit/+bug/1691630[/url].
I’ll let you know when it is fixed.

Thanks,

5

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

6

Thank you Carlos,

I’ll let you know how I get on.

Cheers
Chris

7

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