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."

Comments

what the?

[haxhia]

What's the difference about this release and the "non general" release that was announced a while back?

Re:what the?

[b0r1s]

Friend finders, using zip -> lat/long data to find people 'near' you (for some arbitrary radius).

stored procs and triggers, finally

[cerelib]

I have always been amazed thy MySQL has been able to gain the popularity it has without features like stored procs and triggers.

Re:stored procs and triggers, finally

[SpaceLifeForm]

And if the database is not properly protected with constraints, you can screw up the database. And if the schema has to change, we have to hunt down your code and make changes there.

The best way to manage a database is to only allow applications to modify the database via stored procedures. You'll have far fewer problems that way.

Re:stored procs and triggers, finally

[jferris]

Excellent point. Additionally, putting SQL inside code is just plain bad. Granted, Web Development doesn't have the rigid constructs of deploying a compiled code base where changes to aforementioned statements would require a complete rebuild. Still, it is good practice to include an abstraction of functionality that is not in the code.

Yes, a lot of developers know how to write SQL Statements. I was a DBA at one point, before becoming a developer. But, now that I work at a company that has a dedicated DBA, I do not write stored procedures or SQL Statements for code use. It is better left in the hands of someone who has a job with Database in the title. ;-)

Re:stored procs and triggers, finally

[xelah]

That's an excellent argument for having a layer between applications and the data. Stored procedures are certainly a way to achieve this, but they aren't the only way to achieve it. Is a bunch of, say, Java stored procedures all that different to, say, a Java server which exposes application domain methods via CORBA or J2EE (or whatever), is the only way for the rest of your system to get at the database and contains all of the queries all that different? Not really - and the second method has some advantages (like allowing you to run many copies across many computers). IMHO you really do have to think about your system architecture and it's requirements before making a decision like 'everything goes through SPs'.

Re:stored procs and triggers, finally

[wieck]

MySQL AB needed 10 years to add all those features. Being as popular as MySQL is means (among other things) that a huge horde of self taught users had 10 years to learn how to live without them. Don't expect all of those simple PHP scripters to unlearn and improve over night.

Congratulations MySQL AB. The 5.0 release is a very significant milestone and I am sure, serious database users appreciate the new features.

Jan

Re:stored procs and triggers, finally

[Anonymous Coward]

C'mon, how can you say that ?

One of the challenges of MySQL 5 was precisely to get closer to the SQL:2003 standard. And it did.
Consider the MySQL stored procedures for example : their syntax is probably one of the most respectful of the norm today. And that effort was also made for all the other new funcionality of MySQL 5.

Now since you're talking about the past flaws of MySQL, you shouldn't confuse the absence of a functionality with the proprietary implementation of that functionality.
It's true that until 2 years ago or so MySQL didn't support UNION but when it did it was in a standard-compliant way. But as far as I know MySQL has never had such a proprietary approach as the one Oracle had to outer join syntax for years for instance.

Concerning the LIMIT statement, it is proprietary syntax because there is no equivalent for it in the SQL standard ! By the way you won't find two RDBMS that implement it the same way...

So don't tell us MySQL is one of the less standard-respectful databases because it's just not true. It might not be the most SQL standard-compliant because it lacks standard functionality but what is implemented is fairly normative.
And don't come arguing that MySQL should implement "all of the standard or none of it" because you know pretty well it is not possible for a young RDBMS like this...

Re:stored procs and triggers, finally

[jsebrech]

This is the ONLY way to write decent SQL applications?

In the vast majority of cases, yes. It's stupefying how little mention is made of variable binding / prepared statements in SQL tutorials. Most of the time it not only is more secure, but easier to read (in code), AND faster.

