Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

VERY VERY Odd problem! I'm looking for any help I can get.


phi148

Recommended Posts

Hello,

 

I am experiencing a very, very odd problem. I have collected quite a bit of data on this, but need help resolving it.

 

For some reason, for about 5 minutes, every day, my OScommerce website and my OSTicket system cannot connect to my database.

 

I know EXACTLY when the problem will occur, and can even tell you when it will occur in the future.

 

For example, the problem occurred:

 

Friday at 7AM

Saturday at 8AM

Sunday at 9AM

Monday (Today) at 10AM

 

It will happen again tomorrow at 11AM.

 

It moves everyday by 1 hour. Also, it only lasts for about 5 minutes, and then magically everything works again. I have no idea what the heck could be causing this.

 

All of a sudden, my OSTicket will fail, I will start getting errors. (Since it interacts with my database). "Unable to connect to the database — No such file or directory http://www.mysite.com/help/scp/tickets.php?status=open"

 

My public OSCommerce store will fail "Unable to connect to database server"

 

My administrator area of OSCommerce will also fail. "03-Oct-2016 10:00:35 America/Chicago] PHP Warning: mysqli_connect(): (HY000/2002): No such file or directory in /home/xxx/public_html/admin/includes/functions/database.php on line 20"

 

Basically, anything that contacts my database bombs. It lasts for 5 minutes. Then, it will all of a sudden start working again.

 

The odd thing is ... it is completely predictable... I know when it is going to happen, but I have no idea why it is happening.

 

Now, these "no such file or directory" errors are not accurate by any means. These files ALWAYS exist. They are ALWAYS there. The problem, is that the database appears to be simply offline and does not allow any connections for 5 minutes.

 

Any tips on how to debug this further?

 

Thank you!

 

 

Link to comment
Share on other sites

Are you hosted with a commercial server company, or are you foolishly trying to run an eCommerce site off a PC? Shared, dedicated, VPS, colo, or something else? Have you reported this to hosting support (if it's not you)? Have they said anything? Is the database server running on a different machine than your site?

 

Do you have enough data to say confidently that this happens every 25 hours, for 5 minutes? That doesn't sound like a cron job or scheduled task sucking up all the CPU cycles -- they usually would be every 24 hours. Can you tell if the DB server's clock is keeping proper time, and not losing an hour a day? You might be able to get on to phpMyAdmin or a command line and display now() once a day, to see if it's 86400 seconds later per day. How does this machine sync its clock to the world on a regular basis (use a time server)? If this is on your own PC (or a server you are solely responsible for), have you scanned for malware to see if some hacker is taking over your machine for a once-a-day CPU-intensive task, such as spamming or running a DDoS attack? Either of those should show up with lots of traffic (high bandwidth usage). I don't think that would lose your clock an hour a day, but they might be scheduling it on a 25 hour cycle to reduce the chance that you'd notice it (if it happened at the same time every day). Anyway, be sure to look at cron and at jobs (if Linux), and task scheduler jobs (if Windows) to see if you can account for everything that's running. A malicious job might rewrite its job entry daily, to reschedule it.

 

Hopefully that's enough to get you some leads...

 

 

Link to comment
Share on other sites

Are you hosted with a commercial server company, or are you foolishly trying to run an eCommerce site off a PC? Shared, dedicated, VPS, colo, or something else? Have you reported this to hosting support (if it's not you)? Have they said anything? Is the database server running on a different machine than your site?

 

Do you have enough data to say confidently that this happens every 25 hours, for 5 minutes? That doesn't sound like a cron job or scheduled task sucking up all the CPU cycles -- they usually would be every 24 hours. Can you tell if the DB server's clock is keeping proper time, and not losing an hour a day? You might be able to get on to phpMyAdmin or a command line and display now() once a day, to see if it's 86400 seconds later per day. How does this machine sync its clock to the world on a regular basis (use a time server)? If this is on your own PC (or a server you are solely responsible for), have you scanned for malware to see if some hacker is taking over your machine for a once-a-day CPU-intensive task, such as spamming or running a DDoS attack? Either of those should show up with lots of traffic (high bandwidth usage). I don't think that would lose your clock an hour a day, but they might be scheduling it on a 25 hour cycle to reduce the chance that you'd notice it (if it happened at the same time every day). Anyway, be sure to look at cron and at jobs (if Linux), and task scheduler jobs (if Windows) to see if you can account for everything that's running. A malicious job might rewrite its job entry daily, to reschedule it.

 

Hopefully that's enough to get you some leads...

 

Thanks for the feedback.  Here is some more info!

 

1) I am hosting with a commercial server company.  It is a shared hosting.

2) I have also reported it to hosting support - I'm waiting on the response.

3) I get automatic emails when this happens (from OSTicket) and I have also checked my server logs.  It increases by 1 hour, every day... spot on.

4) I checked my CRON jobs, I don't have any that run like this.

5) I'm not sure about the DB server's clock.  I'll look into that one and let you know.  I'm also not sure how the machine syncs its clock.

