suddenly getting 500 error: lost connection to Mysql

I am using zen cart 1.5

https://www.royal-fleur.com

Everything was working like a charm until now. I tried to switch a category back on. I get to the second screen but when I click update, the page hang there until it becomes a 500 error page.

I am getting the same error when trying to upload new product.

I talked to webhero ( hosting company ) and they said they reset restarted the server and that everything is fine on their hands but i am still getting the error.

Here is the error i am getting in the cache file:

[25-Feb-2013 12:36:27] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products set products_status='0' where products_id='997' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

Any help or suggestion would be more than appreciated

Thank you

nicksab:

[25-Feb-2013 12:36:27] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products set products_status='0' where products_id='997' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

In most circumstances this type of error is caused by a failure of the MySQL server itself, and as such is generally outside of the users control. However, in this case my diagnosis is different, in that it appears to me that you have a problem with your URL rewriting stuff which is causing the server to crash. Probably some kind of infinite loop.

Solution. Get rid of your URL re-writing. These always cause more problems than they solve.

Cheers
Rod

RodG:

In most circumstances this type of error is caused by a failure of the MySQL server itself, and as such is generally outside of the users control. However, in this case my diagnosis is different, in that it appears to me that you have a problem with your URL rewriting stuff which is causing the server to crash. Probably some kind of infinite loop.

Solution. Get rid of your URL re-writing. These always cause more problems than they solve.

Cheers
Rod

First, thank you for the fast reply. I did disabled the rewrite module and it did not change anything.

Second, i don t think the rewrite module as anything to do with it as the problems only happens in the admin section which is not being modify.

According to the web hosting , mysql is fine. Are they lying to me?

nicksab:

I did disabled the rewrite module and it did not change anything.

Sorry, but I don't beleive you. However, if you'd care to disable them again and let me know when you've done so I'll head over to your store again for another look for further clues. (I wasn't just guessing at this possible cause). I discovered that by entering an invalid URL for your store it goes into a spin that it doesn't recover from.

nicksab:

Second, i don t think the rewrite module as anything to do with it as the problems only happens in the admin section which is not being modify.

What you are trying to tell me is that the .htaccess file in the root of your store gets ignored when accessing the /admin/ (or renamed admin) folder(s). This is not the case. There may not be any rules associated with the admin section of the site but that doesn't mean the rules that do exist are perfectly valid and won't cause unexpected problems. The only 'safe' way to be sure is to disable them all. ZenCart doesn't need them.

nicksab:

According to the web hosting , mysql is fine.

Do you expect them to say anything different?

nicksab:

Are they lying to me?

Perhaps. Perhaps not. Now isn't the time to ask such questions. Now is the time to do something to see if you can isolate whether it is a server or a software issue. There's nothing you can do to test/check the server, but you can take steps isolate whether it is a software error or not. I have identified a definitive problem with your stores' basic functionality, namely, the way it doesn't deal with 'page not founds' very well. I suspect that whatever is triggering a similar(?) condition with your admin functions is what is causing admin section errors. Even though this is just speculation it makes sense to fix the identifiable problems (ie: the Page not Found behavour, which is a function of URL rewriting) and then see if the other problems still exist. Best case, one solution for both problems. Worst case, you'll have fixed one of at least two different problems).

Cheers
Rod

The Ceon url mapping is off. I turned it off as you suggested but I am still having the same issues. The store seems to work fine but I cannot upload anything or do anything related to product in the admin section.

I guess you are right. No hosting company would they are at fault. I was just repeating what they told me.

All I know so far is that I am stuck with an unusable admin and I can t seem to figure where the problem comes from.

Thank you though for looking into it and trying to help me. I will leave the url mapping off for now. I would appreciated if you could take a look again and let me know what you find out. To me, the store is fine, it is something with the admin and mysql that is messed up.

nicksab:

The Ceon url mapping is off. I turned it off as you suggested but I am still having the same issues. The store seems to work fine but I cannot upload anything or do anything related to product in the admin section..

If nothing else, you've cured the page_not_found problem.

nicksab:

I would appreciated if you could take a look again and let me know what you find out. To me, the store is fine, it is something with the admin and mysql that is messed up.

I can't see any more issues from the front end, which makes it a little hard going from here.

So, 1st things 1st.. please confirm that the "PHP Fatal error: 2013:Lost connection to MySQL server during query" error message is being generated. Are there any others?

Secondly, please log into your cPanel account (or similar) and use one of the MySQL tools provided and run a 'Repair Database'. This will attempt to fix any corrupt tables that may be causing your problem.

You state that you are using ZenCart V1.5. Was this an upgrade from an earlier version, or a new installation? If an upgrade it could be that you have some old .php files hanging around.

Finally, if "I tried to switch a category back on. I get to the second screen but when I click update, the page hang there until it becomes a 500 error page" was when you 1st noticed the problem, have you tried turning it back off again?

You also stated that the hosting company restarted their servers. Was this before your problems began, or after?

In all honesty, I/we don't have much information to work with here, so any further clues could be a great help.. such as, can you add products/categories, or does the problem only occur when editing/modifying? Is the problem limited to a particular category? Can you edit things like the 'define pages' or EZpages?
Does anything else appear to be broken?

In case you can't tell, I'm clutching at straws looking for anything that could shed a little more light on the matter.

Cheers
Rod.

ps. I'd suggest you leave the URL mapping disabled until the issue has been solved. Although it is apparent that this isn't related to your main problem, the mere fact that it puts your server into a loop with 'page not founds' isn't going to help matters in any way.

pss. It is usually at this point of problem solving where one of the ZenCart Dev team will come along, realise I'm out of my depth (or gone off on a tangent) and will present their own ideas as to the possible causes (a bit like tossing ideas off of each other), so hopefully, if none of my current suggestions prove helpful even more help is at hand. (OTOH, if they don't have any input, it means they are as bamboozled as I am).

