[ale] Disable foreign key checks in mysql

[James Taylor]

One of my clients is trying to test a disaster recover scenario by restoring a mysql database to an offsite disaster recovery server.

He has determined he needs to disable foreign key checking. He add the following to the mysqld section of my.conf

init_connect='SET foreign_key_checks = 0'

But it didn't work for him.
He created a regular user to use for the import, which he thinks he needs to do.

Here's the output when the problem occurs.

omniprise-db1-dr:/utility # mysql -uroot -pchanged < omniprise_backup.sql
ERROR 1005 (HY000) at line 36332: Can't create table 'ERP_DATABASE.heartbeat' (errno: -1)

omniprise-db1-dr:/utility # head -n 36340 omniprise_backup.sql | tail -n 9
CREATE TABLE `heartbeat` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `ts` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `heartbeat`

omniprise-db1-dr:/utility # wc -l omniprise_backup.sql
133974 omniprise_backup.sql

Any thoughts?

Keep in mind that I'm not working directly with this and I'm not strong on mysql administration.

-jt



James Taylor
678-697-9420
james.taylor at eastcobbgroup.com