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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
