auto increment and offset error on installing PrestaShop

[NG12]

Hi, I tried to install Prestashop and I'm already stuck on 1 error when it wants to use MySQL!

 

"The values of auto_increment increment and offset must be set to 1"

 

Prestashop succeed on connecting database but it's not able to fix this by yourself?

Or to reset database? That's incredible!

 

Anyway, I can't find anything on Google to fix this problem.

 

Which SQL command have I to execute to set these values?

Thank you very much

 

[rocky]

I've never encountered this error myself. You can use code like the following to change the autoincrement to 1:

ALTER TABLE `ps_address` AUTO_INCREMENT = 1

Create more queries for each table that needs to be updated.

[headgeek]

I also do have this problem while trying to install it on a percona cluster with 3 nodes . Is there a workaround ?

Edited November 14, 2016 by headgeek (see edit history)

[SiliconAlchemy]

I also do have this problem while trying to install it on a percona cluster with 3 nodes . Is there a workaround ?

 

In src/PrestaShopBundle/Install/Database.php lines 81-83 as of v1.7.0.3, comment these lines out:

                    #if (!Db::checkAutoIncrement($server, $login, $password)) {

                    #    $errors[] = $this->translator->trans('The values of auto_increment increment and offset must be set to 1', array(), 'Install');

                    #}

 

I've no idea what further effect this will have.  I don't understand why a php app should be caring about how the underlying database is setup.  Autoinc increments and offsets are used for multimaster replication and as you've found out galera/PXC.   Both of these are widely used vital replication strategies for those who care about their data.

[headgeek]

 

In src/PrestaShopBundle/Install/Database.php lines 81-83 as of v1.7.0.3, comment these lines out:

                    #if (!Db::checkAutoIncrement($server, $login, $password)) {

                    #    $errors[] = $this->translator->trans('The values of auto_increment increment and offset must be set to 1', array(), 'Install');

                    #}

 

I've no idea what further effect this will have.  I don't understand why a php app should be caring about how the underlying database is setup.  Autoinc increments and offsets are used for multimaster replication and as you've found out galera/PXC.   Both of these are widely used vital replication strategies for those who care about their data.

 

Thanks for the hint, I'll try to test it and report it. However for production, I prefer not to do that unless a prestashop developer jumps in and tell me it won't break anything meanwhile I'll find a different HA strategy.

[f.grande]

I'm jumping in even if - unfortunately - I'm not a PrestaShop Developer... ;-)

 

Just curious to understand the real meaning of this... Why installation care about single auto increments ? I tried to investigate in source code, but I can't see any comment about it.

 

Regars

FabioG

Edited March 20, 2017 by f.grande (see edit history)

[Knowband Plugins]

MySQL uses two variables - auto_increment_increment and auto_increment_offset to set the value of table's primary key. You can check the values of these two variables using following SQL query -

 

SHOW VARIABLES LIKE 'auto_inc%'

Further, you can read about this here - https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

 

During installation, Prestashop checks that value of these two variables is set to 1. If not then it gives error as you are facing. To overcome this issue, you have to update values of these two variables in your MySQL using following statements -

SET [spam-filter]auto_increment_increment=1;

SET [spam-filter]auto_increment_offset=1;

[SiliconAlchemy]

We're aware of what it is and how to set it.  The problem is if it's set to something other than 1, it's for a reason - we've set it that way.  Or, it's set automatically like the case of galera/PXC and cannot be changed.

The point is that Prestashop should have no business down at this level and should not care about row IDs.  If this check is actually needed for prestashop, it points to some bad db design/code architecture decisions.

This single issue precludes using Prestashop in many widely used clustering setups.

 

 

MySQL uses two variables - auto_increment_increment and auto_increment_offset to set the value of table's primary key. You can check the values of these two variables using following SQL query -

 

SHOW VARIABLES LIKE 'auto_inc%'

Further, you can read about this here - https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

 

During installation, Prestashop checks that value of these two variables is set to 1. If not then it gives error as you are facing. To overcome this issue, you have to update values of these two variables in your MySQL using following statements -

SET [spam-filter]auto_increment_increment=1;
SET [spam-filter]auto_increment_offset=1;

 

[madfred]