6) My site is scanned regularly.  No viruses or malware.  I don't see any evidence of a DDoS attack, so I'm ruling that out.

 

Thanks again MrPhil and I'll let you know what I find out!

Link to comment
Share on other sites

Hi. if your server is linux probe to install php5-mysqlnd, apt-get install php5-mysqlnd, i have de same error time a go  wiht mysqli

check your configure.php define('STORE_SESSIONS', 'mysql'); not mysqli. Install php5-mysqlnd in my debian so. solve this problem.

Regards

No external links here, thank you

Link to comment
Share on other sites

I'd sugest that these are the schedules on which the host is performing a server backup

 

Possible, I suppose, but I've never heard of a host doing this on a 25 hour schedule, nor of its only lasting 5 minutes, nor of its bringing services to a halt. It's a badly managed system if that is happening. If the host has customers spread around the world, possibly they could be on such a schedule so certain geographies aren't constantly "picked on" for service disruption, but still, they shouldn't have the server grinding to a halt during a backup. Ask them about it. They may not even be aware of it, until someone complains.

 

Just out of curiosity, how many time zones away from you is your host? Maybe they're doing backups during the early morning hours (low load) for most of their hosted sites. That is, they might cycle midnight to 6am local time and repeat, rather than going through the full day.

Link to comment
Share on other sites

It will be interesting to hear what the cause of this is, so make sure you post back when you find the problem.  Everything I've seen on server functions is based on a 24 hour clock or to run at a certain time.  With Cron, it's within a 24 hour time zone and at the same times daily, weekly, monthy.  Dying to know wha the cause is. 

 

I wonder if it's some kind of DDOS attack on the server?  There were some news reports last week of peripheral devices like cameras being hacked and used to in attacks.

I'm not really a dog.

Link to comment
Share on other sites

So, not solved yet.  Here is the latest response from my hosting company, which *possibly* could make sense.  I'll know tomorrow.  

 

----

"I have found MySQL crashing and getting restarted.  From what I can see from logs, there was few crashed databases on the server(not any databases on your account) which appears to have causing issue prior to crash and I have repaired them. So far MySQL server looks good. If you see this issues happening again, let us know and we will investigate this further."

----

 

Since I am on a shared hosting server, I guess it is possible someone else could have problems, which indeed could crash the database and cause a reboot of mysql ?

Link to comment
Share on other sites

A MySQL crash/auto-restart is certainly possible, although I would expect such a thing to happen at more random times if it's due to server load. There remains the possibility that the server is under attack, but exactly an hour later each day is rather odd (too predictable). Another customer may be doing something CPU-intensive enough (on that strange schedule) to crash the system, which your host will have to deal with if they can find the culprit. Keep your host tech support informed about further glitches, and see what happens now.

Link to comment
Share on other sites

@@phi148 I suggest you look at the raw access log (available in your control panel or from your host) and look at the time around the failure so see if you are being hit by the same IP(s) each day. I would think your host would have looked at this but maybe not.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

Ok, it happened again today..exactly at the time predicted.

 

I contacted customer service again and now have another possible solution.  They were able to capture the MySQL logs in time:

 

Here is what I received:

 

Fortunately this time we had logs available prior to MySQL restart and have found this happening due to mod_security database, modsec. Relevant logs copied below.

=============================
2016-10-05 12:59:46 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:47 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:48 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:55 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:56 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:57 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 12:59:59 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 13:00:01 7779 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-05 13:00:02 7779 [Note] /usr/sbin/mysqld: Normal shutdown
=============================

I have repaired this database.

---

 

We shall see if this database repair fixes it....

Link to comment
Share on other sites

Modsec is a mod_security database.  You can find a lot by searching Mod Security but it is a firewall type device for Apache to cover known vulnerabilities.  Are you on a Cpanel Server?

I'm not really a dog.

Link to comment
Share on other sites

Hopefully, your host gets on top of this quickly and there's a lot of good help available from Cpanel.  However, maybe start looking for hosts.  I think there are some people here that can chiime in.  Maybe Jack has some insight.  A good host is golden.

I'm not really a dog.

Link to comment
Share on other sites

Modsec is a mod_security database.  You can find a lot by searching Mod Security but it is a firewall type device for Apache to cover known vulnerabilities.  Are you on a Cpanel Server?

 

Yes, I have Cpanel.

 

As for your hosts comments, I'd love to know who you all recommend!  I agree.. a good host is golden!  :)

 

Thanks!

Link to comment
Share on other sites

If you've been happy with this host, then give them a chance.  Since they repaired the database you may be in good shape.  I guess you'll know when the crash time comes and passes.  I have a dedicated server, so I don't have any hosts recomendations.  

I'm not really a dog.

Link to comment
Share on other sites

Again this was happening due to modsec database. Relevent logs copied below.

=========================
2016-10-06 13:58:03 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 13:58:04 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 13:58:14 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 13:58:47 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 13:58:49 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 13:59:48 3989 [ERROR] /usr/sbin/mysqld: Table './modsec/hits' is marked as crashed and should be repaired
2016-10-06 14:00:02 3989 [Note] /usr/sbin/mysqld: Normal shutdown
=========================

