Slashdot Mirror
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."
It's time for somebody to do a new, impartial study regarding the performance and feature benefits of this new release of MySQL, PostgreSQL, Firebird, SQLite, and perhaps other open-sourced relational databases.
Cyric Zndovzny at your service.
Every time any database is mentioned on Slashdot we get a load of comments about how MySQL is not a "real" database because it doesn't support {insert random feature here}.
Is it a real database yet?
The global economy is a great thing until you feel it locally.
I welcome stored procedures triggers very much, as long as they are fast enough to compete with the current "just program it yourself in you chosen language" style. Anyway: Another stored procedure language to learn.
My wife's sketchblog Blob[p]: Gastrono-me
How does the source code quality of this new release of MySQL compare to that of projects like PostgreSQL or Firebird, which have a far longer history and/or were formerly commercial developments?
Cyric Zndovzny at your service.
Something that is comparable and as easy to use as SQL Servers Enterprise Manager. The tools available on MySQL's site aren't very good. My main concern is easy importing and exporting to and from Microsoft Access. For example I can just copy records from access and easily paste them into SQL Server. Thanks for the help.
#####Free and Open Source Game Directory#####
Can we trust HP (linux everything campaign) while they are strategic SCO partner?
Can we trust Oracle (Linux is our main development platform) while they are strategic SC partner?
And many many more.
Maybe we can even find the website of a dictatorship using MySQL, oh.
My wife's sketchblog Blob[p]: Gastrono-me
Specifically:
* 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."
Slashdot's rate-of-post filter: Preventing you from posting too many great ideas at once.
I certainly have to give them credit for one thing. The MySQL developers have been subjected to some very harsh criticism over the years, but few would accuse them of ignoring it.
It impresses me that they actually seem to be listening.
Do you like German cars?
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?
"He who would learn astronomy, and other recondite arts, let him go elsewhere. " -- John Calvin, commenting on Genesis 1
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!!
Having to dump to a script is both a blessing and curse. For any database that dumps to less than the size of a CD, it's actually very portable, and allows you to clean up your database with substantially more ease than a binary format. The curse of course is that you can't just reattach your binary backup and have the database go again. Also, since 8.0, Postgres has had a binary backup format, and has done a thorough job resolving dependencies (a really nuisance in 7.x).
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.
The license you describe doesn't say anything about server usage.
Clearly, the main-use for MySQL lies in server-client architecture. As long as you do not ship your self-created web-application, I see no reason you should be suspicious about MySQL.
Write a specification document. Seriously. I haven't used Access since I was at school, and I honestly have no idea what it does that is any use. I suspect a lot of the people who are capable and willing to program an Access-replacement are in a similar position. Tell the community what you need. Just saying `nothing exists that fits my undisclosed requirements' does not help anyone. Please send me the URL once you have put the requirements online.
I am TheRaven on Soylent News
> And Oracle 7 for the two years before -- in a not-so-large database -- I think there is not much to fear...
Actually, no: many databases these days are now supporting various types of logs - in which you've got tables with tens of millions of rows. Oracle and DB2 have the following in place to support massive tables:
1. query parallelism - that provides linear performance improvements up to 4 cpus or so
2. data partitioning - that allows the database to just scan data needed, rather than entire table when indexes aren't appropriate (b-tree indexes only work for around 1-3% of data)
3. materialized views - in which views actually hold data - and this data is kept up to date by the database server. Often used for summary tables.
3. query rewrite - in which your queries are automatically re-written to apply to a summary table (see materialized view) if one exists.
4. clustering - in which data spans multiple servers, and all servers work together on your query.
5. smart optimizer - intelligent score-based optimization responsible for determination of best access paths for your queries.
With the above features db2 or oracle can drive 40x the performance of mysql or postgresql in a reporting application (or transaction one with a few large scan-oriented tables) on identical hardware (say a 4-way SMP). If you didn't see an impact from these features then either you have one of the fairly rare apps that can't benefit, or you should revist the design.
Don't get me wrong - I really like postgresql. But neither it nor mysql is even in the ballpark for performance on db2 or oracle. Nor is the price much different - db2 is only around $700 for 4/5 of the above features on a 2-way smp vs $500 for mysql. Eventually mysql & postgresql will support these features. But it'll probably be five years before they are working well.
is often the better database solution, I'll still be using MySQL. Why? Well a quick search on cwjobs shows 188 jobs requiring MySQL experience vs 7 for postgres!! Its a real shame but having used the best tool (C++ Builder) for my last job and then being unemployed for @2 years because people wanted either VC++ or Unix based C++ experience; I *WONT* be making the same mistake again!
Anyways, that said, Ive already played about with 5.011 and apart from the yukky syntax one has to use to support transactions it seems quite stable. Its might have taken a long time for them to finally make it a "real" database product but it seems good enough for small databases.
One of my next jobs is to test it with 10 million + records and see how it performs though so my assessment may be premature...
Access is an interesting app but has no business use whatsoever. I have been involved in multiple projects to rip apart poorly written applications in Access to move the data and the logic to where it belongs : a real multi-user relational database.
Writing a business application in Access is like writing it in Excel. The logic is poorly located and hard to share, the app is single-user, and the interface is crap.
What you want is a framework for quickly building apps on top of a real database. And thats another debate.
The only so-called "database" that emphasizes it's GUI is Access. Every other vendor/product I'm aware of relies of separation of duties and doesn't try to roll user interfacing into what is rightly a back-end service.
Administration tools for commercial and OSS databases may be easy for small sites and novice DBA's that don't know their tools, but large applications rely on database scripts to handle configuration, not GUIs. The reason is simple: you can't put a mouse click into CVS/RCS/SCCS/???.
I do not fail; I succeed at finding out what does not work.
Well, SCO also ships PostgreSQL with their product, can we trust the PostgreSQL-folks?
Lesbian Nazi Hookers Abducted by UFOs and Forced Into Weight Loss Programs - -all next week on Town Talk.
PHPMyAdmin and MySQL Control Center are database management GUIs. I don't think they're supposed to be Access-like apps, and I'm personally happy about that, since when doing DB admin I don't want a tool that tries to second guess me, hides things from me, etc.
One Access-like app is OpenOffice.org 2.0 Base (Go test it and report issues before the final OpenOffice.org 2.0 release!). It's extremely new and it's rough around the edges, but it looks decent. It's an Access clone to the point where I'm surprised Microsoft aren't suing about stolen icons.
If you're looking for something in the same vein, but not seeking a direct Access rip-off, then Rekall may be an option worth investigating. Both it an OO.o are cross-platform.
Do be aware that tools like Access encourage you to do too much client-side. It's important to consider using in-database stored procedures and (often updateable) views where appropriate for efficiency and clean structure. You can then use an Access-like app to provide a user-interface to that higher level database interface, instead of just poking around in the raw tables.
Most DBA's are adequate at best and most likely don't have much experience trying to squeeze every ounce of CPU out of a box. So I can understand why they do this... I personally don't take 'user' benchmarks very seriously because most times you have no idea how their environment is configured or if it is even configured properly.
"Thanks to the remote control I have the attention span of a gerbil."
AFAIK the clustering and load balancing 'solution' for MySQL means your entire database has to fit in memory. Not very practical...
You are talking about "massive" tables and a "reporting" application. Of course Oracle and DB/2 are the right choices for this. Has anyone ever thought differently?
I've always used MySQL and it has always been extremely fast for me. But I've always known that it isn't meant for data analysis on massive amounts of data. The only time I've ever heard anyone compare MySQL to Oracle and say that MySQL was just as good as Oracle is when using small tables on a web app, which is exactly what MySQL is used for 99% of the time.
Hmm... I'm no database person but if you're creating a table that only allows a value of up to 127 and go over that, I would expect it to give you 127. What would a real database do?
Return an error and don't make the insert.
There isn't?
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.
I forget what 8 was for.
> You are talking about "massive" tables and a "reporting" application. Of course Oracle and DB/2 are the right choices for this. Has anyone ever thought differently?
Yes - everytime a product or technology is overhyped people believe it will do everything. Search for mysql and data warehouse - you'll find plenty of people who think it can handle massive data without really understanding what db2/oracle/informix/etc do that's different.
> The only time I've ever heard anyone compare MySQL to Oracle and say that MySQL was just as good as
> Oracle is when using small tables on a web app, which is exactly what MySQL is used for 99% of the time.
Right - in that context mysql/postgresql are competitive. Of Course, that isn't what MySQL AB is telling people. Little surprise, these are the same people that a few years ago were telling people that they didn't need primary key/foreign key constraints either.
Anyhow, I've got an application right now that for its first two years stayed quite small - just a few thousand rows. Implemented in db2 - since we need a major database anyway for the larger databases with a billion rows, and we can save labor by staying consistent. Anyway, this little database is about to now explode in size due to expanded requirements and new customers - we're expecting one little critical table to go from 3,000 rows to around 500,000. Its history table will go from maybe 10,000 to 10,000,000 rows. Other tables throughout the databae will likewise expand. DB2 was overkill when this database was first created, now it's perfect: it'll support the heavy transactional loads, automated system failover, and very fast scans, reports and other tough queries. This application growth is not at all uncommon - everyone is putting far more data into databases than they were just a few years ago.
So yeah, mysql and postgresql are neat products, but their lack of scalability for massive tables or analytical queries is a major gap. And the requirements for this capability are now commonplace - unlike ten years ago when only a few data warehouses really need it.
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
Shop Smart, Shop S-mart!
I can confirm that MySQL 5.0 doesn't allow you to drop a table if it is referenced by a foreign key.
Also, the way to go when you set up MySQL is to set the default table type to InnoDB, which supports transactions, then you don't need to worry about point 3.
I haven't experienced the other two problems. But then, I don't have millions of rows, only hundreds of thousands.
Don't count your messages before they ACK.
Use Dia http://www.gnome.org/projects/dia/ to create UML diagrams. Then use tedia2sql http://tedia2sql.tigris.org/ to create sql scripts for PostgreSQL, MySQL, Oracle or other RDBMS. For PostgreSQL you have pg_autodoc http://www.rbt.ca/autodoc/ to create diagrams and HTML documentation directly from database server.
I strongly suspect the slashdot editors have a bet as to how many times they can post a MySQL story and have the same debate, with more or less the same bashing, arguments, and counter-arguments repeated all over again.
It's must be their secret revenge on the fraction of the readership screaming DUPE! every time they post a story resembling an earlier story. Now, they can read their latest MySQL story, point at almost any comment, and scream "DUPE!" they too!
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.
I'm can't wait to see the new MySQL now with its "fun features". I'm totally intrigued as to what a fun feature might be in a data base? Will it start replying to queries in style of the Swedish Chef? Bork ,Bork, Bork!
Still great work from the MySQL team.
Where would /. be without them? Probably /.ed!
> That page made 10 calls to the db with a total processing time of roughly 0.0055 seconds.
Right, looks like that's working well for you. With mysql getting that query performance on a 4m row table, it must be using an index, which means that you're selecting less than 3% of the data each time. The scalability issue emerges when your result set is 10% of the total table, you have to sort 100,000 rows, etc.
> Oh, it would still be available... I would just connect to a different db to pull it in. My code is
> already written for that to happen, I just need to change the connection string for the historical db.
> Right now it is exactly the same as the current db connection... so they both live in the same db.
sounds like a good plan
I had a similar problem only this morning. I deleted all the rows from a table (by using DELETE FROM) and SELECT COUNT(*) went from sub-second to two minutes.
That's after going from 400,000 rows to zero.
VACUUM ANALYZE made no difference; however VACUUM FULL sorted it out. You should really have auto vacuum running, but for the dev I am doing I prefer to do it by hand.
From what you're saying, this could be the reason why it takes so long.
For what its worth, I've moved to Postgres because it's just so more solid. It's fantastic software. And I know what I'm talking about- I'm heavily involved in (big) mainframe DB2 at work.
http://blog.grcm.net/
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.
You can't grep a dead tree.