heavy SQL error and server error 500 when customer tries to reset his password, please help - urgent !

DRMasterChief · May 19

Hi everybody,
i get an heavy SQL error yesterday when a customer tried to renew his password. Now the complete site brings an server error 500
and i really do not know what to do.

I really look for @datakick for any help, please!

Please see this error message:

Message:
ThirtyBeesDatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') GROUP BY c.`id_category` ORDER BY c.`level_depth` ASC , categ...' at line 10

Location:
classes/Category.php line 360

               SELECT c.*, cl.*
               FROM `tb_category` c
               INNER JOIN tb_category_shop category_shop
       ON (category_shop.id_category = c.id_category AND category_shop.id_shop = 1)
               LEFT JOIN `tb_category_lang` cl ON c.`id_category` = cl.`id_category` AND cl.id_shop = 1
               LEFT JOIN `tb_category_group` cg ON c.`id_category` = cg.`id_category`
               RIGHT JOIN `tb_category` c2 ON c2.`id_category` = 3 AND c.`nleft` >= c2.`nleft` AND c.`nright`

#0 classes/Category.php(360)
#1 classes/db/Db.php(235): ()
#2 classes/db/Db.php(441): DbCore->displayError(string(624): "\n\t\t\t\tSELECT c.*, cl.*\n\t\t\t\tFROM `tb_category` c\n\t\t\t\t INNER JOIN tb"...)
#3 classes/db/Db.php(1102): DbCore->query(string(624): "\n\t\t\t\tSELECT c.*, cl.*\n\t\t\t\tFROM `tb_category` c\n\t\t\t\t INNER JOIN tb"...)
#4 classes/Category.php(360): DbCore->getArray(string(624): "\n\t\t\t\tSELECT c.*, cl.*\n\t\t\t\tFROM `tb_category` c\n\t\t\t\t INNER JOIN tb"...)
#5 modules/blocktopmenu/blocktopmenu.php(603): CategoryCore::getNestedCategories(3, 1, false, array(0) [])
#6 modules/blocktopmenu/blocktopmenu.php(1004): Blocktopmenu->makeMenu()
#7 classes/Hook.php(833): Blocktopmenu->hookDisplayTop(array(3))
#8 classes/Hook.php(543): HookCore::coreCallHook(Blocktopmenu#1, "hookdisplayTop", array(3))
#9 classes/Hook.php(372): HookCore::execWithoutCache("displayTop", array(3), null, false, true, false, null)
#10 classes/Hook.php(242): HookCore::exec("displayTop", array(0) [], null)
#11 classes/controller/FrontController.php(332): HookCore::displayHook("displayTop")
#12 controllers/front/MyAccountController.php(71): FrontControllerCore->initContent()
#13 classes/controller/Controller.php(209): MyAccountControllerCore->initContent()
#14 classes/controller/FrontController.php(264): ControllerCore->run()
#15 classes/Dispatcher.php(851): FrontControllerCore->run()
#16 index.php(33): DispatcherCore->dispatch()

and the decoded error reporting has the following detail in
Source file: classes/Category.php :