[QUOTE=RodG;1188983]If nothing else, you've cured the page_not_found problem.

I can't see any more issues from the front end, which makes it a little hard going from here.

So, 1st things 1st.. please confirm that the "PHP Fatal error: 2013:Lost connection to MySQL server during query" error message is being generated. Are there any others?

yes the error is still being generated as per the following: 6-Feb-2013 00:48:22] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products set products_status='0' where products_id='997' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101
Secondly, please log into your cPanel account (or similar) and use one of the MySQL tools provided and run a 'Repair Database'. This will attempt to fix any corrupt tables that may be causing your problem.

I may have to look deeper into this as I don t such tool in my cpanel or in my mysql

You state that you are using ZenCart V1.5. Was this an upgrade from an earlier version, or a new installation? If an upgrade it could be that you have some old .php files hanging around.

No it was a fresh install
Finally, if "I tried to switch a category back on. I get to the second screen but when I click update, the page hang there until it becomes a 500 error page" was when you 1st noticed the problem, have you tried turning it back off again?

The category does turn on but none of the product are turned on. This is what got me worried but I also cannot upload new product. I can go thru all the step until the last where i end up on a 500 error instead of going back to the category.

You also stated that the hosting company restarted their servers. Was this before your problems began, or after?

This was after. They said they restarted the server after I mentionend my problem to them

In all honesty, I/we don't have much information to work with here, so any further clues could be a great help.. such as, can you add products/categories, or does the problem only occur when editing/modifying? Is the problem limited to a particular category? Can you edit things like the 'define pages' or EZpages?
Does anything else appear to be broken?

I can edit page thru define page fine. I am not using ez page so i can t say about. Everything else seems fine except that i remembered trying to reset the product ordered to 0 and it did not work. i ended up on 500 error page. Then I noticed the problem by trying to switch a category back on.

In case you can't tell, I'm clutching at straws looking for anything that could shed a little more light on the matter.

