PHP Fatal error: 1055: Expression #1 of ORDER BY...

[Ted Grapler]

ZC 1.5.4, PHP 5.6, Zen Lightbox, Image Handler 4, Edit Orders, Easy Populate

I've got a ZenCart 1.5.4 install that is getting the following error:

[15-Nov-2020 10:42:59 America/New_York] PHP Fatal error: 1055:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'XXXXXX_##########.o.date_purchased' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by :: select p.products_id, p.products_image

from orders_products opa, orders_products opb, orders o, products p
where opa.products_id = '8933'
and opa.orders_id = opb.orders_id
and opb.products_id != '8933'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by o.date_purchased desc
limit 6 ==> (as called by) /home/XXXXXX/public_html/XXXXXX/includes/modules/also_purchased_products.php on line 16 <== in /home/XXXXX/public_html/XXXXX/includes/classes/db/mysql/query_factory.php on line 155

Currently on my site when you run a search it just says "Warning: An Error occurred, please refresh the page and try again" When you open a product listing page the description, main image, and thumbnails load, then the same error message. Sidebars do not load. There could be other issues but haven't noticed yet. I got an order yesterday so appears site still is functioning to some extent.

If someone can give me a temp fix or advice for this issue would be grateful. This setup has served me well for past 6 years or so but I know it's way way past time to update to 1.5.7a and PHP 7.4 and plan on tackling this upgrade as well as moving to another host ASAP.

Thanks, Ted

[lat9]

Besides the upgrade to zc157a, there might be a couple of things you could do

1. Ask your webhost to reduce the restrictions on your SQL server (currently using the ONLY_FULL_GROUP_BY setting).
2. Update the site's /includes/classes/db/mysql/define_queries.php, changing

DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image
                     from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
                            . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
                     where opa.products_id = '%s'
                     and opa.orders_id = opb.orders_id
                     and opb.products_id != '%s'
                     and opb.products_id = p.products_id
                     and opb.orders_id = o.orders_id
                     and p.products_status = 1
                     group by p.products_id
                     order by o.date_purchased desc
                     limit " . MAX_DISPLAY_ALSO_PURCHASED);

to include the update now present in zc157a:

DEFINE('SQL_ALSO_PURCHASED', "SELECT p.products_id, p.products_image, max(o.date_purchased) as date_purchased
                     FROM " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
                            . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
                     WHERE opa.products_id = %u
                     AND opa.orders_id = opb.orders_id
                     AND opb.products_id != %u
                     AND opb.products_id = p.products_id
                     AND opb.orders_id = o.orders_id
                     AND p.products_status = 1
                     GROUP BY p.products_id, p.products_image
                     ORDER BY date_purchased desc, p.products_id");

[swguy]

#1 you can also do yourself; just update your includes/configure.php file to add

define('DB_MYSQL_MODE','NO_ENGINE_SUBSTITUTION');

[Ted Grapler]

Thank you lat9. Just made a small donation to ZenCart Team to show my appreciation.

I updated the code in /includes/classes/db/mysql/define_queries.php as instructed and now my item listing pages appear to be working correctly. Sidebars are showing up, no errors recorded. Was getting about 10 myDEBUG errors a minute in my log before this fix.

Running a Search on my website still gives the "Warning: An Error occurred, please refresh the page and try again" message. Would be grateful if you also have a temporary fix for this.

I ran a search and this is the error that came up:

[15-Nov-2020 13:08:27 America/New_York] PHP Fatal error: 3065:Expression #1 of ORDER BY clause is not in SELECT list, references column 'XXXXXXXX_##########.p.products_sort_order' which is not in SELECT list; this is incompatible with DISTINCT :: SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id
FROM (products p
LEFT JOIN manufacturers m
USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c )
LEFT JOIN meta_tags_products_description mtpd
ON mtpd.products_id= p2c.products_id
AND mtpd.language_id = 1 WHERE (p.products_status = 1
AND p.products_id = pd.products_id
AND pd.language_id = 1
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND ((pd.products_name LIKE '%greenleaf%'
OR p.products_model
LIKE '%greenleaf%'
OR m.manufacturers_name
LIKE '%greenleaf%' OR (mtpd.metatags_keywords
LIKE '%greenleaf%'
AND mtpd.metatags_keywords !='')
OR (mtpd.metatags_description
LIKE '%greenleaf%'
AND mtpd.metatags_description !='')
OR pd.products_description
LIKE '%greenleaf%') )) order by p.products_sort_order, pd.products_name limit 21 ==> (as called by) /home/XXXXXX/public_html/XXXXX/includes/modules/##########_temp/product_listing.php on line 121

[lat9]

#1 you can also do yourself; just update your includes/configure.php file to add

define('DB_MYSQL_MODE','NO_ENGINE_SUBSTITUTION');

Unfortunately, @TedGrapler's site is running zc154; the use of that constant wasn't included in the base until zc155.

[lat9]

@TedGrapler, locate the file /includes/modules/pages/advanced_search_results/header_php.php (for an unmodified zc154 version, starting at line 200):

// Notifier Point
$zco_notifier->notify('NOTIFY_SEARCH_COLUMNLIST_STRING');


//  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price ";
$select_str = "SELECT DISTINCT " . $select_column_list .
              " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id ";

... and add the highlighted fragment, also already 'baked into' zc157a:

// Notifier Point
$zco_notifier->notify('NOTIFY_SEARCH_COLUMNLIST_STRING');


//  $select_str = "select distinct " . $select_column_list . " p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price ";
$select_str = "SELECT DISTINCT " . $select_column_list .
              " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id ";

[Ted Grapler]

Thanks again lat9. This worked and am no longer getting fatal errors.