pt-online-schema-change does not maintain the same AUTO_INCREMENT value when altering the table

Reported by Simon J Mudd on 2011-12-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Daniel Nichter
2.0
Undecided
Daniel Nichter
2.1
Medium
Daniel Nichter

Bug Description

When running pt-online-schema-change we have seen an issue where the new table does now have the same AUTO_INCREMENT value as the old table. This can cause problems as new rows added to the new table may not get inserted in the right place.

I don't have details now but think it's quite easy to show especially if you create a table with an auto_increment column fill it with some rows and then remove some of the last rows so the AUTO_INCREMENT value does not represent MAX(PK)+1.

I can probably get more details if required but I think it's probably quite easy to test this.

Simon J Mudd (sjmudd) wrote :

I should have said: I'm using the following rpm: percona-toolkit-1.0.1-1

Simon J Mudd (sjmudd) wrote :

I found the comments from a colleague about this:

Hi
This is from a run I've made today.

Before the run

AUTO_INCREMENT=776754726

root@host1 [db1]> select max(id) from __old_Arch_MessageData_201101;
+-----------+
| max(id) |
+-----------+
| 776713349 |
+-----------+
1 row in set (0.00 sec)

After the run :

AUTO_INCREMENT=776713350

root@host1 [db1]> select max(id) from Arch_MessageData_201101;
+-----------+
| max(id) |
+-----------+
| 776713349 |
+-----------+
1 row in set (0.00 sec)

In this case, I don't think any problem is caused from this. but I'm wondering if there might be cases where it will...

tags: added: pt-online-schema-change
tags: added: auto-inc
Changed in percona-toolkit:
status: New → Triaged
Daniel Nichter (daniel-nichter) wrote :

Marked as Won't Fix on 2.0 because 2.1 will completely replace 2.0 given that it does all the same things but a lot better than 2.0.

Baron Schwartz (baron-xaprb) wrote :

2.1 is released now; marking this complete.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers