What causes a MySQL database to hang ??
 

I was activating signed up members on my dating site which is hosted with phatservers and the database just hung the index page is now just a white page and when I try and check or repair the database it gets so far and just hangs and I havent got a recent backup with all the recent registered members ..shit :(
Anybody know what the cause is or any way to fix this

Are you trying to do the check/repair through phpmyadmin or are you in on ssh?

Im on the phpmyadmin

Ask for your server to be restarted.

I was having issues with a table last week. It was taking so long to check or repair that phpmyadmin was timing out.

In the front screen of phpmyadmin you can look at the processes running. If you have made several attempts at the check/repair you probably have multiple threads out there waiting for each to complete.

If you do I would get rid of all but the active process.

Once that process finishes, what I did was wrote a quick little php script to do the check table and repair table if necessary. In the script, at the start I turned off the server timeout.

The check table in my case took 6 hours to run but with the server timeout suspended it eventually reported back to the browser when it was done.

Good Luck

by any chance you got a log file on there thats like massive in size?

I have managed to find the table that is causing the problem

Its saying it is 8.7MB in size but I cant browse it and I also have this warning at the bottom of the tables "1 May be approximate. See FAQ 3.11" I have no idea what that means

When I download the table and open it with worpad it is empty. I dont know whether to just delete the table as I cant repair / check or optimize it and I have no idea what it contains either

Hit up Moose and the fellas they should be able to help you...

Sarettah, wtf you got in that database that takes it 6 hours to check/repair?

yeah think I will I cant figure this one out and I hate pestering the tech guys ..lol

Well, we had 22 million urls in it.

I have since created a more efficient solution :thumbsup

You can't view mysql tables with a word processor, they're in a proprietary binary format. DON'T go deleting any tables until you're 100% sure they're corrupted to hell and back (which is probably unlikely)... and even then I'd rename them and keep them anyway.

Do you have SSH access? Or is Phat a fully managed host? If you have SSH, try this...

mysql -u <username> -p
... enter your password ...
use <database_name>
check table <table_name> <-- repeat as many times as necessary
If any tables show up errors,
repair table <table_name>

Also, show processlist when logged into mysql will display what's happening... this may give you a hint why things seem to be "stuck"

MySQL may also be writing an error log, in my setup it is located in the database directory and named <hostname>.err ... eg if your server is called www.mydomain.com the error log will be called www.mydomain.com.err

Here's an example of a line showing a problem with a table...

090601 13:35:35 [ERROR] /usr/local/mysql/libexec/mysqld: Table './sdqueue/pre_urlqueue_e9' is marked as crashed and should be repaired

Thanks Rowan and all the other guys for helping out and giving suggestions. Im gonna let the tech guys have a look at it now before I go deleting anything and completly screw things up

Good old Phatservers has fixed the problem it seems the database got corrupted

Thanks Phatservers for a great fast service :thumbsup

Word, Phat knows their shit, I'd rather let them do it than fuck anything up even more than it is.

They sure do, and they fixed it real quick :thumbsup

For future reference, don't waste a minute, that's what we are here for.

You have an :error, we :helpme you.
:thumbsup

Thanks you guys rock :thumbsup

Cheesiest use of smilies EVAR! :1orglaugh

:thumbsup