Hi all,

I encountered the following problem when I'm trying to open SOME tables in MySQL database:
"Incorrect information in file ./[database name]/[table name].frm"

Other tables within the same database can be opened without any issues, only some tables have this problem. And this is not just happening in one database, but the other database on the same server also has this problem (there are 2 databases AFAIK on this server). Not sure if anyone else is using this server. The database is InnoDB, running on Win2k3. Are there any other information that is required that I didn't provide?

I have read through posts in mysql forum and it seems like none of them is able to solve my problem. The database used to work since being deployed 2 years ago and the last time the my.ini is amended is 2 years ago.

It sounded like a data corruption issue to me and the only way is to perform data restoration from the last known working state. However, is there something wrong somewhere that I can check and fix without needing to do a restoration of the database? The users are dead against it as it would mean loss of data since the date of restoration.

Thanks!

have you tried repairing the tables?

shell> mysqlcheck --repair --databases db_name ...
shell> mysqlcheck --repair --all-databases

the other thing I would try is a mysqldump and then restore.

He "can't" do backup and restore because the database is out of date.

Some things have to learned the hard way.

If you have backups running on the server, you can roll it back to some prior state. You would loose a few days of data, but that's much better than losing years of data.

He can't restore from the old backups, but sometimes doing a dump from the current state and then blowing away the table and then restoring it from the backup will work. I have seen this work before.

Obviously attempting a repair from the command line should be the first step.

How you don't have hourly/daily/whatever backups is the issue here. How the hell that happens I'll never understand.

I used to be in tech support for a company that sold to fortune 500. On a regular basis we'd get a ticket that something wasn't working and it turned out the dba was "cleaning up the database" and deleted something they shouldn't have. Of course they wouldn't do a backup before this, and oh, we haven't backed up for a year.

I'd probably try to do the backup-restore, but if this is happening on more than one database, there may be something going on at the OS level.

The nice thing about MySQL is that a restore auto-magically does upsert restore, so even if he has to roll back to a prior day, he can still run the restore from the current database and get most of the information back (assuming there are no FKs that is going to cause an explosion).

I don't understand the DBA you were working with. I once read that a DBA has three responsibilities, in order of importance: protect the data, protect the data, protect the data. Seems like your DBA wasn't doing their job.

Shutting Down and Restoring a Database
mysqladmin --defaults-file=/usr/local/mysql/my.cnf --user=root --password shutdown
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \
--backup-dir=/export/backups/full \
copy-back
Note
The restored data includes the backup history table, where MySQL Enterprise Backup records details of each backup. Restoring this table to its earlier state removes information about any subsequent backups that you did. This is the correct starting point for future incremental backups, particularly those using the --incremental-base option.
Important
Before restoring a partial backup, you might need to delete first from the backup the .frm files associated with InnoDB tables that were not backed up.

Read and learn following resource where the same issue was solved…
https://www.repairtoolbox.com/mysqlrepair.html Repair Toolbox for MySQL

Both solutions are difference and seems to be effective, I'll try them, thanks man!