MySql in WinXP and Mac OS X

Question

Hello Folks,

I am facing this bizarre problem. I am coding on my machine in Win XP and then moving all the code to Mac OS X machine. Running it using the jars from XP machine. I am coding in java and using hibernate for database processing.

Somehow I am not able to insert one row, while the keys are generated using the increment generator class. I also tried native but same error on Mac.

here is the debug mode. Now, I know the error as a key violation, but the same doesn't occur in WinXP. I truncate my database before running code. I think their is some generator class dependency in Mac. Or some other jars?

And here are some specifics:

• Mac OS X 10.5 (x86_64)
• MYSQL 5.1.35 Community Server
• mysql-connector-java-5.1.7-bin.jar JDBC driver
• Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_16-b06-284)
• Java HotSpot(TM) Client VM (build 1.5.0_16-133, mixed mode, sharing)

please advise

2009-06-20 18:43:01,230 DEBUG [org.hibernate.type.IntegerType] - binding '11266' to parameter: 2
2009-06-20 18:43:01,230 DEBUG [org.hibernate.type.IntegerType] - binding '332' to parameter: 3
2009-06-20 18:43:01,281 DEBUG [org.hibernate.type.IntegerType] - binding '6' to parameter: 4
2009-06-20 18:43:01,281 DEBUG [org.hibernate.persister.entity.AbstractEntityPersister] - Inserting entity: [org.joshua.hibernate.dto.Pagedatecounts#289]
2009-06-20 18:43:01,281 DEBUG [org.hibernate.jdbc.AbstractBatcher] - Executing batch size: 1
2009-06-20 18:43:01,288 DEBUG [org.hibernate.jdbc.AbstractBatcher] - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2009-06-20 18:43:01,288 DEBUG [org.hibernate.jdbc.AbstractBatcher] - closing statement
2009-06-20 18:43:01,289 DEBUG [org.hibernate.util.JDBCExceptionReporter] - Could not execute JDBC batch update [insert into wiki.page (langId, titleId, totalCounts, id) values (?, ?, ?, ?)]
java.sql.BatchUpdateException: Duplicate entry '1-11266' for key 'Page_lang_title'
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
    at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:222)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2224)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.joshua.hibernate.dao.PageDAO.batchSave(PageDAO.java:70)
    at org.joshua.businesservice.PageServiceImpl.savePageDateCountBatch(PageServiceImpl.java:61)
    at org.joshua.wikidumps.DumpHandler.endElement(DumpHandler.java:192)
    at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
    at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
    at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
    at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
    at javax.xml.parsers.SAXParser.parse(SAXParser.java:375)
    at javax.xml.parsers.SAXParser.parse(SAXParser.java:176)
    at org.joshua.wikidumps.PagesByNumberOfRecentEdits.execute(PagesByNumberOfRecentEdits.java:107)
    at org.joshua.wikidumps.PagesByNumberOfRecentEdits_ur.main(PagesByNumberOfRecentEdits_ur.java:19)
2009-06-20 18:43:01,290 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1062, SQLState: 23000
2009-06-20 18:43:01,290 ERROR [org.hibernate.util.JDBCExceptionReporter] - Duplicate entry '1-11266' for key 'Page_lang_title'
2009-06-20 18:43:01,290 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

Answer 1

There is very little to go on, but this is what I can make of it:

1. The unique key that is being violated is a combined key on the title and language column. (judging from the name (Page_lang_title, where lang_id and title_id are columns name) and the value of the key (1-11266, actually a combination of two values) that is shown in the stacktrace).
2. Your maintaining a page (request?) count for a wiki page. This is of course prone to concurrency problems, as multiple threads (requests) are accessing the same page at the same time.
3. Your probably doing a batch update (or something) and you've hit the same <lang_id,title_id> combination twice. Your logic is something like "from Page where page_id=? and lang_id=?" since your running a batch in the same transaction and using hibernate for after transaction synchronization, your insert on the table is only processed after you've committed the transaction and thus you try to insert the same combination of lang_id and page_id twice.

Now there is a few things that you can do:

1. Make sure that Hibernate's AUTO_FLUSH mode is working correctly, so the session is flushed whenever a new entity needs to be inserted just before a query is made on this table. To make this work as intended, you'll have to make sure that Hibernate is aware of transactions that are started.
2. Maintain your own cache of Pagedatecount's that you've created per transaction and query that before you check the database with Hibernate.

---

Come to think of it: Isn't the Pagedatecount entity missing a date column? It is not being inserted it seems.