Prestashop Database Exception

Two Bears Ceramics · October 17, 2020

My shopfront just came up with the following Database Exception message. It does this every time someone tries to access my shopfront. No updates for at least twelve months and no Backend changes for several years and everything has been going smoothly. Any advice on what has happened and how it might have happened and what to do about it? Backend seems to be working fine at this stage - Just the Front end is down. I am Technanderthal so the simpler the better.

Many thanks Eddy

[PrestaShopDatabaseException]

Expression #78 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'twobears_Shopfront.stock.out_of_stock' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, product_shop.`date_add` > "2020-10-07" as new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute FROM `2bearsproduct` p INNER JOIN 2bearsproduct_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `2bearsproduct_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1 LEFT JOIN `2bearsimage` `i` ON i.`id_product` = p.`id_product` LEFT JOIN 2bearsimage_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `2bearsimage_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 1 LEFT JOIN `2bearsmanufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` LEFT OUTER JOIN `2bearsproduct_attribute` pa ON p.`id_product` = pa.`id_product` LEFT JOIN 2bearsproduct_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1) LEFT JOIN 2bearsstock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND (product_shop.`date_add` > "2020-10-07") GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC LIMIT 8

at line 646 in file classes/db/Db.php

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()); 648. } 649. } 650. 651. /**

DbCore->displayError - [line 340 - classes/db/Db.php] - [1 Arguments]

DbCore->query - [line 516 - classes/db/Db.php] - [1 Arguments]

DbCore->executeS - [line 2173 - classes/Product.php] - [1 Arguments]

ProductCore::getNewProducts - [line 102 - modules/blocknewproducts/blocknewproducts.php] - [3 Arguments]

BlockNewProducts->getNewProducts - [line 144 - modules/blocknewproducts/blocknewproducts.php]

BlockNewProducts->hookdisplayHomeTab - [line 510 - classes/Hook.php] - [1 Arguments]

HookCore::exec - [line 41 - controllers/front/IndexController.php] - [1 Arguments]

IndexControllerCore->initContent - [line 180 - classes/controller/Controller.php]

ControllerCore->run - [line 373 - classes/Dispatcher.php]

DispatcherCore->dispatch - [line 28 - index.php]

musicmaster · October 17, 2020

Complicated story.

Mysql has in its newer versions some stricter criteria for queries. That gives problems in Prestashop and many other software packages.

You can disable those criteria with the command "sql_mode=''" and if I remember well Prestashop is doing that.

However, your error shows that for you "sql_mode=only_full_group_by". So that is not good.

The big question is where this comes from. Maybe your hosting provider did some upgrade of Mysql?

Note that these problems don't happen with MariaDb.

Two Bears Ceramics · October 18, 2020

Thank you for that. Where would I find the command "sql_mode=""? (config file?) Would upgrading Prestashop to the latest version fix this? Could I retro Mysql to an older version? Again I appreciate your help. Eddy

musicmaster · October 18, 2020

You don't write which PS version you have. Those changes have been around for several years now so your PS version must be quite old if it doesn't contain commands to set sql_mode.

The best way to start is to do a text search for "sql_mode" in your files.

Two Bears Ceramics · October 20, 2020

According to my settings.inc.php I am running 1.6.0.14. I have been through just about every file in my folder and still haven't come up with sql_mode. Suggestions for where it might be hiding? If I can find it I can change it. I don't have a problem doing that and I have full access to Cpanel etc. AS always appreciate any advice.

define('_MYSQL_ENGINE_', 'InnoDB');

define('_PS_CACHING_SYSTEM_', 'CacheFs');

define('_PS_CACHE_ENABLED_', '0');

define('_COOKIE_KEY_', 'Ve1PdBWLFQ0SZWscsjuJAjQuirYf5v6NcaMoT0iDWJzFnc0JOOPfJOze');

define('_COOKIE_IV_', 'oWqLicP0');

define('_PS_CREATION_DATE_', '2015-10-06'); if (!defined('_PS_VERSION_'))

define('_PS_VERSION_', '1.6.0.14');

define('_RIJNDAEL_KEY_', '6sT92rhuHzsaWmud5pGQYOTqdp9nLeQJ');

define('_RIJNDAEL_IV_', '3pcdKbpENp58bNb7SaNo1w==');

musicmaster · October 20, 2020

Please use a tool like Astrogrep

http://astrogrep.sourceforge.net/

musicmaster · October 20, 2020

PS 1.6.0.14 has in DbPDO.php: $this->link->exec('SET SESSION sql_mode = \'\'');

That should work. You might add the same string at some other locations in the code.

Two Bears Ceramics · October 20, 2020

This is the full DbPDO.php file. Didn't Find any $this->link->exec('SET SESSION sql_mode = \'\'');

<?php
/*
* 2007-2015 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
* @author PrestaShop SA <[email protected]>
* @copyright 2007-2015 PrestaShop SA
* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
* International Registered Trademark & Property of PrestaShop SA
*/

