Easily increase the auto increment for a MySQL table

Introduction

If we are trying to update the auto increment value for a table, we would usually run a function like this:

ALTER TABLE sales_flat_order AUTO INCREMENT = 1000;

This works fine if you know the original auto increment value for the table. What if you want to add 100 to auto increment? Or, what if you want to make this repeatable and do it on 50 or 100 tables?

Use Case:

One of our customers is doing a site-at-a-time migration to an updated version of their platform. The updated version uses a new but structurally identical database. How do we copy the contents of a number of the old tables over to the new database? After the updated version is released, we will have two databases that need to be kept in sync.Our solution is two-fold:

1. Increase the auto-increment.
2. And copy over the data from the old database to the new database.

For this specific instance (using Magento), we have 58 tables to copy from the old to the new. While this doesn’t sound like a lot, going into each table, finding the current auto increment value, and then altering the table takes time. Done manually, there is reasonable room for error.

How do you know how much to add to the auto increment?

In the case of a migration, and syncing over the old data, you need to forecast how many rows will be added to that table between now and when you are finished with the old database. We ran some estimates (based on previous sales history) and then added many times to that number for security. In our case 100,000 was plenty enough and a nice round number. This allowed us to look at the database and see what rows were associated with the old database and which ones are associated with the new.

A Note for Magento:

Don’t forget to also increase the increment ids for entities. These values are found in the eav_entity_store table.

Solution (Stored Procedure Creation Script):

We created a stored procedure to increase the auto increment value by a specified amount. No longer do you have to figure out what the original value was. Just run this SQL query to install the stored procedure, and you can apply it to as many tables as you want.

Note: you need to replace the [[DATABASE NAME GOES HERE]] with your database name.

delimiter //

CREATE PROCEDURE increaseAutoIncrement (INPUT_TABLE_NAME varchar(100), AMOUNT INT(10))
BEGIN
    SET @update_table = INPUT_TABLE_NAME;
   
    SELECT @auto_increment := (`AUTO_INCREMENT` + AMOUNT)
        FROM  INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = ‘[[DATABASE NAME GOES HERE]]’
        AND TABLE_NAME   = @update_table;
        
    SET @query = CONCAT('ALTER TABLE ', @update_table, ' AUTO_INCREMENT = ', @auto_increment);
    
    SELECT @query;
    
    PREPARE alterTableStmt FROM @query;
    EXECUTE alterTableStmt;
    DEALLOCATE PREPARE alterTableStmt;
END//

delimiter ;

Using the Solution:

To call the stored procedure, run this code:

call increaseAutoIncrement(‘table_name_goes_here’, 12345);

The first parameter is the name of the table that you want to increase its auto increment id. The second parameter is how much you want to increase the auto increment id.

Magento Order Migration:

Here is the script that we used to increase the auto increment id in preparation for launching this website. This script applies the changes to the quote / order / invoice / shipment tables as well as the customer tables.

call increaseAutoIncrement('mage_customer_address_entity', 100000);
call increaseAutoIncrement('mage_customer_address_entity_datetime', 100000);
call increaseAutoIncrement('mage_customer_address_entity_decimal', 100000);
call increaseAutoIncrement('mage_customer_address_entity_int', 100000);
call increaseAutoIncrement('mage_customer_address_entity_text', 100000);
call increaseAutoIncrement('mage_customer_address_entity_varchar', 100000);
call increaseAutoIncrement('mage_customer_entity', 100000);
call increaseAutoIncrement('mage_customer_entity_datetime', 100000);
call increaseAutoIncrement('mage_customer_entity_decimal', 100000);
call increaseAutoIncrement('mage_customer_entity_int', 100000);
call increaseAutoIncrement('mage_customer_entity_text', 100000);
call increaseAutoIncrement('mage_customer_entity_varchar', 100000);
call increaseAutoIncrement('mage_idealcheckout', 100000);
call increaseAutoIncrement('mage_persistent_session', 100000);
call increaseAutoIncrement('mage_sales_flat_creditmemo', 100000);
call increaseAutoIncrement('mage_sales_flat_creditmemo_comment', 100000);
call increaseAutoIncrement('mage_sales_flat_creditmemo_grid', 100000);
call increaseAutoIncrement('mage_sales_flat_creditmemo_item', 100000);
call increaseAutoIncrement('mage_sales_flat_invoice', 100000);
call increaseAutoIncrement('mage_sales_flat_invoice_comment', 100000);
call increaseAutoIncrement('mage_sales_flat_invoice_grid', 100000);
call increaseAutoIncrement('mage_sales_flat_invoice_item', 100000);
call increaseAutoIncrement('mage_sales_flat_order', 100000);
call increaseAutoIncrement('mage_sales_flat_order_address', 100000);
call increaseAutoIncrement('mage_sales_flat_order_grid', 100000);
call increaseAutoIncrement('mage_sales_flat_order_item', 100000);
call increaseAutoIncrement('mage_sales_flat_order_payment', 100000);
call increaseAutoIncrement('mage_sales_flat_order_status_history', 100000);
call increaseAutoIncrement('mage_sales_flat_quote', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_address', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_address_item', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_item', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_item_option', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_payment', 100000);
call increaseAutoIncrement('mage_sales_flat_quote_shipping_rate', 100000);
call increaseAutoIncrement('mage_sales_flat_shipment', 100000);
call increaseAutoIncrement('mage_sales_flat_shipment_comment', 100000);
call increaseAutoIncrement('mage_sales_flat_shipment_grid', 100000);
call increaseAutoIncrement('mage_sales_flat_shipment_item', 100000);
call increaseAutoIncrement('mage_sales_flat_shipment_track', 100000);
call increaseAutoIncrement('mage_sales_order_tax', 100000);
call increaseAutoIncrement('mage_sales_order_tax_item', 100000);
call increaseAutoIncrement('mage_sales_payment_transaction', 100000);
call increaseAutoIncrement('mage_salesrule', 100000);
call increaseAutoIncrement('mage_salesrule_coupon', 100000);
call increaseAutoIncrement('mage_salesrule_coupon_usage', 100000);
call increaseAutoIncrement('mage_salesrule_customer', 100000);
call increaseAutoIncrement('mage_salesrule_customer_group', 100000);
call increaseAutoIncrement('mage_salesrule_label', 100000);
call increaseAutoIncrement('mage_salesrule_product_attribute', 100000);
call increaseAutoIncrement('mage_salesrule_website', 100000);

Summary:

To quickly increase the auto increment id on a number of tables:

• Run the stored procedure creation script above.
• Call the stored procedure with the table name and the number to that table’s auto increment id.