MySQL AB Calls v4.1.7 Production Ready

puppetman writes "MySQL announced a few hours ago that 4.1 has been deemed production ready with the release of 4.1.7. The major enhancements of 4.1 include sub-selects, faster communication between client and server (thanks to parameter binding), replication over SSL, and lots more. A full list can be found here. Time to rehash those tired arguments about why MySQL is not a real database, and (Postgres/Oracle/SQL Server/Access/SAPDB/Ingres/etc) is the only real database out there."

Comments

Production ready?

[Pan T. Hose]

Does the "production readiness" include ACID?

Re:Production ready?

[zatz]

Sure, just use the InnoDB storage engine.

MySQL

[m0rph3us0]

MySQL is fine for load/store operations or other places where complex operations on set are not required. MySQL is not suitable for complex queries.

MySQL is a bit like Windows 98 vs. a multi-user OS with virtual memory. Most people just need a single user computer that holds their email and lets them play games.
However, for those who need the features a multi-user OS they cannot simply hack on those features to Windows 98.
It isn't that MySQL isn't a real database it is just that its feature set severely restricts the tasks it is able to do well.
Yes, you COULD build a bank on MySQL just like Diebold makes Windows 98 based ATM machines.

Whether it is a better idea to simply build your bank on Postgresql or Oracle and pay higher machine / licensing costs is an excersize for the reader.

There are spots where Win98 and MySQL are well suited however, there are a lot of cases where it is completely out classed by other products.

Myself, I'd much rather not use all the features of Postgresql and have them available later than build on Mysql and if I need the feature have to port the application. If I run up against performance limitations of Postgresql I simply buy better hardware.

Not everyones SQL usage is limited to "SELECT * from comments were story_id = 23456"

Hardware is almost always cheaper than a programmer's time.

Re:MySQL

[zatz]

MySQL is not suitable for complex queries.

Actually, with the new support for derived tables in 4.1, it's as good as any other database product I have used (Informix, MSSQL/Sybase, Postgres). I have a production system which generates large, hairy SELECT statements (I've encountered MySQL's internal limit of 61 tables in a single join, to give you some idea how awful these can be) and runs them on a database with millions of records. It's quite fast (with appropriate choice of indexes) and implements all the SQL-92 features I expect, although the table-level locking of MyISAM is unfortunate.

If you want to bash MySQL, it has plenty of real weak spots, eg transactions and stored procedures. But if you are going to run messy queries on big piles of read-mostly data, it's brilliant.

Re:MySQL

[digerata]

Ditto.

Our main application was originally on MySQL. Started off at around 3 million records spread across 78 taables. The largest table had around 575,000 records. Now we are much larger running on Oracle.

MySQL handled the load and all of our queries on multiple servers in our app cluster just fine. But when we needed more advanced things that MySQL doesn't provide (and won't for another decade or so) we moved to Oracle.

We defined "more advanced things" as master-master replication (that is easy to setup and maintain and is provided by the DB vendor), real DB clustering, Natural Language searching, complex sub-queries, recycling sequences (auto_increment keys), and a host of administration tools among other things. Granted, some of these MySQL is just now putting out there. But it certainly doesn't have all.

Notice, I never mentioned stored procedures, triggers, or views.

Its funny, way back when we were justifying using MySQL in a production enterprise environment, speed comaprisons against Oracle was a huge argument. But if MySQL ever throws in everything Oracle has feature wise, they will be just as slow if not slower.

wheelbarrow?

[Vaevictis666]

Ok, I'll bite. Why is the Databases icon a wheelbarrow?

Have its developers' attitudes changed?

[Anonymous Coward]

Does MySQL still silently substitute its best guess when you try and insert an invalid value into a column?

Does MySQL still silently substitute a non-ACID table type when you ask for an ACID table type and it isn't available?

Does MySQL still silently alter the data you insert into varchar columns by stripping trailing spaces?

MySQL used to be riddled with all kinds of behaviours that the MySQL developers thought might be handy in some circumstances, but that silently alter or ignore the programmer's/DBA's instructions and the SQL specifications. Have the developers gained enough of a clue to fix these (intentional, documented) problems?

Re:Have its developers' attitudes changed?

[puppetman]

1) Yes. Very bad. The developers believe the application should check all data before sending it to the database. I've posted my displeasure with this on the MySQL mailing lists, and have been told politely to "live with it".

2) Wasn't aware that it did. Most table types are now in the basic download.

3) Not sure

You could ask similar questions of Oracle (or any other DBMS), like,

Does Oracle still require a 1.2+ gigabyte footprint? Yup, getting bigger all the time.

Is Oracle still more complex to set up than any Linux distribution? Yah. And try Real Application Clusters (RAC).

Does Oracle still require you to spend $600 on books to get the important information about what's going on under the hood? Yes. I bought a 1000-page monster the other day, and the list price was $100 CDN. The DBA handbook, written by the same guy, is also 1000 pages, and will cost about the same (not out for another month).

Does Oracle still charge $40,000 per CPU for a perpetual Enterprise License?

Is Oracle still slow and bloated compared to MySQL? Yes. All those extra, needless features that 1-in-50 databases add bloat and slowness.

For Postgres,

Does Postgres still make really bad optimizer choices if the data-types being compared aren't identical? Last I checked.

Does Postgres have a decent replication engine, or standby database option? Yah, if you want to spend almost as much on support as you would on an Oracle Enterprise license for 1 CPU. There are others, but I'm not sure I would throw them into a production environment. Oracle Standby and MySQL replication have both been flawless for us.

Does Postgres have a native Windows port yet? Not yet, but I hear it's coming.

Does Postgres still have a mailing list full of the most informed, polite people you could ever hope to answer your questions? Yup.

No database is perfect. MySQL is a small, fast database and we are running our 2-million hit per day website off of it, and it's been flawless and significantly faster than Oracle was way back on 8i. We code around the strangeness.

Re:Have its developers' attitudes changed?

[ttfkam]

Does Postgres still make really bad optimizer choices if the data-types being compared aren't identical? Last I checked.

At least there's a workaround. You can always explicitly cast your value to the type in question. I don't know of any similarly straightforward workarounds for the data manipulation/loss bugs in MySQL.

Re:Have its developers' attitudes changed?

[Anonymous Coward]

You could ask similar questions of Oracle (or any other DBMS)

No, you can't. That's the whole point. Absolutely none of the questions you raise about other databases are problems with data integrity. These are databases for heaven's sake. Of course they take data integrity seriously. The same can't be said for MySQL, unfortunately.

Does Postgres have a native Windows port yet?

8.0 does, but that's not production-ready yet, so it doesn't count. Three betas have been released if you want to try it out though.

Greater than 20, less than 99

[ttfkam]

• Schemas
• Views
• Rules
• Check constraints
• Domains
• Triggers
• Custom datatypes
• Stored procedures in no less than five different programming languages
• Geometric/spatial datatypes and indexes
• Real type safety
• Errors when the alternative is data corruption
• No silent failures
• Enforcing a difference between NOT NULL and DEFAULT directives
• Consistent foreign key enforcement (Target table must also be InnoDB, ON UPDATE does not allow recursive updates on the same table, out of range violations, brain dead implementation of NO ACTION)
• Better transition to Oracle (closer feature parity)
• Substandard transaction support (The time required to roll back a transaction increases in proportion with the number of operations performed during that transaction; Any non-InnoDB tables referenced in the transaction will not rollback)

And have they fixed the bug where CREATE INDEX, DROP INDEX, and most ALTER TABLEs rebuild the whole InnoDB table? It's been a year.

And how about bugs like this (http://bugs.mysql.com/bug.php?id=5670) where creating an index destroys the table. Nice.

Start the countdown!

[ttfkam]

Now that MySQL users now talk about how much they enjoy their subselects, a feature which only a couple of years ago was being derided as just extra bloat, how long until 5.0 goes stable?

On that day, the hordes of MySQL developers will raise a chorus singing the praises of their new stored procedures, views and cursors. And behold! They didn't even slow down the product. Blessed be the MySQL programmers!

Until that day however, stored procedures are "useless" and "needlessly complex." The same with views and cursors. They only serve to slow things down. Of course those pesky naysayers of MySQL will point out that those features have been in PostgreSQL and Firebird for years. But no! Our golden calf does not yet support them in production and until it does they are obviously useless bloat.

Do you like it? I call this piece "Ode to a MySQL Fanboy."