Prestashop 1.5.6.0 and PHP 5.6 not working.

[plutten22]

Hi.

 

How can i solve this problem ?

 

My host Loopia just change php to 5.6 and now my product list is gone on my webpage.

 

When i go to Back office and products i get following message:

 

Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cykelstaden_se.sav.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

Can i make any changes some where to solve this problem.

 

My prestashop version is 1.5.6.0

Webpage: www.cykelstaden.se

 

Please if someone can help.

 

Regards

 

Plutten22

[musicmaster]

This is not a PHP error. It is a MySQL 5.7 error. 

 

Try to insert somewhere the following Mysql command: 

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

You can also search the internet for "only_full_group_by" for more info.

 

Edit: At the place of the [spam-filter] you should put two at-signs.

 

See for example here

http://johnemb.blogspot.nl/2014/09/adding-or-removing-individual-sql-modes.html

Edited July 9, 2016 by musicmaster (see edit history)

[plutten22]

Hi.

 

Thanks for your help.

 

Please can you tell me where i can insert the mysql command.

 

Regards

 

Plutten22

[plutten22]

Hi again.

 

I contacted my host and got some help how to insert the mysql command.

 

But it dos not work. I get the following message:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'ONLY_FULL_GROUP_BY',''))
LIMIT ' at line 1

 

Please can you help

 

Regards

 

Plutten22

[musicmaster]

I see the Prestashop forum spam filter is active again. It getting more aggressive by the day. On the place of the [spam-filter] text you should put two at-signs.

[plutten22]

Hi
 
I wrote this command:

SET SESSION sql_mode=(SELECT REPLACE([spam-filter]sql_mode,'ONLY_FULL_GROUP_BY','')) with 2 at-signs

instead of [spam-filter]

 

Please look at attached picture.

 

It did not work !

 

Can you write the command ?

 

Regards

 

Plutten22

Edited July 9, 2016 by plutten22 (see edit history)

[musicmaster]

The SET SESSION is for when you implement it in the code. 

 

If you want to implement it the way you do you should likely use SET GLOBAL instead.

 

Also you kept brackets([]) around the at-signs. They shouldn't be there.

Edited July 9, 2016 by musicmaster (see edit history)

[plutten22]

Ok.

 

I think we are getting near now.

 

This message came up: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

what shall i do ?

 

Regards

 

Plutten22

[musicmaster]

For GLOBAL you need extra privileges. So you will need to settle for SESSION. Try at least without those brackets.

[plutten22]

Hi.

 

Thank you for reply.

 

I enter the command without the brackets. But i dont think it solved the problem.

 

See attached picture.

 

The text in green is saying:

 

And i still dont have any products in product_list and homefeatured on my webpage www.cykelstaden.se

 

Please do you have any more idaes. I would really appreciate it.

 

Regards

 

plutten22

[musicmaster]

You should not put this code in phpmyadmin but put in a Mysql query that you call from a line that you insert somewhere in the Prestashop code - at least before the problematic query is called.

[plutten22]

Hi musicmaster.

 

Thanks very much for your help. Im very grateful that you are helping me.

 

Can you explain more precisely what I should do. Im sorry but im not an expert at this.

 

Regards

 

Plutten22

[musicmaster]

You can execute an SQL commando in Prestashop with 

Db::getInstance()->ExecuteS($mysqlcommand);

For the rest I cannot help you. I have no Mysql 5.7 installed and so I am not able to test solutions for you.

 

Maybe someone else can...

[plutten22]

Ok

 

Thank you.

 

Where in prestashop shall i put this ?

 

Regards

 

Plutten22

[plutten22]

Hi again.

 

Shuld i put this in global.css ? Or somwhere else ?

 

Regards

 

Gary

[plutten22]

Ok. Thanks for help.

 

Problem is solved.

 

If any body else have same problem do this.

 

public_html/classes/db/DbPDO.php,

 

Line 75-77

 

