Slashdot Mirror
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."
'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) and found some very significant speed improvements, especially in some of the math functions (SIN, COS, etc).
Mooniacs for iOS and Android
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/?
http://blog.nexusuk.org
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)
The theory of relativity doesn't work right in Arkansas.
I'm not sure about reporting specifically, but phpMyAdmin is the way to go for a generic MySQL front end.
Switching to Linux can be an adventure!
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).
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.
Mooniacs for iOS and Android
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.
The theory of relativity doesn't work right in Arkansas.
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.
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?
http://blog.grcm.net/
That would be what prepared statements are for. No need to use stored procs just for that.
It's a strange world -- let's keep it that way
Do you get these features in all table types, or do you have to use the (much slower) InnoDB tables, as with transactions?
SPs aren't the only way of compiling and optimizing a query once. For instance, caching Perl DBI statements will, for databases which support it, result in the same thing. With Postgresql, for example, DBI will send 'PREPARE blah AS ' before the first execution and then use 'EXECUTE ' afterwards. Unlike SPs these disappear when you drop your connection (and so remain inside the client code and not in the DB).
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.
Results of tests against MySQL 5.0.16-nightly-20051017-log (I downloaded and installed this latest snapshot today)
;-) ... SELECT ... ... Doesn't really bother me.
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
Behavior unchanged - This isn't a real problem at all...
1.4. Case sensitivity in CHAR / VARCHAR fields
Weird behavior which might degrade performance, or help you - depends on what you are doing. But I don't agree with the author's suggested solution redefining the table string as binary since you can simply force a binary comparison on the select, so who really cares about this?
1.5. VARCHAR limited to 255 characters
This restriction was lifted. Current limit is: 2147483647
1.6. VARCHAR's trailing blank allergy <= fixed ^^
1.7. DEFAULT NOW()
This deficit only affected mysql versions below 4.1 - And I can tell you it didn't reappear in 5.0
1.8. INSERT INTO
Like 1.7 this was only true for versions prior to 4.0.13... Nothing to see here
1.9. Comments beginning with --
So ok... comments introduced with -- don't work. As a web developer I never came across having to comment sql inline XD
1.10. UNION and literal values
This bug was fixed. Although I ran into a character set problem on this one since the table and mysql defaults were set different and unions are supposed to have the same character set - or maybe I'm just too tired to understand what just happened...
1.11. Division by zero
This behaviour is still intact 1 divided by 0 results in NULL
1.12. 'concatenation' || 'or'
This "fault" results from not running mysql in ansi mode which makes it overload the || operator diminishing its usefulness.
1.13. What goes in - isn't (always) what comes out
Holy shit, a variable range overflwos! If anyone really falls for this - go take a beginner's programming lesson...
1.14. February 31st
The behaviour has changed. But since date (as is datetime) is basically a string, I don't really like the kind of checking mysql is now performing O_o I must look into that further since you still can insert some "malformed" dates, but only some of them get changed. What's wrong with that?!?
1.15. Space between function name and parenthesis
Although the behavior changed, the author won't be happy with what he sees because it still doesn't behave like his dbms of choice... But if we're honest - this is no bug!
Now, some things got fixed, some things just changed and most of these don't even matter. All in all 5.0 is a nice release and in my opinion MySQL is still very likable and for me as sys admin quite comfortable.
What bothers me most at the moment is 1.14. - because that might have some effect on real world situations. Maybe someone else wants too look into this further so I can read about it tomorrow?
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.
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).
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.