Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

OsC 2.3.4BS Edge, SQL code and install script issues


Denkster

Recommended Posts

Hi friends,

Not sure if this is the right place for it, but ..

 

I found and solved several issues with a clean install of OsC 2.3.4BS Edge.

 

I did a clean install of OsC 2.3.4BS Edge (dd 2017-02-20)

This is my Server Information.

  pixel_trans.gifServer Host: 192.168.178.50 (192.168.178.50)      Database Host: localhost (127.0.0.1) Server OS: WINNT      Database: MySQL 5.7.14 Server Date: 2017-03-05 01:02:29 +0100 CET      Database Date: 2017-03-05 01:02:29 Server Up Time:   pixel_trans.gifHTTP Server: Apache/2.4.23 (Win64) PHP/5.6.25 PHP Version: 5.6.25 (Zend: 2.6.0)

 

While running the install script (install.php) I found 3 issues with SQL and a functional omission

 

1. On the page index.php

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
'ftpde117_winkel2.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 distinct op.products_id from orders o, orders_products op, products p where o.customers_id = '1'
and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by
products_id order by o.date_purchased desc limit 6

The actual error is in
catalog\includes\modules\boxes\bm_best_sellers.php line 37.
Solution:
Remove the word distinct

====================================

2. On the page  create_account_success.php

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
'ftpde117_winkel2.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 distinct op.products_id from orders o, orders_products op, products p where o.customers_id = '1'
and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by
products_id order by o.date_purchased desc limit 6

The actual error is in catalog\includes\modules\boxes\bm_order_history.php line 38.
Solution:
Remove the word distinct

============================

3. On the page create_account.php
 

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
'ftpde117_winkel2.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 op.products_id from orders o, orders_products op, products p where o.customers_id = '1' and
o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by
products_id order by o.date_purchased desc limit 6

The actual error is also in catalog\includes\modules\boxes\bm_order_history.php line 38.
Solution:
Remove the phrase  order by o.date_purchased desc

=========================================

Functional omission

A customer is unable to logout. I could not find a button or a link for a customer to end the customers session

 

I hope this helps someone, if not, excuse me for posting this

Best regards,

Eveline
 

Link to comment
Share on other sites

It's better to reset the MySQL v5.7 SQL mode.

 

In both includes/functions/database.php and admin/includes/functions/database.php files, update the tep_db_connect() function, at the end of the function before

 

 

return $$link;

 

add:

 

 

@mysqli_query($$link, 'set session sql_mode=""');

:heart:, osCommerce

Link to comment
Share on other sites

  • 4 weeks later...

@@burt

did you see, know about this already ?

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Cool the resolution, easy and good.

 

I did change all SQL querrys to the MySQL 5.7 new mode. Only 5 files it work too.

  • The clever one learn from everything and from everybody
  • The normal one learn from his experience
  • The silly one knows everything better

[socrates, 412 before Christ]

Computers help us with the problems we wouldn't have without them!
99.9% of the bugs sit in front of the computer!
My programmed add-ons: WDW EasyTabs 1.0.3, WDW Facebook Like 1.0.0

if(isset($this) || !isset($this)){ // that's the question...

 

Link to comment
Share on other sites

 

It's better to reset the MySQL v5.7 SQL mode.

 

In both includes/functions/database.php and admin/includes/functions/database.php files, update the tep_db_connect() function, at the end of the function before

return $$link;

add:

@mysqli_query($$link, 'set session sql_mode=""');

 

or alternatively you can do this at server startup without changing osc code, with the line

 sql-mode=""

in an option file such as my.cnf

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

Functional omission

A customer is unable to logout. I could not find a button or a link for a customer to end the customers session

 

I hope this helps someone, if not, excuse me for posting this

Best regards,

Eveline

 

 

You need to enable some more modules. There's a button for this in the header modules and it's on the account menu in the navbar modules.

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

  • 3 weeks later...

Cool the resolution, easy and good.

 

I did change all SQL querrys to the MySQL 5.7 new mode. Only 5 files it work too.

Thank you @mannehan.

 

I just found your GIT repository.

Cool job!

Which are the 5 files with queries that needed rework to comply with MySql 5.7 safety standards?

 

Much obliged!

Eveline

Link to comment
Share on other sites

Cool the resolution, easy and good.

 I am not sure wether  adding this:

@mysqli_query($link, 'set session sql_mode=""');

is the best solution.

What it does is disable the new security feature of MySQL v5.7 (see https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html:

Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

 

Maybe for now (it solves issues with all 'suspect' queries), but on the long run it is not a good idea to disable improvements of the platform, or?

 

Rewriting the queries like this person did:

 

I did change all SQL querrys to the MySQL 5.7 new mode. Only 5 files it work too.

 

is necessary.

 

Eveline

Link to comment
Share on other sites

I am not sure wether  adding this:

@mysqli_query($link, 'set session sql_mode=""');
is the best solution.

What it does is disable the new security feature of MySQL v5.7 (see https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html:

Maybe for now (it solves issues with all 'suspect' queries), but on the long run it is not a good idea to disable improvements of the platform, or?

 

Rewriting the queries like this person did:

 

 

is necessary.

 

Eveline

 

 

I beg to differ. What it does is set no sql_mode options for the session, which are described in the second bullet point on the page to which you refer. The new security features are not affected by sql_mode options. All these options do is affect the permitted range of syntax for MySQL queries and operations.

 

The sql_mode options set by default are:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

Harald's change unsets all of these but is simple. You could if you prefer only unset ONLY_FULL_GROUP_BY but this is more complicated.

 

The answers in this thread are about giving people a simple change to get their code up and running now. They aren't about what code is going into the next official release! You don't need to worry on that score; it's always going to be policy to try to have code which runs properly on the default options for a newly set-up server.

Contact me for work on updating existing stores - whether to Phoenix or the new osC when it's released.

Looking for a payment or shipping module? Maybe I've already done it.

Working on generalising bespoke solutions for Quickbooks integration, Easify integration and pay4later (DEKO) integration at 2.3.x

Link to comment
Share on other sites

Hi

Thank you very much for your kind and clear explanations, of the solution and of the answer policy.

 

I am grateful I could learn from you that I was wrong and why.

 

Thank you.

 

Eveline

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...