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

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]

'General' implies usability in production systems. What you really want to read it as is this is the first non-beta release.

We tested many of our sites (including my personal favorite, vobbo, a site for video blogs [vobbo.com]) and found some very significant speed improvements, especially in some of the math functions (SIN, COS, etc).

does that mean they fixed the gotchas?

[RelliK]

Any word on when they are planning to fix this [sql-info.de]? With this careless disregard for data integrity, it's hard for me to take MySQL seriously.

Re:does that mean they fixed the gotchas?

[GabboFlabbo]

Well, straight from the email:

Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises ("gotchas") for those migrating to MySQL from other database systems:

- Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected

sort of

[einhverfr]

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.

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'. Y

Re:what the?

[b0r1s]

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

Re:what the?

[rk]

To use the Pythagorean Theorem, you've got to convert lat and lon to some type of Cartesian Coordinate system to really do it right, although at mid-latitudes, it's not too bad a get a relative proximity as long as you're not interested in a real unit of linear measure (kilometers, miles, etc.).

The planet is round (a geoid, to be a pedantic geography nazi) and lines of longitude are not parallel, although in local coordinate systems you can generally assume they are and not make huge errors. Longer dista

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

[Metteyya]

Because it's easy to deploy, easy to manage and - because not feature-bloated - easy to learn and use. Is that enough?

Re:stored procs and triggers, finally

[tzanger]

It's funny you should mention that. I have always had FAR more trouble installing mysql than postgres. Always.

Speed and simplicity.

[khasim]

For a lot of jobs (websites), they aren't needed. MySQL is very easy to implement and integrate with your site.

Re:Speed and simplicity.

[Marc2k]