Thank you. I really appreciate that you are taking the time to help me with my zen cart issue.

Cheers
Rod.

ps. I'd suggest you leave the URL mapping disabled until the issue has been solved. Although it is apparent that this isn't related to your main problem, the mere fact that it puts your server into a loop with 'page not founds' isn't going to help matters in any way.

pss. It is usually at this point of problem solving where one of the ZenCart Dev team will come along, realise I'm out of my depth (or gone off on a tangent) and will present their own ideas as to the possible causes (a bit like tossing ideas off of each other), so hopefully, if none of my current suggestions prove helpful even more help is at hand. (OTOH, if they don't have any input, it means they are as bamboozled as I am).

nicksab:

yes the error is still being generated as per the following: 6-Feb-2013 00:48:22] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products set products_status='0' where products_id='997' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

Is that just a bad copy, or is "6-Feb-2013 00:48:22" really the date of this log entry? If so, you should probably ignore it.

Also, I note that this log entry is referencing the same products_id (997) as the previous error message. Do you have any idea what which product this is referring to? Have you tried editing/deleting it?

nicksab:

Secondly, please log into your cPanel account (or similar) and use one of the MySQL tools provided and run a 'Repair Database'. This will attempt to fix any corrupt tables that may be causing your problem.
I may have to look deeper into this as I don t such tool in my cpanel or in my mysql

Most cPanels I've used have at least two or three MySQL editing tools. Including the most popular (and powerful) of them all, "PHPmyAdmin"

nicksab:

You also stated that the hosting company restarted their servers. Was this before your problems began, or after?
This was after. They said they restarted the server after I mentionend my problem to them

This is looking more and more like something than you've managed to mess up, and less like anything server related.

nicksab:

Everything else seems fine except that i remembered trying to reset the product ordered to 0 and it did not work. i ended up on 500 error page.

So was THIS when the problem 1st started? (still clutching at straws looking for patterns, clues, etc)

Not wishing to rub salt into any wounds, but why did you try to set the product ordered to zero, when there is a very clear warning that reads:
WARNING: You can only reset the Order ID forward, and not backwards.
So if you set the Orders ID to 25, then change it again to 20, the next Orders ID will still be 26.

I'm assuming this is what you did? (Not that this is really important, because I can't see how this would cause your symptoms anyway).

> nicksab:

Then I noticed the problem by trying to switch a category back on.

To me, this is looking more and more like a database corruption/error rather than anything else.. Oh, sod it. I have to ask. Have you also added any other modules recently (from around the time the problem started?).

If the database has simply crashed, then running the repair tool will generally fix things, but if it has been corrupted by some kind of man made input or action (beyond what Zen would normally allow) this could be near impossible to solve without having access to your store/database for a close inspection. (Sorry, not good news). This doesn't mean its a lost cause, and almost all errors can be recovered from (in one way or another)... it is just that there are too many variables to make a remote diagnosis possible, and no disrespect intended, but other than the 'safe' advice to attempt to fix things with the repair DB tools, most other advice I can think of would involve further direct edits to your database tables, which is never a task to be taken on lightly, and it would be amiss of me to go this route with you.

I still have my fingers crossed that you'll be able to run one of these DB repair tools and all of your problems will be solved. Wishful thinking perhaps, but certainly well within the realms of possibility, so until you do this it is probably best to not speculate of the worst case scenarios.

Besides, even after all I've stated so far, I could still be a long way off the mark... it wouldn't be the first time, and I'd be happy to give you an apology if this is the case and I've simply caused you undue stress... Its times like this that I'll be happy to be wrong about something. :)

Cheers
Rod

ps. Off to bed. no more reps from me til tomorrow. Perhaps the problem will even be solved by then :)

Here is the answer I received from Webhero hosting:

You Wrote: MySQL

I am still having the same issues. Zencart seems to be losing connection to msql as per log file in cache folder.Could you please take another look at it?Thank you
Update:
Mysql queries from this domain are taking a very long time, sometimes hours and causing the mysql server to run slower and affecting other customers so it has been limited to 10 connections.

