Drupal Commerce Kickstart 2 Mysql problem: "Table XYZ is marked as crashed and should be repaired" [closed]

Question

My drupal kickstart 2 site occasionally cause Mysql errors. This is what Drupal Logs say:

"PDOException: SQLSTATE[HY000]: General error: 145 Table './MYSITE/field_revision_body' is marked as crashed and should be repaired: INSERT INTO {field_revision_body} (entity_type, entity_id, revision_id, bundle, delta, language, body_value, body_summary, body_format) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => node [:db_insert_placeholder_1] => 1953 [:db_insert_placeholder_2] => 1953 [:db_insert_placeholder_3] => breaking_news [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi vehicula condimentum placerat. Curabitur vehicula pharetra felis, quis feugiat magna imperdiet non..</p> [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => formato_xxxxxx_calcio ) in field_sql_storage_field_storage_write() (linea 452 di /var/www/MYSITE.it/modules/field/modules/field_sql_storage/field_sql_storage.module)."

In this case general error was on table field_revision_body but sometimes it's another table. Hosting Service say that mysql are all ok. So what happend? This site has been online for several months, and only few days ago give me problem.

It seems that mysql dropped, then after a few minutes he recovers tables. There are specific tools for debugging to see what makes me go down the site?

Pls someone help me.

Answer 1

I have run into this problem a few times. It is indeed not directly Drupal related. However, since we are looking at MySQL through a Drupal lens, I guess we might as well answer it here.

The real source of the problem is a performance problem for your MySQL server. Drupal might have contributed to the problem by adding too much load. When I ran into a similar problem, I found out that a module I had written was making a huge amount of inserts into the access_log table, which then caused the table (and sometimes other tables) to crash. I ended up migrating to stronger hardware, which made the problem occur less frequently. Then I cleaned up my code to insert only what I really needed and the problem was gone.

I suspect, you will probably see this problem first on the tables that have the fastest growth rate. If so, take a look at which Drupal module inserts into them and find out, if you can optimize your insert strategy. If you can't, you might have to move your MySQL server to a separate machine or upgrade the hardware you are running on.