FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum » How To » MYSQL MESS AFTER HOSTING SERVICE CRASH
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21022] Tue, 09 November 2004 00:11 Go to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
We had an outage with our hosting service and afterwards we got mysql error messages every time we posted to our forum, but the posts went through. After about 24 hours of that, the whole forum shut down with this error message displaying. What does this mean and why do shutdowns totally mess up the FUDforum?

(/hsphere/local/home/aag522/wf4bm.net/forum/index.php:112
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:174
/hsphere/local/home/aag522/wf4bm.net/forum/theme/default/index.php:136
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:1144
) 1016: Can't open file: 'fud_thread.MYI'. (errno: 145)
Query: SELECT count(*) FROM fud_msg m INNER JOIN fud_thread t ON m.thread_id=t.id INNER JOIN fud_forum f ON t.forum_id=f.id WHERE m.apr=0 AND (f.forum_opt>=2 AND (f.forum_opt & 2) > 0)
Server Version: 4.0.21-standard
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21032 is a reply to message #21022] Tue, 09 November 2004 08:58 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
You need to repair the mYSQL tables. Fortunately this is very easy to do, on the MySQL command line you need to run
REPAIR [table_name]; command.

The table name is the 1st part of the fud_thread.MYI string, Ex. fud_thread


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21034 is a reply to message #21032] Tue, 09 November 2004 12:48 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
After repair, many files are noted as non-repairable and the forum still doesn't work properly.

We can go back and use an old backup, but I am at a loss as to why this happens when the server shuts down unexpectedly and would really like to know the why of it.
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21035 is a reply to message #21034] Tue, 09 November 2004 13:16 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Here is one of the messages we still get after the repair, any time someone posts. If you close the program and reopen it, the post is there and actually went through but you will have to close the program. If you reverse the browser, the post doesn't go through. There are many other errors as well.

I'm just concerned that a server crash, which happens, should be able to create this much non-repairable damage to a forum, and what can be done so it doesn't happen. We don't have control of our hosting company's servers, so we can't prevent shut downs.


Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 45
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21036 is a reply to message #21034] Tue, 09 November 2004 13:17 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Try asking your ISP about the unrepairable table, it could be file permissions issue meaning that your MySQL user does not have rights to those tables.

This happenss because when mysql crashes or not shutdown properly it could be in the process of writing data to the database and when it dies ends up writing partial data. This partial data causes the database file to be corrupt. Usually because this data is at the end of the database REPAIR table can fix it.


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21037 is a reply to message #21035] Tue, 09 November 2004 13:18 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
creamywf wrote on Tue, 09 November 2004 13:16


Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 45


This is a different error, it's a connection issue. The database server either is not running or the unix domain socket's path had changed. You can find out the server's new address (ip) or new path to the unix domain socket from your ISP.


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21041 is a reply to message #21037] Wed, 10 November 2004 01:13 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Here is the info from the person who tried to repair the database, it seems he is at an impasse. Any ideas on where to go from here that I can relay to him????

To clarify on the repair methods I have attempted with your database.

Method 1.
1. Logging into MySQL.
2. Running: mysqlcheck -B aag522_FUDforum -e -a -u root -p
3. Running: mysqlcheck -B aag522_FUDforum -e -r -u root -p
4. Running: mysqlcheck -B aag522_FUDforum -e -c -u root -p

Method 2.
1. Shutting down MySQL server process.
2. Running the following commands.
a. cd /var/lib/mysql/aag522_FUDforum
b. myisamchk -e -r -f *.MYI

Method 3.
1. Created a new database.
2. Created all tables and moved all *.MYD and *.frm files over to new
database.
3. Recreated *.MYI files via logging into MySQL and running 'REPAIR TABLE
[TABLE_NAME] USE_FRM;' for each table.

The errors would go along with what you have been told--that there is partial written or invalid data in the database. Unfortunately I have found no method to locate and cleanup/remove this invalid data. I am still looking and any additional information you locate I will look over and
attempt if viable.
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21047 is a reply to message #21041] Wed, 10 November 2004 09:10 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Ok it seems like he ran all the appropriate database repair commands. Before we assume the worst could you please tell me what is the error that the forum is now reporting?

FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21053 is a reply to message #21047] Wed, 10 November 2004 14:11 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
There appears to be missing data: images, member login info, member status info: going back several months, not just from the day the server went down.
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21054 is a reply to message #21053] Wed, 10 November 2004 15:48 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Could it be that the ISP restored the data from (old) backups?
You can also try to rebuild the theme and run the consistency checker in the forum.


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21055 is a reply to message #21022] Wed, 10 November 2004 20:10 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
When hitting the back browser button, sometimes, not always????


Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 45
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21056 is a reply to message #21055] Wed, 10 November 2004 20:11 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
We would prefer not to use the last backup as hundreds of posts relating to the election would be lost, and members cherish their trail!
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21057 is a reply to message #21056] Wed, 10 November 2004 20:15 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
The error given it's randomness says to me you are hitting maximum allowed connections to the mysql server. Are you using persistent connections (option inside the forum's database settings)?

FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21060 is a reply to message #21057] Wed, 10 November 2004 23:36 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Now our site is totally down with this error message:

