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
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
"If they have both, tell them we use Linux. And if they have that, tell them the computers are down." -Dave Chapelle
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.
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.
I "think" OpenOffice can do this.
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).
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.
-Rob
Biblical fiscal responsibility
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
did you see the comment in bold letters on that page. Maybe read first and comment later?
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).
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.
Unstable Apps: Our Android Apps Don't Suck
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.
Greg
(Inside a nuclear plant)
Aaaarrrggh! Run! The canary has mutated!
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.
Greg
(Inside a nuclear plant)
Aaaarrrggh! Run! The canary has mutated!
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.
"I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application."
After hearing so much praise and good will towards pgSQL on Slashdot, I finally decided to take the plunge and install pgSQL. Let me tell you, it broke all sorts of records in my books. Among those records were: most time wasted dicking around trying to configure an application, most time wasted dicking around with shared memory "gotchas", and most time dicking around trying to find some good solid tutorials/information about how to properly tune a large database. Not only that, but even after spending a few days trying to get the database tuned correctly, it was still slower than MySQL on a database that was pushing close to 1.3GB at the time and is now even larger. Also, the fact that I had to sit here and write more than one Perl script to finally get all the tables to properly insert using pgSQL just to find out inserts took forever annoyed me beyond belief.
Sure, I've managed to corrupt data using MySQL, but that was only because of my stupidity and it was easily fixed. However, I've been running MySQL for over four years now with large databases and I've never had a problem that wasn't because of something I managed to muck up along the way. With that being said, I'll be giving MySQL 5 a go sometime in the very near future and it looks good from my point of view. I've always been a person to find my own solutions, and after my piss-poor experience with pgSQL I'm going to stick to my guns from now on. Point in fact: if it works for you, keep using it; if it doesn't work for you, don't use it. In my case: MySQL happens to work great for me and I'll be using it until I find something better.
SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this
a tures.html
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 (though I am a big proponant of providing the option of folding to upper).
MySQL supports all operators in the core.
Right... Except that some (like ||) do different things than the spec says unless you change the mode to ANSI mode. This leads to *very* unportable code.
Here are some areas under active development in PostgreSQL at the moment:
SQL/PSM standards support.
SQL/MED standard support
As for SQL-2003 compliance, you can see the list of supported and unsupported features at http://www.postgresql.org/docs/8.0/interactive/fe
Unless you can point to specific SQL-2003 features that MySQL supports properly and PostgreSQL does not, I call FUD.
LedgerSMB: Open source Accounting/ERP
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).
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.
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.
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 distances, though, will require you to break out the trig, and the pure form of the Pythagorean Theorem is less useful. I find it easier to work such problems in spherical coordinates.
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).
LedgerSMB: Open source Accounting/ERP
They'll say this because they want you to BUY an Enterprise support license for use in Business. Otherwise, if you don't need the extra support, I see no reason to buy a license.