MySQL 5.0 Candidate Released

Brian "Krow" Aker (Former Slashdot Coder now MySQL Employee) writes "I am pleased to announce the release candidate for MySQL 5.0. This version has been in development now for three years. We have worked to add update-able views, ansi stored procedures, and triggers. In addition we have added a number of fun features that we are experimenting with and resolved issues with bad data inserts (which personally annoyed the hell out of me when we rewrote Slashdot a couple of years back so I am happy to see this issue go away). We look forward to feedback on the candidate and will show some love for bug reports."

Re:Is it a "real" database yet?

[mmkkbb]

Actually, most of those features are in MySQL 4. The two big problems are: Lots of people still run MySQL 3.x, and these features only work for certain table types.

Re:Anyone know of a good free MySQL GUI?

[Chagrin]

Use the MyODBC driver. Link the tables with Access and cut and paste all you want.

Re:Time for new comparisons to be made.

[schon]

Correct [66.102.7.104]. If you're measuring performance, then you *can't* include Oracle.

Examine t he license carefully!!

[scorp1us]

After the 3.x series, the license changed drastically. Of course you won't find this discussed much. The permitted uses of MySQL as GPL are significantly reduced. You can only use MySQL in conjuction with OSS. Previously, you could use MySQL in a non-GPL environment under several permissive conditions. The new license is so restrictive now that a special exception is made for PHP

Specifically: [mysql.com]

        * MySQL is free use for those who are 100% GPL. If your application is licensed under GPL or compatible OSI license approved by MySQL AB, you are free to ship any GPL software of MySQL AB with your application ('application' means any type of software application, system, tool or utility). You do not need a separate signed agreement with MySQL AB, because the GPL license is sufficient. We do, however, recommend you contact us as there usually are good opportunities for partnership and co-marketing.

        * Under the Open Source License, you must release the complete source code for the application that is built on MySQL. You do not need to release the source code for components that are generally installed on the operating system on which your application runs, such as system header files or libraries.

        * Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not.

        * You are allowed to modify MySQL Software source code any way you like as long as the distributed derivative work is licensed under the GPL as well.

        * You are allowed to copy MySQL binaries and source code, but when you do so, the copies will fall under the GPL license.

        * Optional GPL License Exception for PHP. As a special exception, MySQL AB gives permission to distribute derivative works that are formed with GPL-licensed MySQL software and with software licensed under version 3.0 of the PHP license. You must obey the GNU General Public License in all respects for all of the code used other than code licensed under version 3.0 of the PHP license.

        * FLOSS License Exception. We have created a license exception which enables Free/Libre and Open Source software ("FLOSS") to be able to include the GPL-licensed MySQL client libraries despite the fact that not all open source licenses are compatible with the GPL (this includes the PHP license version 3.0). Read more about the FLOSS License Exception.

Considering the new license and still lacking features, there is little reason to use MySQL. Postgres has "all that anda bag of potato chips."

Re:Anyone know of a good free MySQL GUI?

[Anonymous Coward]

Aqua Studio is very good (http://www.aquafold.com/ [aquafold.com]) links to most different db types as well as standard ODBC

Re:How does the source code quality compare?

[JohanV]

How do you want it to compare?

Source code quality is not easy to compare. At a first glance, MySQL is doing very good. They have this nice blurb [mysql.com] about only having 1 defect in 4000 lines being more then 4 times better then with most commercial software. But if you dig deeper, you notice that PostgreSQL has been tested by the same company and only had 1 defect in every 39000 lines [enterprisedb.com] of code. Wow, so PostgreSQL must really be a lot better then MySQL.
But if you dig even deeper, you will find some explanation from a PostgreSQL developer [advogato.org] and you remember what your mother told you about lies, damned lies and statistics.

You want to know about source code quality? Go read the source.

I think you miss the point

[Fished]

The point is not whether MySQL is a "real" database. Clearly, it is a real database that is capable of doing real work.

The point is that, even in recent versions, MySQL has some serious limitations that other OSS databases (e.g. PostgreSQL) do not suffer from, and no really significant corresponding advantages. MySQL was not designed from the ground up to be many things it is now trying to be--it was not designed to support transactions, it was not designed to support foreign keys, it was not designed to support stored procedures. It was initially conceived as a small, fast database for managing very large datasets in a warehousing sort of role. PostgreSQL, on the other hand, was always conceived of as being a heavier-duty database, and this shows in terms of feature completeness and SQL standard compliance.

Given that the performance differential (which was always overstated) has been overcome, why would you want to go with MySQL only to discover what the latest feature to be missed was? What's the advantage to MySQL?

Please stop the MySQL Bashing...

[Anonymous Coward]

Before all the MySQL bashing starts can we please stop and have an intelligent conversation? The bottom line is that MySQL works great! For all those people who say .. "ya its a kids toy" well look at some of the sites and companies that are using mysql.. "Friendster" , "The Friendfinder network" , "Yahoo!" .. These sites each have millions of active members and are running just fine. Where else can you load up a 50+ database cluster and not have to shell out a fortune on licensing fees? All the developer tools are great!!

What about those [MySQL] gotchas?

[bogaboga]

They have done very little about these MySQL gotchas! They should have eliminated most of them first. You can still read them here: http://sql-info.de/mysql/gotchas.html [sql-info.de].

Re:Anyone know of a good free MySQL GUI?

[JohnnyBigodes]

Sorry about the "free" part, but Navicat [navicat.com] is the best MySQL interface that I've seen to date, and I've tried quite a few (not all of them, obviously :)

Re:What about those [MySQL] gotchas?

[sql-gotcha]

That list addresses issues in MySQL 4.1 and earlier. Unfortunately I haven't had time to try out any of the MySQL 5.0 pre-release versions, so I'm not in a position to provided any kind of qualified commentary. However my impression is that many of the "gotchas" in the list have been addressed.

Re:Anyone know of a good free MySQL GUI?

[FlopEJoe]

Aqua Studio [aquafold.com] is some good stuff. I haven't used it for MySQL but it's an option when registering a new database. Aqua is available "for personal use only" so not quite "free."

OS: Windows, Linux, OSX, Solaris

RDBMS: Oracle, DB2 UDB, MS SQL Server, Sybase, Informix, Postgresql, MySQL

Re:What about those [MySQL] gotchas?

[minginqunt]

No true.

Those gotchas all (mostly) go away if you run MySQL 5.0 in strict mode. Compatibility mode is provided for 4.1 and back-asswards behaviour if you need it.

See: http://dev.mysql.com/doc/mysql/en/server-sql-mode. html [mysql.com]

Martin

Re:Add bloat, stay fast

[Dan Ost]

In PostgreSQL, you can write your stored procedures in
Python, Perl, and a couple of other languages. It probably
won't be too long before MySQL allows the same thing. With
any luck, you won't need to learn a new language at all.

Re:I think you miss the point

[kpharmer]

> InnoDB supports the features you mentioned, and that I quoted, so why is that less desireable than having the table handler builtin?

Because it is core functionality.

Having a separate product responsible for managing io may be one of the reasons that mysql's optimizer is so primitive - and may be a reason why they will struggle to improve it. Note: the optimizer is the component responsible for determination of how your joins will be performed under the covers - mysql is notorious for failing to use indexes it should, using indexes when it shouldn't, never using star-joins, five-way joins that day 10x as long as they would in any other database, etc.

Perhaps it's also why views took forever to implement, and materialized views might take another forever.

It's also perhaps the reason for all the inconsistencies in table creation.

There are also benefits to using innodb - it has undoubtably speed up mysql's development by several years. Still, now it's a boat anchor that should be abandoned.

> For me, the massive community is a big plus. A rich set of tools is another advantage.

yep, although it may be the only advantage that mysql has, it's a huge one.

Re:Anyone know of a good free MySQL GUI?

[pvera]

Please mod up parent. Aqua Data Studio is a very damn good application, and the developer is extremely responsive. It is common to have him hang out in the support newsgroup for hours at a time, walking people thru things even if it is obvious they are too lazy/dumb/etc. to follow the documentation and FAQs. Between Aqua Data Studio (which btw runs on pretty much anything that can run Java) and phpMyAdmin, you should be able to do almost 99% of whatever it is that you need. The other 1%? RTFM and use the CLI.

As for the SQL Server Enterprise Manager, it was a turd in 6.5, less of a turd in 7.0 and then got worse in 2000. The improvements added to Enterprise Manager for the jump from 7 to 20000 were pretty damn good, but they are offset by bullshit mickey mouse Jscript interface errors that have no place in a database management application. This sucks because the SQL Server Query Analyzer only got better and has none of these weird Jscript issues.

For those of you stuck in the Oracle world (and cursing the Oracle provided tools), you may want to check out Benthic (http://www.benthicsoftware.com/ [benthicsoftware.com]), they have been publishing very nice and inexpensive shareware apps that work more or less like the SQL Server Enterprise Manager and the Query Analyzer.

Re:I think you miss the point

[Kainaw]

Given that the performance differential (which was always overstated) has been overcome

Do a side-by-side comparison on your own data. For me, the performance differential has not been overcome. In MySQL, my reports take about 10 minutes to run. In Postgres, they take 3-4 days. When I mentioned this to our local Pg-fanboys, they came in, gave Postgres its own dual-processor server, extra ram, changed my column datatypes, made a slew of indexes, and reduced the report time from 3-4 days to 3-4 hours. I agree that optimizing Postgres will speed it up a great deal, but without spending a couple weeks optimizing MySQL, I had an acceptable report generation time.

However, on a separate project, I required features that were not in MySQL. So, I used Postgres. Speed was not an issue. In my opinion, this all has to do with the purpose. In the project I mentioned first, we have a data warehouse - shove junk in and make massive reports on it. In the second, we are working with the data on a daily basis. I am not surprised that one database engine works better for one purpose and another works better for another purpose.

Re:Anyone know of a good free MySQL GUI?

[j3tt]

The preview version of Toad for MySql is decent enough. (This assume though that you're using 'doze) http://www.toadsoft.com/toadmysql/toad_mysql.htm/ [toadsoft.com]

Re:Time for new comparisons to be made.

[ajs]

From the linked article,

"The standard Oracle license agreement normally prevents users from publishing benchmark results." -http://www.orafaq.com/faqora.htm#SPEED [orafaq.com]

Clear enough?

Re:I think you miss the point

[Anonymous Coward]

I think you might want to try learning a little about MySQL 5.0 before you go posting based on outdated knowledge:

Your MySQL connection id is 6 to server version: 5.0.13-rc-standard-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE TABLE t(t tinyint);
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO t VALUES(300);
ERROR 1264 (22003): Out of range value adjusted for column 't' at row 1
mysql> SELECT * FROM t;
Empty set (0.00 sec)

ODBC

[abulafia]

Now, my one pet-peeve in this area is that there is no linux equivalent of ODBC.

There isn't [unixodbc.org]?

I just finished a project that accesses SQL Server from a Linux/Apache/mod_perl app using ODBC via the FreeTDS drivers. (Don't ask, client requirement.)

Granted, not all of the unixodbc drivers are free. But then, they aren't in MS land either, although you might not notice because you're paying for them via a bundle.

Re:What about those [MySQL] gotchas?

[robnauta]

Of course, as the MySQL line has always been to manage transactions in code, I don't see why they didn't just raise an error and let the application deal with the manual rollback. Who would ever want invalid data to be inserted?

Manual rollback ?The idea is that if you do the following:
insert into mytable values (100);
insert into mytable values (200);
insert into mytable values (300);
rollback;
rolls back ALL changes. This is in Oracle, in Sybase or SQLServer you would start with a 'begin transaction'.
Imagine these inserts are in an upgrade script for an application. Now the script has to either errorcheck and commit after each row, or have nested if-then's. If the second statement fails, delete 100, if the third one fails delete 100 and 200. And you have to do all that in SQL ? No thanks. Give me a REAL database.

If you'd taken some time to dig a little bit...

[JetJaguar]

You would've have found out that MySQL AB isn't paying SCO a dime. In fact, SCO is PAYING MySQL to support MySQL on OpenServer, the cross marketing crap, is well, SCO marketing crap (The following is pasted from a message from one of the MySQL developers to the mysql mailing lists):

Nothing to hide, no conspiracy here ;-)

I think the discussion here has hinged on "the nature of the partnership". Let me assure you that no money has gone towards SCO.

They have provided us with the means to build and support binaries on SCO OpenServer 6. So they're paying us for... developing our software, which is all GPL licensed (yes we do sell non-GPL licenses as well, for the same code).

Knowing this fact (SCO funding GPLed development), most people regard the partnership with a benign smile ;-)

The other issue I spotted was about "commercial binaries". Users with OpenServer 6 get a trial subscription to our MySQL Network subscription service. These are certified binaries, but still GPL licensed. Non-GPL (aka commercial) binaries are an optional (but free) extra under MySQL Network. That option exists mainly to assist companies where using GPL-licensed software runs into policy problems, etc. We do also sell non-GPL licenses separately from MySQL Network, to OEM/embedded customers.

I hope this clarifies the situation to your satisfaction. If you have any further questions, please feel free to ask me.

Regards,
Arjen.
--
Arjen Lentz, Community Relations Manager
MySQL AB, www.mysql.com

Re:Examine t he license carefully!!

[martenmickos]

Yes, please examine the licence carefully, and you will notice that MySQL is friendly to all major open source licences.
 
Thanks to the GPL and our own FOSS Exception, you can mix and match MySQL with other open source software even when the licence texts would otherwise be legally incompatible.
 
The only time you need to be aware of our licensing is when you blend some closed source code into the FOSS stew. And for those situations we can offer a commercial licence.
 
I think it is fair to say that MySQL AB has listened carefully to the feedback from the community and made adjustments to licensing and other practices with the aim to promote the freedom of software. Would you agree? I am keen to hear your viewpoints.
 
Marten Mickos, CEO, MySQL AB
 
P.S. FOSS = Free and Open Source Software

Re:ERD tool ?

[Anonymous Coward]

Use Dia http://www.gnome.org/projects/dia/ [gnome.org] to create UML diagrams. Then use tedia2sql http://tedia2sql.tigris.org/ [tigris.org] to create sql scripts for PostgreSQL, MySQL, Oracle or other RDBMS. For PostgreSQL you have pg_autodoc http://www.rbt.ca/autodoc/ [www.rbt.ca] to create diagrams and HTML documentation directly from database server.

Re:ERD tool ?

[Anonymous Coward]

DBDesigner ...

Wow, very nicely done.

[Some Random Username]

You just posted what is quite possibly the single stupidest comment in the entire history of slashdot. Sorry, I don't think there is a trophy or anything, but you should still be very proud.

First of all, its not graceful degredation, its data corruption. The entire purpose of constraints is to give you an error when you try to insert invalid data. Changing it to be valid data and not even telling you is completely and totally the wrong thing to do. How about if your data doesn't pass a constraint then mysql does a drop table, is that still good for you? Its just as helpful and makes just as much sense.

Second, databases are supposed to have constraints, they store the data, they have all the rules of what is and is not valid data. Duplicating that in your code is absolutely brain dead, although its exactly what php/mysql developers have always had to do. This warps their minds and makes them think like you, that mysql is right, and everything else is wrong. Sorry, mysql is broken, every other database follows the SQL spec and returns an error when there is an error. Randomly changing data is not the correct response to an error condition, nor is there anything graceful about it.

Re:Is it a "real" database yet?

[jpkunst]

From http://www.mysql.com/news-and-events/news/article_ 959.html [mysql.com]

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

Can we now finally retire that tiresome "MySQL gotchas" link? Please?

JP

Re:ERD tool ?

[Anonymous Coward]

Azzuri clay plugin for eclipse makes some funky little database designs and DDL scripts.

Ok, here goes...

[krow]

a MySQL developer say "yeah, I don't know what we were thinking, that's a really fucked up thing to do" Yep, its a fucked up thing. This is why we implemented strict mode for 5.0. In 4.1 you get warnings, in 5.0 if you are using a transaction table it tosses an error. If this is an issue, upgrade to 5.0. Personally for me it is.