Further checking the database in detail, the database was found of having very old logs and data and I have truncated it now. Hopefully this should solve the issue. Please watch the website tomorrow and let us know if you see any more issue.

Link to comment
Share on other sites

So, still not fixed.  Here is the latest.

 

***

We have checked the server in detail and this issue does not look to be server related. The logs that our techs gave you is that of database table that needed repair but that does not or will not cause the entire mysql service to be down. I have also checked by restarting mysql service multiple times and each time it got restarted within 6-8 seconds. This would be unnoticeable to the users and will never attribute to the 5 minute outage that you seem to be experiencing. This can also happen if there are too many concurrent database sessions active for your user. You may need to check your settings to see if you are using persistent mysql connections. I would suggest that you contact the developers of oscommerce to see if they can debug this.
***

 

I replied back letting them know I am not using persistent mysql connections.   My max connections for a database is 500.  I'm coming nowhere near that.

 

I'd like to disable mod_security temporarily and see if that has any impact... since this may be related... ?

Link to comment
Share on other sites

Unless you are the root user on the server using WHM, you cannot disable ModSecurity.  Anyway you look at it there is a problem happening with the modsec table if they have to keep repairing it.  I just restarted MySQL on my server and it's only like 2-4 seconds that the site cannot connect.

I'm not really a dog.

Link to comment
Share on other sites

You could search the Cpanel forums to see if you find anything similar, but if you don't control the server then there's nothing you can do other than point your host to it.

 

I was going to spend some time trying to help find a solution, but I have a tree to go cut up.  Orlando was affected by hurricane Matthew, and thus the tree.  It would probably be more fun to help you.

I'm not really a dog.

Link to comment
Share on other sites

@@phi148 To be clear about persistent connections, in case you are not aware of it, there are two things to consider. One is handled by the .htaccess method but one is handled in your configure files. For the latter, make sure the USE_PCONNECT setting is set to false. And below it, make sure the STORE_SESSIONS is set to mysql.

 

Regarding mod security, we always run with that off since it seems to cause more problems that it is worth.

 

Regarding the database connections, this is why I mentioned looking at the access log. It could be that your site is getting hammered and that could cause the problem. Though, as Phil mentioned, it seems very unlikely someone would be doing this on the schedule you mention.  Access logs can be difficult to read but you may want to look at AWStats, if that is installed in your control panel. It can provide the same details if you look closely enough.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

@@phi148 To be clear about persistent connections, in case you are not aware of it, there are two things to consider. One is handled by the .htaccess method but one is handled in your configure files. For the latter, make sure the USE_PCONNECT setting is set to false. And below it, make sure the STORE_SESSIONS is set to mysql.

 

Regarding mod security, we always run with that off since it seems to cause more problems that it is worth.

 

Regarding the database connections, this is why I mentioned looking at the access log. It could be that your site is getting hammered and that could cause the problem. Though, as Phil mentioned, it seems very unlikely someone would be doing this on the schedule you mention.  Access logs can be difficult to read but you may want to look at AWStats, if that is installed in your control panel. It can provide the same details if you look closely enough.

 

Yes sir, I verified the configure.php was correct.

 

I *think* the problem went away yesterday after disabling MOD_SECURITY and I removed all my CRON jobs.  

 

Point of note, I did not think my CRON jobs were the problem, as none of them run on a 25 hour schedule.  However, I disabled these per the hosts request.

 

I have added back my CRON jobs ... so now, the only thing disabled is MOD_SECURITY.

 

Will monitor today to see if it happens.

 

I'll keep you all posted and can't thank you enough for the feedback!

Link to comment
Share on other sites

Dang it... I was incorrect.

 

I didn't see the problem yesterday because I wasn't logged in.  Happened again today.

 

I'm checking to see if my host re-enabled MOD_SECURITY.  If they didn't, then that is no longer the issue.

Link to comment
Share on other sites

Is any part of Mod Security accessible/configurable by you? It sounds like something that normally is fully controlled by the host. Often it's a list of forbidden terms (likely hacks) in URLs and the like. Sometimes you can turn off Mod Security via .htaccess, but that won't help anyone if its tables are crashing (unless it's something about your site or setup that's crashing it!). At any rate, if it's Mod Security, it's usually something beyond your control, and the full responsibility of the host (unless they can point to something special about your site, they shouldn't have to involve you in debugging it).

Link to comment
Share on other sites

Something with Mod Security was the issue.  I finally threw in the towel, got rid of a shared hosting and now use a VPS.  Problem solved, plus a faster website to boot.  :)

 

Thanks all for the feedback!

Link to comment
Share on other sites

Glad to hear you got this solved.  So, you changed host then?  If so, it's not neccessarily ModSecurity and I've run that on a dedicated box for years without having a problem.  It could be something else on the server you were on, but it seems like you gave them some time to fix it.  However, I think it's worth to be on the best setup you can be.  Everybody in the world likes faster.

 

Edward Demming said "The greatest losses in business are unknown".  Basically, you don't know you lost sales. 

I'm not really a dog.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...