MySQL Bugs: #9407: Linux binary: rollback from JDBC does not work

Bug #9407	Linux binary: rollback from JDBC does not work
Submitted:	25 Mar 2005 21:43	Modified:	26 Mar 2005 11:38
Reporter:	Frank Grimes	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.x and 5.x	OS:	Linux (Linux (Red Hat flavors))
Assigned to:		CPU Architecture:	Any

Description:
Believe me, i know how impossible this seems...
i've installed:
mysql-standard-4.0.24-pc-linux-gnu-i686.tar.gz
mysql-standard-4.1.10-pc-linux-gnu-i686.tar.gz
mysql-standard-4.1.10a-pc-linux-gnu-i686.tar.gz
mysql-standard-5.0.2-alpha-pc-linux-i686.tar.gz
all on the same Fedora Core 3 linux server (one at a time for testing this). i've installed 4.1.10 on an additional separate Fedora Core 3 linux server. i've also installed 4.1.10 on a Red Hat Enterprise Linux WS 3 server.

The install has consisted of a minimal install from the binary, i.e. unzipped/untar'd the file into /usr/local/mysql, run scripts/mysql_install_db, and thats about it. i've started up mysql with bin/mysqld_safe. Everything seems to run just fine. i'm connecting to MySQL from a Java VM using mysql-connector-java-3.1.6-bin.jar.

i use simple Java to get a JDBC connection. i call Connection.setAutoCommit(false). Then i insert a row into a table. Lastly i call Connection.rollback(), however the insert is *not* rolled back and is visible in the table with a query when connecting directly to mysql (via bin/mysql -u root). This seems utterly impossible, but every time i install MySQL on Linux as described above, and i run this simple test, the table insert does *not* get rolled back.

i have tried creating a my.cnf file with an entry "init_connect='SET AUTOCOMMIT=0'" but this does not change anything. i've tried issueing SET AUTOCOMMIT=0 by hand from the JDBC Connection, still the insert does not get rolled back.

Lastly, i pointed the same test at a 4.1.10 Windows install and it worked fine. (This test was run from the same place all the others was run, using the same JDBC driver.) So i'm at a bit of a loss left guessing it is something with the Linux binary.

How to repeat:
- Install mysql-standard-4.1.10 on Fedora Core 3 or Red Had Enterprise Server WS 3.
- Create a simple JDBC app (will attach) that turns off auto-commit, creates a table, inserts a row, rollback the transaction.
- Connect directly to MySQL and query on the table you created to see if the insert occured.

Suggested fix:
i seriously don't know what is causing this, but it seems to have nothing to do with the JDBC driver and it only happens on the Linux binary installs, not the Windows installs.

Note that this happens even on tables that already exist.  The suggestion of creating a table with the same JDBC connection was merely for convenience.

Really simple test i've beenusing (more or less).

Attachment: MySQLLinuxTest.java (application/octet-stream, text), 1.74 KiB.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Your create table doesn't specify the storage engine so it relies on the default one:

 String sql = "create table asset (id bigint not null auto_increment, original_filename varchar(250), primary key (id))";

If the default storage engine is MyISAM ROLLBACK can't work.

That was the problem.  And searching the documentation its all there.  Using a Windows install, the default storage engine is InnoDB whereas the Linux install uses MyISAM.  Being new to MySQL but familiar with RDMSs, i simply ran through the README and INSTALL_BINARY files in the install directory rather than reading the full doc-set.  Nothing of this is mentioned in either of those files.  i'm surprised that the default engine in a non-Windows install is for a non-transactional engine.  Thats kind of rough for idiots like me.  i'll open a doc bug suggesting that some mention of the transactional storage engine be placed in the INSTALL_BINARY file which i think would have alleviated this for me.  But since i seem to be the only person in the wolrd who has had this problem, it might not be that big of a deal.

Thanks very much for the help.