MySQL problem fixed, table corruprtion and repair ?

[eva2000]

Okay thanks to Chris for helping me find what possible was my problem... rackspace installed a standard binary mysql for intel cpus on my AMD K6/2 500.

Chris recompiled a correct version for me and well now my databases are in a different location and am using MySQL 3.23.27 now.

this was done after my server crashed.

soon after i recompiling I opened up the forums and got a

Database error in vBulletin Control Panel: Invalid SQL: SELECT template FROM template WHERE title='options'
mysql error: Got error 127 from table handler
mysql error number: 1030


before i used

/usr/bin/isamchk /var/lib/mysql/forum/*.ISM
/usr/bin/isamchk -r /var/lib/mysql/forum/*.ISM

to check and repair this

but now I am using 3.23.27 and the locations have changed.

so i ran

/usr/local/bin/myisamchk /usr/local/var/mysql/forum/*.MYI

to check and there was corruption on templates and sessions table.

so i ran a repair on template.MYI and session.MYI

/usr/local/bin/myisamchk -r /usr/local/var/mysql/forum/template.MYI
/usr/local/bin/myisamchk -r /usr/local/var/mysql/forum/session.MYI

and rechecked and it's fixed (i think ?)

i could of ran this line as well right ?

/usr/local/bin/myisamchk -r /usr/local/var/mysql/forum/*.MYI



I read the mysql.com documentation on repairs at http://www.mysql.com/documentation/mysql/commented/manual.php?section=Repair and it said

If you are using MySQL 3.23.16 and above you can (and should) use the CHECK and REPAIR commands to check and repair MyISAM tables. See section 7.12 CHECK TABLE Syntax - http://www.mysql.com/documentation/mysql/commented/manual.php?section=CHECK_TABLE. See section 7.16 REPAIR TABLE syntax - http://www.mysql.com/documentation/mysql/commented/manual.php?section=REPAIR_TABLE.

have i done this correctly, or should i use check and repair commands ?

Also after recompiling the reboot of the server didn't work so i rang rackspace to get it back up... they just emailed me saying why it didn't reboot

- Summary: Server not responding after a reboot.
SUGGESTED SOLUTION:
When your server restarted it ran fsck (or file system check). fsck detected some filesystem inconsistencies. It was not able to fix all of these errors in the first pass. I ran reran fsck by hand and was able to get the remainder of the errors fixed. If you have any other problems or conserns please let us know.

could this have caused the corruption or was it the previous crash before chris helped me recompile mysql ?

[Edited by eva2000 on 11-01-2000 at 03:29 AM]

[Chris Schreiber]

Hi Eva!

Yes you should use the /usr/local/bin/myisamchk to check your tables now since I converted your databases to MyISAM format (it should more more reliable and much faster). Also, the index files are now named .MYI instead of .ISM to reflect the new format. Everything you did there is correct.

As far as the reboot goes, it sounds like the system didn't shut down properly. It might have something to do with rebooting the server via the Webmin panel, or maybe there was just something running that prevented a clean reboot. The 'fsck' command in unix is the same the chkdsk or scandisk in windows, it basically checks the file allocation table against the files on disk to make sure everything is ok. With all of the server problems you've had, I am not surprised there were some errors, and it's good RackSpace cleaned that up.

From now on, I would recommend *not* doing two things from Webmin: Shutting down or rebooting the server and shutting down or starting MySQL. Webmin is a great tool for changing config files, but when it comes to starting/shutting down services I would recommend using telnet.

To reboot the server, just:
shutdown -r now
To stop MySQL:
/etc/rc.d/init.d/mysql stop
To start MySQL:
/etc/rc.d/init.d/mysql start

Also, always do a mysql stop before trying a mysql start, even if it appears that MySQL isn't running. This is always the safest way to do it.

-Chris

[eva2000]

Thanks Chris for all the help :D

I am definitely going to do the reboots from telnet from now on :)

[Chris Schreiber]

Originally posted by eva2000
I am definitely going to do the reboots from telnet from now on :)

Great.... 9 times out of 10 Webmin will work just fine... it's just that other time you need to be worried about ;) Through telnet you know the command will run correctly and you can see any problems much easier.

-Chris

[eva2000]

well i get got this error and is it cause of mysql connection time out setting ? or can i increase the max_allowed_packet size - http://www.mysql.com/documentation/mysql/commented/manual.php?section=Gone_away ?

Database error in vBulletin: Invalid SQL: SELECT postid,post.dateline FROM post,thread WHERE post.threadid=thread.threadid AND thread.forumid=3 AND post.visible=1 ORDER BY post.dateline DESC LIMIT 1
mysql error: Lost connection to MySQL server during query
mysql error number: 2013
Date: Wednesday 01st of November 2000 08:55:36 AM
Script: /forums/showthread.php?goto=lastpost&forumid=3
Referer: http://www.animeboards.net/forums/index.php

[Edited by eva2000 on 11-01-2000 at 10:27 AM]

[Chris Schreiber]

It sounds like there was an open connection with a client (vB user) that timed-out waiting for something... I don't think there is any way the max_allowed_packet was exceeded, since this defaults to 1MB and I am sure (or would certainly hope!) that all of your querys are under that size.

Can you post your MySQL extended-status information? I am especially interested in seeing the aborted_clients and aborted_connects numbers.

-Chris

[eva2000]

yep here it is..

Aborted_clients 4
Aborted_connects 0
Bytes_received 23214360
Bytes_sent 128172865
Connections 155
Created_tmp_disk_tables 0
Created_tmp_tables 1244
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_delete 59
Handler_read_first 2
Handler_read_key 219869
Handler_read_next 838191
Handler_read_prev 0
Handler_read_rnd 113271
Handler_read_rnd_next 9384760
Handler_update 3471
Handler_write 47622
Key_blocks_used 311
Key_read_requests 502979
Key_reads 293
Key_write_requests 944
Key_writes 726
Max_used_connections 25
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 33
Open_files 48
Open_streams 0
Opened_tables 39
Questions 173298
Select_full_join 8925
Select_full_range_join 0
Select_range 584
Select_range_check 0
Select_scan 43567
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_range 1161
Sort_rows 505701
Sort_scan 5039
Threads_cached 0
Threads_connected 20
Threads_running 1
Uptime 9012


looking at my uptime, i think maybe rackspace techs were playing around with mysql ?

[Chris Schreiber]

Originally posted by eva2000
Aborted_clients 4
Aborted_connects 0
Connections 155

Basically since MySQL was started, you've had 155 client connections and 4 of those were aborted abnormally... I don't think I would be overly concerned with this, but I would keep an eye on those numbers.


Uptime 9012
looking at my uptime, i think maybe rackspace techs were playing around with mysql ?

2.5 hours? Unless you restarted MySQL a little while ago?

-Chris

[eva2000]

nope i haven't restarted mysql at all since the recompile and reboot. so probably just rackspace following up my problems from previous support tickets on the matter (alot of tickets !)

[Chris Schreiber]

Eva-

Can you post your my.cnf file? I think I remember seeing something in there last night I wanted to change but I forget what it was ;)

Thanks,
-Chris

[eva2000]

okay here it is


# The MySQL server
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=100
set-variable = join_buffer=5M
set-variable = key_buffer=20M
set-variable = record_buffer=5M
set-variable = sort_buffer=5M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = table_cache=256

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M


is that [isamchk] still valid ? now that i use 3.23.27 ?

[Chris Schreiber]

Yes I believe you can still use [isamchk] the way it is, I think myisamchk will read those values.

The thing I saw last night, and just verified, is it looks like the first section there should say [client] and not [mysql]. I am not sure where that came from but that could be a problem. Can you replace that line?

-Chris

[eva2000]

i don't know how that got there.. just cut and paste :)

so change

# The MySQL server
[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock


to

# The MySQL server
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

then

/etc/rc.d/init.d/mysql stop

then
/etc/rc.d/init.d/mysql start

?

[Chris Schreiber]

Yup, that should do it.

[eva2000]

thanks i did it but something unusual happens when i use start mysql command

/etc/rc.d/init.d/mysql start

it returns

Starting mysqld daemon with databases from /usr/local/var/mysql

but does not return to the command prompt # until i hit enter again on the blinking cursor ?

[Chris Schreiber]

Originally posted by eva2000
but does not return to the command prompt # until i hit enter again on the blinking cursor ?

That's perfectly normal.... actually it does display an immediate # prompt, but then when the MySQL server starts, it displays the "Starting daemon" message, which makes it look like you don't have a # prompt until you hit enter.

-Chris

[eva2000]

thanks... it's looking good

[thefenixboy]

The only proble i have: Upload fonts as binary.
THANKS, IS GREAT
Bye.

[thefenixboy]

Sorry...

[eva2000]

Originally posted by thefenixboy
Sorry... you had me very confused :)

[eva2000]

well chris since the recompiling has been done, the forums have been running very well :)

thanks :D

but something weird is happening.. i had another mysql lost connection - checked my mysql server uptime and it must of been restarted or something cause it showed an uptime of 38 mins ? i haven't restarted mysql since the recompiling...

so could it really be something or script restarting mysql ?

[Chris Schreiber]

Originally posted by eva2000
well chris since the recompiling has been done, the forums have been running very well :)

Glad to hear that, hopefully we found the root of your problems :)

so could it really be something or script restarting mysql ?

In your /usr/local/var/mysql directory, there should be a file called domain.net.err or something like that, can you post or email me the contents?

Thanks,
-Chris

[eva2000]

i just got 5 exact 1034 errors


Database error in vBulletin: Invalid SQL: SELECT COUNT(thread.threadid) AS threads FROM thread WHERE thread.visible=1 AND thread.forumid=27 AND lastpost >= 950874575
mysql error: Incorrect key file for table: 'thread'. Try to repair it
mysql error number: 1034
Date: Saturday 04th of November 2000 05:49:35 AM
Script: /forums/forumdisplay.php?forumid=27
Referer: http://animeboards.net/forums/index.php


and a myisamck says

Checking MyISAM file: /usr/local/var/mysql/forum/post.MYI
Data records: 79266 Deleted blocks: 0
/usr/local/bin/myisamchk: warning: 3 clients is using or hasn't closed the table
properly
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check record links
MyISAM-table '/usr/local/var/mysql/forum/post.MYI' is usable but should be fixed


on all tables ?

[Edited by eva2000 on 11-04-2000 at 07:27 AM]