OS: Win2k3
JVM: 1.5.0_11-b03
Hibernate: 3.2.4sp1
JConnector: 5.0.4
Database: MySQL 5.0.36 Enterprise

I have a database model which uses a sharded schema. This means that I have a database per account model with multiple databases residing on a physical instance of MySQL. This model is used to acheive scalability in that I have multiple physical machines all running a subset of the accounts to form a complete dataset. Horizontal Scalability...

I have a SessionFactory for each physical database server. The initial connection is made with no default catalog selected:

jdbc:mysq://192.168.0.1/

In the code which returns my hibernate Session objects I always call the following before returning the session:

Session.connection().setCatalog("account_db")

where "account_db" is the actual account which should be used for all hibernate queries. This seems to work 98% of the time, but every once in a while it seems like hibernate will modify the setting and go back to "no database selected" state.

I am using a c3p0 connection pool.

Anyone have any idea what might be causing the issue? Is this something that isn't good practice with Hibernate because of certain underlying functionality? It seems like the right solution... because the other option is to have a SessionFactory per account which would mean i have hundreds or thousands of SessionFactory objects all with their own connection pools and physical connections to the DB.

Any insight is greatly appreciated.
Thanks,
Geoff

anyone from the Hibernate team care to help me out here?

thanks,
Geoff

Perhaps this is happening 2% of the time because the session has somehow been closed? Then the new session would not have the database set.


How are you opening the sessions, and handling the setCatalog?

Are you using any other frameworks, like Spring?

I return all my Session objects through a HibernateUtil which has private access to the SessionFactory to ensure no external access.

Before the Session object is returned it has the Session.connection().setCatalog() called to point to the correct schema.

I have even resorted to calling setCatalog() before starting new Transactions on a session... aside for more traffic to the DB it doesn't appear to have changed anything.

Thanks,
Geoff