[darkfreak]

Hi!

My problem is the following:

At my work I use MySQL version 3.23 to store information of botanical specimen. Yesterday there happened a little disaster and we lost the data of approximately 1700 specimen because of the following.

I ran the flush-command in a different database (same server) during the development-process of another database which I am mainly working on. Everything else went fine but the Plants database didn't worked after that anymore and instead MySQL complained about corrupted table. I learned that this table in the Plants database was changed some time ago, when two new fields were added to it. If I have understood right, the changer didn't commit the flush-command neither before nor after changing the table. My guess is that some cache file got confused/corrupted at this point including data for both the old and new, changed table.

What is strange is that the database didn't show any signs of malfunction before that flush-command and it appeared that data was indeed added to the actual database. Also, in addition to data, also the two columns were missing from the table. After adding these back, the system worked fine but all the data typed in after the original table change is missing.

Everything (under the "/" in Linux server) is backed up on tape and theoretically I should be able to recover to the exact state of two days before the flush-command. In practice, however, this isn't the case, and even my personal backups that are more than 3 weeks old include the same corrupted MYD-file of that database (the size is something like 10k while it should be more than 1M).

I guess there isn't too much to do about the problem. However, I would like to know IF THERE ARE SOME OTHER FILES THAT MYSQL USES FOR SAVING/CACHING IN ADDITION TO MYI, FRM AND MYD FILES? If I would be able to restore these from the tape, could there be a slight chance of recovery of the system to the state before the flush-command? I have searched the system tmp-directory and database directories from the backup but I haven't found anything useful yet.

Please tell me if you have had any similiar problems and have recovered from them. Or if you think you have any information that could help me. Or if you know any other files MySQL uses for caching. Please DON'T tell me to upgrade to MySQL 4.x because after this it is already on our high-priority list

Thanks in advance and sorry if my english isn't perfect,

Yours,

darkfreak

[Worm]

Originally Posted by darkfreak

Hi!

My problem is the following:

At my work I use MySQL version 3.23 to store information of botanical specimen. Yesterday there happened a little disaster and we lost the data of approximately 1700 specimen because of the following.

I ran the flush-command in a different database (same server) during the development-process of another database which I am mainly working on. Everything else went fine but the Plants database didn't worked after that anymore and instead MySQL complained about corrupted table. I learned that this table in the Plants database was changed some time ago, when two new fields were added to it. If I have understood right, the changer didn't commit the flush-command neither before nor after changing the table. My guess is that some cache file got confused/corrupted at this point including data for both the old and new, changed table.

What is strange is that the database didn't show any signs of malfunction before that flush-command and it appeared that data was indeed added to the actual database. Also, in addition to data, also the two columns were missing from the table. After adding these back, the system worked fine but all the data typed in after the original table change is missing.

Everything (under the "/" in Linux server) is backed up on tape and theoretically I should be able to recover to the exact state of two days before the flush-command. In practice, however, this isn't the case, and even my personal backups that are more than 3 weeks old include the same corrupted MYD-file of that database (the size is something like 10k while it should be more than 1M).

I guess there isn't too much to do about the problem. However, I would like to know IF THERE ARE SOME OTHER FILES THAT MYSQL USES FOR SAVING/CACHING IN ADDITION TO MYI, FRM AND MYD FILES? If I would be able to restore these from the tape, could there be a slight chance of recovery of the system to the state before the flush-command? I have searched the system tmp-directory and database directories from the backup but I haven't found anything useful yet.

Please tell me if you have had any similiar problems and have recovered from them. Or if you think you have any information that could help me. Or if you know any other files MySQL uses for caching. Please DON'T tell me to upgrade to MySQL 4.x because after this it is already on our high-priority list

Thanks in advance and sorry if my english isn't perfect,

Yours,

darkfreak

Have you tried this:

http://www-fr.mysql.com/doc/de/REPAIR_TABLE.html

?

[darkfreak]

Originally Posted by Worm

Have you tried this:

http://www-fr.mysql.com/doc/de/REPAIR_TABLE.html

?

Sure! It complains that there should be 1700+ rows in the table but it can find only about 20 - and that's the number of the rows there is left after the repair. These are the rows that were added before the table was altered and not flushed.

[Worm]

Originally Posted by darkfreak

Sure! It complains that there should be 1700+ rows in the table but it can find only about 20 - and that's the number of the rows there is left after the repair. These are the rows that were added before the table was altered and not flushed.

Well, actually I'm not sure if mysql caches the data. I've never heard about this till now, in my opinion there have always been just logs or something. So good luck anyways

[darkfreak]

Originally Posted by Worm

Well, actually I'm not sure if mysql caches the data. I've never heard about this till now, in my opinion there have always been just logs or something. So good luck anyways

From MySQL manual:

"...
FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...
You should use the FLUSH statement if you want to clear some of the internal caches MySQL uses.
..."

In addition to index fiels, it seems there has to be a cache-file somewhere, because the db returned perfect results to queries before the FLUSH-command, altough the now missing data wasn't actually written neither to the MYI nor MYD file (as the file sizes - both current and backup - indicate).

Far as I have understood, these caches are written to actual files with the FLUSH command and/or when the server is shut down or restarted. Because of this, the user should not have to bother about this - UNLESS any table is to be aletered. In this case I hereby recommend to FLUSH before AND after the ALTER TABLE-command.

I guess the guy who altered the table didn't know exactly what he was doing, but I also think that this is a serious design error / bug in the MySQL (3.23.49) because the user can't be sure if the data is actually written to the db or left "on-the-air" unless (s)he constantly checks the sizes of the db-files. The FLUSH should be done automatically by the server in any case when needed. I haven't checked yet how this is done in later versions.

Thanks for answering and "listening"