I have a strange problem. Whenever I change the page number to some none existing page number then the system throws sql
error.
For example http://domain.com/category.html?p=1
works as expected but when I change it to http://domain.com/category.html?p=100
then it throws below error. In that category only 1-5
pages available.
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ASC' at line 4, query was: SELECT
e
.*,price_index
.price
,price_index
.tax_class_id
,price_index
.final_price
, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
, IFNULL(review_summary.reviews_count, 0) ASreviews_count
, IFNULL(review_summary.rating_summary, 0) ASrating_summary
,stock_status_index
.stock_status
ASis_salable
FROMcatalog_product_entity
ASe
INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = 'product')) INNER JOINcataloginventory_stock_status
ASstock_status_index
ON e.entity_id = stock_status_index.product_id WHERE ((stock_status_index.stock_status = 1) AND (e.entity_id IN (NULL))) AND (e.created_in <= 1) AND (e.updated_in > 1) ORDER BY FIELD(e.entity_id,) ASC
As you can see some SQL
is missing after FIELD(e.entity_id,
. This is how it should be:
SELECT
e
.*,price_index
.price
,price_index
.tax_class_id
,price_index
.final_price
, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) ASminimal_price
,price_index
.min_price
,price_index
.max_price
,price_index
.tier_price
, IFNULL(review_summary.reviews_count, 0) ASreviews_count
, IFNULL(review_summary.rating_summary, 0) ASrating_summary
,stock_status_index
.stock_status
ASis_salable
FROMcatalog_product_entity
ASe
INNER JOINcatalog_product_index_price
ASprice_index
ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = 'product')) INNER JOINcataloginventory_stock_status
ASstock_status_index
ON e.entity_id = stock_status_index.product_id WHERE ((stock_status_index.stock_status = 1) AND (e.entity_id IN (NULL))) AND (e.created_in <= 1) AND (e.updated_in > 1) ORDER BY FIELD(e.entity_id,NULL) ASC
It looks NULL
is missing.
Magento 2.3.3 with elasticsearch 6+
Any idea why this happening? Which section should I be checking to fix this?
elasticsearch
. With mysql search, it's working fine.