/**
* @since 1.5.0
*/
class DbPDOCore extends Db
{
   protected static function _getPDO($host, $user, $password, $dbname, $timeout = 5)
   {
       $dsn = 'mysql:';
       if ($dbname)
           $dsn .= 'dbname='.$dbname.';';
       if (preg_match('/^(.*):([0-9]+)$/', $host, $matches))
           $dsn .= 'host='.$matches[1].';port='.$matches[2];
       elseif (preg_match('#^.*:(/.*)$#', $host, $matches))
           $dsn .= 'unix_socket='.$matches[1];
       else
           $dsn .= 'host='.$host;

return new PDO($dsn, $user, $password, array(PDO::ATTR_TIMEOUT => $timeout, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
}

   public static function createDatabase($host, $user, $password, $dbname, $dropit = false)
   {
       try {
           $link = DbPDO::_getPDO($host, $user, $password, false);
           $success = $link->exec('CREATE DATABASE `'.str_replace('`', '\\`', $dbname).'`');
           if ($dropit && ($link->exec('DROP DATABASE `'.str_replace('`', '\\`', $dbname).'`') !== false))
               return true;
       } catch (PDOException $e) {
           return false;
       }
       return $success;
   }

   /**
   * @see DbCore::connect()
   */
   public function connect()
   {
       try {
           $this->link = $this->_getPDO($this->server, $this->user, $this->password, $this->database, 5);
       } catch (PDOException $e) {
           die(sprintf(Tools::displayError('Link to database cannot be established: %s'), utf8_encode($e->getMessage())));
       }

       // UTF-8 support
       if ($this->link->exec('SET NAMES \'utf8\'') === false)
           die(Tools::displayError('PrestaShop Fatal error: no utf-8 support. Please check your server configuration.'));

return $this->link;
}

   /**
   * @see DbCore::disconnect()
   */
   public function disconnect()
   {
       unset($this->link);
   }

   /**
   * @see DbCore::_query()
   */
   protected function _query($sql)
   {
       return $this->link->query($sql);
   }

   /**
   * @see DbCore::nextRow()
   */
   public function nextRow($result = false)
   {
       if (!$result)
           $result = $this->result;

if (!is_object($result))
return false;

return $result->fetch(PDO::FETCH_ASSOC);
}

   /**
   * @see DbCore::getAll()
   */
   protected function getAll($result = false)
   {
       if (!$result)
           $result = $this->result;

if (!is_object($result))
return false;

return $result->fetchAll(PDO::FETCH_ASSOC);
}

   /**
   * @see DbCore::_numRows()
   */
   protected function _numRows($result)
   {
       return $result->rowCount();
   }

   /**
   * @see DbCore::Insert_ID()
   */
   public function Insert_ID()
   {
       return $this->link->lastInsertId();
   }

   /**
   * @see DbCore::Affected_Rows()
   */
   public function Affected_Rows()
   {
       return $this->result->rowCount();
   }

   /**
   * @see DbCore::getMsgError()
   */
   public function getMsgError($query = false)
   {
       $error = $this->link->errorInfo();
       return ($error[0] == '00000') ? '' : $error[2];
   }

   /**
   * @see DbCore::getNumberError()
   */
   public function getNumberError()
   {
       $error = $this->link->errorInfo();
       return isset($error[1]) ? $error[1] : 0;
   }

   /**
   * @see DbCore::getVersion()
   */
   public function getVersion()
   {
       return $this->getValue('SELECT VERSION()');
   }

   /**
   * @see DbCore::_escape()
   */
   public function _escape($str)
   {
       $search = array("\\", "\0", "\n", "\r", "\x1a", "'", '"');
       $replace = array("\\\\", "\\0", "\\n", "\\r", "\Z", "\'", '\"');
       return str_replace($search, $replace, $str);
   }

   /**
   * @see DbCore::set_db()
   */
   public function set_db($db_name)
   {
       return $this->link->exec('USE '.pSQL($db_name));
   }

   /**
   * @see Db::hasTableWithSamePrefix()
   */
   public static function hasTableWithSamePrefix($server, $user, $pwd, $db, $prefix)
   {
       try {
           $link = DbPDO::_getPDO($server, $user, $pwd, $db, 5);
       } catch (PDOException $e) {
           return false;
       }

       $sql = 'SHOW TABLES LIKE \''.$prefix.'%\'';
       $result = $link->query($sql);
       return (bool)$result->fetch();
   }

   public static function checkCreatePrivilege($server, $user, $pwd, $db, $prefix, $engine = null)
   {
       try {
           $link = DbPDO::_getPDO($server, $user, $pwd, $db, 5);
       } catch (PDOException $e) {
           return false;
       }

if ($engine === null)
$engine = 'MyISAM';

       $result = $link->query('
       CREATE TABLE `'.$prefix.'test` (
           `test` tinyint(1) unsigned NOT NULL
       ) ENGINE='.$engine);
       if (!$result)
       {
           $error = $link->errorInfo();
           return $error[2];
       }
       $link->query('DROP TABLE `'.$prefix.'test`');
       return true;
   }

   /**
   * @see Db::checkConnection()
   */
   public static function tryToConnect($server, $user, $pwd, $db, $newDbLink = true, $engine = null, $timeout = 5)
   {
       try {
           $link = DbPDO::_getPDO($server, $user, $pwd, $db, $timeout);
       } catch (PDOException $e) {
           // hhvm wrongly reports error status 42000 when the database does not exist - might change in the future
           return ($e->getCode() == 1049 || (defined('HHVM_VERSION') && $e->getCode() == 42000)) ? 2 : 1;
       }
       unset($link);
       return 0;
   }

   public function getBestEngine()
   {
       $value = 'InnoDB';

       $sql = 'SHOW VARIABLES WHERE Variable_name = \'have_innodb\'';
       $result = $this->link->query($sql);
       if (!$result)
           $value = 'MyISAM';
       $row = $result->fetch();
       if (!$row || strtolower($row['Value']) != 'yes')
           $value = 'MyISAM';

       /* MySQL >= 5.6 */
       $sql = 'SHOW ENGINES';
       $result = $this->link->query($sql);
       while ($row = $result->fetch())
           if ($row['Engine'] == 'InnoDB')
           {
               if (in_array($row['Support'], array('DEFAULT', 'YES')))
                   $value = 'InnoDB';
               break;
           }
       return $value;
   }

   /**
   * @see Db::checkEncoding()
   */
   public static function tryUTF8($server, $user, $pwd)
   {
       try {
           $link = DbPDO::_getPDO($server, $user, $pwd, false, 5);
       } catch (PDOException $e) {
           return false;
       }
       $result = $link->exec('SET NAMES \'utf8\'');
       unset($link);

return ($result === false) ? false : true;
}

   public static function checkAutoIncrement($server, $user, $pwd)
   {
       try {
           $link = DbPDO::_getPDO($server, $user, $pwd, false, 5);
       } catch (PDOException $e) {
           return false;
       }
       $ret = (bool)(($result = $link->query('SELECT @@auto_increment_increment as aii')) && ($row = $result->fetch()) && $row['aii'] == 1);
       $ret &= (bool)(($result = $link->query('SELECT @@auto_increment_offset as aio')) && ($row = $result->fetch()) && $row['aio'] == 1);
       unset($link);
       return $ret;
   }
}

musicmaster · October 20, 2020

You are right. I see now that this test shop had been updated.

Anyway, try to change the connect function in this file by adding this line. So you get:

    public function connect()
    {
        try {
            $this->link = $this->_getPDO($this->server, $this->user, $this->password, $this->database, 5);
        } catch (PDOException $e) {
            die(sprintf(Tools::displayError('Link to database cannot be established: %s'), utf8_encode($e->getMessage())));
        }

        // UTF-8 support
        if ($this->link->exec('SET NAMES \'utf8\'') === false)
            die(Tools::displayError('PrestaShop Fatal error: no utf-8 support. Please check your server configuration.'));
			
		$this->link->exec('SET SESSION sql_mode = \'\'');

        return $this->link;
    }

Two Bears Ceramics · October 21, 2020

Can I tell you you are a genius? Working so far. Thank you for your help and thank you for persevering.

Sign In

Prestashop Database Exception

Recommended Posts

Two Bears Ceramics

Link to comment

Share on other sites

musicmaster

Link to comment

Share on other sites

Two Bears Ceramics

Link to comment

Share on other sites

musicmaster

Link to comment

Share on other sites

Two Bears Ceramics

Link to comment

Share on other sites

musicmaster

Link to comment

Share on other sites

musicmaster

Link to comment

Share on other sites

Two Bears Ceramics

Link to comment

Share on other sites

musicmaster

Link to comment

Share on other sites

Two Bears Ceramics

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity

Go back to prestashop.com