Hi Heikki - Thank you. The issue is apparently in the JBoss-MySQL session management in our application.
Regards - Noga -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, November 21, 2005 8:16 AM To: Noga Woronoff Cc: mysql@lists.mysql.com; Jim Kan; Kevin Chee Subject: Re: Regarding SET FOREIGN_KEY_CHECKS=0; Noga, yes, the setting SET FOREIGN_KEY_CHECKS=0 is per session. If it is somehow 'inherited' to another session, that is a serious bug. 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 Noga Woronoff wrote: > 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]
