NYCPHP Meetup

[csnyder at chxo.com]

As a mysql user from way back, I don't generally hold with all this fancy
FOREIGN KEY stuff. But recently I've has occasion to work on tables that are
heavily keyed to each other, and I've run into a nasty problem when trying to
backup and restore the database using mysqldump --opt 

Despite the fact that the sql file wraps all the inserts in *!40000 ALTER TABLE
tablename DISABLE KEYS */; the inserts are failing on foreign key checks.

I'm using Mysql 4.0.15a ...

Is this expected behavior? Seems to me that if the foreign key indexes aren't
being created until after everything is inserted, the inserts should succeed no
matter what, especially as mysqldump --opt is advertised as the best/fastest
way to backup and restore a database.

   chris.

>From hans not junk at nyphp.com  Fri Jul  9 14:23:33 2004
Return-Path: <hans not junk at nyphp.com>
Received: from smtp11.intermedia.net (smtp11.intermedia.net [64.78.21.10])
	by virtu.nyphp.org (Postfix) with ESMTP id 3FF62A85F0
	for <talk at lists.nyphp.org>; Fri,  9 Jul 2004 14:23:33 -0400 (EDT)
Received: from ehost011-1.exch011.intermedia.net ([64.78.21.3]) by
	smtp11.intermedia.net with Microsoft SMTPSVC(6.0.3790.0); 
	Fri, 9 Jul 2004 11:15:04 -0700
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [nycphp-talk] mysqldump and foreign keys on InnoDB tables
Date: Fri, 9 Jul 2004 11:23:30 -0700
Message-ID: <41EE526EC2D3C74286415780D3BA9F8702F7C5E1 at ehost011-1.exch011.intermedia.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [nycphp-talk] mysqldump and foreign keys on InnoDB tables
Thread-Index: AcRlyast2+MXiiTPRoeLQhlNgZ0rGQAFsbEQ
From: "Hans Zaunere" <hans not junk at nyphp.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
X-OriginalArrivalTime: 09 Jul 2004 18:15:04.0824 (UTC)
	FILETIME=[A88EFF80:01C465E0]
X-BeenThere: talk at lists.nyphp.org
X-Mailman-Version: 2.1.4
Precedence: list
Reply-To: NYPHP Talk <talk at lists.nyphp.org>
List-Id: NYPHP Talk <talk.lists.nyphp.org>
List-Unsubscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=unsubscribe>
List-Archive: <http://lists.nyphp.org/pipermail/talk>
List-Post: <mailto:talk at lists.nyphp.org>
List-Help: <mailto:talk-request at lists.nyphp.org?subject=help>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=subscribe>
X-List-Received-Date: Fri, 09 Jul 2004 18:23:33 -0000


> As a mysql user from way back, I don't generally hold with all this
fancy
> FOREIGN KEY stuff. But recently I've has occasion to work on tables
that are
> heavily keyed to each other, and I've run into a nasty problem when
trying to
> backup and restore the database using mysqldump --opt
>=20
> Despite the fact that the sql file wraps all the inserts in *!40000
ALTER TABLE
> tablename DISABLE KEYS */; the inserts are failing on foreign key
checks.
>=20
> I'm using Mysql 4.0.15a ...

http://dev.mysql.com/doc/mysql/en/SET_OPTION.html

SET FOREIGN_KEY_CHECKS=3D0;

> Is this expected behavior? Seems to me that if the foreign key indexes
aren't
> being created until after everything is inserted, the inserts should
succeed no
> matter what, especially as mysqldump --opt is advertised as the
best/fastest
> way to backup and restore a database.

Well:

http://dev.mysql.com/doc/mysql/en/Backing_up.html

And, when working with InnoDB, see the 8th bullet (AUTOCOMMIT):

http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

LOAD DATA INFILE and SELECT INTO OUTFILE are also nice.

H