Zencart 1.5.4 on MySQL 5.7.24

[harry2cool]

We have migrated to a new host.

However get below error on the product pages, which probably points out to MySql version error.

How can we make our 1.5.4 work with the latest MySql 5.7.24 (till we upgrade our websites)

Thanks

=================
[13-Nov-2018 01:52:20] PHP Fatal error: 1055:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'xxx_xxx.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 zen_orders_products opa, zen_orders_products opb, zen_orders o, zen_products p

where opa.products_id = '6715'

and opa.orders_id = opb.orders_id

and opb.products_id != '6715'

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//public_html/includes/modules/mytemplate/also_purchased_products.php on line 16 <== in /home/public_html/includes/classes/db/mysql/query_factory.php on line 155

[DrByte]

How can we make our 1.5.4 work with the latest MySql 5.7.24 (till we upgrade our websites)

There were lots of query changes made back in 2016 when preparing v1.5.5 to make it compatible with MySQL 5.7

You will need to upgrade your Zen Cart version, or downgrade your MySQL version.

Ref: https://www.zen-cart.com/content.php...o-run-zen-cart


Or you can compare all the v155 files against v154 and make the corresponding changes. But that's the same as upgrading.

[DrByte]

How can we make our 1.5.4 work with the latest MySql 5.7.24 (till we upgrade our websites)

I forgot about another option: You could configure your MySQL to use a different "mode", so that it relaxes some of the stricter rules imposed out-of-the-box by MySQL 5.7.

To do that I *think* you can safely replace your /includes/classes/db/mysql/query_factory.php with the one from v1.5.6 -- the raw source for that file is at: https://github.com/zencart/zencart/r...ry_factory.php

And then you'll be able to override the MySQL mode by defining DB_MYSQL_MODE in both your Admin and your Catalog.
Example code: https://github.com/zencart/zencart/b...ocalConfig.php (which lists all the available modes in a comment)

To set that "define", you'll need to create a new file (I'd call it "mysql_56_mode.php") in /includes/extra_configures AND /admin/includes/extra_configures containing just the define statement you've decided works best to make your MySQL 5.7 work with MySQL 5.6 or 5.5 lesser-strict rules.

Doing that could buy you a little more time to plan your upgrade. But you really should be upgrading to keep compatible with hosting software changes to PHP, MySQL, etc.

[harry2cool]

Yes we have already started working on the upgrades to use the latest Php and MySql.

I really wanted to move to the new faster server ASAP and got the host to switch off MySql strict mode, after this all was smooth.

Will enable the same after all the upgrades are done.

Thanks for the great support.

[lat9]

The suggestion that DrByte identified in post#3 of this thread works for most conditions, but does not address the case where the length of a character field (e.g. a varchar(64) one) is insufficient to hold the data being inserted or updated.