The following queries are running right now:

[royal-fleur.com @ 209.217.33.22:47976 ] :: [3262] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '630'            and        language_id = '1'
[DELAYED @ localhost ] :: [2984] ::
[royal-fleur.com @ 209.217.33.22:46270 ] :: [2838] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '630'            and        language_id = '1'
[royal-fleur.com @ 209.217.33.22:53457 ] :: [2654] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '190'            and        language_id = '1'
[royal-fleur.com @ 209.217.33.22:50786 ] :: [2210] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '190'            and        language_id = '1'
[royal-fleur.com @ 209.217.33.22:44411 ] :: [1843] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '188'            and        language_id = '1'
[royal-fleur.com @ 209.217.33.22:37432 ] :: [1616] :: update royal_products_description            set        products_viewed = products_viewed+1            where      products_id = '190'            and        language_id = '1'

I am not sure of what they mean by this.

I tried to repair the database like you suggested but the repair tools timed out when i tried to repai some table , i.e product_description.

I can let you take a look at the database if you like. Please let me know.

Thanks for your help

nicksab:

Mysql queries from this domain are taking a very long time, sometimes hours and causing the mysql server to run slower and affecting other customers so it has been limited to 10 connections.That limiting could definitely explain your "lost connection" symptoms.
And if you can't run a "repair table" command because it times out, then that's yet another symptom of problems you need to fix ... granted that one's outside of Zen Cart coding, since it's strictly a database issue.

So, you really need to sort out why you've got queries that are taking hours to run. That's entirely unusual.

A starting point would be to work with your host on sorting out WHICH queries are taking a long time, by working with THEM to review the MySQL "slow queries log" for any queries taking longer than 2 or 3 seconds to run. We can't activate the slow-query-log feature on your server for you. Only your hosting company can do that, so you'll need to work with them.

And really, this is a VERY COMPLICATED thing to sort out. But I note that you said that this was all "working fine" until the problems "started suddenly". That suggests that the problem is not with Zen Cart, but is external to Zen Cart itself.

It is of great significance that (in your first post you mentioned that) your host "restarted the server". If that means they restarted it BEFORE the problem, the "why" behind their choice to restart it is important. And if it means they restarted it AFTER your problem manifested, then it still begs the question of "why" your problem started in the first place.

Also, what's not known is "when" your host decided to limit your site to 10 connections. Was that before or after your problems manifested? When did they notify you that they suddenly throttled (choked) your site into being useless?

What's the order of significant events in all of this?

Assuming the Zen Cart software was never altered immediately before, or subsequent to, any of the problems reported by you or your host, it suggests that the problems are thus probably external to ZC. But of course the host is unlikely to ever admit that.

DrByte:

That limiting could definitely explain your "lost connection" symptoms.
And if you can't run a "repair table" command because it times out, then that's yet another symptom of problems you need to fix ... granted that one's outside of Zen Cart coding, since it's strictly a database issue.

So, you really need to sort out why you've got queries that are taking hours to run. That's entirely unusual.

A starting point would be to work with your host on sorting out WHICH queries are taking a long time, by working with THEM to review the MySQL "slow queries log" for any queries taking longer than 2 or 3 seconds to run. We can't activate the slow-query-log feature on your server for you. Only your hosting company can do that, so you'll need to work with them.

And really, this is a VERY COMPLICATED thing to sort out. But I note that you said that this was all "working fine" until the problems "started suddenly". That suggests that the problem is not with Zen Cart, but is external to Zen Cart itself.

It is of great significance that (in your first post you mentioned that) your host "restarted the server". If that means they restarted it BEFORE the problem, the "why" behind their choice to restart it is important. And if it means they restarted it AFTER your problem manifested, then it still begs the question of "why" your problem started in the first place.

