pt-archiver deletes not archived rows

Hi,

Pt-archiver can delete rows which were not archived.

It should delete the same row what it just inserted/archived but it use a different conditions which is a serious issue because it can delete many important rows from a table.

There is no primary key in the schema.

Schema:

CREATE TABLE `usergroup` (
`UserName` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, -- usually unique values
`GroupName` varchar(64) NOT NULL DEFAULT '', -- 650 different values, table has 5.2 records!
`priority` int(11) NOT NULL DEFAULT '1',
KEY `UserName` (`UserName`(32)),
KEY `GroupName` (`GroupName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Command:

pt-archiver --run-time 10m --no-check-columns --check-slave-lag h=XXXXX,S=/var/run/mysqld/mysqld.sock --why-quit --statistics --host "XXXX" --user "xxxxx" --password "XXXXX" --source h=XXXXX,D=xxxx,t=usergroup --where "UserName IN (SELECT username FROM tableXXXXX)" --dest D=XXXXX,t=XXXXXX

Log:
# generated and executes these SQLs (from mysql general log):
SELECT /*!40001 SQL_NO_CACHE */ `username`,`groupname`,`priority` FROM `xxxxx`.`xxxxx` FORCE INDEX(`groupname`) WHERE (UserName IN (SELECT username FROM xxxxxxx)) AND ((`groupname` >= '100')) ORDER BY `groupname` LIMIT 1

INSERT INTO `xxxx`.`xxxxxx`(`username`,`groupname`,`priority`) VALUES ('37980','100','1')

DELETE FROM `xxxx`.`xxxxx` WHERE (`groupname` = '100') LIMIT 1

The relevant part from the code:

      $del_sql = 'DELETE'
         . ($o->get('low-priority-delete') ? ' LOW_PRIORITY' : '')
         . ($o->get('quick-delete') ? ' QUICK' : '')
         . " FROM $src->{db_tbl} WHERE $del_stmt->{where}";

         if ( $src->{info}->{keys}->{$del_stmt->{index}}->{is_unique} ) {
            PTDEBUG && _d("DELETE index is unique; LIMIT 1 is not needed");
         }
         else {
            PTDEBUG && _d("Adding LIMIT 1 to DELETE because DELETE index "
               . "is not unique");
            $del_sql .= " LIMIT 1";
         }

Should be (Thanks for David Ducos):

    if ( $src->{info}->{keys}->{$del_stmt->{index}}->{is_unique} ) {
         $del_sql = 'DELETE'
            . ($o->get('low-priority-delete') ? ' LOW_PRIORITY' : '')
            . ($o->get('quick-delete') ? ' QUICK' : '')
            . " FROM $src->{db_tbl} WHERE $del_stmt->{where}";

         PTDEBUG && _d("DELETE index is unique; LIMIT 1 is not needed");
      }
      else {
         $del_sql = 'DELETE'
            . ($o->get('low-priority-delete') ? ' LOW_PRIORITY' : '')
            . ($o->get('quick-delete') ? ' QUICK' : '')
            . " FROM $src->{db_tbl} WHERE $del_stmt->{where}";
            . ") AND (".$o->get('where').")"

         PTDEBUG && _d("Adding LIMIT 1 to DELETE because DELETE index "
               . "is not unique");
         $del_sql .= " LIMIT 1";
      }

Possible workaround:

--bulk-delete

with these parameter pt-archiver is going to the delete the right row.

Thanks,
Tibi