Denkster Posted March 5, 2017 Share Posted March 5, 2017 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. Server 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: HTTP 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 incatalog\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 More sharing options...
Harald Ponce de Leon Posted March 5, 2017 Share Posted March 5, 2017 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=""'); , osCommerce Link to comment Share on other sites More sharing options...
♥bruyndoncx Posted March 31, 2017 Share Posted March 31, 2017 @@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 More sharing options...
mcmannehan Posted March 31, 2017 Share Posted March 31, 2017 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 More sharing options...
BrockleyJohn Posted April 6, 2017 Share Posted April 6, 2017 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 More sharing options...
BrockleyJohn Posted April 6, 2017 Share Posted April 6, 2017 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 More sharing options...
burt Posted April 6, 2017 Share Posted April 6, 2017 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! Link to comment Share on other sites More sharing options...
Denkster Posted April 22, 2017 Author Share Posted April 22, 2017 @@burt, @@BrockleyJohn Thank you both for pointing me to the configuration for the log off button. I found it! Link to comment Share on other sites More sharing options...
Denkster Posted April 22, 2017 Author Share Posted April 22, 2017 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 More sharing options...
Denkster Posted April 22, 2017 Author Share Posted April 22, 2017 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 More sharing options...
BrockleyJohn Posted April 23, 2017 Share Posted April 23, 2017 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 More sharing options...
Denkster Posted April 24, 2017 Author Share Posted April 24, 2017 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.