I'm using c3p0 (0.9.1.1) and hibernate with java and ReplicationDriver (for mysql). I have one master and one slave. On certain read-only queries, I want to hit the slave instead of the master. I do this by session.connection().setReadOnly(true) right before I do beginTransaction().
The problem is with a query like this on a read-only connection hitting the slave:
select SQL_CALC_FOUND_ROWS distinct col1 from table1 where ...
The query results return FOUND_ROWS = 1, but the result list has size 0. Stopping the app and looking at the slave then, I see that there should be no results. Although I'm having trouble tracking down why FOUND_ROWS returns 1.
The problem goes away when I don't use c3p0. I haven't had much luck tracking this down. If there's any more information I can provide, please let me know.