You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Brian Parkinson <pa...@avaning.com> on 2007/12/06 14:46:23 UTC
MySQL related question - constraint violation
Hello -
Wondering if someone out there has some MySQL experience - running into
a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
issue, but I received no response over at the MySQL forums, so it's
worth a shot:
I have a very simple data model working in iBATIS (getting familiar with
this, and enjoying how it's put together). It works on Windows and Mac
OS X, with MySQL version 5.0.45.
For our deployment, we're using Debian 5.0.32.
When I run my unit tests on the Debian box, I get foreign key constraint
violations which I do not get on the Windoze or Mac box. To wit:
[junit] ------------- Standard Output ---------------
[junit] --- The error occurred in
com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
[junit] --- The error occurred while applying a parameter map.
[junit] --- Check the Thermostat.LocationParameterMap.
[junit] --- Check the statement (update failed).
[junit] --- Cause:
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails
(`ecobee_test/Location`, CONSTRAINT `FK07` FOREIGN KEY (`ThermostatID`)
REFERENCES `thermostat` (`ThermostatID`) ON DELETE NO ACTION ON UPDATE
NO ACTION); nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
[junit] --- The error occurred in
com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
[junit] --- The error occurred while applying a parameter map.
[junit] --- Check the Thermostat.LocationParameterMap.
[junit] --- Check the statement (update failed).
...
I've double checked the code, and I know it works as the unit tests on
Windoze and Mac are checking that everything works. The insert code
where the problem is:
getSqlMapClient().startTransaction();
getSqlMapClient().startBatch();
int thermostatId = (Integer)
getSqlMapClientTemplate().insert("Thermostat.insert", thermostat);
Location location = thermostat.getLocation();
location.setThermostatId(thermostatId);
getSqlMapClientTemplate().insert("Location.insert", location);
So, the location is certainly getting set with the proper thermostat
identifier, but the constraint is violated.
I've tried moving the Thermostat.insert out of the transaction/batch,
and a couple other things, but nothing works.
I guess the next step is to upgrade the Debian box, but it's a pretty
huge undertaking (a number of databases being used) and this seems that
there's something else going on.
Any ideas?
Appreciated, and thanks.
parki....
RE: MySQL related question - constraint violation
Posted by Poitras Christian <Ch...@ircm.qc.ca>.
My guess is that something changed between those version.
Either LAST_INSERT_ID() changed or how foreign keys are managed. In
either case, you'll find better information on MySQL list or release
notes.
It could also be something not directly linked to the message like
triggers.
Christian
-----Original Message-----
From: Brian Parkinson [mailto:parki@avaning.com]
Sent: Thursday, December 06, 2007 4:36 PM
To: user-java@ibatis.apache.org
Subject: RE: MySQL related question - constraint violation
HI Toby:
Yes - the exact same SQL script is used to set up both databases (and
yes - InnoDB).
Same SQL script to set up the databases and tables (and created from
scratch each time). Same Java code unit tests - the only difference is
Debian (5.0.32) as opposed to Windows/Mac (5.0.45).
Weird, huh?!
parki...
-----Original Message-----
From: Toby Thain [mailto:toby@smartgames.ca]
Sent: Thursday, December 06, 2007 3:15 PM
To: user-java@ibatis.apache.org
Subject: Re: MySQL related question - constraint violation
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
RE: MySQL related question - constraint violation
Posted by Brian Parkinson <pa...@avaning.com>.
HI Toby:
Yes - the exact same SQL script is used to set up both databases (and
yes - InnoDB).
Same SQL script to set up the databases and tables (and created from
scratch each time). Same Java code unit tests - the only difference is
Debian (5.0.32) as opposed to Windows/Mac (5.0.45).
Weird, huh?!
parki...
-----Original Message-----
From: Toby Thain [mailto:toby@smartgames.ca]
Sent: Thursday, December 06, 2007 3:15 PM
To: user-java@ibatis.apache.org
Subject: Re: MySQL related question - constraint violation
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
Re: MySQL related question - constraint violation
Posted by Toby Thain <to...@smartgames.ca>.
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails