FUDforum: How To » MYSQL MESS AFTER HOSTING SERVICE CRASH

Home » FUDforum » How To » MYSQL MESS AFTER HOSTING SERVICE CRASH

Show: Today's Messages :: Polls :: Message Navigator

MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21022]

Tue, 09 November 2004 05:11

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21032 is a reply to message #21022]

Tue, 09 November 2004 13:58

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21034 is a reply to message #21032]

Tue, 09 November 2004 17:48

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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.

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21035 is a reply to message #21034]

Tue, 09 November 2004 18:16

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21036 is a reply to message #21034]

Tue, 09 November 2004 18:17

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21037 is a reply to message #21035]

Tue, 09 November 2004 18:18

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21041 is a reply to message #21037]

Wed, 10 November 2004 06:13

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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.

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21047 is a reply to message #21041]

Wed, 10 November 2004 14:10

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21053 is a reply to message #21047]

Wed, 10 November 2004 19:11

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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.

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21054 is a reply to message #21053]

Wed, 10 November 2004 20:48

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21055 is a reply to message #21022]

Thu, 11 November 2004 01:10

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21056 is a reply to message #21055]

Thu, 11 November 2004 01:11

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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!

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21057 is a reply to message #21056]

Thu, 11 November 2004 01:15

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21060 is a reply to message #21057]

Thu, 11 November 2004 04:36

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21061 is a reply to message #21060]

Thu, 11 November 2004 04:38

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21062 is a reply to message #21061]

Thu, 11 November 2004 04:39

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

Can we pay for some techical assistance on this?

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21063 is a reply to message #21062]

Thu, 11 November 2004 05:23

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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).

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21064 is a reply to message #21063]

Thu, 11 November 2004 05:35

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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).

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21068 is a reply to message #21064]

Thu, 11 November 2004 13:33

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21076 is a reply to message #21068]

Thu, 11 November 2004 18:39

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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?

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21077 is a reply to message #21076]

Thu, 11 November 2004 20:05

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21166 is a reply to message #21077]

Wed, 17 November 2004 20:59

creamywf

Messages: 54
Registered: April 2002

Karma: 0

Member

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

Report message to a moderator

Re: MYSQL MESS AFTER HOSTING SERVICE CRASH [message #21168 is a reply to message #21166]

Wed, 17 November 2004 21:39

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Something causes the table to become corrupt, like a crash of the mysql client/server possible imporper shutdown etc...

FUDforum Core Developer

Report message to a moderator

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) ] [

]

Current Time: Tue Nov 26 02:34:10 GMT 2024

Total time taken to generate the page: 0.02625 seconds