Error 500 with Categories: Column 'active' in where clause is ambiguous

[movieseals]

Hi everyone.  My site started giving me 500 error when I click on some, not all categories.  Basically Best Sellers, price-drop and new products do work properly.  But all the ones i created give me a 500 error.  Any help or hint is appreciated.

Here is the actual error:

 

Decoded exception

Column 'active' in where clause is ambiguous

in file classes/Category.php at line 1727

SQL

		SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
		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 `id_lang` = 1  AND cl.id_shop = 1 )
		LEFT JOIN `tb_category_group` cg ON (cg.`id_category` = c.`id_category`)
		WHERE `id_parent` = 3
		AND `active` = 1
		AND cg.`id_group` =1
		GROUP BY c.`id_category`
		ORDER BY `level_depth` ASC, category_shop.`position` ASC

Source file: classes/Category.php

1708:    public function getSubCategories($idLang, $active = true)
1709:    {
1710:        $sqlGroupsWhere = '';
1711:        $sqlGroupsJoin = '';
1712:        if (Group::isFeatureActive()) {
1713:            $sqlGroupsJoin = 'LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)';
1714:            $groups = FrontController::getCurrentCustomerGroups();
1715:            $sqlGroupsWhere = 'AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '='.(int) Group::getCurrent()->id);
1716:        }
1717:
1718:        $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS(
1719:            '
1720:		SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
1721:		FROM `'._DB_PREFIX_.'category` c
1722:		'.Shop::addSqlAssociation('category', 'c').'
1723:		LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int) $idLang.' '.Shop::addSqlRestrictionOnLang('cl').')
1724:		'.$sqlGroupsJoin.'
1725:		WHERE `id_parent` = '.(int) $this->id.'
1726:		'.($active ? 'AND `active` = 1' : '').'
1727:		'.$sqlGroupsWhere.'
1728:		GROUP BY c.`id_category`
1729:		ORDER BY `level_depth` ASC, category_shop.`position` ASC'
1730:        );
1731:
1732:        foreach ($result as &$row) {
1733:            $row['id_image'] = (file_exists(_PS_CAT_IMG_DIR_.(int) $row['id_category'].'.jpg') || file_exists(_PS_CAT_IMG_DIR_.(int) $row['id_category'].'_thumb.jpg')) ? (int) $row['id_category'] : Language::getIsoById($idLang).'-default';
1734:            $row['legend'] = 'no picture';
1735:        }
1736:
1737:        return $result;

[datakick]

Did you, by any chance, update to bleeding edge and then back to stable?

I bet you have an extra column named 'active' in table 'tb_category_shop'. You can check using core updater

[movieseals]

2 hours ago, datakick said:

Did you, by any chance, update to bleeding edge and then back to stable?

I bet you have an extra column named 'active' in table 'tb_category_shop'. You can check using core updater

Hi!  Yes, I did.  For some reason, even after installing CoreUpdater 1.4.5, it wsa like there was nothing. I could not upgrade to 1.3.  The only way was to upgrade to bleeding edge, which I did.  Then everything actually updated.  But I realized that some plugins where no longer working, so I downgraded to stable.

Coreupdater told me there was problems with the database and fixed them but did not tell me about the Category issue or fix it.

Remove the column, this way in the database, fixed the issue indeed:

ALTER TABLE `tb_category_shop`
DROP COLUMN `active`;

Thank you.

Was there changes to ThemeConfigurator also (see my other post)?  Because I am having issues with that one too and I did not use to have those.

Thank you very much!

[datakick]

2 minutes ago, movieseals said:

Coreupdater told me there was problems with the database and fixed them but did not tell me about the Category issue or fix it.

Yes, core updater does not report extra columns as problems -- that's because a lot of modules actually extends core tables with their own columns. It's next to impossible for core updater to distinguish between valid and invalid columns. 

So extra columns are not automatically fixed.

In the next version of core updater, these extra column will at least be displayed 

2 minutes ago, movieseals said:

Was there changes to ThemeConfigurator also (see my other post)?  Because I am having issues with that one too and I did not use to have those.

None that I know about

[movieseals]

OK.  I shall pursue my investigation then.  Probably a change in a module.  Thank you.