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

[Simon J Mudd]

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)]

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

[Simon J Mudd (sjmudd)]

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...

[Baron Schwartz (baron-xaprb)]

This is part of the specification for https://blueprints.launchpad.net/percona-toolkit/+spec/redesign-pt-online-schema-change

[Daniel Nichter (daniel-nichter)]

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)]

2.1 is released now; marking this complete.

[Shahriyar Rzayev (rzayev-sehriyar)]

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