Products not shown in front end category pages (1.7)

[Major Bob]

I've made several products in the back office and via CSV uploads. None of them are showing up in the categories in the front-end. Everything is correct in the back office and they are set to be displayed.

 

The products are allocated to categories in the front end but the only places they are visible is in 'popular products' on the home page and if you search for them.

Has anybody seen this before?

 

Debugging on one of the category pages displays this:

 

[PrestaShopDatabaseException]

Access denied for user 'xxx' to database 'yyy'
 

CREATE TEMPORARY TABLE ps_cat_restriction ENGINE=MEMORY                                                        SELECT DISTINCT cp.id_product, p.id_manufacturer, product_shop.condition, p.weight FROM ps_category c                                                        STRAIGHT_JOIN ps_category_product cp ON (c.id_category = cp.id_category AND                                                        c.nleft >= 153                                                        AND c.nright <= 162                                                        AND c.active = 1)                                                        STRAIGHT_JOIN ps_product_shop product_shop ON (product_shop.id_product = cp.id_product                                                        AND product_shop.id_shop = 1)                                                        STRAIGHT_JOIN ps_product p ON (p.id_product=cp.id_product)                                                        WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")

at line 744 in file classes/db/Db.php

739.         if ($webservice_call && $errno) {740.             $dbg = debug_backtrace();741.             WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);742.         } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {743.             if ($sql) {744.                 throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');745.             }746. 747.             throw new PrestaShopDatabaseException($this->getMsgError());748.         }749.     }

• DbCore->displayError - [line 378 - classes/db/Db.php] - [1 Arguments]
• DbCore->query - [line 554 - classes/db/Db.php] - [1 Arguments]
• DbCore->execute - [line 1654 - modules/ps_facetedsearch/ps_facetedsearch.php] - [2 Arguments]
• Ps_Facetedsearch->getFilterBlock - [line 37 - modules/ps_facetedsearch/src/Ps_FacetedsearchProductSearchProvider.php] - [2 Arguments]
• Ps_FacetedsearchProductSearchProvider->getFacetCollectionFromEncodedFacets - [line 143 - modules/ps_facetedsearch/src/Ps_FacetedsearchProductSearchProvider.php] - [1 Arguments]
• Ps_FacetedsearchProductSearchProvider->runQuery - [line 314 - classes/controller/ProductListingFrontController.php] - [2 Arguments]
• ProductListingFrontControllerCore->getProductSearchVariables - [line 499 - classes/controller/ProductListingFrontController.php]
• ProductListingFrontControllerCore->doProductSearch - [line 86 - controllers/front/listing/CategoryController.php] - [2 Arguments]
• CategoryControllerCore->init - [line 182 - classes/controller/Controller.php]
• ControllerCore->run - [line 366 - classes/Dispatcher.php]
• DispatcherCore->dispatch - [line 28 - index.php]

 

[Fong-Wan Chau]

Your database's user does not have CREATE TEMPORARY TABLES privilege, you need to grant the privilege to the user by this way:

GRANT CREATE TEMPORARY TABLES ON `yyy`.* TO 'xxx'@'localhost';

[LadyLuckCaravans]

 

Your database's user does not have CREATE TEMPORARY TABLES privilege, you need to grant the privilege to the user by this way:

GRANT CREATE TEMPORARY TABLES ON `yyy`.* TO 'xxx'@'localhost';

hi

 

I'm having the same problem using the latest version of Prestashop what do I replace yyy and xxx with? And where do I execute this query ???

 

thank you for any help

[Fong-Wan Chau]

hi

I'm having the same problem using the latest version of Prestashop what do I replace yyy and xxx with? And where do I execute this query ???

thank you for any help

`yyy` is the table name and `xxx` is the MySQL user name. You should execute this query in the MySQL client after you logged as root.