Zen Cart New Product Error: 1062 Duplicate entry '2147483647' for key 'PRIMARY'

A client of mine contacted me earlier because he was unable to add products to his Zen Cart store. I recently installed a couple modules for him and was thinking that the issue may be related to one of the modules however this did not end up being the case. After some investigation I was able to resolve the problem easily by first deleting a product that had been added and then by issuing a SQL command to modify the auto increment numbers for the Zen Cart products table. Below there is more information on the error as well as details on how to resolve the issue.

Zen Cart New Product MySQL Error:

1062 Duplicate entry '2147483647' for key 'PRIMARY'
in:
[insert into products (products_quantity, products_type, products_model, products_price, products_date_available, products_weight, products_status, products_destination, products_clients_id, products_virtual, products_tax_class_id, manufacturers_id, products_quantity_order_min, products_quantity_order_units, products_priced_by_attribute, product_is_free, product_is_call, products_quantity_mixed, product_is_always_free_shipping, products_qty_box_status, products_quantity_order_max, products_sort_order, products_discount_type, products_discount_type_from, products_price_sorter, products_image, products_date_added, master_categories_id) values ('0', '1', '', '20', null, '0', '1', '', '', '0', '3', '0', '1', '1', '0', '0', '0', '1', '0', '1', '0', '0', '0', '0', '0', '', now(), '187')]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

As you can see above the products products_id being entered is colliding with a previous entry. The reason being is the is the maximum size or the last products_id that can be used. It appears that somehow the products table’s auto increment cache was corrupted somehow and needs to be reset since there are nowhere near that many products.

Delete Previous Product:

First delete the product that has a products_id of 2147483647. I recommend first writing all of the information about the product down so it can be entered again after the problem is resolved. Once the information is noted delete the product using the Zen Cart administration site.

Reset products Auto Increment Setting:

Now view all of the products and sort based on products_id. It is easiest to do this using something like phpMyAdmin since you can sort easily. Take the largest products_id and add “1” to it and save that number as it will be used in the SQL command below. Issue the below command via phpMyAdmin or on the MySQL CLI with the number you just noted. Make sure that the products_id you noted is the highest products_id number.

ALTER TABLE products AUTO_INCREMENT = 12344011;

After the above command is executed the Auto Increment cache should be updated and you should now be able to enter products again. To test simply enter the product you deleted again and then enter another product to make sure that you don’t receive a duplicate key error. You can verify auto increment is working properly by viewing all of the products sorted by products_id. The two products you just entered should have products_id’s of the number used in the SQL command and then that same number plus one.