-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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.
>
Noga,
JBoss is most likely using a connection pool, which means that the
connection doesn't physically close, which also means that the value for
SET FOREIGN KEY CHECKS isn't reset to its default value automatically.
If you can use MysqlConnectionPoolDataSource with JBoss' connection
pool, the MySQL JDBC driver will reset connection state when the
"logical" connection is closed. If you don't use our
ConnectionPoolDataSource, your application is responsible for setting
session-level variables that affect server behavior to appropriate
values before returning them to the connection pool.
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFDgcwYtvXNTca6JD8RApLrAJ45rGcCyU4K1k2Y3Xf/2mO1hXA5GwCePG2U
LuadazxAp4+gR8XH9YsNR8Y=
=yZOd
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
