Jump to content

How to increase the ID product value to more than 4298035920


Recommended Posts

Hello

I'v issues with csv importing products with high ID values from 4298035920 and up (10 digits). These values cannot be saved. Until this moment i have not had similar problems with 9 digits ID values. Somewhere i wrote, that the maximum value is 4294967295 (10 digits). Is that true?

PS version 1.7.8.11

PHP-version: 7.4.33

Please help me to increase the id value explained from the ground up. Thank you

Link to comment
Share on other sites

You can change de database tables structure, by changing size, and object models. But it's a hard work because you also need to change foreign  keys in all tables and object models. And in all modules tables object models that used product id! And you'll have a risk of issue when updating/upgrade PS or modules.

Link to comment
Share on other sites

Hi,
In MySQL, the INT type has a maximum value of 2,147,483,647 for signed integers and 4,294,967,295 for unsigned integers.
You'll need to change the ID columns in your database tables to use the BIGINT type, which can handle much larger values (up to 9,223,372,036,854,775,807)
As there will be foreign key constraints that references the product ID, you’ll need to update those as well. For reference of the table ps_product_lang
ALTER TABLE ps_product_lang MODIFY COLUMN product_id BIGINT UNSIGNED;

Before making any changes, always make sure to have a backup of your database to prevent any kind of discrepancy or data loss.

Link to comment
Share on other sites

  • 2 weeks later...
Il y a 11 heures, Danmant_DK a dit :

Thank you for your help

This is too difficult for me to change, so i will try to find another way to solve the issue

Why don't you create products with PS id's. If you need to sync the flow after you can use a unique identifier like EAN, GTIN, REFERENCE... to match products.

Link to comment
Share on other sites

12 hours ago, Prestashop Addict said:

Why don't you create products with PS id's. If you need to sync the flow after you can use a unique identifier like EAN, GTIN, REFERENCE... to match products.

Thanks for your help
I've tried that, but no luck (have left the ID column blank). I have approx. 30000 active products and 14000 inactive ones. All created with forced ID and unique reference numbers. How do I create new products with ps_id numbers? Should all products be created again from scratch with ps_id numbers?

Link to comment
Share on other sites

7 hours ago, Prestashop Addict said:

You must delete all products, change the autoincrement value in ps_products database to 1. Then reimport with standard PS creation no force id.

I can not find the autoincrement value in the MySQL database ps_product. Please explain where to find it

When creating the products again after all are deleted, i just make a normal csv import with no ID column? And reference as guide?

Link to comment
Share on other sites

On 6/1/2024 at 5:18 AM, Danmant_DK said:

I can not find the autoincrement value in the MySQL database ps_product. Please explain where to find it

When creating the products again after all are deleted, i just make a normal csv import with no ID column? And reference as guide?

If you are deleting all the products you can simply drop the table and re-create the same and after that, you will have the id from 1. Make sure that you copy the current schema of the table so that you can run that insert query again.

Link to comment
Share on other sites

13 hours ago, Knowband Plugins said:

If you are deleting all the products you can simply drop the table and re-create the same and after that, you will have the id from 1. Make sure that you copy the current schema of the table so that you can run that insert query again.

Sorry, but i need a more specific explanation

Do you mean, download the ps_product, ps_product_lang SQL files. Delete current product sql files and upload the downloaded files without ID? Or should i csv import the products?

Link to comment
Share on other sites

10 hours ago, Danmant_DK said:

Sorry, but i need a more specific explanation

Do you mean, download the ps_product, ps_product_lang SQL files. Delete current product sql files and upload the downloaded files without ID? Or should i csv import the products?

Hi,

We meant that you export the schema for the product table only as we did using the adminer file. That will give you something like below

