Re: Foreign key contraints, on delete cascade not working?
Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO | +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? I'm using 4.0.10-gamma as mysql monitor indicates. Also: $ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586 You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html This doesn't apply as MySQL version is 4.0.10. Any other ideas? Your MySQL server is configured without support for InnoDB tables. Hmmm... Could swore I responded to this but I don't see my response. Perhaps that spamcop thingy messed up the post. I will try again. When prototyping my application I found foreign keys and its delete on cascade capability and decided to use that feature. I made sure that I used type=innodb with my table creates. I even tested the feature out and it worked fine. I did not change the configuration of the MySQL server process to turn off support for InnoDB tables. Then, for some reason, on cascade delete stopped working so I'm asking here how do I get it working again. I'm not sure how to configure it with support for InnoDB tables. How would I do that? If I need to rebuild MySQL then I am completely confused as to why it used to work then stopped working when the MySQL server software has not changed. Perhaps a couple of direct questions: * How do I verify that InnoDB support is not turned on? (Is that that show variable thing?) * What do I do to turn it on? Is it just a simple setting of the variable in my.cnf and restarting the server or do I need to rebuild? * If I need to rebuild MySQL, I now have active data. Perhaps a pointer as to how to best rebuild MySQL software while retaining my current data would be really appreciated. Thanks. -- Do witches run spell checkers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key contraints, on delete cascade not working?
Andrew DeFaria [EMAIL PROTECTED] wrote: Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? I'm using 4.0.10-gamma as mysql monitor indicates. Also: $ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586 You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html This doesn't apply as MySQL version is 4.0.10. Any other ideas? Your MySQL server is configured without support for InnoDB tables. -- 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: Foreign key contraints, on delete cascade not working?
Andrew DeFaria [EMAIL PROTECTED] wrote: I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However: drop database if exists MYDB; create database MYDB; use MYDB; create table user ( userid varchar (8) not null, name tinytextnot null, primary key (userid) ) type=innodb; -- user create table useropts ( userid varchar (8) not null, name tinytext, value varchar (128), key user_index (userid), foreign key (userid) references user (userid) on delete cascade ) type=innodb; -- useropts insert into user values (userA, User A); insert into useropts values (userA, option, value); select * from user; select * from useropts; delete from user; select * from useropts; select * from user; $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35215 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql source MYDB.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Empty set (0.00 sec) As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html -- 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: Foreign key contraints, on delete cascade not working?
Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However: drop database if exists MYDB; create database MYDB; use MYDB; create table user ( userid varchar (8) not null, name tinytext not null, primary key (userid) ) type=innodb; -- user create table useropts ( userid varchar (8) not null, name tinytext, value varchar (128), key user_index (userid), foreign key (userid) references user (userid) on delete cascade ) type=innodb; -- useropts insert into user values (userA, User A); insert into useropts values (userA, option, value); select * from user; select * from useropts; delete from user; select * from useropts; select * from user; $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35215 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql source MYDB.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Empty set (0.00 sec) As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? I'm using 4.0.10-gamma as mysql monitor indicates. Also: $ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586 You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html This doesn't apply as MySQL version is 4.0.10. Any other ideas? -- Hidden DOS secret: add BUGS=OFF to your CONFIG.SYS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign key contraints, on delete cascade not working?
I created the following
.sql file to demonstrate a problem I'm having.
According to the manual:
If ON DELETE CASCADE is
specified, and a row in the parent table is deleted, then InnoDB
automatically deletes also all those rows in the child table
whose foreign key values are equal to the referenced key value in
the parent row.
However:
drop database if exists MYDB;
create database MYDB;
use MYDB;
create table user (
userid varchar (8) not null,
name tinytext not null,
primary key (userid)
) type=innodb; -- user
create table useropts (
userid varchar (8) not null,
name tinytext,
value varchar (128),
key user_index (userid),
foreign key (userid) references user (userid) on delete cascade
) type=innodb; -- useropts
insert into user values ("userA", "User A");
insert into useropts values ("userA", "option", "value");
select * from user;
select * from useropts;
delete from user;
select * from useropts;
select * from user;
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35215 to server version: 4.0.10-gamma
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql source MYDB.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
+++
| userid | name |
+++
| userA | User A |
+++
1 row in set (0.00 sec)
+++---+
| userid | name | value |
+++---+
| userA | option | value |
+++---+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+++---+
| userid | name | value |
+++---+
| userA | option | value |
+++---+
1 row in set (0.00 sec)
Empty set (0.00 sec)
As you can see I when I delete from user (the parent table) the
useropts (child table) entry remains. Shouldn't it be deleted?
Heikki Tuuri asked me to look at my innodb variables and I found:
mysql show variables like "%innodb%";
+---+---+
| Variable_name | Value |
+---+---+
| have_innodb | NO |
+---+---+
1 row in set (0.00 sec)
But I still must ask: Why is that? How do I turn it on?
(For the record, when I was prototyping this db I tested this and it
worked like a champ. Sometime later it stopped working).
Hmmm... Interesting:
mysql show variables like "%innodb%";
+---+---+
| Variable_name | Value |
+---+---+
| have_innodb | NO |
+---+---+
1 row in set (0.05 sec)
mysql set variable have_innodb=yes;
ERROR 1193: Unknown system variable 'variable'
mysql set have_innodb=yes;
ERROR 1193: Unknown system variable 'have_innodb'
--
Think "honk" if you're telepathic.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key contraints, on delete cascade not working?
Andrew, please check with SHOW VARIABLES LIKE '%innodb%'; that you really have InnoDB enabled. I tested the script below on Linux with 4.0.8 and 4.0.18, and it worked ok. 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 InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ [EMAIL PROTECTED]:~/mysql-4.0/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.18-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql drop database if exists MYDB; Query OK, 0 rows affected (0.06 sec) mysql create database MYDB; Query OK, 1 row affected (0.00 sec) mysql use MYDB; Database changed mysql create table user ( - useridvarchar (8)not null, - nametinytextnot null, - primary key (userid) - ) type=innodb; -- user Query OK, 0 rows affected (0.03 sec) mysql create table useropts ( - useridvarchar (8)not null, - nametinytext, - valuevarchar (128), - key user_index (userid), - foreign key (userid) references user (userid) on delete cascade - ) type=innodb; -- useropts Query OK, 0 rows affected (0.01 sec) mysql insert into user values (userA, User A); Query OK, 1 row affected (0.00 sec) mysql insert into useropts values (userA, option, value); Query OK, 1 row affected (0.00 sec) mysql select * from user; +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) mysql select * from useropts; +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) mysql delete from user; Query OK, 1 row affected (0.00 sec) mysql select * from useropts; Empty set (0.00 sec) mysql select * from user; Empty set (0.01 sec) mysql - Original Message - From: Andrew DeFaria [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, January 10, 2004 7:03 AM Subject: foreign key contraints, on delete cascade not working? I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However: drop database if exists MYDB; create database MYDB; use MYDB; create table user ( useridvarchar (8)not null, nametinytextnot null, primary key (userid) ) type=innodb; -- user create table useropts ( useridvarchar (8)not null, nametinytext, valuevarchar (128), key user_index (userid), foreign key (userid) references user (userid) on delete cascade ) type=innodb; -- useropts insert into user values (userA, User A); insert into useropts values (userA, option, value); select * from user; select * from useropts; delete from user; select * from useropts; select * from user; $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35215 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql source MYDB.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Empty set (0.00 sec) As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? -- E-mail returned to sender -- insufficient voltage. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
