Noga,
if SET FOREIGN_KEY_CHECKS=0 affects other connections to the database, that
is a serious bug.
If you can repeat the problem, please file a bug report to bugs.mysql.com
I tested this with 5.0.15, and it worked ok:
Connection 1:
[EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.15-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table t(a int primary key) type = innodb;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> create table t2(a int primary key, foreign key (a) references t(a))
type = innodb;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
Connection 2:
[EMAIL PROTECTED]:~/mysql-5.0.15/client$ ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> insert into t2 values (10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`)
REFERENCES `t` (`a`))
mysql>
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
----- Original Message -----
From: ""Noga Woronoff"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, November 18, 2005 11:56 PM
Subject: FW: Regarding SET FOREIGN_KEY_CHECKS=0;
Thank you Heikki!
We had a problem where code in one of our program's EJB did not turn the
FK CHECK back to 1.
Another program that was started afterwards caused some bad data to be
inserted in the database - as though the FK CHECK was =3D 0. It wasn't
until we turned the FK CHECK back to 1 in the first program that the
second one started to behave correctly.
We use JBoss and MySQL 4.0.21.
Any feedback on the nature of JBoss-MySQL transaction management will be
most helpful to us.
Thank you much in advance -
Noga
-----Original Message-----
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, November 18, 2005 2:56 PM
To: Noga Woronoff
Cc: Heikki Tuuri
Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=3D0;
Noga,
please forward this communication to mysql@lists.mysql.com
> mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=3D0;" < /tmp/dump.sql
FOREIGN_KEY_CHECKS is a per-session variable. When the above shell=20
command returns, the session ends. Thus, no problem for others.
Regards,
Heikki
Oracle/Innobase
Noga Woronoff wrote:
Hi Heikki -
=20
=20
=20
I don't know which user group to use and wonder whether you may answer
a
question for me?
=20
=20
=20
When you perform:
=20
mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=3D0;" < /tmp/dump.sql
=20
=20
=20
Does the InnoDB engine set the foreign key checks back to 1 again,
automatically - once the database install is complete? Under what
circumstances one can get into trouble if the FK check is not set back
to 1 at the end of the transaction? Is there a white paper I can read
on the subject since I cannot find anything?
=20
=20
=20
Is there a way to check whether the FK check is turned ON/OF?
=20
=20
=20
Thanks you MUCH in advance and GOOD LUCK in your new Oracle adventure!
=20
=20
=20
Noga Woronoff
=20
Interactive Constructs, Inc.
=20
200 Boston Ave. Suite #1800
=20
Medford, MA 02155
=20
=20
--
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]
