Re: weird innodb foreign key feature
I see,
Thanks for information, yes correct this NO ACTION is confusing me.
BTW, congratulation for your new email @oracle.com :D
Heikki Tuuri wrote:
Ady,
NO ACTION actually means the same as RESTRICT. That is, the foreign
key constraint is still enforced. The name 'NO ACTION' is from the SQL
standard. I agree that the name is confusing, but I cannot help it,
because it is in the standard.
Regards,
Heikki
Oracle/Innobase
- Original Message - From: Ady Wicaksono
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL default '',
`keycode_isactive` enum('Y','N') NOT NULL default 'N',
`keycode_tarif` int(11) NOT NULL default '2000',
PRIMARY KEY (`keycode_id`),
UNIQUE KEY `keycode_val` (`keycode_val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and my second table
CREATE TABLE `t_push_member_unsub` (
`push_member_id` int(11) NOT NULL auto_increment,
`push_keycode` varchar(15) NOT NULL default '',
`push_msisdn` varchar(16) NOT NULL default '',
`push_subscribe_at` datetime NOT NULL default '-00-00 00:00:00',
`push_unsubscribe_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`push_pending` enum('TRUE','FALSE') default 'FALSE',
`push_operator` varchar(30) NOT NULL default '',
`push_unsubscribe_reason` text NOT NULL,
PRIMARY KEY (`push_member_id`),
KEY `t_push_member_unsub_ibfk_1` (`push_keycode`),
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
when i delete rows on t_keycode EVEN if there's foreign key
(push_keycode) that refer to this row i hope
in t_push_member_unsub will do nothing, let it happened.
DELETE from t_keycode WHERE keycode_val='TEST'
Foreign key constraint fails for table `t_push_member_unsub`:
,
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
Trying to delete in parent table, in index `keycode_val` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8090; asc ;;
But in child table `t_push_member_unsub`, in index
`t_push_member_unsub_ibfk_1`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8000cb50; ascP;;
Any information?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL default '',
`keycode_isactive` enum('Y','N') NOT NULL default 'N',
`keycode_tarif` int(11) NOT NULL default '2000',
PRIMARY KEY (`keycode_id`),
UNIQUE KEY `keycode_val` (`keycode_val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and my second table
CREATE TABLE `t_push_member_unsub` (
`push_member_id` int(11) NOT NULL auto_increment,
`push_keycode` varchar(15) NOT NULL default '',
`push_msisdn` varchar(16) NOT NULL default '',
`push_subscribe_at` datetime NOT NULL default '-00-00 00:00:00',
`push_unsubscribe_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`push_pending` enum('TRUE','FALSE') default 'FALSE',
`push_operator` varchar(30) NOT NULL default '',
`push_unsubscribe_reason` text NOT NULL,
PRIMARY KEY (`push_member_id`),
KEY `t_push_member_unsub_ibfk_1` (`push_keycode`),
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
when i delete rows on t_keycode EVEN if there's foreign key
(push_keycode) that refer to this row i hope
in t_push_member_unsub will do nothing, let it happened.
DELETE from t_keycode WHERE keycode_val='TEST'
Foreign key constraint fails for table `t_push_member_unsub`:
,
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE CASCADE
Trying to delete in parent table, in index `keycode_val` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8090; asc ;;
But in child table `t_push_member_unsub`, in index
`t_push_member_unsub_ibfk_1`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8000cb50; ascP;;
Any information?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird innodb foreign key feature
Ady,
NO ACTION actually means the same as RESTRICT. That is, the foreign key
constraint is still enforced. The name 'NO ACTION' is from the SQL standard.
I agree that the name is confusing, but I cannot help it, because it is in
the standard.
Regards,
Heikki
Oracle/Innobase
- Original Message -
From: Ady Wicaksono [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL default '',
`keycode_isactive` enum('Y','N') NOT NULL default 'N',
`keycode_tarif` int(11) NOT NULL default '2000',
PRIMARY KEY (`keycode_id`),
UNIQUE KEY `keycode_val` (`keycode_val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and my second table
CREATE TABLE `t_push_member_unsub` (
`push_member_id` int(11) NOT NULL auto_increment,
`push_keycode` varchar(15) NOT NULL default '',
`push_msisdn` varchar(16) NOT NULL default '',
`push_subscribe_at` datetime NOT NULL default '-00-00 00:00:00',
`push_unsubscribe_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`push_pending` enum('TRUE','FALSE') default 'FALSE',
`push_operator` varchar(30) NOT NULL default '',
`push_unsubscribe_reason` text NOT NULL,
PRIMARY KEY (`push_member_id`),
KEY `t_push_member_unsub_ibfk_1` (`push_keycode`),
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
when i delete rows on t_keycode EVEN if there's foreign key
(push_keycode) that refer to this row i hope
in t_push_member_unsub will do nothing, let it happened.
DELETE from t_keycode WHERE keycode_val='TEST'
Foreign key constraint fails for table `t_push_member_unsub`:
,
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
Trying to delete in parent table, in index `keycode_val` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8090; asc ;;
But in child table `t_push_member_unsub`, in index
`t_push_member_unsub_ibfk_1`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8000cb50; ascP;;
Any information?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change a column type and innodb foreign key constraints
Hi, I have a column 'id' within a table : CREATE TABLE `reference` ( *`*id*`* smallint(5) unsigned NOT NULL auto_increment, `study_name` text, `author` text NOT NULL, `date` date NOT NULL default '-00-00', `reference` varchar(250) NOT NULL default '', `title` varchar(250) NOT NULL default '', `pmid` int(15) default NULL, `project` varchar(35) default NULL, `abstract` text, `datasource` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB that i need to change from smallint to int There are a number of foreign key constraints from other columns in other tables on this column: eg CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `num_peds` int(7) unsigned default NULL, `affected` int(7) unsigned default NULL, `unaffected` int(7) unsigned default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_178` FOREIGN KEY (`gene_ID`) REFERENCES `gene` (`id`), CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE ) TYPE=InnoDB Upon trying to modify the id columns in the reference table, i'm getting the following mysql alter table reference modify id int; ERROR 1025: Error on rename of './nugenob/#sql-2ed1_e001' to './nugenob/reference' (errno: 150) mysql It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? cheers Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
Gabriel PREDA [EMAIL PROTECTED] wrote on 03/16/2005 06:12:14 AM: It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro Yes, Gabriel is right. You do not have to remove ALL of your foreign constraints, only those that include the column you need to change. Imagine the situation that would occur if you had been able to successfully change the definition of the ID column and you didn't un-define your foreign keys or change any of your other referencing columns. You would have had a relationship that was trying to enforce equality between an int on one side and a smallint on the other. Clearly that would be an illegal FK constraint, right? That's why you were not permitted to change the column as it would have made your existing foreign keys illegal. Follow Gabriel's advice, drop the FKs that reference the column you want to change, modify the fields on BOTH ends of your FKs to have matching datatypes, then re-establish your FKs. It may be a little work but that's just part of the job. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
InnoDB foreign key constraints
Hello, Are there any plans to implement foreign keys with deferred integrity constraint checking in the InnoDB storage engine in a future release of the MySQL Server? Many thanks, - glenn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
Tucker, Gabriel [EMAIL PROTECTED] wrote: Victoria=20 That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot = during the restore: ERROR 1114 at line 83 in file: = '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' = is full What can I do here? What is the type of 'cur_reject_tk_sum' table? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
InnoDB - Foreign Key - Error 150.
Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Hi, Are you creating them in the correct order ? object_type must exist before you can create cur_reject_tk_sum otherwise the foreign key will give errors. Marvin -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 14:46 To: Mysql General (E-mail) Subject: InnoDB - Foreign Key - Error 150. Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Marvin I believe that is the problem with the restore. When I create the archive file using the mysqldump command and options previously listed, I get the create table in the order listed below and thus, the foreign key constraint is created on table cur_reject_tk_sum before the object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am using mysqldump? [see commands below] Or, is this a problem with how I am restoring the database? [Which, I create a default mysql database on its own port and then run from the prompt mysql --port= --socket= -p archive_file.sql Thanks again Gabe -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 9:59 AM To: Tucker, Gabriel; Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Hi, Are you creating them in the correct order ? object_type must exist before you can create cur_reject_tk_sum otherwise the foreign key will give errors. Marvin -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 14:46 To: Mysql General (E-mail) Subject: InnoDB - Foreign Key - Error 150. Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List
RE: InnoDB - Foreign Key - Error 150.
Hi, I don't think mysqldump takes foreign key constraints into account when dumping them. You could specify the tables that you want when you dump so you get the correct order. e.g. mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 --master-data DB_NAME object_type cur_reject_tk_sum -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I dont know your database name do you would have to substitute that. this should create the dump in the correct order for your restore. Marvin. -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 15:13 To: Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Marvin I believe that is the problem with the restore. When I create the archive file using the mysqldump command and options previously listed, I get the create table in the order listed below and thus, the foreign key constraint is created on table cur_reject_tk_sum before the object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am using mysqldump? [see commands below] Or, is this a problem with how I am restoring the database? [Which, I create a default mysql database on its own port and then run from the prompt mysql --port= --socket= -p archive_file.sql Thanks again Gabe -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 9:59 AM To: Tucker, Gabriel; Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Hi, Are you creating them in the correct order ? object_type must exist before you can create cur_reject_tk_sum otherwise the foreign key will give errors. Marvin -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 14:46 To: Mysql General (E-mail) Subject: InnoDB - Foreign Key - Error 150. Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED
Re: InnoDB - Foreign Key - Error 150.
On Tue, 4 May 2004 09:46:27 -0400 Tucker, Gabriel [EMAIL PROTECTED] wrote: Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) Try looking over the output of SHOW INNODB STATUS; there might be some helpful messages in there. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
Hi Guy's, first: Your are correct it is contraints: bash-2.05a$ perror 150 Error code 150: Unknown error: 150 150 = Foreign key constraint is incorrectly formed Second you could also try: set foreign_key_check=0; at the beginning of the restore file. set foreign_key_check=1; at the end! Best of luck, Ken - Original Message - From: Marvin Wright [EMAIL PROTECTED] To: Tucker, Gabriel [EMAIL PROTECTED]; Mysql General (E-mail) [EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:22 AM Subject: RE: InnoDB - Foreign Key - Error 150. Hi, I don't think mysqldump takes foreign key constraints into account when dumping them. You could specify the tables that you want when you dump so you get the correct order. e.g. mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys -- opt --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 --master-data DB_NAME object_type cur_reject_tk_sum -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I dont know your database name do you would have to substitute that. this should create the dump in the correct order for your restore. Marvin. -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 15:13 To: Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Marvin I believe that is the problem with the restore. When I create the archive file using the mysqldump command and options previously listed, I get the create table in the order listed below and thus, the foreign key constraint is created on table cur_reject_tk_sum before the object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am using mysqldump? [see commands below] Or, is this a problem with how I am restoring the database? [Which, I create a default mysql database on its own port and then run from the prompt mysql --port= --socket= -p archive_file.sql Thanks again Gabe -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 9:59 AM To: Tucker, Gabriel; Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Hi, Are you creating them in the correct order ? object_type must exist before you can create cur_reject_tk_sum otherwise the foreign key will give errors. Marvin -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 14:46 To: Mysql General (E-mail) Subject: InnoDB - Foreign Key - Error 150. Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd
Re: InnoDB - Foreign Key - Error 150.
Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; 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/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: InnoDB - Foreign Key - Error 150.
Luciano I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the load data infile command. I am not using this command to restore the database. The mysqldump command creates a file with the data and schema. I restore it to a new instance that just has the mysql database using: unix$ mysql --port=port --socket=socket -p archive.sql So, I am not sure where I would insert this line nor if it would work. Should I insert it in the archive.sql from the previous example? Can I use the load data infile to restore the file I generated? Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem. Do you know why it does not? I know I asked a bunch of questions, thanks for whatever you can offer! Gabe -Original Message- From: Luciano Barcaro [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:03 AM To: Tucker, Gabriel Subject: Re: InnoDB - Foreign Key - Error 150. Put in your script: set foreign_key_checks=0; in the first line. mysqldump dumps tables in alphabetical order. Tucker, Gabriel wrote: Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Oooops - I sent that last email before I read this one, please disregard. This appears that it will solve my problem. I will give it a try. Thanks for all that replied! Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; 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/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
On Tue, 4 May 2004 11:01:59 -0400 Tucker, Gabriel [EMAIL PROTECTED] wrote: Luciano I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the load data infile command. Actually, I think that it is used for all operations on the DBMS, not just load data infile, INSERTS, UPDATES, DELETES as well. This is usually what I do when I have a dump created with mysqldump and need to re-create the tables. What would be nicer is if mysqldump had some smarts to dump things in the right order so this would not be an issue. Or course, the logic to do that would be kind of complex, always having to dump the weakest tables (those with foreign keys) first. :) I am not using this command to restore the database. The mysqldump command creates a file with the data and schema. I restore it to a new instance that just has the mysql database using: unix$ mysql --port=port --socket=socket -p archive.sql So, I am not sure where I would insert this line nor if it would work. Should I insert it in the archive.sql from the previous example? Can I use the load data infile to restore the file I generated? Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem. Do you know why it does not? This disables the keys during each INSERT block, but once the INSERT's are done, then it tries to enable the keys, so you will still get problems. --disable-keys is meant more as an optimization. The recommendation to set foreign_key_checks=0 at the start of the dump file, then set foreign_key_checks=1 at the end of the dump file should work just fine, has for me anyway. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Victoria That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot during the restore: ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' is full What can I do here? Thanks - Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; 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/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Hooray! The last problem was b/c I did not have the same InnoDB settings in my cnf file. Again, thank you all for your time in this matter! Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 11:29 AM To: Victoria Reznichenko; [EMAIL PROTECTED] Subject: RE: InnoDB - Foreign Key - Error 150. Victoria That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot during the restore: ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' is full What can I do here? Thanks - Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; 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/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
Gabriel, First of all, sorry for my poor english (I´m just a brazilian guy - eu quase não sei falar o portugues, imaginem o ingles então.) Tucker, Gabriel wrote: Luciano I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the load data infile command. I am not using this command to restore the database. The mysqldump command creates a file with the data and schema. I restore it to a new instance that just has the mysql database using: unix$ mysql --port=port --socket=socket -p archive.sql The set foreign_key_checks=0 disables the referential integrity (for just one session only). So, I am not sure where I would insert this line nor if it would work. Should I insert it in the archive.sql from the previous example? Yes, you should insert in the beginning of the file, OR you can do this: mysql --port=port --socket=socket -p set foreign_key_checks=0; - Disables integrity \. archive.sql - Execute the script exit- quits the client Can I use the load data infile to restore the file I generated? As far as I know, no, you can´t. Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem. Do you know why it does not? No, the foreign key error is generated because mysqldump dumps table in a different order (alphabetical) that it should. I know I asked a bunch of questions, thanks for whatever you can offer! Gabe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb foreign key
Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb foreign key
One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -Original Message- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb foreign key
whoops, yes, thanks, missed that Victor Pendleton wrote: One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -Original Message- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Foreign Key Constraint Issue
David, - Original Message - From: David Griffiths [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 19, 2003 4:07 AM Subject: InnoDB Foreign Key Constraint Issue The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id); ... mysql select distinct address_type_id from address_list; +-+ | address_type_id | +-+ | 100 | | 101 | | 102 | | 104 | | 105 | +-+ And here are all the address_type.address_type_id values: mysql select address_type_id from address_type; +-+ | address_type_id | +-+ | 101 | | 102 | | 103 | | 104 | | 105 | +-+ As you can see, there are no null or 0 address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. as we can see, there is a value 100 in address_list which does not appear in address_type :). ... MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 8064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; InnoDB sets the highest bit in positive integers. Above we have a positive integer 0x64 == 100 in decimal. But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex The closest match is 0x65 == 101 in decimal. ac16; asc ..;; 2: len 7; hex 80002d0084; asc -..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue David. P.S. Since many people are waiting for multiple tablespaces, I am posting the status here: I have now got also crash recovery working with multiple tablespaces. I spent this day tracking a memory corruption bug, which turned out to be an unfreed semaphore when I drop a tablespace. There is still a simple bug that all secondary indexes get created in the system tablespace, but that should be easy to fix. ALTER TABLE fails in an error 030919 3:40:22 InnoDB: Error creating file ./test/#sql-15f_3.ibd. 030919 3:40:22 InnoDB: Operating system error number 17 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 17 means 'File exists'. ALTER TABLE apparently does not work because RENAME TABLE does not work yet with .ibd files, they do not get renamed. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Foreign Key Constraint Issue
I can't believe I missed that. The insert statement is in the script - not sure why it wasn't added - not sure why I missed something so obvious. David. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:50 PM Subject: Re: InnoDB Foreign Key Constraint Issue David, - Original Message - From: David Griffiths [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 19, 2003 4:07 AM Subject: InnoDB Foreign Key Constraint Issue The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id); ... mysql select distinct address_type_id from address_list; +-+ | address_type_id | +-+ | 100 | | 101 | | 102 | | 104 | | 105 | +-+ And here are all the address_type.address_type_id values: mysql select address_type_id from address_type; +-+ | address_type_id | +-+ | 101 | | 102 | | 103 | | 104 | | 105 | +-+ As you can see, there are no null or 0 address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. as we can see, there is a value 100 in address_list which does not appear in address_type :). ... MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 8064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; InnoDB sets the highest bit in positive integers. Above we have a positive integer 0x64 == 100 in decimal. But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex The closest match is 0x65 == 101 in decimal. ac16; asc ..;; 2: len 7; hex 80002d0084; asc -..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue David. P.S. Since many people are waiting for multiple tablespaces, I am posting the status here: I have now got also crash recovery working with multiple tablespaces. I spent this day tracking a memory corruption bug, which turned out to be an unfreed semaphore when I drop a tablespace. There is still a simple bug that all secondary indexes get created in the system tablespace, but that should be easy to fix. ALTER TABLE fails in an error 030919 3:40:22 InnoDB: Error creating file ./test/#sql-15f_3.ibd. 030919 3:40:22 InnoDB: Operating system error number 17 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 17 means 'File exists'. ALTER TABLE apparently does not work because RENAME TABLE does not work yet with .ibd files, they do not get renamed. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Foreign Key Constraint Issue
The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id); The address_list table looks like: mysql desc address_list; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | ADDRESS_LIST_ID | int(10) | | PRI | 0 | | | ADDRESS_1| varchar(100) | YES | | NULL| | | ADDRESS_2| varchar(100) | YES | | NULL| | | ADDRESS_3| varchar(100) | YES | | NULL| | | CITY | varchar(100) | YES | MUL | NULL| | | ZIP_CODE | varchar(20) | YES | | NULL| | | PHONE_NUM_1 | varchar(100) | YES | | NULL| | | PHONE_NUM_2 | varchar(100) | YES | | NULL| | | PHONE_NUM_FAX| varchar(100) | YES | | NULL| | | STATE_PROVINCE_ID| int(10) | YES | MUL | NULL| | | USER_ACCOUNT_ID | int(10) | YES | MUL | NULL| | | MARINA_ID| int(10) | YES | MUL | NULL| | | COMMERCIAL_ENTITY_ID | int(10) | YES | MUL | NULL| | | ADDRESS_TYPE_ID | int(10) | | MUL | 0 | | | DISTRIBUTOR_ID | int(10) | YES | MUL | NULL| | | CONTACT_INFO_ID | int(10) | YES | MUL | NULL| | | COUNTRY_ID | int(10) | YES | MUL | NULL| | | LANG_ID | int(10) | YES | | NULL| | | BOAT_LISTING_ID | int(10) | YES | MUL | NULL| | +--+--+--+-+-+---+ mysql select count(*) from address_list; +--+ | count(*) | +--+ | 202118 | +--+ The address_type table looks like: mysql desc address_type; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ADDRESS_TYPE_ID | int(10) | | PRI | 0 | | | ADDRESS_TYPE_DESC | varchar(100) | | | | | +---+--+--+-+-+---+ mysql select count(*) from address_type; +--+ | count(*) | +--+ |5 | +--+ There is, of course, and index on address_list.address_type_id. Here are all the unique address_type_id values in address_list: mysql select distinct address_type_id from address_list; +-+ | address_type_id | +-+ | 100 | | 101 | | 102 | | 104 | | 105 | +-+ And here are all the address_type.address_type_id values: mysql select address_type_id from address_type; +-+ | address_type_id | +-+ | 101 | | 102 | | 103 | | 104 | | 105 | +-+ As you can see, there are no null or 0 address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. There are indexes on both columns (address_list.address_type_id and address_type.address_type_id (via the primary key)), the data types are exactly the same and have the same size. show innodb status gives a LATEST FOREIGN KEY ERROR: LATEST FOREIGN KEY ERROR 030918 16:39:46 Transaction: TRANSACTION 0 1534907, ACTIVE 19 sec, process no 5741, OS thread id 1106012224 inserting, thread declared inside InnoDB 231 617 lock struct(s), heap size 44352, undo log entries 72029 MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 8064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex ac16; asc ..;; 2: len 7; hex 80002d0084; asc -..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue David. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 3:14 PM Subject: Update question I'm not quite sure why I haven't run across this
re: InnoDB Foreign Key
On Wednesday 19 March 2003 15:19, Thorsten Schmidt wrote: how can I remove a foreign key in InnoDB? ALTER TABLE DROP (FOREIGN) KEY `key` isn't working (and also not specified in documentation)... : ( Currently you should recreate table to remove FOREIGN KEY CONSTRAINTS. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
InnoDB Foreign Key
Dear all, how can I remove a foreign key in InnoDB? ALTER TABLE DROP (FOREIGN) KEY `key` isn't working (and also not specified in documentation)... : ( Thanks and best regards Thorsten (For bypassing the Filter: Here is sql,query,queries,smallint) - 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: InnoDB Foreign Key
Dear all, how can I remove a foreign key in InnoDB? ALTER TABLE DROP (FOREIGN) KEY `key` isn't working (and also not specified in documentation)... : ( It's not implemented yet. You can copy your data to another table without the foreign key constraint, drop original one and rename. sql, query -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - 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: Innodb Foreign Key Problems.
Scott, http://www.innodb.com/ibman.html#InnoDB_foreign_keys Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. Regards, Heikki Innobase Oy sql query Subject: Innodb Foreign Key Problems. From: Scott Wong Date: Wed, 5 Feb 2003 10:03:17 -0800 Hi. Simple parent/child table generates some weird output based on the order possible bug? Mysql 3.23.54 CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; show create table commands give this : FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) when it should be FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE insert into parent set id = 1; insert into child set id=1, parent id=1; delete from parent where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Now if the child was created like this : drop table child; CREATE TABLE child(id INT, parent id INT, INDEX par ind (parent id), FOREIGN KEY (parent id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; show create table gives this : FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE Order matters! :) and another bug from same tables: (do this with the create table command above) alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON UPDATE CASCADE ON DELETE CASCADE alter table child add FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE --reversed from above ON UPDATE CASCADE and you'll get some nice output from show create table: FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent id`) REFERENCES `test.parent` (`id`) Thank you for your time. Fix? Scott Wong, Meiko America, INC - 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
Innodb Foreign Key Problems.
Hi. Simple parent/child table generates some weird output based on the order possible bug? Mysql 3.23.54 CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ) TYPE=INNODB; show create table commands give this : FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) when it should be FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE insert into parent set id = 1; insert into child set id=1, parent_id=1; delete from parent where id = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Now if the child was created like this : drop table child; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; show create table gives this : FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE Order matters! :) and another bug from same tables: (do this with the create table command above) alter table child add FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON UPDATE CASCADE ON DELETE CASCADE alter table child add FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE --reversed from above ON UPDATE CASCADE and you'll get some nice output from show create table: FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) ON DELETE CASCADE, FOREIGN KEY (`parent_id`) REFERENCES `test.parent` (`id`) Thank you for your time. Fix? Scott Wong, Meiko America, INC - 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: InnoDB Foreign Key Questions
Muhammed, - Original Message - From: Muhammed Syyid [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, January 16, 2003 3:06 PM Subject: Re: InnoDB Foreign Key Questions Second the same question :). What does the CONSTRAINT keyword regarding FOREIGN KEYS do? the constraint name is simply ignored. Karam Chand, as far as I know, the drop command hasn't been implemented yet. So the only way to drop a foreign key is to drop and re-create the table. DROP FOREIGN KEY will come in some 4.1.x version. Since the constraint name is not stored, and is not required by the FOREIGN KEY syntax, I think that the syntax could be ALTER TABLE frobboz DROP FOREIGN KEY (column1) REFERENCES abbaguu (column2); Muhammed Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query [EMAIL PROTECTED] (Karam Chand) wrote in message news:avva22$168e$[EMAIL PROTECTED]... Hello I have two tables of InnoDB type. CREATE TABLE `ledger` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `id` (`name`) ) TYPE=InnoDB; CREATE TABLE `voucher` ( `id` int(11) NOT NULL default '0', `vdate` date NOT NULL default '-00-00', `amount` decimal(10,0) default '100', `name` varchar(50) default 'Karam', PRIMARY KEY (`id`,`vdate`) ) TYPE=InnoDB; Now I add a Foreign Key reference to ledger.id for voucher id by using the following command - alter table voucher add constraint fk_key_1 foreign key (id) references ledger (id) now when i do a query - show table status like 'voucher' I am getting the following value in the comment field. InnoDB free: 23552 kB; (id) REFER rohit/ledger(id) It shows that foreign key has been made but i had specified it to be fk_key_1. Even if I make the Foreign Key without the keyword constraint the same thing happens ? So, what is the purpose of CONSTRAINT keyword. How can I give a name to a relationship. Also after reading the MySQL and InnoDB docs I am not able to guess how to drop a FOREIGN KEY ? How can I do that ? I am using mysql-mx-nt 3.23.54 running as a service in WinXP. Any help will be appreciated. Thanks in advance. Karam sql, query ( filteraide ) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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 - 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
InnoDB Foreign Key Questions
Hello I have two tables of InnoDB type. CREATE TABLE `ledger` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`id`), KEY `id` (`name`) ) TYPE=InnoDB; CREATE TABLE `voucher` ( `id` int(11) NOT NULL default '0', `vdate` date NOT NULL default '-00-00', `amount` decimal(10,0) default '100', `name` varchar(50) default 'Karam', PRIMARY KEY (`id`,`vdate`) ) TYPE=InnoDB; Now I add a Foreign Key reference to ledger.id for voucher id by using the following command - alter table voucher add constraint fk_key_1 foreign key (id) references ledger (id) now when i do a query - show table status like 'voucher' I am getting the following value in the comment field. InnoDB free: 23552 kB; (id) REFER rohit/ledger(id) It shows that foreign key has been made but i had specified it to be fk_key_1. Even if I make the Foreign Key without the keyword constraint the same thing happens ? So, what is the purpose of CONSTRAINT keyword. How can I give a name to a relationship. Also after reading the MySQL and InnoDB docs I am not able to guess how to drop a FOREIGN KEY ? How can I do that ? I am using mysql-mx-nt 3.23.54 running as a service in WinXP. Any help will be appreciated. Thanks in advance. Karam sql, query ( filteraide ) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
mysql / innodb foreign key
hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? any suggestions are appreciated. thanks to all. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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: InnoDB foreign key problems - is Heikki out there?
Hi!
Please send your postings to [EMAIL PROTECTED] The newsgroup
mailing.database.mysql is only a mirror of that mailing list.
The error message below is misleading. It should really be 'Cannot update a
parent row...'. Internally InnoDB does the update by deleting and
reinserting the index record. That is the logic behind the misleading error
message 'Cannot delete a parent row...'.
There is also a bug related to this. Below you see that also an update of
the PRIMARY KEY fails, because it internally means deleting and reinserting
the referenced secondary index record. I have fixed that bug in upcoming
4.0.7. In 4.0.7 only updating the columns mentioned in the foreign key
constraint can generate a foreign key constraint error. These bug fixes will
be backported to 3.23.xx only if the bugs cause problems to users of 3.23.
Good news: in 4.0.7 you can declare
FOREIGN KEY (...) REFERENCES ... (...) ON UPDATE CASCADE
That should solve your problem if you want to update the referenced key in
the parent row. That will make InnoDB automatically update also the child
rows.
Regards,
Heikki
sql query
...
Tested on Linux, 4.0.6:
mysql create database despatches_inno;
Query OK, 1 row affected (0.02 sec)
mysql use despatches_inno
Database changed
mysql CREATE TABLE pickup_points (
- PP_RecordId int(11) NOT NULL auto_increment,
- PP_AffId int(11) default NULL,
- PP_Code varchar(10) NOT NULL default '',
- PP_CompanyName varchar(40) NOT NULL default '',
- PP_BranchName varchar(40) NOT NULL default '',
- PP_Contact varchar(40) default NULL,
- PP_Address1 varchar(40) NOT NULL default '',
- PP_Address2 varchar(40) default NULL,
- PP_Address3 varchar(40) default NULL,
- PP_PostCode varchar(40) default NULL,
- PP_Town varchar(40) NOT NULL default '',
- PP_County varchar(40) default NULL,
- PP_Country varchar(40) NOT NULL default '',
- PP_Tel1 varchar(40) default NULL,
- PP_Tel2 varchar(40) default NULL,
- PP_Fax varchar(40) default NULL,
- PP_Email varchar(40) default NULL,
- PP_Locality varchar(40) default NULL,
- PP_Issuer varchar(40) default NULL,
- PP_AffCode varchar(10) default NULL,
- PP_Status varchar(10) default 'ACTIVE',
- PP_StdCharge varchar(10) default NULL,
- PRIMARY KEY (PP_RecordId),
- UNIQUE KEY PP_Code (PP_Code),
- KEY PP_StdCharge (PP_StdCharge)
- ) TYPE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql
mysql --
mysql -- Table structure for table 'towns_pickup_points'
mysql --
mysql
mysql CREATE TABLE towns_pickup_points (
- TPP_TownId int(11) NOT NULL default '0',
- TPP_PPCode varchar(10) NOT NULL default '',
- TPP_Default char(1) default 'Z',
- TPP_RecordId int(11) NOT NULL auto_increment,
- PRIMARY KEY (TPP_RecordId),
- UNIQUE KEY TPP_RecordId (TPP_RecordId),
- KEY TPP_TownId (TPP_TownId),
- KEY TPP_PPCode (TPP_PPCode),
- FOREIGN KEY (`TPP_PPCode`) REFERENCES
- `despatches_inno.pickup_points` (`PP_Code`),
- ) TYPE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql
mysql
mysql insert into pickup_points(PP_Code) values ('abc');
Query OK, 1 row affected (0.07 sec)
mysql insert into towns_pickup_points(TPP_PPCode) values ('abc');
Query OK, 1 row affected (0.01 sec)
mysql
mysql
mysql update pickup_points set PP_Status = 'abbaguu';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql update pickup_points set PP_Code = 'frobboz';
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql
mysql update pickup_points set PP_RecordId = 10;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql
...
From: My Deja ([EMAIL PROTECTED])
Subject: InnoDB foreign key problems - is Heikki out there?
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.mysql
Date: 2002-12-17 16:13:36 PST
I have this problem with foreign keys.
Below is the schema of the tables.
Whenever I try to update a value in the parent table I get this error
message:
Error: Cannot delete a parent row: a foreign key constraint fails
I am trying to update a value in the parent, why does the error
message refer to a delete?
-- MySQL dump 8.22
--
-- Host: localhostDatabase: despathces_inno
-
-- Server version 3.23.53-max-nt
--
-- Table structure for table 'pickup_points'
--
CREATE TABLE pickup_points (
PP_RecordId int(11) NOT NULL auto_increment,
PP_AffId int(11) default NULL,
PP_Code varchar(10) NOT NULL default '',
PP_CompanyName varchar(40) NOT NULL default '',
PP_BranchName varchar(40) NOT NULL default '',
PP_Contact varchar(40) default NULL,
PP_Address1 varchar(40) NOT NULL default '',
PP_Address2 varchar(40) default NULL,
PP_Address3 varchar(40) default NULL,
PP_PostCode
InnoDB, Foreign Key
Hello I use Linux Red Hat 7.3 and MySQL-max version 2.23.51. I have one problem. I created table: CREATE TABLE Folders ( FOLDER_ID INTEGER NOT NULL, FOLDER_NAME VARCHAR(50) NOT NULL, PARENT INTEGER NULL, PRIMARY KEY (FOLDER_ID), KEY XIE1_Folders (PARENT), FOREIGN KEY (PARENT) REFERENCES Folders (FOLDER_ID) ON DELETE CASCADE ) TYPE=INNODB; And when I try to execute this statement: INSERT INTO Folders VALUES (1, 'Main', NULL); I get error message: 'ERROR 1216: Cannot add a child row: a foreign key constraint fails'. On Windows all is ok in this case. Can you advise? Thank you Eduard - 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: InnoDB, Foreign Key
Edouard, you have a foreign key referencing a column in its own table. PB - - Original Message - From: Serdioukov Edouard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 29, 2002 3:23 AM Subject: InnoDB, Foreign Key Hello I use Linux Red Hat 7.3 and MySQL-max version 2.23.51. I have one problem. I created table: CREATE TABLE Folders ( FOLDER_ID INTEGER NOT NULL, FOLDER_NAME VARCHAR(50) NOT NULL, PARENT INTEGER NULL, PRIMARY KEY (FOLDER_ID), KEY XIE1_Folders (PARENT), FOREIGN KEY (PARENT) REFERENCES Folders (FOLDER_ID) ON DELETE CASCADE ) TYPE=INNODB; And when I try to execute this statement: INSERT INTO Folders VALUES (1, 'Main', NULL); I get error message: 'ERROR 1216: Cannot add a child row: a foreign key constraint fails'. On Windows all is ok in this case. Can you advise? Thank you Eduard - 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 - 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: InnoDB, Foreign Key
From: Heikki Tuuri To: Serdioukov Edouard Cc: [EMAIL PROTECTED] Sent: Tuesday, October 29, 2002 7:23 PM Subject: Re: InnoDB, Foreign Key Edouard, I tested this on Linux using mysql-max-3.23.51, the .tar.gz distro. It worked ok. Are you sure you were using 3.23.51? Starting from 3.23.50, InnoDB does not check a foreign key constraint when the column value is NULL. The behavior you had would have happened with 3.23.49a, for example. http://www.innodb.com/ibman.html#InnoDB_foreign_keys Regards, Heikki sql query heikki@hundin:~/mysql-max-3.23.51-pc-linux-gnu-i686/bin mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.51-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE t_Folders ( - FOLDER_ID integer NOT NULL, - FOLDER_NAME varchar(50) NOT NULL, - PARENT integer NULL, - PRIMARY KEY (FOLDER_ID), - KEY XIE1t_Folders (PARENT), - FOREIGN KEY (PARENT) REFERENCES t_Folders (FOLDER_ID) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql INSERT INTO t_Folders VALUES (1, 'Notes', NULL); Query OK, 1 row affected (0.01 sec) mysql select * from t_Folders; +---+-++ | FOLDER_ID | FOLDER_NAME | PARENT | +---+-++ | 1 | Notes | NULL | +---+-++ 1 row in set (0.01 sec) mysql mysql mysql show create table t_Folders; +---+--- --+ | Table | Create Table | +---+--- --+ | t_Folders | CREATE TABLE `t_Folders` ( `FOLDER_ID` int(11) NOT NULL default '0', `FOLDER_NAME` varchar(50) NOT NULL default '', `PARENT` int(11) default NULL, PRIMARY KEY (`FOLDER_ID`), KEY `XIE1t_Folders` (`PARENT`), FOREIGN KEY (`PARENT`) REFERENCES `test.t_Folders` (`FOLDER_ID`) ON DELETE CAS CADE ) TYPE=InnoDB | +---+--- --+ 1 row in set (0.00 sec) mysql - Original Message - From: Serdioukov Edouard To: Heikki Tuuri Sent: Tuesday, October 29, 2002 6:55 PM Subject: RE: Question ... - Original Message - ... From: Serdioukov Edouard To: [EMAIL PROTECTED] Sent: Wednesday, October 23, 2002 3:42 PM Subject: Question Hello I use Linux Red Hat 7.3 and MySQL-max version 2.23.51. And I have one problem. I created table: CREATE TABLE t_Folders ( FOLDER_ID integer NOT NULL, FOLDER_NAME varchar(50) NOT NULL, PARENT integer NULL, PRIMARY KEY (FOLDER_ID), KEY XIE1t_Folders (PARENT), FOREIGN KEY (PARENT) REFERENCES t_Folders (FOLDER_ID) ON DELETE CASCADE ) TYPE=INNODB; And when I try insert this record INSERT INTO t_Folders VALUES (1, 'Notes', NULL); I get error message: 'ERROR 1216: Cannot add a child row: a foreign key constraint fails'. On Windows all is ok in this case. Can you advise? Thank you Eduard - 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
InnoDB foreign key corruption
Hi all, We are using MySQL v. 3.23.49 on both Windows 2K and Solaris 8 with InnoDB support. We have created many relational tables since InnoDB supports foreign keys. After some unidentified time, the foreign keys relating only one table corrupts but the other foreign keys remain valid. At the comment of the table, it used to store the foreign key relations but after the corruption it writes nonsense characters like #sql2-218-48... InnoDB free: 40960 kB; (MSISDN) REFER inoxpp/#sql2-218-48(MS Has anyone else faced this problem before? Regards Okan - 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
InnoDB foreign key constraints
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
First of all the referenced key must be on PRIMARY KEY.
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
...
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
...
At 10:59 2002.06.10. +0300, you wrote:
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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
-
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: InnoDB foreign key constraints
Heya!
You need an INDEX.
Try doing this first :
alter table ip_name_tbl add INDEX(name_id);
And add then your constraint.
EG
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be
incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:17 am, you wrote:
First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
I've probably messed something up while fooling around with indexes and trying
to get it to work. Now, I took the advices I got, but I still can't get it to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
mysql show create table ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
mysql show create table conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn (name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
*sigh*
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:44 am, Markus Lervik wrote: mysql show create table ip_name_tbl\G *** 1. row *** Table: ip_name_tbl Create Table: CREATE TABLE `ip_name_tbl` ( [snip] `name_id` int(11) NOT NULL default '0', [snip] mysql show create table conn\G *** 1. row *** Table: conn Create Table: CREATE TABLE `conn` ( [snip] `name_id` int(10) unsigned NOT NULL default '0', [snip] Ok, thank's to Jocelyn the problem is solved and can be seen above. Kinda makes sence that both fields should be the same. : ) Slipped my attention ; ) Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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: InnoDB foreign key constraints
What I think, is that your syntax for creating the primary key is slightly
incorrect. I'm not sure if this is also true vor MySQL but I got teached at
school that a foreign key can only point to the primary key of a table.
Perhaps you can try to do the following:
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
FOREIGN KEY (name_id) REFERENCES conn ON DELETE CASCADES
KEY `id` (`id`)
) TYPE=InnoDB;
Notice the foreign key already in the table definition, and off course
creating table `conn` before the other one. If this doesn't work, try
rewriting your foreign key constraint to:
ALTER TABLE ip_name_tbl
ADD CONSTRAINT FOREIGN KEY (name_id)
REFERENCES conn
ON DELETE CASCADE;
* without pointing to which column the key points, just the table.
Btw, why do you have an `id` field, set as NOT NULL and with an
auto_increment, with besides another field `name_id` set as primary key?
Greetzz,
Wouter
(being my first msg to this list, btw: hello everybody .. i'm wouter and new
to this list ;) hihi )
--
Alle door mij verzonden email is careware. Dit houdt in dat het alleen
herlezen en bewaard mag worden als je goed omgaat met al het leven op aarde
en daar buiten. Als je het hier niet mee eens bent dien je mijn mailtje
binnen 24 uur terug te sturen, met opgaaf van reden van onenigheid.
All email sent by me is careware. This means that it can only be reread and
kept if you are good for all the life here on earth and beyond. If you don't
agree to these terms, you should return this email in no more than 24 hours
stating the reason of disagreement.
-Oorspronkelijk bericht-
Van: Markus Lervik [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 10 juni 2002 10:45
Aan: Kiss Dániel
CC: [EMAIL PROTECTED]
Onderwerp: Re: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:17 am, you wrote:
First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
I've probably messed something up while fooling around with indexes and
trying
to get it to work. Now, I took the advices I got, but I still can't get it
to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
mysql show create table ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
mysql show create table conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn (name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
*sigh*
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
Before posting, please check:
http://www.mysql.com
Re: InnoDB Foreign Key Constraints
Daniel, - Original Message - From: Daniel Rand [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 13, 2002 5:24 PM Subject: InnoDB Foreign Key Constraints Hi, Does anyone know if it's possible to set up a foreign key constraint where one table references the primary key of another table in a different database (both tables being of InnoDB type). yes, you can use the databasename.tablename syntax: heikki@hundin:~/mysql/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.51-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show create table aaa; +---+--- ---+ | Table | Create Table | +---+--- ---+ | aaa | CREATE TABLE `aaa` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) TYPE=InnoDB | +---+--- ---+ 1 row in set (0.01 sec) mysql use test11 Database changed mysql create table mmm (a int not null, b int, primary key (a), index (b), fore ign key (b) references test.aaa (a)); Query OK, 0 rows affected (0.00 sec) mysql show create table mmm; +---+--- + | Table | Create Table | +---+--- + | mmm | CREATE TABLE `mmm` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`), FOREIGN KEY (`b`) REFERENCES `test.aaa` (`a`) ) TYPE=InnoDB | +---+--- + 1 row in set (0.00 sec) mysql Thanks, DAN Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.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
