We have a couple of platforms that serve the QA and DEV environments for testing our growing e-learning product that I maintain.

The tables are all InnoDB and I run nightly and weekly maintenance tasks that perform Check Tables, Defrag Tables, Flush Tables, daily backup and a few more.

Two days ago the foreign keys on two of the platforms simply vanished. Thus, the backup from the day before HAD the foreign keys while the next day did not.

Trying to run my foreign key script to build them again did not run on one of the platform and I dropped the database and recreated it from the backup that had the foreign key and now all is back to normal.

The fact that it happened though is still bugging me and reviewing Heikki Tuuri responses to some problems I saw a bug associated with the ALTER TABLE query that used to be a bug and that used to drop the FK without re-creating them.

The Defrag tables script is made of the ALTER TABLE query and I wonder whether it might have been related to the problem. Also, on the day of the FK disappearance we had serious network problems. I do not know whether script was run during the network problems and whether it has anything to do with the problem.

Until two days ago the maintenance scripts have been running perfectly well without any problem.

Will greatly appreciate any feedback especially from Heikki.

Thank you.

Noga
Interactive Constructs, Inc.
Medford, MA