java MySQL transaction / INSERT IGNORE and ON DUPLICATE KEY UPDATE

Question

Connection con= DriverManager.getConnection("jdbc:mysql://"+ host, user, pass);
Statement state = con.createStatement();

con.setAutoCommit(false);

state.executeUpdate("INSERT IGNORE `kb_manage`.`serial` (`serial`) VALUES ('10001')");

state.executeUpdate("INSERT `kb_manage`.`serial` (`serial`) VALUES ('10001') ON 
DUPLICATE KEY UPDATE `serial`='10002'");

con.commit();

This is a simplified version of code that I am using in one of my applications. The application itself is written in java and connects to a MySQL database. I am trying to make a transaction that that has multiple insert queries which make use of IGNORE and ON DUPLICATE KEY UPDATE.The table in this example contains 1 column named serial which is set to be the PRIMARY KEY. The issue that I have is that I am getting a :

java.sql.SQLException: DUP_PK_KEY
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1654)
at remote.SerialValue.main(SerialValue.java:19)

At first I thought that I am doing something wrong with SQL until the point where I removed the transaction entirely and everything worked .... just not as a transaction which is quite a problem. Any idea on what is going wrong ? Does the driver simply ignore IGNORE and ON DUPLICATE KEY or is it me that is doing something terribly wrong ? Thanks for the help in advance.

What version of MySQL are you using? And is the logic of inserting and updating only a bit confusing because it is only a code example? :-) — Dominik Sandjaja
– Dominik Sandjaja, Commented Sep 6, 2012 at 14:59

Joop Eggen · Accepted Answer · 2012-09-06 15:12:47Z

1

Create your statement after changing your autoCommit state:

con.setAutoCommit(false);
Statement state = con.createStatement();

edited Sep 6, 2012 at 15:12

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

answered Sep 6, 2012 at 15:06

Glenn

9,2102 gold badges44 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dominik Sandjaja Over a year ago

Nice, I didn't realize that would make a difference.

Collectives™ on Stack Overflow

java MySQL transaction / INSERT IGNORE and ON DUPLICATE KEY UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related