Hi,

Weird problem I haven't seen before: I can insert and delete rows without problem when the MySQL table is MyISAM. However, when I change the storage engine to InnoDB, Hibernate can only insert, but not delete.

The only way I've found to programatically delete the rows is to use straight SQL directly on the Connection.

I've replicated this on two MySQL databases (5.0.45 and 5.0.27). I've used two different drivers (5.0.4 and 5.1.6).

I'm using Hibernate 3.2.2.ga.

The table is dead simple with no relationships and one autogenerated key.

Any ideas?

Michael

I wrote a very simple main method program:

Player player2 = new Player();
player2.setId(2);
player2.setName("aName");

Transaction t = HibernateUtil.beginTransaction();
HibernateUtil.delete(player2);
t.commit();
HibernateUtil.flush();

The database has a Player table with one row with primary key = 2. This program cannot delete the row when the table is InnoDB.

I wrote another test that tries to modify a record inside a transcation:

player2.setName("newName");
Transaction t = HibernateUtil.getSession().beginTransaction();
HibernateUtil.saveOrUpdate(player2);
t.commit();
HibernateUtil.flush();
HibernateUtil.closeSession();

The fourth line throws a TransactionException: Transaction not successfully started.

So it appears there's a problem with starting the transaction, and this is probably the root problem.

I figured it out. The problem goes away when I comment out the the line in the hibernate.properties file that sets the c3p0.max_size.

Leaving all else equal, a hibernate.properties file with

hibernate.c3p0.max_size 20 will cause my tests to fail. On the other hand, when that line is commented out, all the tests pass.

Very strange. Anybody on this forum know why?


****

To future Googlers with the same problem: award me some points if this solution helped you!

Actually after further testing, I have discovered the real problem: I needed to flush() then commit() rather than commit() then flush().