// disable ONLY_FULL_GROUP_BY
        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)
                die(Tools::displayError('PrestaShop Fatal error'));

 

Regards

 

Gary

[dapperman2014]

Hi Guys, 

 

I have migrated prestashop cloud to a local host to test everything is working before I transfer it to a hosting service. 

 

Everything looks mostly fine, however in the backoffice/admin page I can't access or create new products, I get the following error: 

 

#13 select list not group clause and contains nonaggregated column 'backuppdnb_downloadable' which not functionally dependent columns group clause; this incompatible with sql_mode=only_full_group_by'.

 

Can anyone guide me through this? I am not that tech savvy, so a step by step process would be greatly appreciated.  

 

I ran an error report and get the following:

 

641. WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);

642. 		}643. 		elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))644. 		{645. 			if ($sql)646. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');647. 			throw new PrestaShopDatabaseException($this->getMsgError());

regards

 

Sammy

Edited January 13, 2017 by dapperman2014 (see edit history)

[Johann]

Ok. Thanks for help.

 

Problem is solved.

 

If any body else have same problem do this.

 

public_html/classes/db/DbPDO.php,

 

Line 75-77

 

// disable ONLY_FULL_GROUP_BY

        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)

                die(Tools::displayError('PrestaShop Fatal error'));

 

Regards

 

Gary

 

That was the solution for me too after an upgrade from mysql 5.6 to 5.7

[sylvir]

Thank you for this tip Johann.

It's work fine for me after a migration of 5.5 to 5.7.

[thomasharko]

Le 12/07/2016 à 10:43 AM, plutten22 a dit :

Ok. Thanks for help.

 

Problem is solved.

 

If any body else have same problem do this.

 

public_html/classes/db/DbPDO.php,

 

Line 75-77

 

// disable ONLY_FULL_GROUP_BY
        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)
                die(Tools::displayError('PrestaShop Fatal error'));

 

Regards

 

Gary

 

Works perfectly, 

Thanks

[natalie123123]

Please can you provide a solution for Prestashop 1.4 with MySQL 5.7?

[jason14]

On 7/12/2016 at 2:43 PM, plutten22 said:

 

 

 

 

 

4

On 7/12/2016 at 2:43 PM, plutten22 said:

Ok. Thanks for help.

 

Problem is solved.

 

If any body else have same problem do this.

 

public_html/classes/db/DbPDO.php,

 

Line 75-77

 

// disable ONLY_FULL_GROUP_BY
        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)
                die(Tools::displayError('PrestaShop Fatal error'));

 

Regards

 

Gary

Hello,

I tried to solve the same problem. But after editing it is giving me parser error. Can you tell me how to put this line on the DbPDO.php,

 

 

[R Santos]

[Solved this way]

Hello guys...
This was hard but it is now solved.

On filezilla ftp you look for:

/public_html/classes/db/DbPDO.php

Inside DbPDO.php

Edit and add the follow:

 // disable ONLY_FULL_GROUP_BY
        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)
                die(Tools::displayError('PrestaShop Fatal error'));

Now...

Make sure to add the comand before this → return $this->link;

Which is around line 72-77 (this may vary)

P.S. See attached image

Thank you guys!!!
You saved my shop :)

[Gipielle]

On 7/17/2019 at 5:10 PM, R Santos said:

[Solved this way]

Hello guys...
This was hard but it is now solved.

On filezilla ftp you look for:

/public_html/classes/db/DbPDO.php

Inside DbPDO.php

Edit and add the follow:

 // disable ONLY_FULL_GROUP_BY
        if ($this->link->exec('set session sql_mode=\'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\'') === false)
                die(Tools::displayError('PrestaShop Fatal error'));

Now...

Make sure to add the comand before this → return $this->link;

Which is around line 72-77 (this may vary)

P.S. See attached image

Thank you guys!!!
You saved my shop :)

Perfect !

[Eolia]

Add just:

$this->link->exec('SET SESSION sql_mode = \'\'');