Prestashop 1.6 - SQL 5.7

[Simply123]

Our hosting company is updating their server.  Our site works on the old server, but not on the new one.  Our Prestashop version is 1.6, the hosting company says the old server is running on sql 5.6 and the new server is runs on sql 5.7. 

Is there a patch that can be installed to run Prestashop 1.6 on sql 5.7?

I found this post:

https://www.prestashop.com/forums/topic/493398-mysql-57-causes-error-when-ps-try-to-insert-date-as-0000-00-00-000000/

but the change could not be applied.  There is no sql config file under the etc/ directory.  Any idea where the code should be inserted?

[Knowband Plugins]

Hi,

Can you please share the issue (screenshot, details etc) which you are facing with MYSQL 5.7?

 

Also, share the PS exact version as well because we are running PS 1.6.1.11 on MYSQL5.7.23 without any issue.

 

[Simply123]

Hi,

We're on version 16.1.1.  I have added images below.

I've checked 1-click upgrade - I can upgrade to 17.4.2.

 

[Knowband Plugins]

Okay.

This problem can be solved by making changes in my.cnf file (Its basically mysql config file). I think you might need to contact the hosting to make the changes.

You need to change the sql mode in the mysql config file (Need to copy these lines in the mysql config file & need to restart the mysql)

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

 

[musicmaster]

if you don't have access to the mysql config file you can also use the following mysql command

"SET SESSION sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'ONLY_FULL_GROUP_BY',''))"

It should be called somewhere early in the code (like config,inc.php).

[Simply123]

Thank you so much for your help Knowband Plugins, this fixed worked.  The error no longer appears on the debug screen.  I have tried a few screens, and still no errors.

Products are accessible and can be added to cart.

There is still an error on signing in though - is this also related to the sql as well?  I activated the console on Chrome and have added the screenshot below.  This is a test domain, as our live site is still on the old server until we can test the process and be sure it is working. 

[Knowband Plugins]

Hi,

I think it's not related to sql. Can you please go to the Network tab of Google Developer toolbar & share the response of the request which is being sent for login?

 

[Simply123]

Please see screenshot below -  is this the correct screen?

I activated the https:// recently - I notice everything is popping up with mixed content - I believe the images are still http://.  I just haven't found yet where to change it.  Could this be causing the issue?

[Knowband Plugins]

 Yes, Its the correct screen.

I can see that login request is blocked by the browser because of mixed content (means you are surfing the site on the https and login requests are going to http). It seems that you have not enabled the HTTPS correctly. 

Go to Admin -> Shop Parameter -> General & enable the "Enable SSL" option (The option which is mentioned in the below screen).

 

First, you need to click on the "Please click here" link to enable this option. Kindly check if this is enabled OR not.

 

 

Edited August 22, 2018 by Knowband Plugins (see edit history)

[Simply123]

Thank you, thank you Knowband Plugins.  I didn't see that under General.  All issues resolved.  Green lock showing in address bar instead of the warning.  Login working, order placed.  Back in business :).  Thank you so much!!

[Knowband Plugins]

Great

 

[jisas7]

Hi,

Now our hosting company is updating their server. Our Prestashop version is 1.6, the hosting company says the old server is running on sql 5.6 and the new server is runs on sql 8. 

There is a post that recommends using the code:

"SET SESSION sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'ONLY_FULL_GROUP_BY',''))"

Do you know exactly how I should put it?

thanks

[Prestachamps]

Hi,

you have a similar issue discussed here at the end of this forum post:

Have a nice day, Leo.

Edited May 27, 2022 by Prestachamps (see edit history)

[jisas7]

great Leo! thanks!

have a nice day too.