{"id":453,"date":"2008-01-04T19:59:24","date_gmt":"2008-01-05T01:59:24","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/archives\/000453"},"modified":"2008-01-04T19:59:24","modified_gmt":"2008-01-05T01:59:24","slug":"batchupdateexception-using-hibernate-and-mysql5","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/453","title":{"rendered":"BatchUpdateException using Hibernate and MySQL5"},"content":{"rendered":"<p>I ran into a crazy error last week.  One of my clients was upgrading from MySQL4 to MySQL5.  The application in question was using Hibernate 3.2.  Here&#8217;s the table structure, and the hibernate bean definition.  See if you can spot the issue:<\/p>\n<p><code \/><\/p>\n<pre>mysql&gt; desc stat;\r\n+--------------+-------------+------+-----+---------------------+-------+\r\n| Field        | Type        | Null | Key | Default             | Extra |\r\n+--------------+-------------+------+-----+---------------------+-------+\r\n| stat_date    | date        |      | PRI | 0000-00-00          |       |\r\n| stat_type    | varchar(50) |      | PRI |                     |       |\r\n| stat_count   | int(11)     | YES  |     | NULL                |       |\r\n| last_updated | datetime    |      |     | 0000-00-00 00:00:00 |       |\r\n+--------------+-------------+------+-----+---------------------+-------+\r\n4 rows in set (0.00 sec)\r\n\r\n&lt;class name=\"com.foo.common.data.Statistic\" table=\"stat\" lazy=\"false\"&gt;\r\n&lt;cache usage=\"read-write\"\/&gt;\r\n&lt;composite-id name=\"statisticId\" class=\"com.foo.common.data.StatisticId\"&gt;\r\n&lt;key-property name=\"date\" type=\"java.util.Date\" column=\"stat_date\"\/&gt;\r\n&lt;key-property name=\"type\" column=\"stat_type\"\/&gt;\r\n&lt;\/composite-id&gt;\r\n&lt;property name=\"count\" column=\"stat_count\"\/&gt;\r\n&lt;property name=\"lastUpdated\" type=\"java.util.Date\" column=\"last_updated\" \/&gt;\r\n&lt;\/class&gt;<\/pre>\n<p>The exception stack trace I was seeing was something like this:<\/p>\n<p><code \/><\/p>\n<pre>2007-12-31 14:15:09,888 ERROR [Thread-14] def.AbstractFlushingEventListener (AbstractFlushingEventListener.java:301)\r\n\r\n- Could not synchronize database state with session\r\norg.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update\r\nat org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)\r\nat org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)\r\nat org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)\r\nat org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)\r\nat org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)\r\nat org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)\r\nat org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)\r\n....\r\nCaused by: java.sql.BatchUpdateException: Duplicate key or integrity constraint\r\nviolation message from server: \"Duplicate entry '2007-12-31-stattype' for key 1\"\r\nat com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1492)\r\nat com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)\r\nat org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)\r\nat org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)\r\n... 57 more<\/pre>\n<p>I ended up turning on the mysql logging (the <code>log<\/code> setting in the my.ini file, which logs all sql statements mysql makes) to see what was happening.<\/p>\n<p>Basically, I was looking to see if an entry in the stat table existed; if it did, increment and update, if it did not, insert.  And the insert was always happening, so the entry was not found--it did exist because mysql threw the 'integrity constraint' exception.<\/p>\n<p>The cause of the issue was the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/datetime.html\">date<\/a> type of stat_date and the fact that I incorrectly mapped it to <code>java.util.Date<\/code>.  It really should have been mapped to <code>java.sql.Date<\/code>.  How this worked in mysql4 is beyond me, but it did.  Changing the hibernate dialect to mysql5 had no impact.<\/p>\n<p>[tags]mysql upgrade,hibernate[\/tags]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I ran into a crazy error last week. One of my clients was upgrading from MySQL4 to MySQL5. The application in question was using Hibernate 3.2. Here&#8217;s the table structure, [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,5,28],"tags":[],"class_list":["post-453","post","type-post","status-publish","format-standard","hentry","category-databases","category-java","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/comments?post=453"}],"version-history":[{"count":0,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/posts\/453\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/media?parent=453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/categories?post=453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mooreds.com\/wordpress\/wp-json\/wp\/v2\/tags?post=453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}