PHP PDO class to set the MySQL auto_increment_increment

A few days ago, I posted how to temporarily change the auto increment increment in MySQL. This post has an extension class to PHP's PDO to get the current auto increment value, change it to a new value and then reset it back to the original.

Download the PHP file here, compressed as a ZIP file.

More information

This PHP script has a class called PDOMySQLExtension, which extends PDO. You then create the database object in the same way as you would when using PDO, but there's three additional methods and a protected property which stores the original auto increment value for restoring it to how it was before you started messing around with it.

If you don't use PDO, then it should be easy enough to use the code in the functions as separate functions or as a subclass from another database class.

Subclass methods

getAutoIncrement(): this returns the current auto increment increment value from the current MySQL connection. By default this is 1, but on replicated master-master servers it's often set to a different number, typcially the number of servers in the replication chain. When this is called, the original value is stored in the protected originalAutoIncrement property if it hasn't already been.

setAutoIncrement(): this changes the setting to the new value, storing the original value in the protected originalAutoIncrement property if it hasn't already been. Note that it only retains this setting for as long as the connection to the MySQL server is going. A second script connecting to the database will insert records using the default increment.

restoreAutoIncrement(): resets the auto increment increment value back to what it was before being changed with setAutoIncrement.

But why?

I personally needed to do this for a particular project so have shared the code here for others to use. In my case, the two servers had an increment value of 2 and increment offsets of 1 & 2 (so one will do 1, 3, 5 and the other 2, 4, 6 etc) but the table in question needed to increment in 1s each time a new record was added, as per the client's request.

So the code looked something like this:

$pdo->setAutoIncrement(1);
$pdo->query('sql query to insert record into table');
$pdo->restoreAutoIncrement();

Gotcha #1

One thing to be aware of is that the increment value in the database will be stored as part of the table after a record is inserted.

If you have an auto increment increment value of 3 and an offset of 1, then after the first record is inserted (1) the next one will be 4, even if you change the increment value to 1 after the first insert. I found this out from testing/experimenting with the script linked to from this post.

Gotcha #2

Another gotcha is that the next record inserted won't necessarily be the last record plus the increment value. Again using 3 as the example with 1 as an offset, records would normally be inserted as 1, 4, 7, 10, 13 and so on.

If you change the increment to 1 the next two records inserted will be 16 then 17.

If you then change it back to 3, the next record will be 19, because that's the regular sequence for a table incrementing in 3s with an offset of 1.

Conclusion

I hope this script is useful to some people. It's a bit of an edge case but useful to share for the odd person who might need it.

Related posts:

• Temporarily changing the auto increment increment in MySQL
• PHP PDO DSN Connection String
• Handling errors when connecting to a database using PHP PDO