Forum Moderators: coopster
After lengthy sessions with my host, they told me this morning that it must be something in my php code that is corrupting the database. I am in the process of installing a series of backed up files, a bit at a time, to see if I can stabilize things. I have made frequent changes to the code, and I am self-taught. (I hear some groans. *wink*) Though, I've been doing this for about a year and a half, and have a good grasp of things.
Now, my question is, are there common errors that people make in their php code where it looks as though everything is working fine, but puts stress on the database?
I always thought that if I'm not getting any errors, parse errors, fatal errors, etc. then I'm doing pretty good. Apparently not.
In any case, the only errors that I ever get are when the database stops communicating, and they all look like this, except for a difference in file name and line #.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/foo/public_html/header.php on line 134
(Of course this is telling me that the code cannot access the database.)
Also, I do not know if this is relevent, but I've had a slew of errors in the error logs for the site that look like this:
[Thu Aug 17 22:05:36 2006] [error] [client 66.000.000.00] mod_security: Warning. Pattern match "([0-9a-zA-Z]+[-._+&])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}" at POST_PAYLOAD [hostname "www.foobar.com"] [uri "/user.php?action=lostpassword"]
Anyone with any ideas or general suggestions? Anything would be welcome at this point. My only other option, if I don't find my way to stabilization, is to hire someone to look over my code and find the weak spots. And I'm not a rich girl.
Suddenly, either part of the database or the entire database will go inaccessible
I would be very surprised if bad PHP code could crash MySQL... I'm not sure I believe the story your host has told you.
Is there any way of testing what has actually happened once MySQL stops responding - can you set up a test page that tries a mysql_connect and reports debugging info?
Are you able to get the MySQL log files from your server?
What versions of webserver, PHP and MySQL are you running?
As for if I can get the logs, I am tending to doubt whether my host would just freely hand those out. I do have access to phpMyAdmin (I know; I better well have that), and I have been able to determine that my database is in almost pristine condition ... no errors, almost completely optimized ... when this happens. I do have an average of 150 people accessing the database at one time. There've been times when I've wondered if that was putting stress on it.
I didn't think that my code was corrupting it. I've gotten fatal errors before that end up crashing the db. (Trial and error.) But when I write code I test it out completely because I do not like my members having down time. Who does?
Maybe it has something to do with their mysql variables. I don't know.
I have a plan to ease the stress on one of the tables, the one that is most often accessed by splitting it up. Currently there are 45 fields in it and we have readers constantly accessing it and admins constantly accessing it. I could take some of the stress off by splitting off the stuff the admins need into another table entirely.
Do you think that perhaps the amount of calls by the php code to the same table could be part of it?
I'm at a loss and am brainstorming...
Is there any way of testing what has actually happened once MySQL stops responding - can you set up a test page that tries a mysql_connect and reports debugging info?
As for this, I don't know where to start to write up a quick page that tries something like this... I could easily ftp it up to an admin area, but how to write the code I wouldn't know where to begin.
PHP v. 4.4.1, Apache API version 19990320, mysql version 4.0.16 (this is all from my phpinfo page.
Sorry to reply with yet more questions, but...
What OS is this?
What version of Apache?
Can you get a mysql upgrade? 4.0 is pretty old - there was a 4.1, but 5.0 is the version that's currently recommended for general use, and 5.1 is in testing.
I do have an average of 150 people accessing the database at one time. There've been times when I've wondered if that was putting stress on it.
MySQL can handle load much much higher than that.
Do you have any statistics on how much load the server is under?
Is PHP running in safe mode?
a plan to ease the stress on one of the tables, the one that is most often accessed by splitting it up.
Changing database schema doesn't strike me as the first thing I'd try.
Have you thought of trying your setup on a different server?
Can you share your visitors and pageview numbers with us?
Did you write the PHP code yourself or is it an off-the-shelf app?
Has anyone else reviewed your code?
First of all, please note that I am on a shared server, not a dedicated one. I can ask, but it is not assured that I can get anything upgraded. The host has not been exactly on top of the latest upgrades, but they do upgrade.
Here is the Apache info. I am pretty sure it is version 1.3.33
Apache Version Apache
Apache Release 10333100
Apache API Version 19990320
(Not the latest, I know.)
I will be asking the host if they plan on upgrading the mysql anytime soon. Can't hurt to ask.
Do you have any statistics on how much load the server is under?
Traffic
Received 2 GB
Sent 282 MB
Total 2 GB
ø per hour
Received 18 MB
Sent 3 MB
Total 21 MB
I am not sure if this helps. I have a feeling most of the traffic is mine. This is statistics since the server was restarted a little over 4 days ago.
Is PHP running in safe mode?
Safe Mode is off. In fact, that is how I need it since my particular php software won't run properly with it on.
Did you write the PHP code yourself or is it an off-the-shelf app?
The software is half off-the-shelf and half written by me, through improvements and additions. The original software was eFiction 1.1. I'm not sure if you are familiar with it or not. I have since morphed it into something else, but have kept the original idea and everything is based off the original code.
Can you share your visitors and pageview numbers with us?
16 Aug 2006 Visits: 1738 Pages: 10841 Hits: 57497 Bandwidth: 482.22 MB
17 Aug 2006 Visits: 1668 Pages: 10441 Hits: 54664 Bandwidth: 437.92 MB
18 Aug 2006 Visits: 1525 Pages: 10638 Hits: 61337 Bandwidth: 504.02 MB
I hope the last three days is enough. It's too difficult to prepare for forum view.
Has anyone else reviewed your code?
No. I have not had anyone do that yet. What I am doing now is comparisons. Bringing the critical (most used) pages as text into Word and comparing documents from backup copies when the code was working without any database problems. I am doing that now and will gradually re-introduce my (improved) changes. I wouldn't be adverse to having someone look and give me advice, but I don't like to make my code too public, if you know what I mean.
I hope some of these answers help. The only one I wasn't too sure of was your asking what OS it was. I believe it is a unix based OS called FreeBSD. That is what I got from their FAQ.
I have found that changing the dbuser in my dbconfig file will fix it, but it lasts half a day to a couple days at most.
This is the part that is most confusing to me. I'm not sure what else is happening here except perhaps PRIVILEGES are being flushed. Unless tables are REPAIRed then as well. I would ask to see the logs or perhaps you can find out what the last query run is before the corruption occurs. Are you doing a lot of table updates/deletes? Perhaps you need to OPTIMIZE [dev.mysql.com] and/or REPAIR [dev.mysql.com] tables more often? The second link (REPAIR) has some reading you will certainly want to review. Best of luck on this, and keep us posted as to your findings!