-- Adminer 4.8.1 MySQL 5.7.33-0ubuntu0.16.04.1 dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `ps_product`;
CREATE TABLE `ps_product` (
  `id_product` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_supplier` int(10) unsigned DEFAULT NULL,
  `id_manufacturer` int(10) unsigned DEFAULT NULL,
  `id_category_default` int(10) unsigned DEFAULT NULL,
  `id_shop_default` int(10) unsigned NOT NULL DEFAULT '1',
  `id_tax_rules_group` int(11) unsigned NOT NULL,
  `on_sale` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `online_only` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ean13` varchar(13) DEFAULT NULL,
  `isbn` varchar(32) DEFAULT NULL,
  `upc` varchar(12) DEFAULT NULL,
  `mpn` varchar(40) DEFAULT NULL,
  `ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
  `quantity` int(10) NOT NULL DEFAULT '0',
  `minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
  `low_stock_threshold` int(10) DEFAULT NULL,
  `low_stock_alert` tinyint(1) NOT NULL DEFAULT '0',
  `price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `unity` varchar(255) DEFAULT NULL,
  `unit_price_ratio` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `additional_shipping_cost` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `reference` varchar(64) DEFAULT NULL,
  `supplier_reference` varchar(64) DEFAULT NULL,
  `location` varchar(255) NOT NULL DEFAULT '',
  `width` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `height` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `depth` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `weight` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `out_of_stock` int(10) unsigned NOT NULL DEFAULT '2',
  `additional_delivery_times` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `quantity_discount` tinyint(1) DEFAULT '0',
  `customizable` tinyint(2) NOT NULL DEFAULT '0',
  `uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
  `text_fields` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `redirect_type` enum('404','301-product','302-product','301-category','302-category') NOT NULL DEFAULT '404',
  `id_type_redirected` int(10) unsigned NOT NULL DEFAULT '0',
  `available_for_order` tinyint(1) NOT NULL DEFAULT '1',
  `available_date` date DEFAULT NULL,
  `show_condition` tinyint(1) NOT NULL DEFAULT '0',
  `condition` enum('new','used','refurbished') NOT NULL DEFAULT 'new',
  `show_price` tinyint(1) NOT NULL DEFAULT '1',
  `indexed` tinyint(1) NOT NULL DEFAULT '0',
  `visibility` enum('both','catalog','search','none') NOT NULL DEFAULT 'both',
  `cache_is_pack` tinyint(1) NOT NULL DEFAULT '0',
  `cache_has_attachments` tinyint(1) NOT NULL DEFAULT '0',
  `is_virtual` tinyint(1) NOT NULL DEFAULT '0',
  `cache_default_attribute` int(10) unsigned DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `advanced_stock_management` tinyint(1) NOT NULL DEFAULT '0',
  `pack_stock_type` int(11) unsigned NOT NULL DEFAULT '3',
  `state` int(11) unsigned NOT NULL DEFAULT '1',
  `product_type` enum('standard','pack','virtual','combinations','') NOT NULL DEFAULT '',
  PRIMARY KEY (`id_product`),
  KEY `reference_idx` (`reference`),
  KEY `supplier_reference_idx` (`supplier_reference`),
  KEY `product_supplier` (`id_supplier`),
  KEY `product_manufacturer` (`id_manufacturer`,`id_product`),
  KEY `id_category_default` (`id_category_default`),
  KEY `indexed` (`indexed`),
  KEY `date_add` (`date_add`),
  KEY `state` (`state`,`date_upd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 2024-06-04 07:37:23

 

Once you have this query you can drop the table, and reimport the products. Make sure you have the complete data of the product.

Link to comment
Share on other sites

6 hours ago, Knowband Plugins said:

Hi,

We meant that you export the schema for the product table only as we did using the adminer file. That will give you something like below

-- Adminer 4.8.1 MySQL 5.7.33-0ubuntu0.16.04.1 dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `ps_product`;
CREATE TABLE `ps_product` (
  `id_product` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_supplier` int(10) unsigned DEFAULT NULL,
  `id_manufacturer` int(10) unsigned DEFAULT NULL,
  `id_category_default` int(10) unsigned DEFAULT NULL,
  `id_shop_default` int(10) unsigned NOT NULL DEFAULT '1',
  `id_tax_rules_group` int(11) unsigned NOT NULL,
  `on_sale` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `online_only` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ean13` varchar(13) DEFAULT NULL,
  `isbn` varchar(32) DEFAULT NULL,
  `upc` varchar(12) DEFAULT NULL,
  `mpn` varchar(40) DEFAULT NULL,
  `ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
  `quantity` int(10) NOT NULL DEFAULT '0',
  `minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
  `low_stock_threshold` int(10) DEFAULT NULL,
  `low_stock_alert` tinyint(1) NOT NULL DEFAULT '0',
  `price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `unity` varchar(255) DEFAULT NULL,
  `unit_price_ratio` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `additional_shipping_cost` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `reference` varchar(64) DEFAULT NULL,
  `supplier_reference` varchar(64) DEFAULT NULL,
  `location` varchar(255) NOT NULL DEFAULT '',
  `width` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `height` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `depth` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `weight` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `out_of_stock` int(10) unsigned NOT NULL DEFAULT '2',
  `additional_delivery_times` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `quantity_discount` tinyint(1) DEFAULT '0',
  `customizable` tinyint(2) NOT NULL DEFAULT '0',
  `uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
  `text_fields` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `redirect_type` enum('404','301-product','302-product','301-category','302-category') NOT NULL DEFAULT '404',
  `id_type_redirected` int(10) unsigned NOT NULL DEFAULT '0',
  `available_for_order` tinyint(1) NOT NULL DEFAULT '1',
  `available_date` date DEFAULT NULL,
  `show_condition` tinyint(1) NOT NULL DEFAULT '0',
  `condition` enum('new','used','refurbished') NOT NULL DEFAULT 'new',
  `show_price` tinyint(1) NOT NULL DEFAULT '1',
  `indexed` tinyint(1) NOT NULL DEFAULT '0',
  `visibility` enum('both','catalog','search','none') NOT NULL DEFAULT 'both',
  `cache_is_pack` tinyint(1) NOT NULL DEFAULT '0',
  `cache_has_attachments` tinyint(1) NOT NULL DEFAULT '0',
  `is_virtual` tinyint(1) NOT NULL DEFAULT '0',
  `cache_default_attribute` int(10) unsigned DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `advanced_stock_management` tinyint(1) NOT NULL DEFAULT '0',
  `pack_stock_type` int(11) unsigned NOT NULL DEFAULT '3',
  `state` int(11) unsigned NOT NULL DEFAULT '1',
  `product_type` enum('standard','pack','virtual','combinations','') NOT NULL DEFAULT '',
  PRIMARY KEY (`id_product`),
  KEY `reference_idx` (`reference`),
  KEY `supplier_reference_idx` (`supplier_reference`),
  KEY `product_supplier` (`id_supplier`),
  KEY `product_manufacturer` (`id_manufacturer`,`id_product`),
  KEY `id_category_default` (`id_category_default`),
  KEY `indexed` (`indexed`),
  KEY `date_add` (`date_add`),
  KEY `state` (`state`,`date_upd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 2024-06-04 07:37:23

 

Once you have this query you can drop the table, and reimport the products. Make sure you have the complete data of the product.

Thank you for your help

I am not a technician, so it will be too overwhelming for me to do and what will i do with the products that are not yet created? 

Is there anyway to do this with csv import of all products? 

Link to comment
Share on other sites

It doesn't need to be troublesome, but it's very simple:
1. Export your database as a text file through phpmyadmin.
2. Open the database file, find the int fields in it and replace them with bigint in batches
3. Save the above changes and re-import the database.
End.

Link to comment
Share on other sites

17 hours ago, ZHSoft said:

It doesn't need to be troublesome, but it's very simple:
1. Export your database as a text file through phpmyadmin.
2. Open the database file, find the int fields in it and replace them with bigint in batches
3. Save the above changes and re-import the database.
End.

Thank you for your help

I am sorry, but i am still confused

1) Export which product file? Is csv format okay?

2) Is it the ps_product file to be opened in "structure"? Is it the id_product line to be altered? In "datatype" i can change to bigint, but what is batches?

3) After re-importing the database, can i then import new products with a high id value as normal from BO? Do i need to make any changes after updating ps versions?

Link to comment
Share on other sites

1) Use phpmyadmin to export your database as a text file with a .sql suffix. It must be .sql.
2) You can find the corresponding tables in the structure one by one and change the bigint type, but this is too slow and inefficient. The batch method is as mentioned in 1).
3) This only changes all ints in the database to bigint, and does not affect any other functions. If you feel something is wrong after upgrading PS, do the method mentioned in 1) again.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...