Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-143

pt-archiver SELECT query fails because of primary key

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 3.0.3
    • Fix Version/s: 3.0.4
    • Component/s: None
    • Labels:
      None

      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`,`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"

      Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1691630

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              carlos.salguero Carlos Salguero
              Reporter:
              carlos.salguero Carlos Salguero
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 5 hours
                  5h