I open a new thread because I want you to read the solution directly instead of digging for it in another thread which has lots of "noise" in it.

My problem was that although I rolled back a transaction in code (tx.rollback), the data has not been rolled back in my MySQL DB.

Then I figured out that MySQL 4.0 uses MyISAM tables by default. Don't ask me what MyISAM means. To me it means that these tables are not capable of rolling back a transaction. They seem to be doing autocommit.

A MySQL table has to be of table type InnoDB in order to be rollback capable. You can convert any MySQL table to InnoDB. But I was looking for a more general solution. I wanted to set the default table type to InnoDB. So I would never have to worry again.

It seems that the only way to do this is to use a start the database with a special start parameter: C:/mysql/bin/mysqld-max-nt.exe --default-table-type=InnoDB

If you start the MySQL DB server like this, every normally created table will be of type InnoDB and thus rollback capable. I think thats what most of the serious database people want.

First I wanted to use a Windows service. But for that purpose I needed to start the service with that parameter (--default-table-type=InnoDB). I can enter a parameter in Windows XP Professional, but when the service starts automatically (when the PC is rebooted), the service is started WITHOUT that parameter. That meant MyISAM default table type!

Then I tried to modify the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySql\ImagePath

But I could not set the parameter successfully in the registry. When I did, the service did not start properly.

So my solution now is to use a .bat file which I named startMySQL.bat. It contains a single line: C:/mysql/bin/mysqld-max-nt.exe --default-table-type=InnoDB

To start the MySQL DB server, I just double click on that .bat file. The server starts after a few seconds. Then you can kill the DOS box. Nevertheless the server remains up and running in the background.

This solution is not so nice like a Windows service. So if anyone knows how to achieve the same with a Windows service (startind the MySQL DB with default table type InnoDB), please tell me.

I hope I have contributed to solve this problem.

This is really a MySQL setup question and I think you will get a quicker answer on one of the MySQL forums.

Christian, 99% of my motivation was to provide a solution and not to ask a question.

Verstehe ich dich richtig, dass du meinst, ich h

I only read the first half, sorry. If you can summarize it in a short paragraph, post it to the Wiki (there is a page about database issues in the community area).

I think that my thread is in a good place here. You can post it on wiki if you want. I wount do it.

I don't agree that the way you start mysql changes the way it handles ablility to commit. I am using V4.15 of mysql, and have no problems with rollback. I tested so rollback tonight in fact using hibernate 2.11.

Major thing was in create table scripts, to specify the "INNODB" table type.

I am using fabforce dbdesigner which is a free gui (not bad) that allows one to create tables, and synch w/ mysql. There's a pulldown to change from "MYISAM" to INNODB.

If you don't want to use the tool, then i believe table type is specified in the "create table blah" syntax.

--James

James, I didn't wanted to worry about table type when creating tables by Schmemaexport. And I don't wanted to modify my schmemaexport target just because I am using MySQL. I wanted to keep that Ant target independant of the database which I use. Thats why I solved it in this way. I'm not saying its the only solution. But I think its a good solution for everyone who always wants to use InnoDB table type by default.

I use DBVisualizer 4.0.4 as a database tool. It is free too.

Didn't realize the scope of what you were doing.

sorry!
? though
So, how do you compensate for different key generation techniques used by different databases?



James