Hi Heikki -
I tested the two connections in MySQL 4.0.26 and got the same results as
you did:
Connection 1:
mysql> create table t(a int primary key) type = innodb;
Query OK, 0 rows affected (0.13 sec)
mysql> create table t2(a int primary key, foreign key (a) references
t(a)) type = innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
Connection 2:
mysql> insert into t2 values (10);
ERROR 1216: Cannot add or update a child row: a foreign key constraint
fails
mysql>
Am I right to assume that when a MySQL-client session ends the FK checks
is back to 1 automatically?
That when an EJB turns SET FOREIGN_KEY_CHECKS=0; WITHOUT SETing
FOREIGN_KEY_CHECKS=1; after a set of sql queries - the subsequent EJB
session termination by JBoss should restore the FK state checks=1?
I do realize that the prudent approach in the EJB is to turn the FK
check back to 1 at the conclusion of the sql queries - HOWEVER just as
(MySQL 4.1) mysqldump sets the FK checks to 0 at the top of the file
without setting it back to 1 at the bottom of the file I assume that
when the MySQL-client session ends - the FK's checks is set back to 1.
I am turning my attention now to JBoss-MySQL EJB session management and
any advice will be greatly appreciated.
Regards -
Noga
-----Original Message-----
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Monday, November 21, 2005 4:21 AM
To: mysql@lists.mysql.com
Cc: Noga Woronoff
Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0;
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]
