Problem with iBATIS + MySQL + auto increment keys

[Matteo Di Furia]

Hi everyone, this seems quite a simple problem to solve, still I can't find a solution for it, nor can find on the web some usefull info to head me in the right direction.
So, I'm using MySQL and iBATIS as my ORM tool. I got a table defined like this :

CREATE TABLE FOTO_SEZIONE
(
	FOTO_SEZIONE_ID				INT NOT NULL AUTO_INCREMENT,
	FOTO_SEZIONE_NOME			VARCHAR(100) NOT NULL,
	FOTO_SEZIONE_DESCRIZIONE		VARCHAR(255) NOT NULL,
	FOTO_SEZIONE_TIPO				INT NOT NULL,
	FOTO_SEZIONE_DATA			DATE NOT NULL,
	FOTO_SEZIONE_ATTIVO			BIT NOT NULL DEFAULT 1,
	PRIMARY KEY (FOTO_SEZIONE_ID)
);

While my insert statement is defined like this in the sql mapping xml generated by iBator.

 
	<insert id="mtquery_insert_key_retrieve" parameterClass="com.mt.db.generated.model.FotoSezione">
		insert into foto_sezione (FOTO_SEZIONE_NOME, FOTO_SEZIONE_DESCRIZIONE,
		FOTO_SEZIONE_TIPO, FOTO_SEZIONE_DATA)
		values (#fotoSezioneNome:VARCHAR#, #fotoSezioneDescrizione:VARCHAR#,
		#fotoSezioneTipo:INTEGER#, #fotoSezioneData:DATE#)
		<selectKey keyProperty="fotoSezioneId" resultClass="int">
			SELECT LAST_INSERT_ID() AS fotoSezioneId
	        </selectKey>
	</insert>

Please note that this <insert> node has not been generated by iBator, I defined it by hand starting from the default one generated by Ibator and added the <selectKey> element to retrieve the last inserted autoincrement id (this because Ibator generates an insert statement assuming the object has the key field yet assigned, thus ignoring the autoincrement option).
Inserting the first record on DB runs without any error and I can get back the generated autoincrement key (which is, by no surprise, 1!).
During the 2nd execution, anyway, I get this exception :

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        ...

So, it seems that MySQL is doing something wrong when generating the 2nd key for the next record inserted. Please also note that I suspect this is an iBATIS problem since using autoincrement keys with raw JDBC code brings no errors at all and everything works fine. I didn't make test to see if I get this to work WITHOUT retrieving the last inserted key (thus deleting the <selectKey> element in mapping xml), but that would not be what I need the code to do.

Any suggestions to solve this ?

Thank you for your help and support.

[Matteo Di Furia]

Sorry everyone, I figured out the problem was not in the mapping I posted, just in a very similar part later in code flow. I asked Scott Selikoff to close and delete this, just to not let anyone to loose time on an unexistent problem.

[David W Brown]

Hello Matteo, I would appreciate if you would post the difference in what you thought was a problem. I am currently having a similar problem with a referenced table unable to acquire the auto-generated key. I know the key is being generated in the parent table as I set @Rollback(value=true) on the JUnit test method. And, as a result, the parent table record gets inserted with a new record primary key ID. But, then, the new key ID value is lost on the child table that needs the generated primary key value to allow the insertion of a new record into the child table. And, as a result the record insertion attempt on the child table throws the following exceptions. Any ideas are considered helpful. Regards, David.

org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];


com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException

--- The error occurred in com/st/data/master_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the master.insert-InlineParameterMap

Cannot add or update a child row: a foreign key constraint fails (`elblanco`.`master`, CONSTRAINT `fk_Master` FOREIGN KEY (`Id`) REFERENCES `master` (`Id`) ON UPDATE CASCADE); nested exception is com.ibatis.common.jdbc.exception.NestedSQLException

[silvanoh silvanoh]

Matteo Di Furia wrote:

 
	<insert id="mtquery_insert_key_retrieve" parameterClass="com.mt.db.generated.model.FotoSezione">
		insert into foto_sezione (FOTO_SEZIONE_NOME, FOTO_SEZIONE_DESCRIZIONE,
		FOTO_SEZIONE_TIPO, FOTO_SEZIONE_DATA)
		values (#fotoSezioneNome:VARCHAR#, #fotoSezioneDescrizione:VARCHAR#,
		#fotoSezioneTipo:INTEGER#, #fotoSezioneData:DATE#)
		<selectKey keyProperty="fotoSezioneId" resultClass="int">
			SELECT LAST_INSERT_ID() AS fotoSezioneId
	        </selectKey>
	</insert>

hi, I have same problem. I've resolved changing position in tag <insert> between query text and tag <selectKey>.
It's strange for me but it works