[ale] Disable foreign key checks in mysql

[David Tomaschik]

Are you sure the init_connect is in the global/mysql section of the
my.cnf?  Placing it in, say, mysqld on the client won't get the job
done.  I know this sounds obvious, but it's worth checking.
Alternatively, you can do (untested, from memory):

mysql> SET foreign_key_checks = 0;
mysql> SOURCE omniprise_backup.sql

Which will pull in omniprise_backup.sql that way.

David


On Tue, Sep 25, 2012 at 11:59 AM, James Taylor
<James.Taylor at eastcobbgroup.com> wrote:
> 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
>
>
>
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo



-- 
David Tomaschik
OpenPGP: 0x5DEA789B
http://systemoverlord.com
david at systemoverlord.com