Also, what's not known is "when" your host decided to limit your site to 10 connections. Was that before or after your problems manifested? When did they notify you that they suddenly throttled (choked) your site into being useless?

What's the order of significant events in all of this?

Assuming the Zen Cart software was never altered immediately before, or subsequent to, any of the problems reported by you or your host, it suggests that the problems are thus probably external to ZC. But of course the host is unlikely to ever admit that.

They limited my domain to 10 connections today according to them. I first started by complaining to them about the parsing time which was showing an overloaded server. That s when they restarted the server and said everything should be fine now.

I tried to reset to 0 product purchased to have a diiferent display of the best seller. That s when the first 500 error showed after the page hang there for awhile. So I just turned off Best seller on main page.

Then I tried to turn a categorie back on ( goes to second screen then 500 error after clicking update and hanging there for awhile)

What s strange is that I noticed today that the category was green but not all the product in it were active.

I tried to turn one of the product in another category back on and same thing happened ( 500 error )

I also tried to upload a new product in different category and the same thing happened ( getting a 500 error after the last step )

I then log back in into the admin and checked. I was surprised to see that this product was actually on ( green square ) even though I had the 500 error.

I did a full repair on the database and everything came back with status ok. ( I had to do one table at a time )

I requested to have the limitation removed and to have slow queries log enable but it won t happened until tomorrow morning.

At this point I am not too sure what to do

P.S: I have not added any module or anything and the admin/store was working fine 2 weeks ago. I was able to turn category/product on or off. I was also able to upload new product fine.

Here is the last log I have from the cache ( prob was pulled after I tried to upload the product )

