MYSQL crashes on XAMPP while working with drupal

corruptioncrashdrupalMySQL

Good day,
just today morning i was using drupal to add some content, it crashed and mysql shutdown unexpectedly at xampp!!

last thing i was doing was inserting a data into a field with "auto complete entity reference"..but after inserting, it gave me an error in red: "no data exist in entity reference", next thing i did to fix my insertion, it got this error for mysql crashing!

i tried to restarted it many time, but each time it started, it doesn't respond to anything, then shuts down again.

sometimes i was hardly able to logon to phpmyadmin, check the database, (all looed ok to me) but before going deep, it crashed again!
(its been just less than 1 min time, and slow response before the crash!)

i tried writing any sql statment(i thought it has something to do with drupal cache, so i wanted to delete their data), but it hangs and eventually mysql shuts down again.

here's a screen shot of drupals' initial error:
drupal error

but after trying to refresh and restart mysql at xampp, i get this error:
mysql error

i looked around google and stack exchange, and found that i need to check the error log file at mysql..after long hours of determining the problem i THINK i found my problem is with a corrupt table:
(i couldnt post all lines of error reporting here cuz they're too long)

so from between the lines i found this:

InnoDB: table "bitnami_drupal7"."field_revision_field_citizenship"iis corrupted. Please drop the table and recreate.

[Warning] InnoDB: Cannot open table bitnami_drupal7/field_revision_field_citizenship from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

i was surprised that a whole service is shutdown because a simple table, how come? and why would a table be corrupted by itself? i mean i was just minding my own business with drupal contents and adding setting and thing(not even coding, nothing), how could such a thing happen?

i found some solutions, (the one with increasing the packet size didnt work btw, cuz it happened before and it solved, but now it seems different), here are some solutions which say:

(1).

[mysqld]

innodb_force_recovery = 1

or
(2).

Delete: "ibdata1" file at "C:\xampp\mysql\data"

so, the reason im asking here is that, are those methods safe? will they not affect my drupal tables?

it would be disastrous for me if anything goes wrong, because 1st solution says "FORCE" which sounds scary and could end up corrupting things based on what i read. (im not entirely sure though)

also, 2nd solution which is deleting that file (im not sure wht that file is or does), does that file contain any database stuff that affect my database files? or is it only for configuring purpose? i mean maybe some people are not having serious stuff in database so they tried it and worked for them.

but for me its very critical if anything bad happens to my data from those solution.

I'd like to know if those solution wont affect my data.

I'm pretty new to this database stuff, so im not sure..thats why im trying to take a cautious step to ask here.

Please guide me from your experiences.

Thanks

Best Answer

ok, i best solution which suited me well.

for being cautious in choosing the above solutions provided in my question above..

(cuz the solutions didn't guarantee 100% that my data would be intact), i made a backup for my drupal database folder just in case.

chose solution (2), which is adding the force_recovery..but no luck in fixing the problem..

so i went to the location of the corrupted file in "C:\xampp\mysql\data\bitnami_drupal7", and moved the corrupted file to another folder (NOTE: i did not delete the corrupted file).

then started mysql service, and viola! my drupal was back to life again.

and i was able to access phpmyadmin with sql queries.

(back then, mysql couldn't execute sql queries because it was in a state where its about to crash).

i went straight to the cache tables of drupal to clear them..

(back then, i wanted to clear drupals' cache since the error started, cuz ever since i knew that mysql crashes because of a table in drupal; but mysql couldn't have enough time to execute sql queries in phpmyadmin, again cuz of the the crash).

hence, after clearing all drupal cache at "bitnami_drupal7" with this code:

TRUNCATE TABLE cache;

TRUNCATE TABLE cache_block;

TRUNCATE TABLE cache_bootstrap;

TRUNCATE TABLE cache_field;

TRUNCATE TABLE cache_filter;

TRUNCATE TABLE cache_form;

TRUNCATE TABLE cache_image;

TRUNCATE TABLE cache_menu;

TRUNCATE TABLE cache_page;

TRUNCATE TABLE cache_path;

TRUNCATE TABLE cache_token;

TRUNCATE TABLE cache_update;

then, i replaced the corrupted file back again to try if it becomes recognized or not, and started mysql..and it worked!!

mysql never crashed again and complain about that file being corrupted anymore, and that saved me from deleting or affecting any data.

Related Question