Jump to content

Prestashop Database Exception

Two Bears Ceramics

By Two Bears Ceramics
in General topics

 Share

Recommended Posts

Two Bears Ceramics Newbie

Two Bears Ceramics

Posted
Posted

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]

Link to comment
Share on other sites
musicmaster Mentor

musicmaster

Posted
Posted

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.

Link to comment
Share on other sites
Two Bears Ceramics Newbie

Two Bears Ceramics

Posted
  • Author
Posted

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==');

Link to comment
Share on other sites
Two Bears Ceramics Newbie

Two Bears Ceramics

Posted
  • Author
Posted

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;
    }
}
 

Link to comment
Share on other sites
musicmaster Mentor

musicmaster

Posted
Posted

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;
    }

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...