Crashing Tables

[lindasdd]

On all of my sites (and one more often than the others), I get crashed sites due to specific database tables seizing up at least once a week.

75% of the time it is the table sessions
25% of the time it is the table whos_online

It is becoming enough of an issue that I can no longer overlook it.

Is this the type of thing that can be traced back to a source, or are there too many things acting on those tables to figure out the cause?

[lindasdd]

In this thread, https://www.zen-cart.com/showthread....keeps-crashing data_digger suggested converting the table to InnoDB.

Any reason I shouldn't do that?

[mc12345678]

Didn't clearly identify what was meant by the tables "crashing out". Are there error logs generated in the logs folder?

There was an issue identified in ZC 1.5.4 related to the handling of sessions (corrected in ZC 1.5.5 and at the following post for 1.5.4): https://www.zen-cart.com/showthread....12#post1273512

Not sure if the above relates or not or if the patch has been applied.

[mc12345678]

In this thread, https://www.zen-cart.com/showthread....keeps-crashing data_digger suggested converting the table to InnoDB.

Any reason I shouldn't do that?

Backup the database before doing it. If that doesn't seem to fix it, then could either change it back or restore the database...

[lindasdd]

I have dug into this a little more on the server level with my hosting company. There does seem to be a RAM overload occurring (not totally definitive since it is difficult to replicate the issue) since I also am getting MySQL limit messages prior to the tables corrupting.

Something I find interesting is the site is having this issue more often since I upgraded from 1.5.5e to 1.5.5f. It is possible that is related to a screwed up upgrade, except it was a fairly minor upgrade.

I am wondering if the changes made to includes/classes/db/mysql/query_factory.php , the "Use this form of the Execute method to ensure that any SELECT result is pulled from the database, bypassing the cache." are causing the MYSQL queries to become significantly more intensive.

[mc12345678]

I have dug into this a little more on the server level with my hosting company. There does seem to be a RAM overload occurring (not totally definitive since it is difficult to replicate the issue) since I also am getting MySQL limit messages prior to the tables corrupting.

Something I find interesting is the site is having this issue more often since I upgraded from 1.5.5e to 1.5.5f. It is possible that is related to a screwed up upgrade, except it was a fairly minor upgrade.

I am wondering if the changes made to includes/classes/db/mysql/query_factory.php , the "Use this form of the Execute method to ensure that any SELECT result is pulled from the database, bypassing the cache." are causing the MYSQL queries to become significantly more intensive.

That would depend on the installed software that perhaps uses that function/feature. The mere existence of the function does not necessarily cause an effect like described. Yes, the more code that needs to be loaded, the slower things can get, but talking parts of a second and shouldn't really be of concern. Further the "results" of that function can be obtained in the base code by use of additional parameters when calling the Execute method. Adding it improves code readability and reduces the likelihood of incorrect coding by accidentally forgetting a parameter, or using the wrong one.

[carlwhat]

i would like to add a few items for thought:

• if there was a problem with query_factory.php, i think we would be seeing MANY more complaints about it. In my troubleshooting, whenever i get that deep, i have mis-diagnosed the problem.
• i see little harm in deleting all records from the aforementioned tables and see if it helps. have you looked to see how many records are in each table?
• i see little harm in converting the tables to innoDB.
• backing up the DB is a good idea, however restoring the whole DB for these 2 tables would be a bad idea. one could recontruct the 2 tables (if needed) by grabbing the sql statements from the github repo (or other repo).
• you say you have many stores. how much traffic does each store get?
• my inclination is that you either have a config wrong, or you have inadequate hosting. if the sites do not get that many orders, than you might have multiple spiders dragging you down. how hard would it be to move a site to another host? are all your sites on the same server at the same host? could you move one site to another server at that host and see if it helps?

best.

[lindasdd]

My mention of query_factory.php wasn't to say the changes in query_factory.php were bad. Rather there may be certain add-on module queries that ran longer as a result.

That said, for now at least (a couple days), the issue and any other related issues such as MAX_USER_Connections debug logs have gone away. More digging led to the following changes:
1) deactivate comments on a lightly used blog - the comment page was being slammed by spiders and taking up more CPU usage than the main site
2) tighten up or deactivate queries in an add-on module that is resource heavy
3) upgrade hosting plan a couple levels higher