The freeze-up did result in a "too many connections" message when I reloaded. I am no longer using persistent connections in config.
This is puzzling, since this problem had never occured under Perl. Could it be that PHP is still holding on to the connections too long even though the config file now has the flag off?
When you change to non-persistent those old httpd children still have the mysql connections open. After awhile these will close by themselves when the httpd children recycle.
------------------- Rick Baker UBBThreads developer
What version of mysql are you running? It sounds like the LOCKED status means the tables are getting locked in which case no queries can be run on the tables until they are unlocked.
------------------- Rick Baker UBBThreads developer
Just did some searching on deja, and it looks like not only do you need to restart mysql when doing this change but the webserver should be restarted as well. Is this possible for you to do this?
------------------- Rick Baker UBBThreads developer
No, I can't restart the webserver, but the sysadmins are on the lookout to see if they can determine what's going on.
But things seem fine since my last mysql restart, and I'm suspecting that the order of things might matter (ie restart mysql / change config options). Might be academic now, things seem OK: no dormant/locked processes shown in mysqladmin processlist.
mysql status shows: ************************* ection id: 4891 Current database: Current user: xxxxx@localhost Current pager: stdout Using outfile: '' Server version: 3.23.37 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 2 hours 31 min 3 sec
Threads: 1 Questions: 143606 Slow queries: 20 Opens: 78 Flush tables: 1 Ope n tables: 64 Queries per second avg: 15.845
*********************************
I'll post again here if the problem continues to occur. Immense thanks for all the help.
I spoke too fast... 30 seconds after my previous post, the problem occured again. Dozens of locked processes, too many connections.
I wonder if it's something I'm doing... ie I went into mysql and ran status, came out, ran ps aux | more, checked the boards and it crashed. I'm going to try to keep my grubby hands off to see if that helps. But if this rings a bell, please advise.
OK, now we might be getting somewhere. I just noticed that one of my boards is not accessible, and is giving me errors. All the other boards are OK.
When I try to access the bad board, it give me:
SQL ERROR: Unable to do_query: SELECT B_Number,B_Parent,B_Posted,B_Last_Post,B_Username,B_Subject,B_Main,B_Status,B_Approved,B_Icon,B_Reged,B_Counter,B_Sticky,B_Color,B_Replies,B_UStatus FROM w3t_Posts WHERE B_Topic = 1 AND B_Board = 'marilyn' ORDER BY B_Last_Post DESC LIMIT 11 Got error 127 from table handler: 1030 Warning: Supplied argument is not a valid MySQL result resource in /home/maxfisch/public_html/thehang/mysql.inc.php on line 99
Is my database corrupted? (I wouldn't be surprised, considering the company it keeps)
sudo /usr/local/mysql/bin/myisamchk -r /usr/local/mysql/var/WWWThreads/w3t _Boards.MYD /usr/local/mysql/bin/myisamchk: error: '/usr/local/mysql/var/WWWThreads/w3t_Boar ds.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair
OK, it's amazing the things you find out when you read the mySQL manual <img border="0" title="" alt="[Eek!]" src="images/icons/shocked.gif" /> I needed to stop the mysql server while running myisamchk <img border="0" title="" alt="[Embarrassed]" src="images/icons/blush.gif" />
I successfully repaired the table, and restarted the mysql server. Let's see if it'll crash again. I'm keeping my fingers crossed.
Max
PS- please forgive the many deperate messages. I truly appreciate your help, Rick
I'm at my wit's end - crashed again twice. Repaired tables and restarted server each time. I'm monitoring processlist... everything is ok for a while, then for no apparent reason, dozens of locked processes pop up, and boards freeze up.
It's increasingly looking like I'll have to switch back to Perl soon <img border="0" title="" alt="[Frown]" src="images/icons/frown.gif" />
Have your admins restarted the webserver? It definitely sounds like you have some corruption going on. The strange thing is that the queries are exactly the same between the PHP and PERL version. Do you actually have users accessing the PHP forums or are they still using the PERL ones?
I've been looking for a common thread on deja to see what the problem might be. Any idea of the mysql version you are running, and what version of php?
------------------- Rick Baker UBBThreads developer
Ok, reread all your posts and see your mysql version. It's strange that you say it works ok for awhile and then the locked processes start to appear. There must be something that triggers this. You might want to try running myisamchk -e on all of your data files to see if there is any other corruption going on. Then, you might try OPTIMIZING all of your tables as well. You can do these from the mysql command prompt (make sure the mysql server is down when doing this):
SHOW TABLES; (this will show you the names of all of your tables)
OPTIMIZE TABLE tablename; (for each table)
------------------- Rick Baker UBBThreads developer
The webserver hadn't been restarted after the last table repair.
1) crashed again 2) I repaired all the tables this morning with myisamchk -r 3) webserver and mysql server restarted by admins 4) Boards seemd stable 5) ran myisamchk -e on all tables, all was ok 6) couldn't get into mysql with mysql server stopped in order to run optimize, so I ran myisamchk --sort-index instead 7) restarted mysql server, my fingers are crossed. Boards seem stable and fast as hell.
I had sent you a private message this morning on the ubbthreads board, which you can ignore for now ... I'll report developments over here.
I know I'm repeating myself, but thanks for all the help.
As you stated, php 4.0.5 was running. Although I had verified prior to the UBBThreads installation that 4.0.6 was running, it apparently got downgraded accidentally when I asked the admins to recompile PHP with mysql support.
At this point, the boards seem stable, and wickedly fast. I'll post an update as necessary.
This problem happened a lot on my old host's forums server. They had quite a few forums systems running on this server, and the MySQL server died about once every day (at least).
I'm not sure if this ever got fixed, because they decided to chmod 700 my forums directory (after I used a php system command to create a DB backup).
I moved my forums over to our new dedicated server.
I was actually trying to establish that you weren't the only one having this problem. <img border="0" title="" alt="[Smile]" src="images/icons/smile.gif" />
I don't know how PHP utilizes the MySQL API, but if perl's DBI works fine, there must be some difference between the way they use the API. It makes me wonder if php 4.1.0/4.0.7 (which are, from what I read on zend.com, going to be released simultaneously) will have updates to this situation.
I'll have to hope that a PHP upgrade will solve the problem - I'm using 4.06 right now.
In the meantime, it looks like my server's 256 Mb of memory is getting a run for its money, and there's periodic use of the swap file (under perl). I think I'm going to switch to PHP again, just to monitor memory utilization. As each child process carries a copy of PHP with it, it's possible that PHP is being much more demanding of memory, causing the system to slow down as it starts swapping to disk, and in turn causing the mySQL/PHP combo to misbehave, lock processes in an attempt to recover from the slowdown, etc.
That's my best theory so far. I'll start by buying some more memory.
The problem is finally solved: I was running under the Linux 2.2 kernel, and an upgrade to 2.4 fixed the problem for some mysterious reason. Thanks for all who tried to help! Max