Re: Exporting/Importing Databases
David Blomstrom [EMAIL PROTECTED] wrote: --- 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? 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? 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? 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? You should put these lines to the dump file that contains CREATE TABLE statements, not to the *.frm files. Then load tables. If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
David Blomstrom [EMAIL PROTECTED] wrote: --- Egor Egorov [EMAIL PROTECTED] wrote: David Blomstrom [EMAIL PROTECTED] wrote: --- Robert A. Rosenberg [EMAIL PROTECTED] wrote: 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. You should put these lines to the dump file that contains CREATE TABLE statements, not to the *.frm files. Then load tables. If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; Does anyone on this list know... 1. Can I also open the file with Notepad and insert the statements? Yes. 2. Where's the location of this dump file? Where did you save it? 3. After I import my SQL file online, do I have to then change the file back, deleting the two statements I added? Up to you. If you plan to reuse this dump file, you can keep SET FOREIGN_KEY_CHECKS statements too. I'm leery of using the command-client, because it's unfamiliar to me, and I'm worried about making a major mistake. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
--- Egor Egorov [EMAIL PROTECTED] wrote: David Blomstrom [EMAIL PROTECTED] wrote: If you use command-line client you can do: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE file_name; mysql SET FOREIGN_KEY_CHECKS = 1; Does anyone on this list know... 1. Can I also open the file with Notepad and insert the statements? Yes. 2. Where's the location of this dump file? Where did you save it? I don't know; I didn't even know it existed! When I EXPORT a database, am I creating just ONE file or more than one? At first, I thought there was just one - the file that landed on my desktop. I inserted these two statements in that file (using Notepad), but I couldn't publish it online. Then I thought I understood that there's a SECOND file - a dump file that I'm supposed to modify. But I didn't knowingly save it to any particular location, because I wasn't even aware of its existence. During the export operation, I wasn't prompted to save a dump file to a certain location. So let me go back to sqaure one... 1. When I export a database, how many files are created? 2. If more than one file are created, including a dump file, I'm supposed to insert the referential integrity statements in the dump file, not the SQL file, right? 3. How can I determine the location of the dump file I saved? Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
Wow, I tried importing my SQL file again, with the referential integrity statements. This time I got a NEW error: Database geoblue_world - Table continents running on localhost Error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 5 STR: / SQL: br / bWarning/b: mysql_free_result(): supplied argument is not a valid MySQL result resource in bC:\xampp\phpmyadmin\libraries\export\sql.php/b on line b273/bbr / SQL-query : Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in C:\xampp\phpmyadmin\libraries\export\sql.php on line 273 MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'br / bWarning/b: mysql_free_result(): supplied argument * * * * * * * * * * I decided to try a new strategy - exporting and importing one table at a time. I exported one table, inserted the two referential integrity statements, and it worked. So I guess I'll use this strategy for the time being, but it would be nice to be able to publish an entire database, all in one fell swoop! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
At 21:54 -0700 on 06/02/2004, David Blomstrom wrote about Re: Exporting/Importing Databases: --- 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]
Exporting/Importing Databases
I just got my first MySQL database online, but I have some questions. First, if I want to import a database named, say username_jack, do I have to create a database named username_jack online first? In other words, could I access an online MySQL account that's empty, with no databases, then just import username_jack? Second, after I've published a database online, if I modify the database on my local computer, can I just republish it online on top of whatever's already online? 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? Ironically, counties comes after continents alphabetically, so it nixed all the tables that followed. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting/Importing Databases
--- 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? 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? 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? 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. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting/Importing
Hi, I need to export data from development to production. Development - Win98 Production - Unix What is the best way to transport data to fro dev prod ? mysqldump doesn't seem to work - keeps giving me errors (of course, I don't know if I'm doing it the right way, and whether that command is the solution at all). Any help will be greatly appreciated. Thanks in advance, Ravi http://BabyNamesIndia.com __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting/Importing
On Fri, Apr 13, 2001 at 01:48:26PM -0700, Ravi Jayagopal wrote: Hi, I need to export data from development to production. Development - Win98 Production - Unix What is the best way to transport data to fro dev prod ? mysqldump doesn't seem to work - keeps giving me errors (of course, I don't know if I'm doing it the right way, and whether that command is the solution at all). If you're using MySQL 3.23.xx, just copy the raw files from one place to the other. (Taking care to flush tables and whatnot.) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
