Re: mysqldump and foreign keys
Nico Rittner wrote: Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico Hi Nico, the 1217 error means that your foreign key constraints are working, but not necessarily that the action clauses are defined properly (or at all). Anyway, either I don't get what your problem really is, or there is something wrong with 4.1.13. I've tried an on delete clause on 4.1.14 and it worked fine (also using alter table to add the foreign key). Maybe you can upgrade your server? If you want to, I can try the steps you've used to create the tables. Just post the SQL or send it to me. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico Hi Nico, not sure what is going on here. In a simple test I did, everything was fine as yo can see below. I've also created your groups table (without the references to other tables) and added and on delete clause, which was dumped fine. Could be a bug in 4.1.14 of course. Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? What do you see when you execute show create table groups? Cheers Frank mysqldump -d test child -- MySQL dump 10.9 -- -- Host: 127.0.0.1Database: test -- -- -- Server version 4.1.13 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; [snip] -- -- Table structure for table `child` -- DROP TABLE IF EXISTS `child`; CREATE TABLE `child` ( `parent_id` int(11) NOT NULL default '0', PRIMARY KEY (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico Hi Nico, what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? The fact that show create table does not display them, implies that they might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hello, does anybody now how mysqldump handles the actions for 'on delete' and 'on update' ? In my Version 4.1.14 the action clauses are missing. When reimporting the dump, how does mysql know about the 'action' when altering the table with forein keys. Thanks, Nico Hi Nico, are you using the InnoDB storage engine for your tables? If not, the clauses will not be included in the dump, as only InnoDB supports foreign keys. When you create a non-InnoDB table which has a foreign key specification, MySQL just ignores the specification. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and foreign keys
hello, does anybody now how mysqldump handles the actions for 'on delete' and 'on update' ? In my Version 4.1.14 the action clauses are missing. When reimporting the dump, how does mysql know about the 'action' when altering the table with forein keys. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and Foreign Keys
Hello. Put SET FOREIGN_KEY_CHECKS=0 at the top of your dump file. In 4.1, mysqldump puts that automatically to the dump file. See comments at: http://dev.mysql.com/doc/mysql/en/mysqldump.html Michael Satterwhite [EMAIL PROTECTED] wrote: mysqldump backs all of the tables up in alphabetic order, including the table create statements. The problem is that if foreign keys exist on the tables, the reload will not work unless the backup file is hand edited to insure that tables depended upon are created first. Is there a way around this? If not, shouldn't the tables be created first and the foreign keys created at the end of the backup? I *REALLY don't want to hack mysqldump and am hoping that there's already a way around this - or that a way is being developed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
mysqldump and Foreign Keys
mysqldump backs all of the tables up in alphabetic order, including the table create statements. The problem is that if foreign keys exist on the tables, the reload will not work unless the backup file is hand edited to insure that tables depended upon are created first. Is there a way around this? If not, shouldn't the tables be created first and the foreign keys created at the end of the backup? I *REALLY don't want to hack mysqldump and am hoping that there's already a way around this - or that a way is being developed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and Foreign Keys
On Wednesday 02 February 2005 14:11, Michael Satterwhite might have typed: mysqldump backs all of the tables up in alphabetic order, including the table create statements. The problem is that if foreign keys exist on the tables, the reload will not work unless the backup file is hand edited to insure that tables depended upon are created first. Is there a way around this? If not, shouldn't the tables be created first and the From mysqldump that ships with 4.1: -- MySQL dump 10.8 -- -- Host: localhostDatabase: -- -- -- Server version 4.1.7 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; . . . . SET [EMAIL PROTECTED]; SET [EMAIL PROTECTED]; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: mysqldump and foreign keys (possible Bug ??)
hi, I think u r right.. because after recovering from the dumped database, when insert a value in the foreign key column which is not present in the primary key, it accepts the row inserted. which is not true when I do the same think from the mysql command prompt,( before dumping) Anybody who has experience with the same problem and is it really the true.. if it is true then what's the use of mysqldump ..( which makes someone's database scattered.) Help me also !! it is urgent .. Thanks and regards, Chetan Lavti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 9:35 PM To: Chetan Lavti Subject: Re: mysqldump and foreign keys (possible Bug ??) No, not working, When I run the command SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'; shown in the manual (7.5.4.2 Foreign Key Constraints) I will see the foreign key (in the comments collumn). But if I dump and then reload the dump into mySQL the foreign key is gone. Experts, is this a BUG Adib. Chetan Lavti wrote: hi, I am also using the Innodb table type for my database. when I am using the mysqldump for dumping the tables it really doesn't shows the foreign key in the table structure. but if after deleting my previous database I again create my database using the same dumped file and executes the mysqldesc tablename (at mysql command prompt) it shows the foreign keys.. am i right..?? Actually, I also wanted to know !! I am telling u what i have done and seen. Please, u also try for the same and see if it works fine. please, tell me also regarding this... Thanks and regards, Chetan Lavti - 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 -- WIGE DATA GmbH Wiesenring 11, D-04159 Leipzig, Germany Adib Taraben Electronic Engineer PHONE +49 (0)341 - 46 21 100 FAX +49 (0)341 - 46 21 400 E-MAIL [EMAIL PROTECTED] INTERNETwww.wige-data.de A company of WIGE MEDIA AG - 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
mysqldump and foreign keys
Hi, I just installed the MySQL max 4.0.1 and created a database with two INNODB and FOREIGN KEY tables as described in the Docs. When I run the mysqldump -a -B -c test I don't get the foreign key information. Any hint? Adib. - 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: mysqldump and foreign keys
hi, I am also using the Innodb table type for my database. when I am using the mysqldump for dumping the tables it really doesn't shows the foreign key in the table structure. but if after deleting my previous database I again create my database using the same dumped file and executes the mysqldesc tablename (at mysql command prompt) it shows the foreign keys.. am i right..?? Actually, I also wanted to know !! I am telling u what i have done and seen. Please, u also try for the same and see if it works fine. please, tell me also regarding this... Thanks and regards, Chetan Lavti - 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