I saw the topic of your comment, and was all ready with about 43,204 reasons why speed and simplicity just don't cut it for enterprise applications...but you're right. Views and triggers are awesome (Stored procedures are not, but they are, unfortunately, a necessary evil. Writing them is about as fun as stabbing yourself in the foot with a fork, but you'll always find statistics that speculate that 60,70,80%+ of business logic is written in stored procedures, however precise those numbers may be, they are

Re:Speed and simplicity.

[commanderfoxtrot]

Got to agree with you- VIEWS and triggers are great, but stored procedures are a real pain. However, they make triggers SO powerful (check out this blog entry on synchronising Apache passwords [planetpostgresql.org] by one of the PostgreSQL developers).

I'd really like to know how you are supposed to edit stored procedures without programs like phppgadmin.

Re:stored procs and triggers, finally

[b0r1s]

For small websites that need 1-2 dynamic features ("news" and "mailing list", for example), it's cheaper to go with MySQL, where devs are all over and damn near everyshared hosting account supports it out of the box.

Re:stored procs and triggers, finally

[User 956]

Almost every database out there impliments an ISO or similar SQL standard as it's base (SQL-92 in most cases). They then build on top of that by adding their own features, while still supporting the common SQL syntax. It's not about being a barebones implimentation of a standard, it's about supporting the standard as your base.

PostgreSQL supports SQL-92, while adding it's own extra features (which describes most other databases like Oracle and MS SQL too), including the support of the "LIMIT" statement. MySQL doesn't support any standard base, instead existing as an arbitrary mish mash of standard and propritary SQL. It wasn't until the current version, 4, that MySQL even bothered to add support for UNION.

With every other database you can start working safe in the knowledge that while having it's own extensions, you're working with a normal "SQL" database. MySQL, while posing as SQL, has little if anything in common (in particular see threads about optimization - getting fast code in MySQL means learning an entirely new system filled with quirks and vomit inducing workarounds to solve language faults)

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

Standards woes with MySQL

[einhverfr]

SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

MySQL is further from PostgreSQL here. The standard specifies that identifiers which are not double quoted should be folded to upper case. MySQL provides no case folding which breaks compatibility with the standard pretty clearly. PostgreSQL violates the standard by folding to lower case (as opposed to upper) which is compatibible with the standard in 99%+ of real world applications (

Re:stored procs and triggers, finally

[lpangelrob]

Yeah, haven't really needed those feature at this company. I've temporarily inherited a MS SQL Server database, so decision time on the fate of that database is nearing (and I don't even consider myself a DBA).

At my old company, their reservation system relied on advanced database procedures, so they used... an advanced database, namely Oracle. Imagine that. MySQL not necessarily competing with Oracle. Most blogs (small blogs, and wikis) don't need Oracle.

Re:stored procs and triggers, finally

[msuzio]

Both of these can be done in the client-side code, and in some ways are easier for a smaller system. Moving app logic out into the DB can yield some benefits, but the fact that now you have two paths of execution to consider can complicate things too much.

We use both in our systems (and have been since 5.0 was beta), and we've had mixed results. Triggers still can be very flaky. The stored procedures are handy, though, and work pretty well.

Why MySQL is popular

[einhverfr]

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.

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

Re:Why MySQL is popular

[einhverfr]

Date's Central Rule (attributed to C. J. Date) states that the data integrity (and by extension business rules) should be enforced by the database schema and client applications should not be able to circumvent it.

In reality, this usually manifests in the idea that what you put into a database should always be what you get out of it, and that your database schema should enforce such things as arbitrary data constraints (CHECK) and referential integrity (FOREIGN KEY).

Re:stored procs and triggers, finally

[djwavelength]

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.

Re:stored procs and triggers, finally

[temojen]

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.

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

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 dedicate

Re:stored procs and triggers, finally

[xelah]

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

The OP is presumably referring to work which stored procedures do which isn't part of a query (like running business logic, chopping text about, or god knows what else). Performance wise it /might/ be better to do this on your database machine if it drastically reduces the amount of data sent across a network. Otherwise,

Re:stored procs and triggers, finally

[GregWebb]

Not with MSSQL they're not. ANY parameterised query gets its execution plan cached for exactly the same length of time, according to Books Online. SProc or elsewhere makes no difference.

Re:stored procs and triggers, finally

[yabos]

Huh? AFAIK if you send a SQL string to a DB server it does all the work and returns you the requested records. It's no different than embedding that SQL in a stored procedure and then calling that SP.

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

[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

[einhverfr]

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)

Re:stored procs and triggers, finally

[IANAAC]

A lot of times (particularly all these tiny web apps), stored procedures aren't needed. But when you have this behemoth of an app (say, AR/AP/GL... very non-trivial stuff), that does the same thing in several different places, stored procedures are a godsend.

Re:stored procs and triggers, finally

[JoeD]

With hardcoded SQL, you run the risk of SQL injection exploits, unless you're very careful to escape each and every user-written field that makes its way into a SQL statement.

Re:stored procs and triggers, finally

[commanderfoxtrot]

You should always use bound placeholders in SQL. Then you don't get SQL injection exploits.

i.e. define a parameter then execute: SELECT x FROM y WHERE p = '?'

It is also faster as the DB can use an already prepared query plan.

This is the ONLY way to write decent SQL applications?

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 on

Re:stored procs and triggers, finally

[Jearil]

what if '?' = '1; delete employees;'

Then it would probably return:

Empty Set (0.00 sec)

as I doubt any record in field p will actually equal the string '1; delete employees;'

Now what you're probably thinkog of is setting that ? in '?' to be something like:

1'; delete employees;

attempting to escape out of the select prematurely with a well placed ' after the 1. However, using prepaired statements (which is what I believe the GP was speaking of) runs the statement through a parser to set escape characters into the query strings so something like that can't happen. The example above would turn into something like:

1\'; delete employees;

which would still yeild a fun result of Empty Set (0.00 sec). You don't actually need a stored procedure for safe queries with user input, just a language that has prepared statements (or heck write you're own version of it if your language of choice doesn't have it, it's not that difficult of a thing to make).

Re:stored procs and triggers, finally

[msuzio]

That would be what prepared statements are for. No need to use stored procs just for that.

Re:stored procs and triggers, finally

[homb]

Stored procs don't completely shield you from injection exploits either. They're better, but you absolutely always have to carefully screen any user input.
An example of stored proc injection potential is when a stored proc has dynamic sql in it.

Re:stored procs and triggers, finally

[CastrTroy]

You know, you bring up a good point. There's going to be a lot of people who disagree with you, but I think that what you say has a lot of fact. What they really need, is a way to include stored procedures in your code, so that queries can be run with the speed of a stored procedure, but still keeping the code for the stored procedure with the rest of your code. There's no reason why you couldn't have a language construct that says, i'm starting a stored procedure here, and then process that part to cre

Re:stored procs and triggers, finally

[arkanes]

This is totally retarded, and the OPs "problem" is equally retarded. If you have a problem with "other people" modifying "your code", then you need to fix yourself, not your database.

Re:stored procs and triggers, finally

[Thomas Charron]

And when someone finds a way to inject SQL into one of your queries, who's fault is it?

You guessed it, also yours.. ;-)

One of the largest benifits IMHO.

Re:stored procs and triggers, finally

[GregWebb]

There's still enough ways of injecting SQL into stored procedures - assuming that your app using stored procedures automatically protects you from SQL injection isn't clever, and you should be filtering your input regardless.

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,
Ad

Re:stored procs and triggers, finally

[GweeDo]

That was the GP's point. MySQL has gained all this popularity *with out* those features. It wasn't until today that they officially had them.

Innovation

[chrysalis]

Wow, triggers and stored procedures. MySQL really does innovation.

Re:Innovation

[choas]

So there I go, looking up stored procedures on Wikipedia and it gives me this:

Wikipedia has a problem

Sorry! This site is experiencing technical difficulties.

Try waiting a few minutes and reloading.

(Can't contact the database server: Lost connection to MySQL server during query (10.0.0.101))

Well at least I now know you're not a troll and it DOES gave something to do with MySQL ;)

Re:Innovation

[Jamesday]

10.0.0.101 is Adler. Its uptime is currently 2017391 seconds (23 days). Adler's uptime is that short because it had a hardware repair. It was probably overload - several DB servers are dead right now and Monday is the busiest day for the site. So far the site is consistently filling to capacity all the hardware which is ordered and that shows no sign of stopping. It's now at 4500 pages per second, 400 megabits/s. For scale, the biggest Slashdotting the site saw was about 650 pages per second.

Averages over 23 days for this one server: 1620 selects per second, 10 inserts and 3 replaces per second. That is: 140 million selects per day average. Peak rates are about double average rates, typically in the 3000-5000 qps range.

I'm one of the roots at Wikipedia. Figures from SHOW STATUS just before typing this reply.

Re:Innovation

[Jamesday]

Adler is a dual Opteron with 16GB of RAM and 6 15,000 RPM SCSI drives. We have two like that, one with 8GB and 15K SCSI, several with 4GB and slower drives.

Suda, a dual Opteron 4GB box with 6 10K SCSI drives has 8 day uptime and 580 qps average but it's probably been out of normal load quite a bit of that time for various chores.

Holbach, a dual Opteron 4GB box with 6 10K SCSI drives has 28 day uptime and 616 qps average.

Ariel, a dual Opteron 8GB box with 6 15K SCSI drives has 8 day uptime after repairs and 1280qps.

Samuel, the current master, is another 32GB dual Opteron with 6 15K SCSI drives, has 83 day uptime (that is, no crashes or deliberate MySQL server shutdowns for 83 days). Only 367 qps and I think it was not in service for quite a while before it was made master - think we put it into service earlier than planned because of the hardware problem on Adler. It's typically running nearer 2000-4000qps now it is in service.

khaldun and bacon, both 4GB dual Opterons, one with 10KK SATA and the other with 7200RPM SATA are both down. All are running Fedora core.

Add up those query averages and it comes to 385 million per day cross 5 servers. We might pass the billion select per day mark this year; hard to predict.

Some people will say that MySQL is incapable of doing serious work, even though just 5 main database servers are powering a top 100 site delivering 1 in every 1,000 web pages viewed in Alexa.com's sample. Others will say they use MySQL because it gets the job done. Including me. :)

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:Innovation

[b0r1s]

Postgres was free ('as in beer') and free ('as in a real license'), and gave away these features long ago.

Besides, for 'freedom', the BSD license used by Postgres beats the GPL hands-down.

Re:Innovation

[iBod]

True, but how does that devalue what MySQL have done - as your agressive, zealotish, tone implies that it does?

Sorry. I just don't get the MySQL PostgreSQL hate thing. They are both good pieces of software, both good choices for given (maybe different) situations, as far as I'm concerned.

I don't really care about who was first or what license you have.

Was PostgreSQL the first to implement these features? Uhh, I think not!

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

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!

Re:Well done MySQL AB

[Tyler Eaves]

Yep, that basically sums it up. It's the AOL of databases.

Re:Well done MySQL AB

[iBod]

Well, I think it's a lot better than the 'AOL' of databases.

Remember, AOL charges for their service. MySQL (mostly) lets you use it for free.

What the hell will it take before snotty geeks stop looking gift horses in the mouth.

MySQL is fine for what it does - simple, flexible, low(ish) end RDBMS with a fair turn of speed.

Re:Well done MySQL AB

[JoeD]

No no no.

It's the Windows ME of databases [cafepress.com].

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

[User 956]

It is still lacking compared to other free databases such as PostgreSQL and Firebird, but version 5 is a real improvement. (as mentioned, now you have things like triggers, stored procedures, views and sub-queries.) If you use strict mode integrity checking will work reasonably.

What I'm currently miss the most in the new version is that it can't handle domains and the ability add check constraints as you create tables is somewhat lacking. So, even if MySQL have done a tremendous job improving their product I would still go for PostgreSQL, or Firbird any day both for technical and legal reasons. Both Postgresql and Firebird also seam to be better at internationalization.

The fact that Oracle just bought the company that supplies the default MySQL storage engine doesn't spell good for the future. Even though MySQL could continue to use InnoDB in the future under the GPL licence it is in Oracles power to raise the licence fees for commercial use. That would mean less incomes to MySQL AB and that could hurt their ability to develop the product further. However, afaik Oracle have not said anything about raising the prices other than that the licence deal with MySQL is going to be renegotiated in '06. To me that sounds a bit ominous.

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

[iBod]

I agree there are better RDBMSs, but MySQL is offered free by almost any web hosting outfit you care to name.

For small web-based projects, this gives it an edge over the (still slighly esoteric) PostgreSQL (which I would probably use given the choice).

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

[einhverfr]

For small web-based projects, this gives it an edge over the (still slighly esoteric) PostgreSQL (which I would probably use given the choice).

Sure, as long as money isn't involved. If strict mode is off, MySQL will happily truncate numbers for you :-) Personally I cannot believe that so many shopping carts use it.

Where MySQL really shines is for light-weight CMS projects. I would still use it for this. For anything else, I would use PostgreSQL. If you need to integrate PostgreSQL with MySQL, there is

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

[iBod]

If you're worried about MySQL truncating/rounding numbers, then you should take a few database design classes and learn how to handle numbers and choose the correct data types in your schema.

"It's a poor workman that blames his tools" - [somebody 1655].

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 silentl

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

[Fahrvergnuugen]

The fact that Oracle just bought the company that supplies the default MySQL storage engine

InnoDB is not the default storage engine in MySQL... MyISAM is.

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

[MourningBlade]

If Oracle was thinking smart, they'd make sure InnoDB is free, or at least really cheap.

MySQL is about the best argument out there for Oracle.

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:Well this is neat

[ivoras]

Now if only the remaining 80% of ISP's and other hosting providers were to move away from version 3.23.x...

Re:Well this is neat

[GregWebb]

While it's good to see more features I agree, I have specific problems with stored procedures.

Each time I've looked at a different database, they've been implemented in an incompatible way. This means that as soon as I have to switch DBMS for the application, all the queries have to be moved in a huge, laborious job with SProcs.

Alternatively, I've worked in a system that held its database queries separately as simple SELECTs, INSERTs etc - and, wherever the syntax or commands differed between DBMSs, it had

Gotchas

[Ed Avis]

It would be cool if someone knowledgeable could check the old MySQL Gotchas [sql-info.de] list and see how many have been fixed in 5.0. My hope is, nearly all of them.

Re:Gotchas

[aug24]

I would be quite surprised if any of those behaviours were changed, as they are all "working as designed" if not as an Oracle or Sql Server guru would expect.

That said, there may be other ways to define things so that they behave more 'normally'.

Cheers,
Justin.

Current results of the MySQL Gotchas

[Anonymous Coward]

Results of tests against MySQL 5.0.16-nightly-20051017-log (I downloaded and installed this latest snapshot today)

1.1. NULL, or when NULL IS NOT NULL
The behavior was not changed, but it's of no importance anyway.
1.2. AUTO_INCREMENT
The behavior was not changed, and I must admit that all that sounds scary. On the other hand we're using a LOT of mysql where I work and never run into a single problem caused by this particular problem.
1.3. ENUM

Re:Current results of the MySQL Gotchas

[jadavis]

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?

Just in time...

[WindBourne]

to deliver to SCO. I wonder how much money that cost them?

Woohoo! MySQL is finally ready!

[philovivero]

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)

Re:Woohoo! MySQL is finally ready!

[iBod]

>> I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

Are you serious, or was that just a throwaway remark, or a joke?

I specialize in VLDBs and I'd be really interested in some details if it's actually true.

Not that MySQL would even be on my radar for such a job, I think you would write a very interesting case study if you are doing what you claim.

Care to provide any more info?

Re:Woohoo! MySQL is finally ready!

[Fahrvergnuugen]

Nice to see it scaled well for you. My MySQL server is currently running around 150 queries per second (540,000 an hour) with no problems.

Big Concerns with MySQL

[baggins2002]

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

Same question I asked when it went beta:

[jbellis]

Do you get these features in all table types, or do you have to use the (much slower) InnoDB tables, as with transactions?

Quite a news day

[dtfinch]

NASA drops the whole "shuttle" idea. Andy releases a new version of Minix. DrDOS steals from FreeDOS. And MySQL becomes a real database server.

Now with SAP...

[MosesJones]

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.

Unofficial slashdot MySQL thread checklist

[jonfelder]

1. I love MySQL!
2. Who cares? Postgres is and always has been better.
3. I used to use MySQL, but now I don't.
4. I used to not use MySQL, but now I do.
5. If you use MySQL you are stupid.
6. If you do not use MySQL you are stupid.
7. Only Nazis and CowboyNeal use MySQL.
8. Did anyone say goatse.cx?

Some thoughts

[ngunton]

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 [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

Some more thoughts

[Just Some Guy]

It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc.

I also cringe whenever a MySQL story comes out because it seems like the conversation devolves into two opposing opinions:

1. Database administrators who understand DB theory, have managed terrabyte servers, and know what a real database looks like. This group hates MySQL.
2. People who used MySQL to implement a tiny pet project successfully. This group loves MySQL.

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.

Re:Some more thoughts

[ngunton]

You seem certain that PostgreSQL can use more than one index per query. Well, a cursory search on Google comes up with this page [redhat.com]. The "Red Hat Database" is basically PostgreSQL (I think!), and a little way down this page you can see this comment:

"Note that a query or data manipulation commands can only use at most one index per table."

Here's another link [postgresql.org] which seems to confirm this.

I believe I have seen comments somewhere regarding experimental support for multiple indexes in queries in PostgreSQL, but I am

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.

Q: using older JDBC connector (LGPL)?

[MarkWatson]

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.

Re:Q: using older JDBC connector (LGPL)?

[shutdown -p now]

According to MySQL AB and their interpretation of GPL, any sofware talking to the server using their protocol is to be considered a derived work, and thus has to be GPL'd as well (or you must buy a commercial license). Of course, such interpretation is completely brain-dead, not what GPL is about at all, and would most likely not hold in the court; but then again, IANAL. Either way, they don't want you to use it that way, even if they can't enforce it.

So close... almost no longer a toy!

[Safety Cap]

C:\>mysql -V
mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)

[snip]

mysql> show columns from foo;
| Field .| Type . . . . . . . .| Null | Key | Default | Extra
| id . . | bigint(20) unsigned | NO.. | PRI | NULL. . | auto_increment
| mydate | date. . . . . . . . | NO.. | . . | . . . . |

2 rows in set (0.02 sec)

mysql> insert into foo (mydate) values (0);
Query OK, 1 row affected (0.09 sec)

mysql> select * from foo;
| id | mydate
| 5 | 0000-00-00 |
1 row in set (0.00 sec)

mysql>

WTF is 00/00/0000, 5cr!pt K!dz Day?

D'ooh!

Re:So close... almost no longer a toy!

[imroy]

You must have a strange definition of "integrity". If you want to insert strange dates and other invalid data into your toy database, go ahead. Every web programmer knows that you do all your checking in the client code anyway, right? But try to run a business like that, just try. In a year your tables will have lots of nonsense entries that you'll have to fix by hand. And foreign keys are only used by fancy GUI apps to draw diagrams [wikipedia.org], right? No-one uses them to ensure the integrity of their database, hell n

Re:Almost caught up to MSSQL!

[DaHat]

Na... add the CLR (from Mono perhaps) into the DB so that stored procedures can be written in high level languages like C# and we might shut up as you will have come closer to catching up with what Microsoft will be releasing on November 7th.

Re:Almost caught up to MSSQL!

[Anonymous Coward]

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

Re:Almost caught up to MSSQL!

[chabotc]

Might have had something to do with the fact that MsSql was branched of Sybase 4.0 :-)

