Newbie question: MySQL commit / rollback

Anon

I noticed the MySQL functions don't have the commit / rollback functions available for other DBMSs (e.g. odbc_commit() and odbc_rollback()).

Are these going to be added in the future?
(i.e. mysql_commit() & mysql_rollback()).

Until that day, is there an alternate way of starting / comitting / rollingback DB changes
when working with transaction-safe tables in MySQL (e.g. Gemini table type)?

Thanks,
Jacek

Anon

MySQL doesn't have transactions at this time. I don't think that they are planning on implementing it since the database was written for a specific purpose and that purpose was SELECT speed. If you need transactions then you should look at PostgreSQL.

Anon

Sorry, but you are wrong. MySQLL DOES have full blown transactions as of version 3.23.23 or so. This is implemented via transaction-safe table types (i.e. Inno-DB and Gemini).

The NuShpere distribution of MySQL (www.nusphere.com) is all geared around promoting the Gemini table type...

So, how do you do COMMIT/ROLLBACK with the transaction-safe tables in MySQL?

If you can't I will need to connect via ODBC to it, since that already has odbc_commit and odbc_rollback...

Jacek

Anon

Very sorry, I guess I haven't been keeping up with MySQL as much as I should to answer a question like this.

Anon

I just found out what you are talking about. The Gemini table structure was added to MySQL by NuSphere and so it's not a part of the original MySQL code base. And also the extensions added by NuSphere aren't Open Source like MySQL is.

MySQL - www.mysql.com
NuSphere - www.mysql.org

Very confusing if you are trying to support something that has the same name but is actually 2 different products.

At least Red Hat and Great Bridge are giving there PostgreSQL products different names.

Anon

Yeah, I also just found out that Gemini was NOT open-source (I thought it was part of the GPLed original MySQL source).

Looks like they're trying to pull a fast one on the original MySQL team and it really stinks.

Oh well, I guess I'll stick with SQL Anywhere for the time being :-)

Jacek

Anon

The gemini tables might not be open source, but InnoDB tables are. I would suggest hitting MySQL's site it looking up the new InnoDB table type in the documentation. It is part of the --max binary installation. I don't know much about it, but I have it installed and I am starting to toy with it...

Hope this helps!
James

Anon

Well, I looked at the Inno-Db stuff but a lot of it seem so be still in beta stage so to speak and not fully integrated with the rest of MySQL (e.g. you can't drop the DB, you have to drop each table individually).

Also, right off the bat they start talking about tablespaces and their maintenance, etc. and that's really one thing I don't want to do for an embedded app (reminds me of the nightmare that administering Oracle is).

An embdedded DB has to be self-running, zero-maintenance, self-recovering.

MySQL + Gemini seemed to fall into that category, but InnoDB might need some more work. I will look at it though, undoubtedly it's the direction the MYSQL team will go in the future...

Jacek