| Author |
Message |
Dominik Klein
*nix forums beginner
Joined: 15 Mar 2006
Posts: 35
|
 Posted: Thu Jul 20, 2006 11:10 am Post subject:
How to disable foreign_key_checks when using mysqlimport?
|
|
|
Hello
When I re-insert dumped data with "mysql < file.sql", I can simply put
"set foreign_key_checks=0;" at the beginning of the file and this works
fine.
How can I achieve this when inserting a text file that is read with
mysqlimport?
I tried to put the mentioned sql-statement in the txt-file, but this
does not affect anything.
Background:
One of my developers accidently dropped a table yesterday which had to
be re-created from a dump.
I was sort of lucky as the table was in a not too large database, so I
could just open the dumpfile of that database and get the lines I needed
to recreate the table and data.
If I imagine this happened on a larger database which results in
several-GB-size dumpfiles, it would have taken MUCH longer to get that
table back.
So now I'm playing with "mysqldump --tab" which gives nice per-table
data and structure files.
So if there's any other well-known solution for per-table dumpfiles, let
me know. I'm not too keen on writing something myself right now.
Thanks for your help.
Dominik
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
 |
Chris White
*nix forums beginner
Joined: 21 Apr 2006
Posts: 43
|
| Posted: Thu Jul 20, 2006 2:46 pm Post subject:
Re: How to disable foreign_key_checks when using mysqlimport?
|
|
|
On Thursday 20 July 2006 04:10 am, Dominik Klein wrote:
| Quote: | Hello
When I re-insert dumped data with "mysql < file.sql", I can simply put
"set foreign_key_checks=0;" at the beginning of the file and this works
fine.
|
So do it that way
| Quote: | So if there's any other well-known solution for per-table dumpfiles, let
me know. I'm not too keen on writing something myself right now.
|
mysqldump -u [user] -h [host] -p [database] [table] > fixme.sql
mysql -u [user] -h [host] -p [database] < fixme.sql
and for the paranoid, you can always use the ever efficient test database to
test things first:
mysql -u [user] -h [host] -p test < fixme.sql
| Quote: | Thanks for your help.
Dominik
|
--
Chris White
PHP Programmer/DBlankRounds
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Gabriel PREDA
*nix forums addict
Joined: 04 Feb 2005
Posts: 72
|
| Posted: Thu Jul 20, 2006 7:29 pm Post subject:
Re: How to disable foreign_key_checks when using mysqlimport?
|
|
|
Try:
ALTER TABLE `tbl_name` DISABLE KEYS;
-- now insert in the TXT file
ALTER TABLE `tbl_name` ENABLE KEYS;
I think this is what you were looking for !
--
Gabriel PREDA
Senior Web Developer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Dominik Klein
*nix forums beginner
Joined: 15 Mar 2006
Posts: 35
|
| Posted: Fri Jul 21, 2006 8:01 am Post subject:
Re: How to disable foreign_key_checks when using mysqlimport?
|
|
|
Gabriel PREDA schrieb:
| Quote: | Try:
ALTER TABLE `tbl_name` DISABLE KEYS;
-- now insert in the TXT file
ALTER TABLE `tbl_name` ENABLE KEYS;
I think this is what you were looking for !
|
That would have been a possibility. I did it this way now:
....
echo "set sql_log_bin=0; set foreign_key_checks=0; use $db; load data
infile \"$txtfilename\" into table $tablename fields enclosed by '\"';"|
$MYSQL_BIN $MYSQL_CONNECT
....
Script performs several checks before this statement and puts the
txtfile in the appropriate directory.
Regards
Dominik
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Dominik Klein
*nix forums beginner
Joined: 15 Mar 2006
Posts: 35
|
| Posted: Fri Jul 21, 2006 8:03 am Post subject:
Re: How to disable foreign_key_checks when using mysqlimport?
|
|
|
| Quote: | mysqldump -u [user] -h [host] -p [database] [table] > fixme.sql
|
This is for one table.
As I need it for all my tables in all my databases, I'd have to write a
script for that.
And as --tab uses less space, I prefer --tab option for mysqldump.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
MySQL