From Wikki:
The code base for Microsoft SQL Server originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and Sybase. Microsoft, Sybase and Ashton-Tate teamed up to create and market the first version named SQL Server 4.2 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 4.0 on Unix, VMS, etc. Microsoft SQL Server for NT

Re:Almost caught up to MSSQL!

[SpaceLifeForm]

The random crash feature may be available in the certified version of MySQL 5.0 for SCO OpenServer 6 [eweek.com] later this year.

as usual, open-source goes one better

[jbellis]

MySQL gives you random corruption! Even better than crashing!

If you want actual stability, PostgreSQL and Firebird are better bets.

Re:Almost caught up to MSSQL!

[TheKubrix]

I hope someone mods the parent down, because thats just stupid/ignorant.

I've been running MS SQL 2000 for about 4 years now and it has NEVER crashed. Nor has it corrupted any data or any other such destruction.

I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why. Your comment is a case in point.

Re:Almost caught up to MSSQL!

[orabidoo]

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.

Re:Generic Web-Frontends for MySQL

[LDoggg_]

OpenOffice 2.0 comes with a database front end application like Access.
You can create a database with its small embedded hsqldb or connect to an external database like Mysql or postgres.

You'll need Sun's jvm for this stuff to work as well

Re:Generic Web-Frontends for MySQL

[LDoggg_]

d'oh!
Web-Frontends

My bad.

Re:Generic Web-Frontends for MySQL

[FireFury03]

This is slightly off-topic, but I was wondering if anyone is aware of any generic web-frontends for MySQL?

How about http://www.phpmyadmin.net/ [phpmyadmin.net]?

phpMyAdmin

[smelroy]

I'm not sure about reporting specifically, but phpMyAdmin [phpmyadmin.net] is the way to go for a generic MySQL front end.

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!