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?
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]
