--- "Robert A. Rosenberg" <[EMAIL PROTECTED]> wrote:At 20:36 -0700 on 06/02/2004, David Blomstrom wrote about Exporting/Importing Databases:
>Third, my practice database includes half a dozen >tables, but only one made it online, followed by this >error message: > >Database USERNAME running on localhost > >Error > >SQL-query : > >CREATE TABLE `counties` ( >`SCode` varchar( 6 ) NOT NULL default '', >`NameC` varchar( 255 ) default NULL , >`TypeC` varchar( 255 ) default NULL , >`Seat` varchar( 255 ) default NULL , >`Area_MI` decimal( 10, 1 ) default NULL , >`Area_KM` decimal( 10, 1 ) default NULL , >`Pop_2000` int( 10 ) default NULL , >`Pop_1990` int( 10 ) default NULL , >`Pop_MI` decimal( 10, 1 ) default NULL , >`Pop_KM` decimal( 10, 1 ) default NULL , >`Race1` int( 10 ) default NULL , >`Race2` int( 10 ) default NULL , >`Amerindian` int( 10 ) default NULL , >`White` int( 10 ) default NULL , >`Black` int( 10 ) default NULL , >`Asian` int( 10 ) default NULL , >`Pacific_Island` int( 6 ) default NULL , >`Some_Other_Race` int( 10 ) default NULL , >`Hispanic` int( 10 ) default NULL , >`id` int( 6 ) NOT NULL AUTO_INCREMENT , >PRIMARY KEY ( `id` ) , >KEY `SCode` ( `SCode` ) , >CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES >`statesarticles` ( `SCode` ) ON UPDATE CASCADE >) TYPE = InnoDB AUTO_INCREMENT =3143 > >MySQL said: >#1005 - Can't create table './[USERNAME]/counties.frm' >(errno: 150) > >Do you know what this means?
You just got caught with the old Referential Integrity Gotcha. It will not allow you to create countries since statesarticles does not (yet) exist.
The fix is to temporally turn off RI during the load. Put this command at the start of your file:
SET FOREIGN_KEY_CHECKS = 0;
and this one at the end:
SET FOREIGN_KEY_CHECKS = 1;
and it will work.
OK, is this something I can do in phpMyAdmin or another software program, or do I have to open the MySQL file itself?
Yes you need to edit the dumped file.
BTW: As of 4.1, mysqldump will automatically add these statements. I am thinking of filing a bug report against PHPMyadmin to add the statements.
I've scarcely touched MySQL files, but I found the file counties.frm, which I assume I would open with Notepad, right?
And then I can make SET FOREIGN_KEY_CHECKS = 0; the very first line and SET FOREIGN_KEY_CHECKS = 1; the very last line, after which I would EXPORT my database, then import it, right?
They do into the EXPORTED file after you create it.
And since I like to plan ahead, is it OK to insert these two lines in ALL my MySQL documents, just to be prepared for this error? If I inadvertently stick these lines in a file that doesn't have a foreign key, will it cause a problem?
No it will not cause a problem. You are just turning off the check for RI (just like the "If Exits" clause on the DROP TABLE command turns off the check to see if there is a table to delete before doing the CREATE TABLE).
Finally, after I've imported my database online, do I have to go back and remove these two lines, or can I just leave them there indefinitely as a safeguard?
Thanks.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
