Not the answer you need?
Register and ask your own question!

pt-archiver SELECT query fails because of primary key

pastobaspastobas EntrantInactive User Role Beginner
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

Comments

  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Could you share your create table statement to try to reproduce it?
  • pastobaspastobas Entrant Inactive User Role Beginner
    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
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    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,
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
  • pastobaspastobas Entrant Inactive User Role Beginner
    Thank you Carlos,

    I'll let you know how I get on.

    Cheers
    Chris
  • pastobaspastobas Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.