Jump to content

Archived

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

Denkster

OsC 2.3.4BS Edge, SQL code and install script issues

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
 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

@@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

Share this post


Link to post
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...

 

Share this post


Link to post
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


For a new install or if your store isn't mobile-friendly, get the community-supported responsive osCommerce (Phoenix).

here: on the official osc download page

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

Share this post


Link to post
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.


For a new install or if your store isn't mobile-friendly, get the community-supported responsive osCommerce (Phoenix).

here: on the official osc download page

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

Share this post


Link to post
Share on other sites

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.

In addition to the above, there is a logoff in the Footer Modules;

https://github.com/gburton/Responsive-osCommerce/blob/master/includes/modules/content/footer/cm_footer_account.php#L44

 

So many options!


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
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.

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

Share this post


Link to post
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

Share this post


Link to post
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.


For a new install or if your store isn't mobile-friendly, get the community-supported responsive osCommerce (Phoenix).

here: on the official osc download page

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×