I have a Redhat Linux Enterprise/Cpanel/Apache 1.3.33 web hosting server with MySQL 4.0.22, with all of the latest updates. It has Dual P4 Xeons with 1 GB of RAM.

I am having a recurring problem with MySQL server. The server will work perfectly for 3-5 days, and then between 12AM and 2AM, the server will stop working properly. It's very strange and difficult to explain. I apologize for the length of this post, but it is necessary to explain what is happening. The problem gets stranger as my explanation goes on...

The server apparently responds to some queries, but not to others. For instance, I host a PHP-coded directory site (call it Site A) with database search capabilities. If you do a search on that site, things go fine. The search page uses a query similar to this:

SELECT *,CONCAT(lastName, firstName, middleName) as name,UNIX_TIMESTAMP(dateCreated) as dateCreated,UNIX_TIMESTAMP(datePosted) as datePosted FROM tblUsers WHERE visible=1 AND type='p' ORDER BY name asc

In fact, it appears that any query being done on Site A works fine.

On another site (Site B), which has phpBB, you can view a listing of posts in a category, but cannot use the search feature. What is stranger is that the search feature seems to work if the keyword you typed turns up no results. Other keywords will just cause the page to just hang there and never display anything. Once it hangs on one page, you can't go to ANY other pages, even ones that worked before unless you close the browser window and start a new session. Another site (Site C) running the same phpBB software as Site B but with a different database works without any problem.

On yet another site (Site D) which uses the same php code to connect the database as Site A, I can view a page which displays all users using this query:

SELECT userID,userName,g.name as groupName,CONCAT(lastName, ', ', firstName) as name,UNIX_TIMESTAMP(dateCreated) as dateCreated,UNIX_TIMESTAMP(lastLogin) as lastLogin FROM tblUsers u,tblGroups g WHERE g.groupID=u.groupID && disabled=0 AND u.userID>1 ORDER BY name asc

But it just hangs if I try to view a listing of the orders in the system -- php uses this query:

SELECT o.orderID,p.name as productName,o.propertyAddress,os.status,o.totalAmt,o.customerID,c.email,CONCAT(c.lastName, ', ', c.firstName) as name,c.company,c.website,UNIX_TIMESTAMP(o.dateOrdered) as dateOrdered,UNIX_TIMESTAMP(o.dateModified) as dateModified FROM tblOrders o,tblProducts p,tblCustomers c,tblOrderStatuses os WHERE o.archived=0 AND p.orderID=o.orderID AND o.customerID=c.customerID AND o.statusID=os.orderStatusID ORDER BY dateOrdered desc,orderID desc

NOW, here is where it gets even stranger. Last night, I just found out that even though the queries are not going through by viewing the web pages that execute the queries. I can still execute the queries by logging onto the machine with SSH and using the MySQL console. And even more strange, is that if I run an identical copy of web Site D (mentioned above) on my PC web server at home, and set the database server to be my webhosting server out on the net. The web site running on my PC works perfectly with no problems whatsoever. ALL PAGES LOAD PERFECTLY, ALL QUERIES EXECUTE PERFECTLY.

- mysqladmin flush-hosts, flush-privileges, flush-tables, flush-threads, reload, refresh, all do not fix it
- Restarting MySQL does not fix it, nor does restarting Apache
- mysqladmin shutdown shuts down the MySQL server but restarting it again does not fix it. same with shutdown and restart of apache.
- memory usage during the last time of problem was 20%, cpu was 0.04

The only way I have found to fix the problem is to completely reboot the machine. I then have to wait it out for 3-5 days for things to start going wrong again.

Can someone please help me. What is causing this to happen? I looked through the MySQL help pages that suggest what to do if MySQL keeps crashing. But the thing is, the server isn't crashing as is apparent by my ability to fully use the server from an external host. And like I said, some queries go through fine from the localhost.

I would extremely appreciate any help anyone can provide me.

I skimmed this, so sorry if you've already addressed this.

In phpMyAdmin, pull up the privilege information for the usernames you have setup. See if they have a maximum query limit. If so, delete the number.

If your mySQL server maintains a daily average, you'd hit the query limit around the same time every few days. One other solution is to have mySQL be restarted every night at like midnight. If this is a accumulation problem, then restarting nightly should provide a fix.

Jakiao,

Thank you for your response. I checked for query limits as you suggested. All limits were set to 0 for all users.

Please read my original post more thoroughly. Hopefully you can give me some other suggestions.

Thanks.

I don't if this will help, but...

I was just doing some checks using phpMyAdmin and I noticed that the sites that didn't seem to be affected at all (when MySQL server starts not working properly) have a different host setting than the sites that have problems getting queries answered.

The sites that seem to work OK connect to "localhost". While the others specify the IP address of the server. When I looked in phpMyAdmin at the process list, for the sites that specify localhost, the host field says "localhost". But for the sites that specify an IP address, its shows my fully qualified domain name, followed by a : and some numbers. Like this:

servername.mydomain.com:46420

The number on the end just keeps going up for each process.

I'm going to switch one of the sites that has problems when MySQL server stops working properly to be connecting to localhost instead of the IP address. Who knows, that may help.

But I'd still like to get to the bottom of this. Perhaps what I've just written here will make a light go on in someone's head out there.

Update:
When I switched one of my site's database server setting to be "localhost" instead of the xxx.xxx.xxx.xxx, the site did not experience any problems. Another site I did not make any changes to (it continued to use the IP address as its db server) experienced the same problems as usual after 3 days of working perfectly fine.

So it looks like a solution is to use localhost instead of the IP address as the DB server. It doesn't solve the core problem, but it does do the job...

But why must I do this? I should be able to use localhost or the ip, right?

This now sound like a privilege problem. The easiest way to fix this is to login to phpMyAdmin, pull up the userlist, and check if it restricts which host you can access the database from.