341:                (int) $rootCategory.(int) $idLang.(int) $active.(int) $useShopRestriction
342:                .(isset($groups) && Group::isFeatureActive() ? implode('', $groups) : '')
343:            );
344:
345:        if (!Cache::isStored($cacheId)) {
346:            $result = Db::readOnly()->getArray(
347:                '
348:				SELECT c.*, cl.*
349:				FROM `'._DB_PREFIX_.'category` c
350:				'.($useShopRestriction ? Shop::addSqlAssociation('category', 'c') : '').'
351:				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').'
352:				'.(isset($groups) && Group::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON c.`id_category` = cg.`id_category`' : '').'
353:				'.(isset($rootCategory) ? 'RIGHT JOIN `'._DB_PREFIX_.'category` c2 ON c2.`id_category` = '.(int) $rootCategory.' AND c.`nleft` >= c2.`nleft` AND c.`nright` <= c2.`nright`' : '').'
354:				WHERE 1 '.$sqlFilter.' '.($idLang ? 'AND `id_lang` = '.(int) $idLang : '').'
355:				'.static::getActiveColumnCondition($active, $useShopRestriction).'
356:				'.(isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN ('.implode(',', $groups).')' : '').'
357:				'.(!$idLang || (isset($groups) && Group::isFeatureActive()) ? ' GROUP BY c.`id_category`' : '').'
358:				'.($sqlSort != '' ? $sqlSort : ' ORDER BY c.`level_depth` ASC').'
359:				'.($sqlSort == '' && $useShopRestriction ? ', category_shop.`position` ASC' : '').'
360:				'.($sqlLimit != '' ? $sqlLimit : '')
361:            );
362:
363:            $categories = [];
364:            $buff = [];
365:
366:            if (!isset($rootCategory)) {
367:                $rootCategory = Category::getRootCategory()->id;
368:            }
369:
370:            foreach ($result as $row) {

Edited May 19 by DRMasterChief

30knees · May 19

$result = Db::readOnly()->getArray(
    '
    SELECT c.*, cl.*
    FROM `'._DB_PREFIX_.'category` c
    '.($useShopRestriction ? Shop::addSqlAssociation('category', 'c') : '').'
    LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').'
    '.(isset($groups) && Group::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON c.`id_category` = cg.`id_category`' : '').'
    '.(isset($rootCategory) ? 'RIGHT JOIN `'._DB_PREFIX_.'category` c2 ON c2.`id_category` = '.(int) $rootCategory.' AND c.`nleft` >= c2.`nleft` AND c.`nright` <= c2.`nright`' : '').'
    WHERE 1 '.$sqlFilter.' '.($idLang ? 'AND `id_lang` = '.(int) $idLang : '').'
    '.static::getActiveColumnCondition($active, $useShopRestriction).'
    '.(isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN ('.implode(',', $groups).')' : '').'
    '.(!$idLang || (isset($groups) && Group::isFeatureActive()) ? ' GROUP BY c.`id_category`' : '').'
    '.($sqlSort != '' ? $sqlSort : ' ORDER BY c.`level_depth` ASC').'
    '.($sqlSort == '' && $useShopRestriction ? ', category_shop.`position` ASC' : '').'
    '.($sqlLimit != '' ? $sqlLimit : '')
);

I can't help unfortunately but if you want to try what ChatGPT suggests until someone competent answers, use the code above. Maybe it'll work and we can also test 4o's debugging skills like this. :-)

datakick · May 19

@DRMasterChief You didn't provide entire invalid sql

DRMasterChief · May 19

9 minutes ago, datakick said:

@DRMasterChief You didn't provide entire invalid sql

what file do you need? please advise. i just posted the error log here, if this is not enough i am happy to complete it with your advise 🙂

datakick · May 19

Error message says there is an error near GROUP BY, but there is no group by in the SQL you posted.

the.rampage.rado · May 19

Isn't this the code?

