[ale] Disable foreign key checks in mysql

James Taylor James.Taylor at eastcobbgroup.com
Tue Sep 25 17:07:27 EDT 2012


Any suggestions for editing a 37GB file?
-jt 
 
>>> "James Taylor" <James.Taylor at eastcobbgroup.com> 9/25/2012   04:15 PM >>> 
He did indicate in another email that he did drop the the table.
I'll pass this along and see what he has to say.
-jt
 
 
>>> Mike Harrison <cluon at geeklabs.com> 9/25/2012   03:46 PM >>> 

We do this XX times a day. Usually works great.


Is there a good chance it already exists?

I don't usually see the COLLATE= statement,
other than that, it looks OK. (and that should be ok.. but worth playing 
with?)

Usually there is a "drop table" command just above the "create table"
command, might be worth checking.

In rare cases, when transported via weird methods,
I've seen garbage in these files, especially when dealing with strange 
character sets.

What I would do:

Cut out that section of the file and try running it.

from the drop above the create to the next table's drop/create statement.











============================================================
Mike Harrison   bogon at geeklabs.com  cell: 423.605.6943

On Tue, 25 Sep 2012, James Taylor 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
>
_______________________________________________
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


If this is an unsolicited spam message, please click this link to report it: http://control.eastcobbgroup.com:49285/contents/spamreport.shtml?rptid=13494&srvid=16vl15t




_______________________________________________
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


If this is an unsolicited spam message, please click this link to report it: http://control.eastcobbgroup.com:49285/contents/spamreport.shtml?rptid=13496&srvid=16vl15t





More information about the Ale mailing list