Post by soamjena » Mon Feb 12, 2018 6:42 pm

My Opencart website was loading like a rocket till yesterday.
Yesterday MySQL was upgraded by cPanel officially across all servers and since then, my site is almost dead.

Pages and refresh or click takes forever to load.
Whats wrong ?

Isn't it compatible with latest MySQL ?



MysqlI Support enabled
Client API library version 5.7.18
Active Persistent Links 0
Inactive Persistent Links 0
Active Links 0
Client API header version 5.7.18
MYSQLI_SOCKET /var/lib/mysql/mysql.sock

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by straightlight » Tue Feb 13, 2018 7:22 am

No OC version posted.
since then, my site is almost dead.
Vague information to troubleshoot this problem.
Isn't it compatible with latest MySQL ?
Opencart uses MySQLi library since a few years now as it has never encountered a systematic issue other than the reserved field names that were used to insert / replace values on the database. More information would be needed in order to track down the problem you are reporting about - including URL / screenshot.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by soamjena » Tue Feb 13, 2018 5:16 pm

OpenCart © 2009-2018 All Rights Reserved.
Version 3.0.2.0

And I solved it by disabling the MySQL governor I turned it off all to abusers only.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by soamjena » Sun Feb 18, 2018 4:42 am

Oh its not solved yet.
Still spiking up MySQL crazy and sites are dead.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by straightlight » Sun Feb 18, 2018 4:57 am

Still spiking up MySQL crazy and sites are dead.
Still with still missing information.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by soamjena » Mon Feb 19, 2018 6:33 pm

straightlight wrote:
Sun Feb 18, 2018 4:57 am
Still with still missing information.


OpenCart
Version 3.0.2.0

PHP INFO is here https://goo.gl/hMNig2
This issue has started exactly after MySQL upgrade to 5.7.x

You can see here http://prntscr.com/igrsfi, how its killing the CPU each second, even if hardly 1-2 hits in a second.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by straightlight » Tue Feb 20, 2018 1:21 am

Contact your host to see how the resources could be re-handled due to this upgrade.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by soamjena » Tue Feb 20, 2018 12:11 pm

straightlight wrote:
Tue Feb 20, 2018 1:21 am
Contact your host to see how the resources could be re-handled due to this upgrade.

Im the host itself, its my dedicated server.
I have contacted cPanel and CLOUDLINUX both and they said, its something to do with opencart with latest mySQL, because all other sites and CMS run fine in my server after the upgrade, but just this opencart websites doesnt work.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by straightlight » Tue Feb 20, 2018 6:17 pm

its something to do with opencart with latest mySQL, because all other sites and CMS run fine in my server after the upgrade, but just this opencart websites doesnt work.
Contradiction. Other servers you own works with the latest version of mySQL but only one server does not provide the same results with the latest version of mySQL while using Opencart. How does that indicate it's an Opencart issue exactly if you are indicating that only one server is having an issue among other servers with the latest version of mySQL already?

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by soamjena » Tue Feb 20, 2018 7:55 pm

I mean other websites in the same server.
Its a big server with many websites.
And only 2 opencart websites.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by qahar » Wed Feb 21, 2018 2:11 am

sites are dead
It would be good start to inform was it blank white, infinite loading, any error log you can share etc.

I'm not sure what is your previous MySQL version, but your issue might be related to sql mode change only_full_group_by that enabled by default since MySQL 5.7.5. There is lot of solution out there to disabled only-full-group-by sql mode.

Of course the best solution would be editing mysql driver configuration considering you use dedicated server. In shared hosting, I would override the sql-mode session through query.

Code: Select all

"SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"

And it's only 1 of 2 of OpenCart get this issue is possibly because your other opencart site have a modification that change sql-mode silently.

User avatar
Expert Member

Posts

Joined
Tue Jun 29, 2010 10:24 pm
Location - Indonesia

Post by uksitebuilder » Wed Feb 21, 2018 4:54 am

and of course the best solution seeing as you have root access would be to migrate over to MariaDB ;-)

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by soamjena » Wed Feb 21, 2018 5:33 pm

yes actually no changes were done.
It was all good and fast with mySQL 5.6

But its dead with 5.7 only.

Has anyone got their opencart tested with 5.7 yet ?

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by soamjena » Wed Feb 21, 2018 5:34 pm

It just takes a lot of time to load and when I monitor server, I can see MySQL usage is super high due to this 2 accounts.
This server has 30 websites, and only 2 opencart 3.x and only those 2 are affected.
qahar wrote:
Wed Feb 21, 2018 2:11 am
sites are dead
It would be good start to inform was it blank white, infinite loading, any error log you can share etc.

I'm not sure what is your previous MySQL version, but your issue might be related to sql mode change only_full_group_by that enabled by default since MySQL 5.7.5. There is lot of solution out there to disabled only-full-group-by sql mode.

Of course the best solution would be editing mysql driver configuration considering you use dedicated server. In shared hosting, I would override the sql-mode session through query.

Code: Select all

"SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"

And it's only 1 of 2 of OpenCart get this issue is possibly because your other opencart site have a modification that change sql-mode silently.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by straightlight » Wed Feb 21, 2018 6:26 pm

If this issue occurs only from two domains among other domains on the same server since the upgrade to mySQL v5.7+ , please follow these steps and provide the query logs that the issues may indeed originate from Opencart: https://stackoverflow.com/questions/368 ... ries-error

In addition, you can also implement this step as followed: https://stackoverflow.com/questions/377 ... 7/37743777

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by soamjena » Wed Feb 21, 2018 8:36 pm

uksitebuilder wrote:
Wed Feb 21, 2018 4:54 am
and of course the best solution seeing as you have root access would be to migrate over to MariaDB ;-)
Ok questions :
1. Can we have MariaDB installed alongside mySQL in the same server ?
2. Do you think MariaDB will perform better than this MySQL 5.7 ?
3. Is there any automated method to convert the MySQL database dump directly to MariaDB import file ?

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by uksitebuilder » Wed Feb 21, 2018 9:12 pm

Please note, I am no server Guru, so depending on your server set-up I would advise exploring the Maria DB knowledgebase

Maria DB is a drop in replacement for MySQL, but it can run on the same server:
https://mariadb.com/kb/en/library/insta ... ide-mysql/

Maria DB does perform better than mySQL in my opinion.

SQL Queries, dumps etc work in the same way as mySQL dumps and queries.
The only difference is if you convert the antiquated myISAM storage engine to Aria, you will obviously not be able to import Aria storage engine tables in your mySQL Databases.
You can of course use myISAM storage engine tables in Maria DB, but it is advisable to either switch to Aria storage engine or innoDB

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by soamjena » Wed Feb 21, 2018 11:47 pm

Isn't it better to just change all tables engine to InnoDB with one click ?
Will that improve ?

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm

Post by uksitebuilder » Wed Feb 21, 2018 11:52 pm

Yes of course.

User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by soamjena » Fri Feb 23, 2018 8:40 pm

I copied the site to my another server which is running same MySQL 5.7 and it works perfect there.
But not in my main server.
Is there a way to track down what exactly is lagging the performance ?

It used to load like rocket few days back and been like this sluggish now, so for sure, something is dragging down.

User avatar
Active Member

Posts

Joined
Mon Feb 13, 2012 3:35 pm
Who is online

Users browsing this forum: No registered users and 96 guests