Another "Mysql Doesn't Do that?!" Moment
http://adam.blog.heroku.com/past/2008/9/3/ddl_transactions/
I think the real reason there was resistance to this change is that MySQL doesn't support DDL transactions; and wrapping migrations in a transaction is mostly pointless without this feature. Since MySQL doesn't support it, most Rubyists don't know what DDL transactions are - so here's a quick primer on this incredibly useful and certainly underrated feature.
DDL = data definition language, also known as the schema. DDL commands include CREATE TABLE, DROP TABLE, ALTER TABLE, and CREATE INDEX. DDL transaction support means that the following sequence of SQL commands will not modify your database
I never even thought of that. I was fairly confident in my vision of Postgres getting faster and faster, and Mysql become a "real" database... that the two systems were converging on something resembling a common speed/feature set. But things like this still occasionally rear their ugly heads and I'm happy to be a Postgres user.
Trackbacks
Use the following link to trackback from your own site:
http://journal.dedasys.com/trackbacks?article_id=2108
about 3 hours later:
Hi!
The reason for the lack of DDL transactions is that a storage engine works in conjunction with an external data store (called an FRM). It makes it damn hard to implement this feature (and to be fair, many databases do not).
When we have been refactoring for Drizzle we have pushed the FRM out the door and made the engine the primary store for its meta data. So now it is just a matter of one of the engine developers deciding to implement this.
Cheers, -Brian
about 13 hours later:
Oh well, another post just asking for a Postgres vs. MySQL flamewar. But anyway:
I would switch to Postgres in a minute if I found Postgres administration even half as easy as MySQL administration. More importantly though, I would need a suitable synchronization solution which allows realtime synchronization of two or more databases to facilitate loadbalancing (on pure read accesses) and failover. All solutions for Postgres I found either are extremely complex to set up, don't allow two-way synchronization (for resynchronization of old master before failback) or require that the receiving database server is offline to load the synchronization logs. Mostly two or three of those apply.
Regarding MySQL there is one feature I really miss on all transactional storage engines I looked at: Full text searches (currently to the best of my knowledge still only available with old MyISAM).
Neither RDBMS is perfect, not even for the applications I use. But currently MySQL is a better fit for my purposes.
Still, DDL transactions are most certainly a very useful feature.
about 16 hours later:
Sven, Your comment was a fair and well balanced response.
THis is the internet. We have NO ROOM FOR REASONABLE PEOPLE HERE! SO CUT IT OUT!
If you don't have some unusually sarcastic cutting remark, or a batantly stupid thingh to say, DON'T SAY ANYTHING AT ALL!
ABOVE ALL NEVER NEVER NEVER ADMIT THAT BOTH SIDES OF A POSITION MAY HAVE MERIT!
UNREASONABLE FANATICISM IS THE ONLY ACCEPTABLE FORM OF RESPONSE HERE!
WE NEED PAGE VIEWS DAMNMIT! CONTROVERSY, NOT EDUCATION!
MY GAWD MAN! THINK OF THE ADVERTISING REVENUE!
OH THE HUMANITY!
about 20 hours later:
Sven, actually what caused me to write this was that
Lately, I have noticed fewer and fewer big differences between the two, as I mentioned in the post, meaning that there's less space to get all excited about "but this one's better!".
This problem cost me a few hours researching it, thus increasing my irritation levels. I thought to myself "but InnoDB does transactions just fine!", without realizing that no it does not when it comes to the database schema itself.
Hopefully, as Brian says, someone will quickly fix the problem in the near future.