Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Windows Server 2008 R2 x64
MySQL Server 5.6.14 x64

Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by choice) and a half a dozen Linux servers running MySQL. For some reason, the tables on the Windows servers get marked "crashed" every so often. We never have these problems on our Linux (Ubuntu) servers.

2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\ffcont' is marked as crashed and should be repaired
2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\bldr' is marked as crashed and should be repaired
2014-01-23 10:11:43 1868 [ERROR] MySQL: Table '.\elite_prod\frtfwd' is marked as crashed and should be repaired
2014-01-23 10:11:44 1868 [ERROR] MySQL: Table '.\elite_prod\histry' is marked as crashed and should be repaired

It seems we have to deal with this issue every couple of weeks or so. Very frustrating.

share|improve this question
    
Maybe free space issues on disk or partition? –  Mihai Jan 23 at 17:17
    
Antivirus messing with the files? –  ypercube Jan 23 at 17:24

2 Answers 2

up vote 2 down vote accepted

From the error message I can quickly tell the following:

  • The database is elite_prod
  • ffcount,bldr,frtfwd, and histry are all MyISAM tables

Because you are using the MyISAM Storage Engine, you have two major handicaps:

HANDICAP #1

Changes to MyISAM are cached differently (regardless of the OS)

HANDICAP #2

Microsoft Windows is terrible at caching disk changes. Even running FLUSH TABLES; in MySQL in a Windows environment is not a panacea. IMHO anyone using PostgreSQL or Oracle should be able to voice this same complaint about Windows. I will leave it to the SQL Server gurus to answer how they find Windows caching in terms of SQL Server.

ANALYSIS

MyISAM maintains a count of open file handles against the tables.

If the mysqld process or the Windows Server crashes, every MyISAM that had open file handles will retain the open file handle count internally.

When you access a MyISAM table for the first time since mysqld was started, it should have a zero file handle. Otherwise, you get that error message marked as crashed and should be repaired. This explains the tables periodically coming up as crashed crashed.

See my post MyISAM table keeps crashing. What are my options?

SUGGESTIONS

You could switch those tables to InnoDB and let the InnoDB Buffer cache everything, or at least better caching. I would still worry about Windows in this respect because the option innodb_flush_method is disabled in the Windows version of MySQL. I say disabled because the MySQL Documentation says:

Controls the system calls used to flush data to the InnoDB data files and log files, which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

If you want leave the tables as MyISAM, please go back to Ubuntu because it is a bit more diligent when it comes to flushing disk changes, especially when it has enough RAM. Even a terabyte of RAM will not do a thing for MyISAM tables in Windows.

share|improve this answer
    
Thank you! This explains a lot. I will review your other articles. This database is READ-ONLY is updated by another process (a dump from a mainframe). The tables are not being changed by the application. Now I understand that it doesn't matter if it's read-only, its the fact that the table is open. –  Van Jan 23 at 18:14

why don't you repair them

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

And after if error continues then troubleshoot !

  • Make sure nothing is "touching" the data directory externally, such as virus checkers
  • Shutdown MySQL properly
  • Use --myisam-recover option to automatically check/repair your tables in the event that shutdown wasn't done properly
  • Or you can convert to all myisam tables to InnoDB(as InnoDB has a less chance of corruption)
share|improve this answer
    
I think the Op is asking why this happens (or how to find out why.) Not how to repair them. –  ypercube Jan 23 at 17:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.