#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6

    Disable foreign key checks when rebuilding database from dump?


    How do I disable foreign key checks when rebuilding a database from a dump file?

    I notice that there are commented-out lines in the dump file referring to this function. Do I just un-comment them?
    What is the proper syntax?

    As an example, here is what the dump file looks like:
    Code:
    -- --------------------------------------------------------
    -- Host:                         192.168.0.31
    -- Server version:               5.5.27 - MySQL Community Server (GPL)
    -- Server OS:                    Win32
    -- HeidiSQL version:             7.0.0.4053
    -- Date/time:                    2013-03-21 13:09:56
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET FOREIGN_KEY_CHECKS=0 */;
    -- Dumping data for table cdcol.cds: 3 rows
    /*!40000 ALTER TABLE `cds` DISABLE KEYS */;
    INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
    	('Beauty', 'Ryuichi Sakamoto', 1990, 1),
    	('Goodbye Country (Hello Nightclub)', 'Groove Armada', 2001, 4),
    	('Glee', 'Bran Van 3000', 1997, 5);
    /*!40000 ALTER TABLE `cds` ENABLE KEYS */;
    /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    Those are special comments that may be interpreted by MySQL. Docs.

    tl;dr: /*!40014 ... */ comments will be executed in MySQL >= 4.00.14.

    So you don't have to do anything.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    64
    Rep Power
    6
    Wow, thanks! It worked!

    I'm surprised because it never worked for me in the past. I have no idea why.

IMN logo majestic logo threadwatch logo seochat tools logo