• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 945
  • Last Modified:

MySQL database crashing regularly with error 145 table marked as crashed and should be repaired

I have had this database running for about 3 years now and for the past few months I keep getting tables that are crashing.  They appear to be the same tables each time.  What are some causes for this to happen?  I have checked the server and it has not been shutting down or restarting other than when I install updates and restart it myself.  That has yet to cause a problem.  

At first this happened just once and awhile but now it seems to happen every week.  I have not been able to pinpoint anything going on when this happens.  Any ideas on what to look for would be greatlly appreciated.  

I have this running on a windows server 2003 box using apache server.

Also, if you know of any way I can use a script to check the database and fix it automatically that would be great since I am not always able to check on the database on the weekends.

Thanks,
David
0
grizzly_adams
Asked:
grizzly_adams
  • 4
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
How many records do you have in the database and how frequently is it written too?

I have seen this before on application for secure logging (auditing) which has a couple hundred million records and gets gets peaks of thousands of records in a very short period of time.  Occassionally will find the database crashed and have to run the REPAIR TABLE command but there is a myisamchk option that will repair as well that you could probably setup in a CRON/scheduled task.
0
 
UmeshCommented:
Tell me which types of tables crashing(MyISAM,InnoDB)?

If its MyISAM then you can add below statement in my.ini

myisam-recover=FORCE,BACKUP


Pls check this more details http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_myisam-recover


Also can you post your MySQL error log contents...???????
0
 
UmeshCommented:
perror utility reports that some tables are corrupted and need to be repaired..

$perror 145
MySQL error code 145: Table was marked as crashed and should be repaired

Stop the mysqld server with mysqladmin shutdown, run myisamchk --silent --force */*.MYI from the data directory to check all MyISAM tables, and restart mysqld. This ensures that you are running from a clean state

Also, take a look at here..

http://dev.mysql.com/doc/refman/5.0/en/crashing.html
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
grizzly_adamsAuthor Commented:


Here is what the error log contains:

081104  2:20:46 [ERROR] Error writing file 'f:/apache2triad/mysql/logs/myaccess.log' (errno: 13)
081104  9:15:33 [Warning] Aborted connection 5652 to db: 'revolve' user: 'revolve' host: 'localhost' (Got an error reading communication packets)

The table has about 3.5 million rows and gets written to constantly.  The one that crashes the most frequently get about 100k entries per day.  The table are all MyISAM.  I will try out stopping the mysql server tonight when everyone quits using it so that I can run the myisamchk command on it.

 
0
 
UmeshCommented:
perror utility reports that error no:13 is something related to permission

$perror 13
OS error code  13:  Permission denied

Pls can you check whether "f:/apache2triad/mysql/logs/myaccess.log" has proper read/write permissions??

0
 
UmeshCommented:
Did you check this?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now