pt-archiver deletes not archived rows
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Undecided
|
Carlos Salguero |
Bug Description
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,
Log:
# generated and executes these SQLs (from mysql general log):
SELECT /*!40001 SQL_NO_CACHE */ `username`
INSERT INTO `xxxx`.
DELETE FROM `xxxx`.`xxxxx` WHERE (`groupname` = '100') LIMIT 1
The relevant part from the code:
$del_sql = 'DELETE'
. ($o->get(
. ($o->get(
. " FROM $src->{db_tbl} WHERE $del_stmt-
if ( $src->{
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");
}
Should be (Thanks for David Ducos):
if ( $src->{
$del_sql = 'DELETE'
. ($o->get(
. ($o->get(
. " FROM $src->{db_tbl} WHERE $del_stmt-
PTDEBUG && _d("DELETE index is unique; LIMIT 1 is not needed");
}
else {
$del_sql = 'DELETE'
. ($o->get(
. ($o->get(
. " FROM $src->{db_tbl} WHERE $del_stmt-
. ") AND (".$o->
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
Changed in percona-toolkit: | |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
status: | New → In Progress |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
milestone: | none → 2.2.19 |
status: | Fix Committed → Fix Released |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/ /jira.percona. com/browse/ PT-1360