[26-Feb-2013 23:02:16] PHP Fatal error: 2013:Lost connection to MySQL server during query :: insert into royal_products (products_quantity, products_type, products_model, products_price, products_date_available, products_weight, products_status, products_virtual, products_tax_class_id, manufacturers_id, products_quantity_order_min, products_quantity_order_units, products_priced_by_attribute, product_is_free, product_is_call, products_quantity_mixed, product_is_always_free_shipping, products_qty_box_status, products_quantity_order_max, products_sort_order, products_discount_type, products_discount_type_from, products_price_sorter, products_image, products_date_added, master_categories_id) values ('0', '1', 'BWG', '59.99', null, '0', '1', '0', '1', '0', '1', '1', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0', '0', 'BWG.jpg', now(), '25') in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

Here is the one I got when trying to turn a product on ( which actually did turn on )

[26-Feb-2013 23:10:39] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products
set products_status = 1, products_last_modified = now()
where products_id = '1105' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

I just notice that my hosting added a list of the query in my log files.

I have to admit it is a lot of queries and I have no clue what to do .

All I am guessing is that there is a lot of bot like googlebot, tweetmbot, 360 spider, ubuntu, twitterbot ....

If anyone would be willing to take a look at the file and see what they can discover from it.

Any help here would be greatly appreciated.

nicksab:

Here is the last log I have from the cache ( prob was pulled after I tried to upload the product )
[26-Feb-2013 23:10:39] PHP Fatal error: 2013:Lost connection to MySQL server during query :: update royal_products
set products_status = 1, products_last_modified = now()
where products_id = '1105' in /var/www/ro/royal-fleur.com/public_html/includes/classes/db/mysql/query_factory.php on line 101

Please can you confirm whether these errors were generated before, or after.......

nicksab:

I did a full repair on the database and everything came back with status ok. ( I had to do one table at a time )

The situation NOW could be that a corrupt database was the original problem of your errors, but even though this has now been successfully repaired you are still getting a similar error as a result of the restrictions your host has placed on your server.

In other words, to move forward from here, you'll need to convince your host that there was an error, that has now (probably) been fixed (by virtue of the successful DB repair, and therefore they need to lift the imposed restrictions to prove/disprove the point.

nicksab:

I tried to reset to 0 product purchased to have a diiferent display of the best seller. That s when the first 500 error showed after the page hang there for awhile. So I just turned off Best seller on main page.

I need to ask. What is the connection (that you see) between reseting the product purchased to zero and the 'Best seller' on the main page? Although there almost is certainly a connection between the two, it isn't something that would be immediately obvious to most people.. "Hmmm. I have an SQL server problem, perhaps I'll turn off the best sellers to see if that solves it" isn't the 1st thing I'd think of, so what was it that allowed you to make this 'connection'?

Quite often, if we know the reason/logic behind the association you made we may be able to expand on whatever clues you had that haven't come out yet.

Cheers
Rod

RodG:

Please can you confirm whether these errors were generated before, or after.......

The situation NOW could be that a corrupt database was the original problem of your errors, but even though this has now been successfully repaired you are still getting a similar error as a result of the restrictions your host has placed on your server.

In other words, to move forward from here, you'll need to convince your host that there was an error, that has now (probably) been fixed (by virtue of the successful DB repair, and therefore they need to lift the imposed restrictions to prove/disprove the point.

I need to ask. What is the connection (that you see) between reseting the product purchased to zero and the 'Best seller' on the main page? Although there almost is certainly a connection between the two, it isn't something that would be immediately obvious to most people.. "Hmmm. I have an SQL server problem, perhaps I'll turn off the best sellers to see if that solves it" isn't the 1st thing I'd think of, so what was it that allowed you to make this 'connection'?

Quite often, if we know the reason/logic behind the association you made we may be able to expand on whatever clues you had that haven't come out yet.

Cheers
Rod

My hosting company finally agreed to remove the limitation. The problem seems to have been fixed. My guess is that it was coming from their limitation are they were to clear on when this limitation was put in place.

But all this leave me with another problem. My hosting will put the limitation back or I have to get a VPS account with them.

They are complaining about queries taking too long and they cannot enable " slow queries logs"

How can I reduce my queries or have them be faster?

Do you know anything about Query_cache? Does it work with ZC 1.5? If yes, would this help?

Thank you

nicksab:

But all this leave me with another problem. My hosting will put the limitation back or I have to get a VPS account with them.
A limit of 10 queries per second is not enough to run most modern CMS / e-commerce systems. Just imagine - with only one query on a page and 10 people trying to view your site at once... Already you would be at the limit.

nicksab:

They are complaining about queries taking too long and they cannot enable " slow queries logs"
You can try enabling the "database" SQL_CACHE method... which may help a small amount... See this post if you want to try enabling the SQL_CACHE...

However, ultimately it boils down to your hosting account using an overloaded or slow MySQL server.

nicksab:

Do you know anything about Query_cache? Does it work with ZC 1.5? If yes, would this help?
It is included with Zen Cart 1.5.1 and newer. And yes, query_cache does reduce the overall number of queries :)

nicksab:

But all this leave me with another problem. My hosting will put the limitation back or I have to get a VPS account with them.

They are complaining about queries taking too long and they cannot enable " slow queries logs"
Those are both VERY good reasons to find a host who can handle running a database-driven website. Clearly your host doesn't have much skill with that.
Upgrading to a VPS will only cost you money, and give you nothing better since the host apparently doesn't tune the servers very well anyway.

Making note to self not to use Webhero for contemporary hosting.

nicksab:

My hosting will put the limitation back or I have to get a VPS account with them.

DO NOT get a VPS with this provider (whoever they are). It is a common misconception that a VPS will improve performance and fix all manner of problems. Rarely is this ever the case. What it usually means is that you'll end up paying more for less (plus an increased responsibility to maintain the VPS)

Shared servers usually have mutliple core processors and gigabytes of ram available for use at any given time. A VPS can/will cripple this due to the hard limits placed on the VM's that are generally used for VPS's these days.

VPS's do have their place/use, but most hosts seem to recommend them for all the wrong reasons, this instance being a classic example.

If the host is unable to maintain a stable shared server, I wouldn't trust their VPS offerings either.

Cheers
Rod