pt-archiver "a" option for "use" database doesn't work

[David Sheldon]

If you use the "a" option in your DSN, it will correctly USE the database, however later, when TableParser runs, it will 'USE' the original database again, in get_create_table.

Version: 2.2.7
MySQL version: Server version: 5.1.49-1~bpo50+1-log (Debian)

I ran:
 PTDEBUG=1 ./pt-archiver --source h=10.10.10.93,D=carrierwebus,a=noarchivereplication,t=routegeofencehistory,u=XXX,p=YYYY -P3306 --where "tmEvent < '2011-01-01'" --purge --progress 5000

There's no debug output for the USE on line 5619, so the output doesn't actually show them both happening, and which order they were in, however it does output:

# $DBD::mysql::VERSION: 4.016 $DBI::VERSION: 1.612
# pt_archiver:5614 23048 Inspecting table on D=carrierwebus,P=3306,a=noarchivereplication,h=10.10.10.93,p=...,t=routegeofencehistory,u=root
# VersionParser:2771 23048 VersionParser got a dbh, trying to get the version
# VersionParser:2819 23048 InnoDB support: NO
# VersionParser:2831 23048 InnoDB version: NO
# TableParser:1874 23048 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:1879 23048 DBI::db=HASH(0x28b9d58) USE `carrierwebus`
# TableParser:1883 23048 SHOW CREATE TABLE `carrierwebus`.`routegeofencehistory`
# TableParser:1893 23048 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
# TableParser:2071 23048 Storage engine: MyISAM
# TableParser:1925 23048 Table cols: `nid`, `nvehicleid`, `ncustomerid`, `nroutegeofenceid`, `tmevent`, `bcode`
# TableParser:2071 23048 Storage engine: MyISAM
# TableParser:2087 23048 Parsed key: PRIMARY KEY (`nid`),
# TableParser:2107 23048 PRIMARY key cols: `nid`
# TableParser:2087 23048 Parsed key: KEY `nvehicleid_tmevent` (`nvehicleid`,`tmevent`),
# TableParser:2107 23048 nvehicleid_tmevent key cols: `nvehicleid`, `tmevent`
# TableParser:2087 23048 Parsed key: KEY `ncustomerid_nid` (`ncustomerid`,`nid`)
# TableParser:2107 23048 ncustomerid_nid key cols: `ncustomerid`, `nid`
# pt_archiver:5660 23048 SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")
# pt_archiver:5750 23048 sel cols: nid nvehicleid ncustomerid nroutegeofenceid tmevent bcode
# TableParser:1986 23048 Indexes sorted best-first: PRIMARY, ncustomerid_nid, nvehicleid_tmevent
# TableParser:2004 23048 Best index found is PRIMARY
# TableNibbler:3183 23048 Columns needed for DELETE: nid
# TableNibbler:3193 23048 Ordinals needed for DELETE: 0
# TableNibbler:3039 23048 Will ascend index PRIMARY
# TableNibbler:3051 23048 Will ascend columns nid
# TableNibbler:3062 23048 Will ascend, in ordinal position: 0
# VersionParser:2771 23048 VersionParser got a dbh, trying to get the version
# VersionParser:2819 23048 InnoDB support: NO
# VersionParser:2831 23048 InnoDB version: NO
# pt_archiver:5824 23048 Index for DELETE: PRIMARY
# pt_archiver:5838 23048 DELETE index is unique; LIMIT 1 is not needed
# pt_archiver:5904 23048 get first sql: SELECT /*!40001 SQL_NO_CACHE */ `nid`,`nvehicleid`,`ncustomerid`,`nroutegeofenceid`,`tmevent`,`bcode` FROM `carrierwebus`.`routegeofencehistory` FORCE INDEX(`PRIMARY`) WHERE (tmEvent < '2011-01-01') AND (`nid` < '161822') LIMIT 1
# pt_archiver:5905 23048 get next sql: SELECT /*!40001 SQL_NO_CACHE */ `nid`,`nvehicleid`,`ncustomerid`,`nroutegeofenceid`,`tmevent`,`bcode` FROM `carrierwebus`.`routegeofencehistory` FORCE INDEX(`PRIMARY`) WHERE (tmEvent < '2011-01-01') AND (`nid` < '161822') AND ((`nid` >= ?)) LIMIT 1
# pt_archiver:5906 23048 del row sql: DELETE FROM `carrierwebus`.`routegeofencehistory` WHERE (`nid` = ?)
# pt_archiver:5907 23048 ins row sql:
SELECT /*!40001 SQL_NO_CACHE */ `nid`,`nvehicleid`,`ncustomerid`,`nroutegeofenceid`,`tmevent`,`bcode` FROM `carrierwebus`.`routegeofencehistory` FORCE INDEX(`PRIMARY`) WHERE (tmEvent < '2011-01-01') AND (`nid` < '161822') LIMIT 1
SELECT /*!40001 SQL_NO_CACHE */ `nid`,`nvehicleid`,`ncustomerid`,`nroutegeofenceid`,`tmevent`,`bcode` FROM `carrierwebus`.`routegeofencehistory` FORCE INDEX(`PRIMARY`) WHERE (tmEvent < '2011-01-01') AND (`nid` < '161822') AND ((`nid` >= ?)) LIMIT 1
DELETE FROM `carrierwebus`.`routegeofencehistory` WHERE (`nid` = ?)

Which includes the errant "USE" on line 1879, in TableParser. As far as I can tell, removing that USE will fix it.

--- pt-archiver.orig 2014-02-28 17:11:49.000000000 +0000
+++ pt-archiver 2014-02-28 17:11:56.000000000 +0000
@@ -1875,10 +1875,6 @@
    eval { $dbh->do($new_sql_mode); };
    PTDEBUG && $EVAL_ERROR && _d($EVAL_ERROR);

- my $use_sql = 'USE ' . $q->quote($db);
- PTDEBUG && _d($dbh, $use_sql);
- $dbh->do($use_sql);
-
    my $show_sql = "SHOW CREATE TABLE " . $q->quote($db, $tbl);
    PTDEBUG && _d($show_sql);
    my $href;

[Muhammad Irfan (muhammad-irfan)]

I am able to reproduce this problem. option 'a' seems to be broken.

$ pt-archiver --version
pt-archiver 2.2.7

$ PTDEBUG=1 pt-archiver --source h=localhost,D=world,a=test,t=City,u=msandbox,p=msandbox -S /tmp/mysql_sandbox5616.sock --where "ID<4000" --purge --progress 1000 > archive.log 2>&1

# /usr/bin/perl 5.010001
# Linux centos63 2.6.32-279.el6.x86_64 #1 SMP Fri Jun 22 12:19:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
# Arguments: _[--source]_ _[h=localhost,D=world,a=test,t=City,u=msandbox,p=msandbox]_ _[-S]_ _[/tmp/mysql_sandbox5616.sock]_ _[--where]_ _[ID<4000]_ _[--purge]_ _[--progress]_ _[1000]_
# OptionParser:927 2087 Option rule: Specify at least one of --dest, --file, or --purge.
.
.
# TableParser:1874 2087 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:1879 2087 DBI::db=HASH(0x2120850) USE `world`
# TableParser:1883 2087 SHOW CREATE TABLE `world`.`City`
# TableParser:1893 2087 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
# TableParser:2071 2087 Storage engine: InnoDB

[Shahriyar Rzayev (rzayev-sehriyar)]

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