This is a wonderful upgrade that might finally convince some users to leave the mess that is MySQL behind.
It's been a long wait but replication is now finally as easy as it gets. And, by design, it can not silently lose or corrupt data. Unlike a certain other popular RDBMS.
I honestly can't imagine why folks continue to use MySQL over PostreSQL. All technical benefits of PostgreSQL aside, I can't help but wonder if it really comes down to something silly, like MySQL being much easier to pronounce properly.
For my part, I'm stoked about 9.0. It can't get here soon enough.
Very simple: a lot of pre-packaged software doesn't use except for MySQL.
Well, to clarify, a lot of popular pre-packaged software is out there in PHP. And PHP never had a good db abstraction layer + the entire style of the PHP language lent itself to the direct use of queries in the code, and the direct usage of the MySQL connectors.
When users want a forum, a wiki, a helpdesk, a blog, etc. and it's in PHP, chances are it doesn't have a PostgreSQL backend. And if you have to have MySQL, and MySQL works for everything (for some definition of the word "works") while PostgreSQL doesn't... there's your answer.
The real reason is: PostgreSQL doesn't have any must-have apps that only run PGSQL (which is a good thing), and MySQL does (which is a bad thing) so MySQL wins.
There is indeed ADODB, and I have used it. It still more or less requires writing queries, though — and most ORM systems — redbean, Kohana's ORM — still seem to interact directly with MySQL instead of using ADOdb.
And even for PHP apps like MediaWiki that do support PostgreSQL the primary development target is still MySQL.
So it's not worth the trouble to use PostgreSQL in most cases, you'll eventually want to install some extension that only supports MySQL, or get tired of reading documentation that assumes MySQL etc.
In my case, I can tell you it's because I don't already know PG, and MySQL hasn't let me down to the point that I've had to consider switching.
I've done the 'apt-get install postgresql' routine a few times, and started to play with it, but due to it being different enough, and without any compelling motivation, I've never been able to learn it beyond scratching the surface.
That said, I'm a developer, and not a DB admin, so the limitation is almost certainly mine.
If MySQL hasn't let you down, then, frankly, you are just lucky. In fact, I'd have trouble to even call MySQL a database as it even lacks the one feature that a database needs to have: The ability to get the data back that you put in it.
If by accident you put Latin 1 (or any other non-utf-8-data) into a database that is configured as UTF8, MySQL will go ahead and cut off your data at the first byte with the 8th bit set. No error. No warning. The data is just gone. The only way to find out this happened is by reading back after every write - something I don't want to have to do.
Or allowing to
alter table whatever add somecolumn text not null;
that succeeds and consequently sets every row of somecolumn to NULL, violating the constraint.
Or inserting strings longer than allowed by the datatype which MySQL doesn't complain about but just truncates them - another case of having to read and compare the data just stored.
And don't get me started about corrupt on disk data, leading to unreadable tables. But worse - mysqldump at least once exited with an exit code of 0 even though it failed to read one of these corrupt tables. What's worse: It stopped the dumping process and didn't dump any tables and even databases following the corrupt table - yeah. I thought I backed up, but in fact I didn't.
Stuff like this must not happen with a database.
Stuff like this never happened to me with PostgreSQL.
It might be harder to set up. It might feel a bit foreign at first. It might provide features you think you don't need. But at least it doesn't destroy data I entrusted it with.
In MySQL, you can set the SQL mode to make it just as strict as Postgres. I'm still using MySQL for two reasons: a) It's already in place and it works great and b) I'm very familiar with it. For the next project (which may never come), I'll definitely be looking into Postgres.
In MySQL, you can set the SQL mode to make it just as strict as Postgres.
How much are you inclined to trust this mode which was bolted on as an afterthought, though?
I'm maintaining a bunch of MySQL installs for customers and the amount of random failures I've seen even with InnoDB and strict mode during backup/restore and upgrades is just not funny.
Obscure second guesses during even a minor version upgrade are the rule rather than the exception. 'mysql_upgrade' hardly ever worked right for me at first try. A straightforward dump/restore is not an option either because they change the schema of the system tables all the time (which, to add insult to injury, are still MyISAM).
Then you also frequently bump into gems like the following:
Incompatible change: As of MySQL 5.5.3, the server includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers.
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
(From: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo...)
Oh, and don't get me started on what they call "replication", which will happily, silently desync or corrupt data in various situations.
So well, yeah. Perhaps strict mode indeed works right in all cases. Perhaps.
It depends, lots of people know mysql and don't want to deal with switching costs if what there doing isn't going to be limited by database performance. So even if PostreSQL would be the better choice it may not be in terms of migration costs.
Have you seriously compared MySQLs documentation to Postgres? It has such mindblowingly retarded gems such as http://dev.mysql.com/doc/refman/5.1/en/connection-access.htm.... I mean people actually sat around and thought this was the best way to do user auth.
Feeling is what matters, especially for newbies.. Documentation should be written is a light, non-boring style. Django's or Android's is a best examples.
Note how half the page of the mysqldump docs is spent explaining the braindead semantics and dangerous interaction between --opt* and pretty much everything else.
Moreover the MySQL docs generally read as if they were written by a monkey with ADD:
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.
This is frankly just a random example from recent memory. Compare any two pages and you get similar results.
This is a wonderful upgrade that might finally convince some users to leave the mess that is MySQL behind.
I agree. PG is becoming better and better with every release. It can compete with Oracle in some applications.
Personally I hope they implement Row Level Security some time. It's my favorite feature in Oracle and I hope they will implement something similar soon.
To be fair some other popular RDBMS had useable replication way before it came to PG.
Also it would be interesting to see how much PG is better when used on the scale MySQL is used: the case of benefits on paper vs. real benefits.
Where is FUD in my comment? Or am I wrong thinking that MySQL has more and different scenarios production install than PG?
MySQL is also used on very large scale every day, so?
It's been a long wait but replication is now finally as easy as it gets. And, by design, it can not silently lose or corrupt data. Unlike a certain other popular RDBMS.