0

On one of the site's I manage, I have a table set to MYISAM, and it constantly crashes. Automatic repair fails every time, so I have to go in and run the REPAIR TABLE command. I've looked at possible solutions such as this one: MySQL table is marked as crashed and it looks likes the best way might be to change the table to InnoDB.

However, the table has a FULLTEXT index, and if I could remove it then it would be no problem, but the site is running on a framework (specifically Elgg), so I don't know what kind of damage or problems I may have later on, so that might not be a good option.

So for my question: How can I find the cause of the crash? The site is on a web host, and running on PHP.

Community
  • 1
  • 1
kennypu
  • 5,950
  • 2
  • 22
  • 28
  • Put a trouble ticket into your hosting provider company, to find out whether their mysqld server software is crashing a lot. If it is, ask them to move your database to a different MySQL server machine. Or, fire your hosting provider and get a better one. Also, ask about this on the Elgg support forum. This shouldn't happen unless something is terribly wrong. – O. Jones Jan 12 '13 at 03:20
  • How often are your writing to the database? If you're running a huge volume of `INSERT`s or `UPDATE`s on the table you might want to start using transactions... but if you're not running hundreds of writes a minute I'm inclined to agree with Ollie Jones on this... it really shouldn't happen unless there's something wrong with MySQL. – Ben D Jan 12 '13 at 18:09
  • 1
    Is the MySQL server itself crashing? Check the uptime of the server with `SHOW GLOBAL STATUS LIKE 'Uptime'`. If the server uptime is short, it's more likely that the whole server is crashing which could be a much different solution. – jeremycole Jan 12 '13 at 22:20
  • thanks for the pointer. I would assume not because, although I'm not too sure how the web host handles it's databases, but I have many other databases/tables for different sites under the same account and all of those are fine. the only thing crashing is one specific table for one specific database/site everytime. – kennypu Jan 13 '13 at 06:25

1 Answers1

0

It may due to large number of connections on the same table then allocated in the my.cnf file.

As MyISAM locks the table and all threads remain in waiting list.

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81