Java / EJB 3.0 CMT / JTA / MySQL / Hibernate - Transaction not rolling back

Question

I´m loosing my sanity with EJB CMT about a week and can't find a solution! Any enlightenment will be appreciated...

I´m just trying to create a CMT EJB, do some success database operation, and fails (SQLException) on the last one. I expected to rollback all operations, but it's not happening....

My Environment:

MySQL 5.5
Glassfish 3.0.1
Hibernate

My EJB:

@Stateless(name = "UpdaterFacade", mappedName = "ejb/UpdaterFacade")
@TransactionManagement(TransactionManagementType.CONTAINER)
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
@Interceptors(DefaultFacadeInterceptor.class)
public class UpdaterFacade implements UpdaterFacadeRemote {
  @Resource
  private EJBContext context;

  @PersistenceContext(name = "EJB3DS")
  private EntityManager em;

  @Override
  public void updatePluginDatabase() throws Exception {
    try {
      em.createNativeQuery("UPDATE `core_systemproperties` SET `value` = '0' WHERE `property` = 'CMTTest';").executeUpdate();
      em.createNativeQuery("UPDATE `core_systemproperties` SET `value` = '1' WHERE `property` = 'CMTTest';").executeUpdate();
      em.createNativeQuery("UPDATE `core_systemproperties` SET `value` = '2' WHERE `property` = 'CMTTest';").executeUpdate();
      em.createNativeQuery("UPDATE `core_systemproperties` SET `value` = '3' WHERE `property` = 'CMTTest';").executeUpdate();

      em.createNativeQuery("ALTER TABLE `itemprices` DROP FOREIGN KEY `fk_itemprices_itemcodes1`;").executeUpdate();

      em.createNativeQuery("UPDATE `core_systemproperties` SET `value` = '4' WHERE `property` = 'CMTTest';").executeUpdate();
      em.createNativeQuery("RENAME TABLE `foo` TO `noo`;").executeUpdate();
    } catch (Exception e) {
      // throw new DatabaseCriticalException("ERP_000048", null, e);
      context.setRollbackOnly();
    }
  }
}

Except by the last line where I try to rename table 'foo', which doesn't exists, the code executes successfully. So injections, SQL Connection, DataBase are working (maybe misconfigured).

I notice when debugging line by line (and querying database line-by-line with another program), before the "DROP FOREIGN" statement nothing was changed in table, after that, the previous statements are committed (visible by other connection/program). Before it reached the end of the method!

When EJB returns, my "CMTTest" property has value '8' and my FOREING KEY was DROPPED, nothing was RolledBack... =\

I already tried to throw an Exception with "@ApplicationException(rollback = true)", later I changed to explicit call "setRollbackOnly()" without any effect... Actually I don't believe this is the problem, otherwise nothing would be committed before EJB ends it's end. Somehow I'm failing in configuration. Maybe some kind of autocommit on CMT? Looks desperate madman...

My persistence.xml

<persistence-unit name="EJB3DS" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>jdbc/ERPDS</jta-data-source>
    <jar-file>CoreClient.jar</jar-file>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true" />
    </properties>
</persistence-unit>

My GlassFish Pool is configured as:

Edit JDBC Connection Pool

Edit JDBC Connection Pool

Edit JDBC Connection Pool Advanced Attributes

Edit JDBC Connection Pool Advanced Attributes

Edit JDBC Connection Pool Properties

Edit JDBC Connection Pool Properties

Don't know what else could be important to say! Just thanks in advance any kind of help or tip...

I'll come back post the answer if I found it my self, but by now I´m completely lost!

Thank you all..

Did you configure your MySQL tables to by of type MyISAM or InnoDB? The first isn't transactional, the second is. If your table is not transactional, you might have weird behavior. Can you confirm it's InnoDB? — ewernli, Oct 23 '13 at 19:16
To be honest, I didn't ever remember about that!!! But I reviewed each one and Yes! All are InnoDB "version 10". - Thanks for your reply, just got excited about the possibility to solve and forgot to thanks! — Rodrigo Leitão, Oct 23 '13 at 19:37
I think this may because you are using a non-transactional engine in MySQL, try with another database like `PostgreSQL`, but changing the MySQL database engine would solve the issue buddy :) — , Oct 23 '13 at 20:36
@user2511414 I checked that as ewernli has suggested, InnoDB is transactional, it works until "DROP FOREIGN" statement... but as Gabriel Aramburu answered below, and unfortunately seems to be right, MySQL does not suport rollback on DDL statements. I made a little research, based on your suggestion, and it could be a solution since PostgreSQL supports this kind of rollback I need. The problem is the cost of migration on large app, and I'm just hired to dev the update module. But anyway, thanks for your reply, I'll keep this option in mind too. — Rodrigo Leitão, Oct 24 '13 at 01:01

score 2 · Accepted Answer · answered Oct 23 '13 at 21:36

2

When you execute the ALTER TABLE statement the db engine implicity commits the transaction.

answered Oct 23 '13 at 21:36

Gabriel Aramburu

2,951
2
16
20

I refuse to believe at first but you are right, I found this: http://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql It lists which database supports, and which doesn't. Thanks for your reply and, not solve, but end my problem ;) – Rodrigo Leitão Oct 24 '13 at 01:04
Oh, that's evil! I would have expected DDL statements to simply fail if they are not supported in a transaction. – ewernli Oct 24 '13 at 06:16

Java / EJB 3.0 CMT / JTA / MySQL / Hibernate - Transaction not rolling back

1 Answers1