How to increase the ID product value to more than 4298035920

Danmant_DK · May 16

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

Prestashop Addict · May 17

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.

Knowband Plugins · May 20

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.

Danmant_DK · May 29

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

Prestashop Addict · May 30

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.

Danmant_DK · May 30

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?

Prestashop Addict · May 31

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

Danmant_DK · May 31

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?

Knowband Plugins · June 3

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.

Danmant_DK · June 3

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?

Prestashop Addict · June 4

@Danmant_DK if you're not a technician, I recommend you to pay for that kind of service, you'll save a lot of time and energy 😉

Knowband Plugins · June 4

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.

Danmant_DK · June 4

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?

ZHSoft · June 5

It depends on whether your field type is int or bigint.

https://dev.mysql.com/doc/refman/8.4/en/integer-types.html

Edited June 5 by ZHSoft (see edit history)

Danmant_DK · June 5

5 hours ago, ZHSoft said:

It depends on whether your field type is int or bigint.

https://dev.mysql.com/doc/refman/8.4/en/integer-types.html

Thank you for your help

As i am told, changing to BIGINT demands changing a lot of other stuff due to multilingual (danish) webshop and possible change with every update. Right?

ZHSoft · June 6

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.

Danmant_DK · June 6

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?

ZHSoft · June 7

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.

Danmant_DK · October 4

On 6/7/2024 at 7:29 AM, ZHSoft said:

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.

Sorry for my late response

I have downloaded the .sql database and opened it with Notepad++ Something went wrong and not all the data is present. Is Notepad++ not good for this purpose? Better suggestions?

In which categories should i change the int to bigint? Only in id_product?

Paul C · October 4

Don't change the database structure. At various times upgrades have modified the size of database fields so you're just saving up a problem for yourself down the line.

4.3 billion products should be enough....

Is your store live?

Danmant_DK · October 5

52 minutes ago, Paul C said:

Don't change the database structure. At various times upgrades have modified the size of database fields so you're just saving up a problem for yourself down the line.

4.3 billion products should be enough....

Is your store live?

No, it's not live. I was told to change the int to bigint in the database. I use the wholesalers id numbers and they are up to 10 figures. Any other idea to increase the product id numbers to 10 figures?

Paul C · October 5

6 hours ago, Danmant_DK said:

No, it's not live. I was told to change the int to bigint in the database. I use the wholesalers id numbers and they are up to 10 figures. Any other idea to increase the product id numbers to 10 figures?

If you need to preserve the wholesaler's id numbers then you should use them either as the reference or as a supplier_reference but not as the PrestaShop product id. The only time you would use the id field in an import is if you're updating existing products and need to preserve the id (they form part of the url for your products so will affect indexing in search engines).

There's a whole lot of business logic that's built around the assumption that those fields are defined in a specific way and any addon or module that uses those fields (directly or indirectly) have a good chance of not working if you mess with them, so you don't just have the core functionality to worry about. You will spend the rest of the lifetime of the store messing with fixing stuff rather than selling if you insist on using those ids in place of PrestaShop generated ones.

My advice would be to delete any products you've added so far and start again (or re-install the store from scratch, which might be your easiest option). This time use those wholesaler numbers as the reference or supplier_reference (those are both 64 characters long, so plenty big enough) and let the import assign an internal product id. It might seem like a lot of extra work but it will be well worth it in the long run - and you'll have a store that can (a) be supported, and (b) have a chance of working properly.

If you're going to be doing a lot of data import and updating, then I'd strongly advise working with a developer on a bespoke solution or using a "Store Manager" 3rd-party add on. It's hard enough selling online without also doing all your own tech support and data management with basic tools. I've never used the latter but I'm sure there will be recommendations on here (and not just from the author....).

Danmant_DK · October 5

15 hours ago, Paul C said:

If you need to preserve the wholesaler's id numbers then you should use them either as the reference or as a supplier_reference but not as the PrestaShop product id. The only time you would use the id field in an import is if you're updating existing products and need to preserve the id (they form part of the url for your products so will affect indexing in search engines).

The wholesalers id numbers are specific product numbers in which customers search. Isn't it an advantage to have these numbers in the url? I do update the existing products regularly. Can i do this with the reference numbers alone?

There's a whole lot of business logic that's built around the assumption that those fields are defined in a specific way and any addon or module that uses those fields (directly or indirectly) have a good chance of not working if you mess with them, so you don't just have the core functionality to worry about. You will spend the rest of the lifetime of the store messing with fixing stuff rather than selling if you insist on using those ids in place of PrestaShop generated ones.

Maybe it would be an idea to make a module that could make the use of bigint more easy?

My advice would be to delete any products you've added so far and start again (or re-install the store from scratch, which might be your easiest option). This time use those wholesaler numbers as the reference or supplier_reference (those are both 64 characters long, so plenty big enough) and let the import assign an internal product id. It might seem like a lot of extra work but it will be well worth it in the long run - and you'll have a store that can (a) be supported, and (b) have a chance of working properly.

If i delete all products and re-import them, does ps not continue the id numbers from where it ended?

If you're going to be doing a lot of data import and updating, then I'd strongly advise working with a developer on a bespoke solution or using a "Store Manager" 3rd-party add on. It's hard enough selling online without also doing all your own tech support and data management with basic tools. I've never used the latter but I'm sure there will be recommendations on here (and not just from the author....).

For sure help from a developer would help out, but i need a solution in which i myself can create and update products, because it is on a regularly basis. I have had ps for many years and i think it's become more and more difficult to handle. It should be easier

Thank you for your reply. Please have a look on my comments above

Danmant_DK · October 18

On 10/6/2024 at 1:10 AM, Danmant_DK said:

After using truncate in MySQL i have tried to csv import all products again, without ID. It does not work (product can not be saved)

Does anyone know the reason?

Paul C · October 19

11 hours ago, Danmant_DK said:

After using truncate in MySQL i have tried to csv import all products again, without ID. It does not work (product can not be saved)

Does anyone know the reason?

Can you post a screenshot of the errors you're seeing? Also what are the global import settings you are using?

I don't import products using the standard importer so am not an expert in it but I seem to remember people having to set the id column to 0 for every product.

Danmant_DK · October 21

On 10/19/2024 at 1:58 PM, Paul C said:

Can you post a screenshot of the errors you're seeing? Also what are the global import settings you are using?

I don't import products using the standard importer so am not an expert in it but I seem to remember people having to set the id column to 0 for every product.

Yes, i remembered also the point with ID set to 0. It works

I have tried several times to import all the products after truncate the ps_products files, but only once did it work. If the starting point is the same, the result should be the same, right?

Danmant_DK · November 20

I have issues with no images. Does the images refer to the ID number?

In case, how can i easy change from ID reference to Reference number? 30000+ products

Any help appreciated

Danmant_DK · November 20

Just now, Danmant_DK said:

I have issues with no images. Does the images refer to the ID number?

In case, how can i easy change from ID reference to Reference number? 30000+ products

Any help appreciated

Example: https://billigbyggemarked.dk/kuglehane-og-tilbehor/21351-kuglehane-mercury-tea-11-2-muffe-nippel.html

Danmant_DK · November 20

10 minutes ago, Danmant_DK said:

Example: https://billigbyggemarked.dk/kuglehane-og-tilbehor/21351-kuglehane-mercury-tea-11-2-muffe-nippel.html

No images when regenerate images

No images when importing/updating products using Reference number and image links

How to increase the ID product value to more than 4298035920

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in