(/hsphere/local/home/aag522/wf4bm.net/forum/index.php:112
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:174
/hsphere/local/home/aag522/wf4bm.net/forum/theme/default/index.php:136
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:1144
) 1030: Got error 134 from table handler
Query: SELECT count(*) FROM fud_msg m INNER JOIN fud_thread t ON m.thread_id=t.id INNER JOIN fud_forum f ON t.forum_id=f.id WHERE m.apr=0 AND (f.forum_opt>=2 AND (f.forum_opt & 2) > 0)
Server Version: 4.0.21-standard
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21061 is a reply to message #21060] Wed, 10 November 2004 23:38 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Earlier error messages when browsing or posting:

(/hsphere/local/home/aag522/wf4bm.net/forum/index.php:112
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:96
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:21
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:112
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:206
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:1047
/hsphere/local/home/aag522/wf4bm.net/forum/theme/default/post.php:1663
/hsphere/local/home/aag522/wf4bm.net/forum/theme/default/post.php:3952
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:1144
) 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
Query: UNLOCK TABLES
Server Version:
[Referring Page] http://wf4bm.net/forum/index.php?t=post&reply_to=125558&rid=4&S =676599ecf4df29aaa40c51aabc6ebfb1



Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 57

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /hsphere/local/home/aag522/wf4bm.net/forum/index.php on line 45

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21062 is a reply to message #21061] Wed, 10 November 2004 23:39 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Can we pay for some techical assistance on this?
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21063 is a reply to message #21062] Thu, 11 November 2004 00:23 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Latest report from our hosting company guy:

Hi there,

I’ve done a repair on the database as the error indicated would indicate damage of sorts. It seems to have taken care of the issues with accessing your database, but the corruption in the database still appears to be causing the MySQL process to restart after each query(which is why you have been getting the connection errors).


Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21064 is a reply to message #21063] Thu, 11 November 2004 00:35 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
After each query to your database, this is the error indicated in the MySQL logs.



Number of processes running now: 0

041111 00:10:27 mysqld restarted

041111 0:10:27 InnoDB: Started

/usr/sbin/mysqld: ready for connections.

Version: '4.0.21-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM



This would generally indicate there was an error in the database, which causes MySQL to stop and restart itself (which is why you have been getting messages that MySQL cannot be connected to, or that you lost the connection).






Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21068 is a reply to message #21064] Thu, 11 November 2004 08:33 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
If the database is corrupt like your web host is saying, unfrotunately there is not much I could do other then what your web host had already tried.

it seems like the only option now is to recover database data from some backup made either by you or your host.


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21076 is a reply to message #21068] Thu, 11 November 2004 13:39 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
I have another question, which I think is important.

Server crashes and inappropriate shutdowns happen. If the mysql database gets corrupted and has unrepairable files and this happens numerous times over a year, you end up with these unrepairable files hanging on even when you don't know they are there and after a while, collectively they can cause problems.

We had a hosting service whose servers crashed frequently and we repaired the database each time. The last time, over a year ago, it took a lot of work to salvage the databases. Now we've experienced our first improper shutdown and the unrepairable files from a year ago, plus now, are creating havoc.

Members of our forum would prefer to NOT lose any current data (posts, images) as our last back up was prior to the election and there was a lot of discussion during that time. Even going back to the last backup which is now about 3 weeks ago, won't help because that backup contained existing problems from what we went through with crashes with our other host over a year ago.

Other than daily backups, is there anything that can be done about this situation where mysql database files become so corrupted that files are unrepairable when a server shuts down suddenly?
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21077 is a reply to message #21076] Thu, 11 November 2004 15:05 Go to previous messageGo to next message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Frequent updates are always good.

Running OPTIMIZE TABLE [forum tables] (forum cosnsitency checker will do this for you) once per week or so is good preventative measure.

That said in 99% of cases premature shutdown of MySQL server will not cause a permanent corruption like the one you seem to have experienced and a simple REPAIR TABLE cannot fix.


FUDforum Core Developer
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21166 is a reply to message #21077] Wed, 17 November 2004 15:59 Go to previous messageGo to next message
creamywf is currently offline  creamywf   United States
Messages: 54
Registered: April 2002
Karma: 0
Member
add to buddy list
ignore all messages by this user
Every 3 or 4 days we continue to get this message and our forum shuts down. This despite the repair. Why would the database work fine for a few days, then go nuts and shut down with this message? Another 'repair' is done, and it is back up for another few days before shutting down again.

(/hsphere/local/home/aag522/wf4bm.net/forum/index.php:112
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:174
/hsphere/local/home/aag522/wf4bm.net/forum/theme/default/index.php:136
/hsphere/local/home/aag522/wf4bm.net/forum/index.php:1144
) 1030: Got error 134 from table handler
Query: SELECT count(*) FROM fud_msg m INNER JOIN fud_thread t ON m.thread_id=t.id INNER JOIN fud_forum f ON t.forum_id=f.id WHERE m.apr=0 AND (f.forum_opt>=2 AND (f.forum_opt & 2) > 0)
Server Version: 4.0.21-standard
Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21168 is a reply to message #21166] Wed, 17 November 2004 16:39 Go to previous message
Ilia is currently offline  Ilia   Canada
Messages: 13241
Registered: January 2002
Karma: 0
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Something causes the table to become corrupt, like a crash of the mysql client/server possible imporper shutdown etc...



FUDforum Core Developer
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Can Profile defaults for "Select Notification" and "Private Message Notification"
Next Topic: can't create categories
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Oct 16 22:02:44 EDT 2017

Total time taken to generate the page: 0.01050 seconds