Hibernate/mysql - Batch update returned unexpected row count and ascii characters

Question

I was facing a problem deleting entity read by hibernate. A simple call like load entity from database and immediately delete the entity without any change was failing with the exception

org.springframework.orm.hibernate4.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I finally traced the error to an errant string which had following ascii characters - 239 (0xef), 191 (0xbf) and 189 (0xbd). After removing these characters, everything works properly. Since hibernate delete call matches all the fields in the where clause while deleting, call was failing when these characters are present in the string.

It seems like there is issue with character encoding in our setup somewhere as entities/columns returned by hibernate can not be used as part of where clause in criteria - or something is getting lost in translation.

What should I do - mysql setup or hibernate setup so that these issues can be avoided in future

Answer 1

I remember I used to call SET CHARACTER SET 'utf-8'` at the beginning of each mysql session. That was in PHP. Now Hibernate / JDBC should handle that, but still you need to set it on both server and client.

See the server config and the JDBC config.

You need to have the database, the connection, and the client encoding all aligned.
Edit: Also note that the tables and columns may have their own encoding, so check that in the INFORMATION_SCHEMA.

Also, I'd suggest also to watch the MySQL server logs which I belive would tell you they failed performing the query, or perhaps warn on the invalid value.