How to Change the Order Increment ID and Allow Very Large Order Numbers in OpenCart

Problem

I recently found myself needing to set up various stores with very large custom order numbers to avoid conflicting order numbers when centrally processing orders from multiple stores.

Background Information

When OpenCart is installed the tables with order_id columns are setup as datatype INT(11) and by default these columns are signed, meaning they support negative or positive numbers (which doesn’t make any sense for order numbers). As an INT(11) SIGNED column it could contain order numbers up to 2147483647 and if the field was INT(11) UNISGNED it could then contain order numbers up to 4294967295. But for my administrative needs this was still not enough.

Solution

Note: The following method has been tested on OpenCart v1.5.2 only and I can’t say how it will behave on other versions.

Update (7/9/2014): This method does not work on OpenCart v1.5.4+. OpenCart is now converting the large order number into a smaller integer before it is written to the database thereby breaking this method.

First things first, you should take this opportunity to backup your OpenCart database in case something goes wrong.

Allowing Very Large Order Numbers

After eliminating the other integer data types I turned to the BIGINT UNSIGNED datatype which can contain order numbers up to 18446744073709551615.

To make use of this datatype you will need to run some SQL queries to alter some columns in the database schema. If your install has a table prefix setup adjust the queries as necessary.

ALTER TABLE `affiliate_transaction` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `coupon_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `customer_reward` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `customer_transaction` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `order_download` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_fraud` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `order_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_option` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_product` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_total` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `order_voucher` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `return` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `voucher` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;
ALTER TABLE `voucher_history` CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NOT NULL;

Setting the Order Increment ID

Now you can the set the order table’s order_id column AUTO_INCREMENT value to a non-negative number between 1 and 18446744073709551615. The SQL command for that follows:

ALTER TABLE `order` AUTO_INCREMENT = 9010003001;

Once again, if your install is using a table prefix, update the query as necessary.

Hopefully in the future the OpenCart developers will recognize that some need to use very large custom order numbers and adjust the install database schema so that we don’t have to hack the tables. But until then this is what works for me.