Slashdot Mirror
MySQL 5.0 Now Available for Production Use
chicagoan writes "MySQL AB today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. The major new version delivers advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines. Over 30 enterprise platform and tool vendors have also expressed enthusiastic support for the new release of the world's most popular open source database."
It would be cool if someone knowledgeable could check the old MySQL Gotchas list and see how many have been fixed in 5.0. My hope is, nearly all of them.
-- Ed Avis ed@membled.com
With all due respect, SQL2K has been one of the most stable databases I've ever worked with. Sybase was a close second, Oracle was fine once you got it installed. Say what you will about their consumer products, but MS can make some damn fine products *when it wants to*.
And if its in your code, the query executes using the resources of the machine running your code, as opposed to the resources of that (usually) bigged database server.
I've been waiting for years for stored procedures, triggers, and... ah. Wait a minute. No, actually, I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.
Well. Anyway. Now all the little shops that have been making excuses about why not to use MySQL can now start using it.
(In fairness, actually, yes, the MySQL gotcha's page scares me, too)
fifth sigma, inc.
I initially started using MySQL because it was faster than PostgreSQL.
But now with the involvement of SCO and Oracle in this little project I am looking to write future applications on PostgreSQL or SQLlite. I cannot see any good coming from Oracle's involvement with Innobase or SCO involvement with MySQL.
I could understand Oracle becoming more involved with PostgreSQL, because I can see PostgreSQL being more of a stepping stone to Oracle.
SCO well their just SCO, and I don't see them doing anything but creating mischief within the OS community.
Any word on when they are planning to fix this? With this careless disregard for data integrity, it's hard for me to take MySQL seriously.
___
If you think big enough, you'll never have to do it.
I stopped using MySQL as my primary RDBMS in 2000 (I still use it when apps require it, but I almost never program for it.
When I started using PostgreSQL 6.5, I noticed that it was *far* harder to use than MySQL. It had a *huge* learning curve and was missing obvious functionality such as alter table drop column. But it provided better data integrity checking than MySQL. So for the next two years, I would prototype databases in MySQL before moving them over to PostgreSQL.
MySQL was good enough for simple CMS type tasks and extremely user friendly at a critical time in the market. PostgreSQL, designed for enterprise apps from the beginning, placed technological soundness ahead of ease of use. However, over the last five years, PostgreSQL has actually become the simpler RDBMS to use and program for. No questions of "I misspelled InnoDB and now it created a MyISAM table instead" or such.
Unfortunately, it seems that by the time PostgreSQL became easy to use, MySQL already had cornered the low-end market. However, I would say that aside from light-weight CMS tasks, PostgreSQL is still far and away the better application for a number of reasons:
1) ACID compliance is pervasive throughout the engine. Creating operations outside a transaction, while possible, requires an untrusted programming language (like C, PL/PerlU, PL/PythonU, etc).
2) Date's Central Rule is designed into the RDBMS and cannot be circumvented by the application (which is not the case in MySQL 5.0 as strict mode can be disabled by an application).
3) PostgreSQL, while not perfectly standards-compliant, is far more standards-compliant than MySQL. This allows for much more portable code to be written for PostgreSQL than MySQL.
4) PostgreSQL is much more extensible than MySQL. You can add language handlers to allow you to create stored procs in whatever languages you want. PostgreSQL currnetly ships with PL/PGSQL, PL/Perl, PL/Python, PL/TCL. Other languages, such as PL/PHP, PL/Java (or PL/J), PL/SH, and PL/R are available as addons. I believe there is an attempt to make Mono available for stored procedures. Also you can add new data types without too much difficulty.
5) PostgreSQL has better Business Intelligence capabilities than MySQL. Capabilities include table partitioning and more. Parallel queries (across nodes) are under development in a spinoff project called Bizgres.
LedgerSMB: Open source Accounting/ERP
It's funny you should mention that. I have always had FAR more trouble installing mysql than postgres. Always.
The biggest thing here isn't the stored procs et al... its that SAP, you know the worlds biggest enterprise software vendor... will CERTIFY its application on MySQL (when using the old SAPdb stuff). This means that organisations that spend MILLIONS on SAP systems can get support if they run it on OSS.
That is the big deal, not functionality its about the support. MySQL might be the poor relation to Postgres in terms of functionality, but MySQL has a MUCH big best friend who can open doors where functionality doesn't count.
This is a real moment IMO, as a well known OSS database has a massive seal of approval from one of the most famous for reliability vendors in the market.
Next time your boss says that OSS can't do a DB, tell him that SAP disagrees.
An Eye for an Eye will make the whole world blind - Gandhi
I've been using MySQL for about six years now, and it's been working very well for me. I utilize it on my crazyguyonabike.com, a bicycle tour journals website. It has about 750 journals on there, with over 60,000 pictures. I use replication to back up the database remotely, and all in all it works very well. I honestly can't understand the level of hatred towards the tool that emanates from many of the posts here.
I have to say that I cringe every time I see a MySQL story on slashdot these days, because it just seems like there is a legion of PostgreSQL zealots just waiting for any chance to denigrate MySQL. It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc. Even when MySQL AB adds a feature or does fix some perceived failing, then the detractors simply ignore this and move on to some other apparent showstopper. For example, it's not enough that MySQL has transactional capabilities - no, now they simply moan that it's not the default (MyISAM still is).
We seem to have people who have what can only be described as a religious mindset when it comes to these issues. "Religious" in the sense that their minds are closed, and no matter what new facts come to light, they will simple twist everything around to match with their existing worldview. So, in these people's minds, MySQL AB adding features is not a positive thing, it's rather a sign of how wrong Monty was in the past to suggest that most people really don't need transactions for everything. Well, at what point exactly do we have "proof" that I don't really need transactions for my website? Is six years of 24/7 use enough? If not, then how long exactly?
Yes, I've had problems, of course I have. You will with any tool, PostgreSQL included. No matter the fact that PG has had transactions from day 1, people still got corrupted tables occasionally. But at the end of the day, the results are the same - do you still have your data? Is it intact and internally consistent? I can answer yes to that. I don't mind having some logic in my application to delete some records when some other records get deleted. It works really well, and while in theory it could cause data inconsistency, in practice this has never happened. Even if it did, a quick perl script would be sufficient to clean things up - I'm doing that kind of thing all the time anyway, as the database evolves and I need to shift stuff around or change table structures. It's no big deal, really! Some will say No, this is a Horrible Solution and you should put business logic into stored procedures... I say, get a life. That's *your* solution, it's not everybody's. You're simply moving your complexity around, you'll never really get rid of it. Some people are more comfortable with their complexity in stored procedures, I'm perfectly comfortable with it in my Perl application. So what, does it work for you? If so, then who cares.
There *are* some things in MySQL that disturb me, but I don't know if they are common to other DBMS solutions out there. One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better? Informed opinions please, rather than fanboy noise.
Also, speed. I hear lots of anecdotal tales about how much faster PostgreSQL is these days, especially under load from multiple connections. I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application. Can PostgreSQL really replace MySQL now? Or is this another case of wishful thinking?
Thanks,
-Neil
I have a question: if I use the older JDBC connector (from June 2002) before the connector project was absorbed by MySQL and became GPLed, is it OK to use MySQL on a leased server with a Java web application that is not GPLed?
:-)
That is, if my web application links with the old LGPLed connector which uses a socket connection to the GPLed MySQL server, then that is fine license-wise, right?
This is a question for all the 'Slashdot lawyers'
Seriously, from reading the licenses, I believe that the scenario that I mentioned using the older LGPLed JDBC connector is OK, while using the newer GPLed JVBC connector(s) is not.
Also: I believe that this is not an issue with Ruby since the client MySQL connector is not GPLed.
I also cringe whenever a MySQL story comes out because it seems like the conversation devolves into two opposing opinions:
People in the latter group don't understand why anyone would dislike it - after all, their home-written blog software renders DB-backed pages in less than five seconds.
People in the former group can't imagine why anyone would put up with its many, many shortcomings when other faster, more capable, more Free databases are widely available. They don't understand why some people wouldn't want to use the best tool for the job when there's no legitimate reason in the world not to.
One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better?
I'm migrating my companies data from an old FoxPro setup to PostgreSQL. I don't have the option of normalizing the data (it would break too much legacy code, although I might look into making backward-compatible views sometime down the road), but selective indexing on columns (and functions on columns!) made 20-table joins work astoundingly well. Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.
Dewey, what part of this looks like authorities should be involved?
PostgreSQL has another option as well. Views and rules. When I use stored procs for these, I always wrap them in views in order to keep the interfaces clean and portable. YOu can essentially define custom select/insert/update/delete using these tools in ways that are more flexible than triggers though triggers have an edge in other areas.
I usually think of app structure this way (this is a flexible guide, not a hard map):
User Interface
Application Logic
Data Access
Data Presentation (views/rules, can include multi-app business logic)
Data Maintenance (triggers)
Data Storage (base tables)
LedgerSMB: Open source Accounting/ERP
Chr!$t almighty! How many layers of abstraction do we really need to code in the name of portability and "enterprise-worthiness"?
I've done my share of stored proc programming, shell scripting, OO design, and J2EE implementations, and after 15+ years of it, and while all the theory around this appears sound, I continue to see these systems collapse not on their own weight, but the weight of the surrounding corporate IT infrastructure.
When was the last time you witnessed a project that, with a little nip here and a little tuck there, went from Oracle and iPlanet on Solaris to DB2 and Jrun on Windows? It's never "a little nip here and tuck there". The enterprise ecosystem is too diverse to make it that simple. So why bother in the first place?!?!?!?
Oh, I forgot...the "consulting" body shops like to push these "enterprise architectures". Gotta migrate platforms due to the latest corporate buyout/merger? That'll be $5.5M, half up front, thank you very much.
Makin' money, makin' friends, makin' whoopee and wearin' Depends
Strict Mode attempts to solve many of them. I understand that there is a new set of gotchas, but we shall see (MySQL is not my primary RDBMS).
Strict mode is only a partial solution, however, because applications can turn it off(!) and thus circumvent the protection it affords.
LedgerSMB: Open source Accounting/ERP
well, I did do a large project with MSSQL, and while it didn't crash or fail spectacularly the way other Microsoft products tend to do, it did have a few issues with locking.
specifically, it had an overly complicated strategy of automatically escalating types of locks (row-level, page-level, table-level, etc), the end result of which was that you never quite knew what was going to happen. I did have a rather fun bunch of hours tracking down transaction deadlocks that should not really have ocurred with a better engine.
the result of it all was that it made me realize how much better MVCC databases (which are able to hold more than one version of a record at a time, and show each client the appropriate version of the universe) are than the ones based on simple locking and exclusive access. on a non-MVCC database, an open transaction which has modified a row will freeze any other client that attempts to read it! imagine how happy your users are when all their front-ends stop working just because one user's computer crashed at the wrong moment.
AFAIK, all the major open source transactional db engines are MVCC: PostgreSQL, MySQL+InnoDB and Firebird are (dunno about SapDB, Ingres and the various Java engines).
in the proprietary world, Oracle does MVCC, but Sybase and DB2 don't. apparently the next version of MSSQL will have some sort of MVCC support too.
btw, all this talk of database independence ("it's all SQL dialects anyway") is an oversimplification in the real world. MVCC or not is actually a big deal in how a database application is engineered. as soon as you want to do anything sightlycomplicated in your transactions, and maintain integrity in the face of multiple clients, you have to think hard about locking, and start using things like "SELECT ... FOR UPDATE". at that point, the code you write will depend heavily on whether your database is MVCC or not.
D'ooh!
Yeah, right.
Can someone please inform me about InnoDB?
I heard that InnoDB builds up dead tuples with lots of inserts/updates, sort of like PostgreSQL without VACUUM. Is that accurate? Can someone explain? Do InnoDB tables just keep getting bigger? Is it fixed in 5.0?
Social scientists are inspired by theories; scientists are humbled by facts.