Prestashop won't work without auto_increment_increment=1. If you try to install it on a MySQL with Master-Master Replication it will fail because of the wrong id's.

Very poor code, never seen something strange before. The values are inserted, no matter what the value of the relation table is.

So you will get an error during install that you are not allowed to install modules. That's correct because the employee id_profile is "1", but the id_profile within ps_profile is "2" for the master. Same behavior within the shops, languages, categories and so on.

 

But this only on the second master, on the first master you are able to install, but during the work you will get a lot of errors.

 

 

[benwiggy]

Still seeing this error on Prestashop 1.73. I can't install as this error prevents me from continuing.

[fskhan]

Well, So sad and sorry to write what I am writing. I spent hours working on PrestaShop to find the following.

1. Database design is **bad** -- If it depends upon row IDs. Period.

2. Front Display is not configurable from any menu, it requires code changes to increase the number of products in a row. 

3. There is no SLider Scroll within the prestashop unless you pay to find out that it breaks something else.

3. There is no Documented Import or Export details. It is left for user to figure out who must have a programmer level understanding.

4. Full screen or width control is poor.

5. Its Core is heavy duty inert code, which badly affects performance.

Yes it is a good learning tool for a programmer but not ready for any prime time. Most folks who started to use it in 2014 and paid for modules - ended up wasting their money. When I click on their stores links in 2018 - they are all broken links. That tells us a lot. Well ... looking for something better and stable. Wish me luck   ... No pun intended. At least this was a good start.  

[jlit]

I just tried to install prestashop and got the same error. On my hosts DB, auto increment is set to 2. My hosting company advised this should not be changed.  It looks like prestashop has indeed be hard coded to only work with auto increment of 1, which I have never seen in 30 years of database development.  If their DB code is this bad, to only work with very basic mysql installs, its not something I would want to use in production.  I also frequently use galera cluster in larger installations. Looks like I need to find a different e-commerce provider. I am surprised that the e-commerce platform comparison/review sites don't mention this major show stopping limitation.

I am also surprised that noone from prestashop has jumped in with guidance.  

Edited February 13, 2019 by jlit (see edit history)

[Digital Human]

On 2/13/2019 at 11:56 PM, jlit said:

I just tried to install prestashop and got the same error. On my hosts DB, auto increment is set to 2. My hosting company advised this should not be changed.  It looks like prestashop has indeed be hard coded to only work with auto increment of 1, which I have never seen in 30 years of database development.  If their DB code is this bad, to only work with very basic mysql installs, its not something I would want to use in production.  I also frequently use galera cluster in larger installations. Looks like I need to find a different e-commerce provider. I am surprised that the e-commerce platform comparison/review sites don't mention this major show stopping limitation.

I am also surprised that noone from prestashop has jumped in with guidance.  

yes I just landed on the same problem. My god.

 

Solution 1):   

1) Goto:   src/PrestaShopBundle/Install

2) Open Database.php

3) Escape line 81,81 and 83;

 

Solution 2):  When using MariaDB Galera in Cluster setup. Like MultiMaster of MasterSlave settings.

!!! *** Do this only on 1 of the nodes *** !!!

1) Edit /etc/my.cnf.d/server.cnf

2) Add or edit:

wsrep_auto_increment_control=OFF
auto_increment_increment=1
auto_increment_offset=1

3) Install Prestashop

4) When finished remove the settings

Edited March 5, 2020 by Digital Human
Found solution (see edit history)

[masterblaster]

So is anybody happily using Prestashop in production on a multi-master DB cluster ?

I managed to install it by bypassing the installer controls thanks to  this PR
but after installation I found the "home" category id set to 2 in ps_shop,
while being set with id 5 in ps_category due to auto-increment offset so category tree
was not showing off unless manual-fixing the ID 😥

@Digital Human solution 2 should work around this on very first install
(you should also take care to "point" installer to the single modified node DB instance
and not to the DB load balancer) 
but I am quite concerned that there are other auto-increment code issues so I think I'll stick with a
Master/Slave setup right now.

[SiliconAlchemy]

I certainly wouldn't be basing an ecommerce business on a software platform that you've had to hack and slash to get to install on a widely used enterprise database platform.  I spoke to two very good DBAs about this and they both said: walk away, this points to bad design and who knows what else is hiding down the line.