SQL_BIG_SELECT syntax

[DanielaPetkova]

Hi there,

I need help with the correct syntax and where exactly to add the statement. I'm having issue with Stock management in Ps 1.7.3 and i need to add SQL_BIG_SELEC=1 before the select. It's not possible to change server settings. Below is the function with the query in it. I know i have to add one of those statement but dont know where to insert it. Actually shouldn't be SET SESSION... Too many unknows for me.

Db::getInstance()->execute('SET SQL_BIG_SELECTS=1');

Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('SET SQL_BIG_SELECTS=1');

StockRepository.php

    protected function selectSql(
        $andWhereClause = '',
        $having = '',
        $orderByClause = null
    )
    {
        if (is_null($orderByClause)) {
            $orderByClause = $this->orderByProductIds();
        }

        return str_replace(
            array(
                '{left_join}',
                '{and_where}',
                '{having}',
                '{order_by}',
                '{table_prefix}',
            ),
            array(
                $this->joinLimitingCombinationsPerProduct(),
                $andWhereClause,
                $having,
                $orderByClause,
                $this->tablePrefix,

            ),
            'SELECT SQL_CALC_FOUND_ROWS
            p.id_product AS product_id,
           ......
          .......
            GROUP BY p.id_product, COALESCE(pa.id_product_attribute, 0)
            HAVING 1 {having}
            {order_by}
        ');
    }

check that link for the full code https://github.com/PrestaShop/PrestaShop/blob/develop/src/PrestaShopBundle/Entity/Repository/StockRepository.php

Edited January 22, 2021 by DanielaPetkova (see edit history)

[musicmaster]

Try to put it in the function tryToConnect()

in /classes/db/DbMySQLi.php

[DanielaPetkova]

Hmm....first of all thank you for supporting. Second i did it but it seems there is some cache that i am missing to delete. In var/cache there is nothing , not even folders and i deleted class_index.php from prod and dev folders. Any new ideas ?

[musicmaster]

It is a bit of a problem with Prestashop that it is half on Symfony and half not.

The first thing you can do is check that this function is really called in your case. A simple "die()" command will be enough for that.

There is also one other file that you can try: /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliConnection.php

[DanielaPetkova]

Even if i rename the .php file nothing has changed in back office. MysqliConnection.php or DbMySQLi.php. Everything is working normal in the BO even without both files. So they are not in use or there is some cache that i don't know. Continue searchin what is going on. Here is a screenshot of the error i'm fighting with.  Usually it happens after i added new product eve thow they are not a lot. After deleting and re-adding is all good but not now.

 

[musicmaster]

Most likely it is classes/db/dbpdo.php. That is at least used.

[DanielaPetkova]

Yep , but function tryToConnect is not used in dbpdo.php also. There is 

 

    public function connect()
    {
        try {
            $this->link = $this->getPDO($this->server, $this->user, $this->password, $this->database, 5);
        } catch (PDOException $e) {
            throw new PrestaShopException('Link to database cannot be established: '.$e->getMessage());
        }

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

        return $this->link;
    }

which is in use but still cannot make SQL_BIG_SELECT work.

[DanielaPetkova]

I could not manage to add SQL_BIG_SELECT before the query no matter what i tried so i went the other way. Try to find what is not ok with the query. It turns out that part [ {table_prefix} is replaced with my DB prefix. ]

...

    COALESCE(i.id_image, 0) AS combination_cover_id,
....

            LEFT JOIN ps4e_image i ON ( i.id_product = p.id_product AND
                COALESCE(FIND_IN_SET(i.id_image, images_per_combination.image_ids), 0) > 0
           )

 

If i comment out that part is working ok. I need to investigate more why is that.  It was working ok before i bet something wrong with the images went.