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.