On the other hand, if you're doing data mining, with the complex queries that go along with that, you start to get a performance hit from the database not knowing the exact values of the variables you've bound (unless you're using one of the higher end databases, which have support for variable peeking). But at that point you probably are not running your queries from a PHP front-end, since that would make DoS all too easy.

I suppose a large part of the reason for variable binding getting so little mention is that mysql didn't support it for quite a while, at least not in PHP (maybe it did with another API?).

Re:stored procs and triggers, finally

[adolfojp]

Lets say that you didn't create the database. Now you must remake every piece of code to work with the data. Lets hope that you didn't make any mistake or miss any important thing that had to be done when processing the data. Lets hope that it is not a banking solution processing my money. Lets hope that you are not working in a group and someone else screws up.

Use the procedures in your software when something SHOULD be done. Use stored procedures in the database when something MUST be done.

Cheers,
Adolfo

Well done MySQL AB

[iBod]

It's not the fanciest, or the fastest, but it's ubiquitous and free!

I for one have found it invaluable on many projects where a full-featured, high-capacity RDBMS would have been more trouble and expense than it was worth.

Props to MySQL!

Well this is neat

[lewp]

No matter if you're a MySQL supporter or someone who thinks that everyone should use a "real" RDBMS, having all these new features available to MySQL developers is a good thing. There's quite a few apps, I'm sure, that don't use these features in databases where they're available simply because they're aiming for the lowest common denominator that was MySQL's feature set.

Anyway, not trying to start an argument about the relative merits of any particular RDBMS, but this is a good thing all the way around. I look forward to taking it for a spin.

Re:Innovation

[iBod]

Although these features may no longer be 'innovative' they take a lot of work to implement and MySQL is giving you that effort for free (as in beer).

What have you innovated lately?

Re:no x.0 for me...

[sethadam1]

I never get the .0 release of anything ... And I've been wanting to try out Postgres anyway...

What's the purpose of posting something like this? I am not trolling, I'm just curious why everytime there's a MySQL thread, someone has to chime in about why they won't use it and why Postgres is better. What is the motivation to visit a thread solely to post something negative. Seriously, what is wrong with internet culture?

So I'm not completely off topic, I read the feature list, and this thing looks fantastic. Views, triggers, sp's, a new data type, BIT, for storing Booleans, which MSSQL has and is AWESOME. You may not want to try it, but some of us are excited to get our hands in it and have been waiting for the first "blessed" release!

Re:does that mean they fixed the gotchas?

[Entrope]

The meaning of that comment in bold is pretty clear: The gotcha list was verified against previous versions of MySQL. The author does not know which still apply to version 5.0. The comment does NOT say that MySQL 5.0 fixes the gotchas; the question "does that mean they fixed the gotchas?" is entirely appropriate.

Re:It's good, but there's better...

[einhverfr]

Where does that leave *NIX whose basic commands can do terrible damage silently?

To be honest, I am not a huge fan of the "silence is golden" attitude that UNIX has developed. After all, we have STDERR for a reason. But it does make sense in that the shell was originally designed to be a lightweight scripting engine for system tasks, and that there are different design criteria for an operating system and an information management system.

However, this being said, PostgreSQL used to truncate strings silently (this was fixed some time ago). The SQL 92, 99, and 2003 specs are very clear about strings and numbers not being truncated automatically unless you do this with an explicit cast-- strings only, such as CAST ('abcd' AS CHAR(2)) will store 'ab'. MySQL tries too hard to do *something* and fails only after having tried things that it should not try.

Re:GPL ! BSD

[einhverfr]

I know I'm following an obvious rabbit trail here, but I feel it's important. The BSD license may be arguably more vendor-friendly, but the GPL does a better job ensuring the sustained freedom of the code. Besides, there isn't anything in the GPL which makes it difficult to marry GPL'ed code with closed-source code, as long as credit is given where due and the source is available for the OSS portions.

The only thing that determines the sustained viability of a quality open source project is the size of its active developer community. The license is largely irrelevant as long as it provides the basic freedoms that are the prerequisite for open source software.

PostgreSQL started out at UCB, and the core team feels that they should respect their heritage by continuing its role as a reference implimentation, available for commercial and foss spinoffs alike. Personally I don't see why that is a problem. They have a much larger active developer community in terms of contributions to the core tree than MySQL, and their core developers are highly educated in the theory of RDBMS's. The core team seems to have loyalties as follows:

1) To relational and database theory
2) To the SQL standards (minor issues here, but not too bad)

If you have a sufficiently large community, proprietary spinoffs will never be able to compete. I would point out that when work on the Win32 port was launched, SRA contributed most of their proprietary work in this area (Powergres) back to the project to jump-start it. You can't compete with Free, so you have to proprietize only what is essential to your differentiation strategy. Failure to follow this rule means increased costs, and decreased competitivity.

Re:Some more thoughts

[Anonymous Coward]

You seem certain that PostgreSQL can use more than one index per query.
Yes, and that is correct. It can only use one index per table, though.

One of the big ones for me currently is that the query optimizer only uses one index in queries.

Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

"Note that a query or data manipulation commands can only use at most one index per table." (and multi-column indices are _one_ index)

Re:Why MySQL is popular

[lukej]

I think I am pretty agnostic about the whole Postgres/Mysql love affair. But I do find amusement in the 'personalities' of those supporting both sides.

Point #3;I always like the standards = portable argument. Reality check:

a> if somebody writes a huge DB app, standards compliant or not, their going to stick with their base DB
b> if it is a small DB app, then it's trivial to rewrite if you do want to migrate DBs

With all my Postgres and Mysql based stuff, I've never rewritten one for the other. Often times I've upgrade the DB or it's host machine. But, with both Postgres Mysql, performance for my (granted, small) DBs is fine with both, the DBs are free, and installing is simple.

I just don't think DB migration takes place often, even if you can find two compliant DBs!

Re:Some more thoughts

[rtaylor]

Technically PostgreSQL 8.1 can merge two scans of single column indexes together into a single table scan. This falls somewhere between a bitmap and regular indexing -- it builds a lossy bitmap on the fly to do all of the inter-column tricks.

With the beta's I've been taking my multi-column indexes and splitting them up to let the bitmap Index Scan deal with them instead.

Re:sort of

[bedessen]

That, and it's not turned on by default.

As an analogy, consider the case of PHP and its register_globals setting. Originally this defaulted to 'on' but this puts great pressure on the author of the code to take extra care not to introduce serious security bugs, and it was widely recommended that people disable this and not write scripts that depend on it.

I guess the PHP developers got tired of being blamed for all the shoddy PHP code out there, so a few years ago they changed the stock default to 'off'. Yet there are still lots of (dare I say POORLY WRITTEN) scripts out there that require register_globals=on despite the fact that this is generally a bad idea. So even today you still find entire servers running with register_globals=on just because some dinky script needs it.

If that is any indication, it means that many/most MySQL sites will continue to run with strict checking disabled, because a) people don't know any better, b) it's the default, and c) there are probably deployed applications that break with it turned on. And even if they eventually make it enabled by default, people will still continue to rely on the old behavior. Though in this case I think the setting can be enabled per-connection, rather than being a server-wide setting -- but I'm not positive.