/**
     * @param int|null $rootCategory
     * @param bool $idLang
     * @param bool $active
     * @param array|null $groups
     * @param bool $useShopRestriction
     * @param string $sqlFilter
     * @param string $sqlSort
     * @param string $sqlLimit
     *
     * @return array|null
     *
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public static function getNestedCategories(
        $rootCategory = null,
        $idLang = false,
        $active = true,
        $groups = null,
        $useShopRestriction = true,
        $sqlFilter = '',
        $sqlSort = '',
        $sqlLimit = ''
    ) {
        if (isset($groups) && Group::isFeatureActive() && !is_array($groups)) {
            $groups = (array) $groups;
        }

        $cacheId = 'Category::getNestedCategories_'.md5(
                (int) $rootCategory.(int) $idLang.(int) $active.(int) $useShopRestriction
                .(isset($groups) && Group::isFeatureActive() ? implode('', $groups) : '')
            );

        if (!Cache::isStored($cacheId)) {
            $result = Db::readOnly()->getArray(
                '
				SELECT c.*, cl.*
				FROM `'._DB_PREFIX_.'category` c
				'.($useShopRestriction ? Shop::addSqlAssociation('category', 'c') : '').'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`'.Shop::addSqlRestrictionOnLang('cl').'
				'.(isset($groups) && Group::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON c.`id_category` = cg.`id_category`' : '').'
				'.(isset($rootCategory) ? 'RIGHT JOIN `'._DB_PREFIX_.'category` c2 ON c2.`id_category` = '.(int) $rootCategory.' AND c.`nleft` >= c2.`nleft` AND c.`nright` <= c2.`nright`' : '').'
				WHERE 1 '.$sqlFilter.' '.($idLang ? 'AND `id_lang` = '.(int) $idLang : '').'
				'.static::getActiveColumnCondition($active, $useShopRestriction).'
				'.(isset($groups) && Group::isFeatureActive() ? ' AND cg.`id_group` IN ('.implode(',', $groups).')' : '').'
				'.(!$idLang || (isset($groups) && Group::isFeatureActive()) ? ' GROUP BY c.`id_category`' : '').'
				'.($sqlSort != '' ? $sqlSort : ' ORDER BY c.`level_depth` ASC').'
				'.($sqlSort == '' && $useShopRestriction ? ', category_shop.`position` ASC' : '').'
				'.($sqlLimit != '' ? $sqlLimit : '')
            );

            $categories = [];
            $buff = [];

            if (!isset($rootCategory)) {
                $rootCategory = Category::getRootCategory()->id;
            }

            foreach ($result as $row) {
                $current = &$buff[$row['id_category']];
                $current = $row;

                if ($row['id_category'] == $rootCategory) {
                    $categories[$row['id_category']] = &$current;
                } else {
                    $buff[$row['id_parent']]['children'][$row['id_category']] = &$current;
                }
            }

            Cache::store($cacheId, $categories);
        } else {
            $categories = Cache::retrieve($cacheId);
        }

        return $categories;
    }

datakick · May 19

That's PHP code responsible for generating the SQL. I need to know how does the generated SQL statement look like.

DRMasterChief · May 19

i have this in error log, is this what you are looking for or do you need something from phpMyAdmin?

SELECT c.*, cl.*
				FROM `tb_category` c
				 INNER JOIN tb_category_shop category_shop
		ON (category_shop.id_category = c.id_category AND category_shop.id_shop = 1)
				LEFT JOIN `tb_category_lang` cl ON c.`id_category` = cl.`id_category` AND cl.id_shop = 1 
				LEFT JOIN `tb_category_group` cg ON c.`id_category` = cg.`id_category`
				RIGHT JOIN `tb_category` c2 ON c2.`id_category` = 3 AND c.`nleft` >= c2.`nleft` AND c.`nright`

datakick · May 19

That's not complete sql.

The error message says: ThirtyBeesDatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') GROUP BY c.`id_category` ORDER BY c.`level_depth` ASC , categ...' at line 10

but I don't see this section in the SQL you posted above.

DRMasterChief · May 19

i am sorry, i can also see this NOT in any SQL table... I have now checked with phpMyAdmin and searched all files for this content.

datakick · May 19

Copy the entire sql from the exception.

DRMasterChief · May 19

This error occurred yesterday when a customer tried to renew his password. I only became aware of this today, which is of course very annoying.
I don't use any special modules that could cause something like this, there are only the modules that come with the system.

What I did now: I called up this customer (from Poland), gave him a new password, checked the customer group, and simply saved it again.
I saw that this customer had left an open shopping cart. He probably didn't know his password anymore...

The error has now disappeared with the things mentioned. The shop is accessible again without error 500.

But what was going on? I'm very worried that this could happen again at any time.

DRMasterChief · May 19

1 hour ago, datakick said:

Copy the entire sql from the exception.

sorry, maybe I'm too nervous, but where can I find this?

Sign In

heavy SQL error and server error 500 when customer tries to reset his password, please help - urgent !

Question

DRMasterChief

12 answers to this question

Recommended Posts

30knees

datakick

DRMasterChief

datakick

the.rampage.rado

datakick

DRMasterChief

datakick

DRMasterChief

datakick

DRMasterChief

DRMasterChief

Create an account or sign in to comment

Create an account

Sign in