Jump to content
thirty bees forum
  • 0

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

DRMasterChief

Asked by DRMasterChief,

 Share

Question

DRMasterChief Enthusiast

DRMasterChief

Posted
Posted (edited)

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 by DRMasterChief
Link to comment
Share on other sites

12 answers to this question

Recommended Posts

  • 0
30knees Rising Star

30knees

Posted
Posted 
$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. :-)

 

Link to comment
Share on other sites
  • 0
the.rampage.rado Proficient

the.rampage.rado

Posted
Posted

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

 

Link to comment
Share on other sites
  • 0
DRMasterChief Enthusiast

DRMasterChief

Posted
  • Author
Posted

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`
Link to comment
Share on other sites
  • 0
datakick Mentor

datakick

Posted
Posted

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. 

Link to comment
Share on other sites
  • 0
DRMasterChief Enthusiast

DRMasterChief

Posted
  • Author
Posted

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.

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 question listing
×
×
  • Create New...