Hi,

I'm having problems with this simple piece of code (with transactions) that just inserts an entry in the USER table of a MySQL database with InnoDB as table type:

Session session = null;
Transaction transaction = null;
try {
// Get the session with the DB
session = HibernateSessionFactory.getSessionFactory().getCurrentSession();

// Build the UserDTO.
User userDTO = new UserDTO(.........);

// Begin the transaction within the session.
transaction = session.beginTransaction();

// Insert the DTO of the user.
session.save(userDTO);

// Commit the transaction.
transaction.commit();
} catch (Exception e) {
// Rollback the transaction.
transaction.rollback();
throw e;
}

Hibernate version: 3.2.5

Full stack trace of any exception that occurs: no exception thwown

Name and version of the database you are using:MySQL 5.0.27 (type of tables: InnoDB, as they're the ones that allow transactional operations in MySQL)

These are the logs of the INSERT statement that doesn't make effective in the database:

14:58:54,906 [http-8080-1] DEBUG org.hibernate.jdbc.AbstractBatcher.log{1}:401 - insert into webmail.USER (LANGUAGE, COLOR, EMAIL, USER, PASSWORD, NAME, SURNAME, UNUSED1, UNUSED2) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into webmail.USER (LANGUAGE, COLOR, EMAIL, USER, PASSWORD, NAME, SURNAME, UNUSED1, UNUSED2) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
14:58:54,915 [http-8080-1] DEBUG org.hibernate.jdbc.AbstractBatcher.getPreparedStatement{1}:484 - preparing statement
14:58:54,940 [http-8080-1] DEBUG org.hibernate.persister.entity.AbstractEntityPersister.dehydrate{1}:1992 - Dehydrating entity: [com.dto.webmail.User#<null>]
14:58:54,941 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '1' to parameter: 1
14:58:54,942 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '1' to parameter: 2
14:58:54,942 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding 'ttth@yahoo.com' to parameter: 3
14:58:54,943 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding 'ttth' to parameter: 4
14:58:54,949 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '111111' to parameter: 5
14:58:54,949 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '' to parameter: 6
14:58:54,952 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '' to parameter: 7
14:58:54,953 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '' to parameter: 8
14:58:54,954 [http-8080-1] DEBUG org.hibernate.type.NullableType.nullSafeSet{1}:133 - binding '' to parameter: 9
14:58:55,257 [http-8080-1] DEBUG org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity{1}:37 - Natively generated identity: 4
14:58:55,258 [http-8080-1] DEBUG org.hibernate.jdbc.AbstractBatcher.logClosePreparedStatement{1}:374 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
14:58:55,259 [http-8080-1] DEBUG org.hibernate.jdbc.AbstractBatcher.closePreparedStatement{1}:533 - closing statement
14:58:55,259 [http-8080-1] DEBUG com.data.dao.webmail.UserDAO.save{1}:27 - save successful
14:58:56,685 [http-8080-1] DEBUG org.hibernate.transaction.JDBCTransaction.commit{1}:103 - commit
14:58:56,685 [http-8080-1] DEBUG org.hibernate.impl.SessionImpl.managedFlush{1}:337 - automatically flushing session
14:58:56,686 [http-8080-1] DEBUG org.hibernate.jdbc.JDBCContext.beforeTransactionCompletion{1}:201 - before transaction completion
14:58:56,686 [http-8080-1] DEBUG org.hibernate.impl.SessionImpl.beforeTransactionCompletion{1}:393 - before transaction completion
14:58:56,716 [http-8080-1] DEBUG org.hibernate.transaction.JDBCTransaction.commit{1}:116 - committed JDBC Connection
14:58:56,717 [http-8080-1] DEBUG org.hibernate.jdbc.JDBCContext.afterTransactionCompletion{1}:215 - after transaction completion
14:58:56,719 [http-8080-1] DEBUG org.hibernate.jdbc.ConnectionManager.aggressiveRelease{1}:404 - aggressively releasing JDBC connection
14:58:56,720 [http-8080-1] DEBUG org.hibernate.jdbc.ConnectionManager.closeConnection{1}:441 - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
14:58:56,722 [http-8080-1] DEBUG com.mchange.v2.resourcepool.BasicResourcePool.trace{1}:1644 - trace com.mchange.v2.resourcepool.BasicResourcePool@ed9f47 [managed: 10, unused: 8, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1ff2e1b)
14:58:56,723 [http-8080-1] DEBUG org.hibernate.impl.SessionImpl.afterTransactionCompletion{1}:422 - after transaction completion
14:58:56,724 [http-8080-1] DEBUG org.hibernate.impl.SessionImpl.managedClose{1}:353 - automatically closing session
14:58:56,725 [http-8080-1] DEBUG org.hibernate.impl.SessionImpl.close{1}:273 - closing session
14:58:56,726 [http-8080-1] DEBUG org.hibernate.jdbc.ConnectionManager.cleanup{1}:375 - connection already null in cleanup : no action

As you may see, an ID is assigned to the "inserted" user, but when I query the USER table, no rows were added.

My hibernate.cfg.xml is this:
<hibernate-configuration>

<session-factory>

<!-- Properties-->
<property name="dialect">
org.hibernate.dialect.MySQL5InnoDBDialect
</property>
<property name="connection.url">
jdbc:mysql://127.0.0.1/webmail
</property>
<property name="connection.username">webmail</property>
<property name="connection.password">webmail</property>
<property name="connection.driver_class">
com.mysql.jdbc.Driver
</property>

<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">2</property>

<!-- Enable Hibernate's automatic session context management -->
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="current_session_context_class">thread</property>

<!-- Disable the second-level cache -->
<property name="cache.provider_class">
org.hibernate.cache.NoCacheProvider
</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>

<!-- configuration pool via c3p0-->
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">100</property> <!-- seconds -->

<!-- Drop and re-create the database schema on startup
<property name="hbm2ddl.auto">create</property>-->

<!-- Queries -->
<mapping
resource="com/newfacet/data/dao/webmail/user/User.hbm.xml" />

<!-- Mapping of the tables in the DB to DTO objects in JAVA -->
<mapping resource="com/newfacet/dto/webmail/Language.hbm.xml" />
<mapping resource="com/newfacet/dto/webmail/User.hbm.xml" />
<mapping resource="com/newfacet/dto/webmail/Color.hbm.xml" />
<mapping resource="com/newfacet/dto/webmail/Alias.hbm.xml" />
</session-factory>

Do you have any clue of what may be happening? May it be a database issue (runnig the INSERT statement generated by Hibernate directly in the MYSQL command line works just fine)? May I need to configure something else in Hibernate?
I've tries flushing and closing the session explicitly with the same results.

Thanks in advance,
Carlos.[/b]

Is it possible you are calling this transaction within another transaction? If there is a transaction hibernate doesn't know about, it sometimes gets confused and never commits the session.

Thanks a lot piccione99 !. It was not exactly the problem, but you gave me an important hint for finding the error: I was using different sessions when beginning the transaction and when saving the object. I didn't paste the exact code, here it is what I'm doing (and I'm doing it wrong):

// Get the session with the DB.
session = HibernateSessionFactory.getSessionFactory().getCurrentSession();

// Begin the transaction within the session.
transaction = session.beginTransaction();

User userDTO = new UserDTO(blablabla);

new UserDAO().save(userDTO)

transaction.commit();

Where UserDAO is a class (generated automatically by the MyEclipse hibernate reverse-engineering) that extends BaseHibernateDAO and whose save() method is like this:

public void save(User transientInstance) {
log.debug("saving User instance");
try {
getSession().save(transientInstance);
log.debug("save successful");
} catch (RuntimeException re) {
log.error("save failed", re);
throw re;
}
}

The getSession() method of BaseHibernateDAO is returning a different session than the one I created the transaction from, and that's why the commit wasn't really committing.

Thanks a lot,
Carlos.