Perhaps it's due to me being in the PostgreSQL camp, but what happened to MariaDB? I was convinced that's where MySQL developers went and that's where development proceeded, while MySQL was just half dead, because Oracle did not care about it.
When you consider the network effect of all the stacks already heavily invested in MySQL, all Oracle really needed to do was put in a modicum of effort to MySQL to stave off the MariaDB migrations (and from my understanding they've put in quite a bit more than a modicum).
Lastly, Postgres is an interesting player. Migrating from MySQL to Postgres might be a scarier prospect than to MariaDB, but since the Oracle acquisition a lot of MySQL-veterans were looking for something non-MySQL for new projects and in that I get the sense Postgres beat out MariaDB.
I absolutely adore Postgres's tweaks to the SQL language, that dialect is amazing and has served us extremely well. And, since we passed pg10 a while back, the performance tuning you can do on it is pretty amazing.
Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else?
The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!
But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant.
Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...
You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking.
I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.
The best way to learn the SQL standard is to read the Postgres docs since they clearly call out non-compliances - there is no other easy to access and clear explanation of the SQL standard.
PostgreSQL documentation is very good about comparing each feature to the standard.
For example, the SQL standard specifies that triggers fire in the historical order they are added to the table.
PostgreSQL chose to fire them alphabetically.
Technically nonconformant, but in practical terms, far better.
This was surprising for my old tech lead who was sheepish on moving away from MySQL.
He would later refer to the MySQL bindings as “brain dead”
Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)
And then any second trigger that relies on that value
But I also think that it's unnecessarily problematic to make the order non-deterministic.
It just makes easier to understand what happens when triggers are invoked, without having to actually force specific cases by direct experimentation.
I did not even know that the standard prescribed a FIFO order, looks like a pretty bad idea to me, actually.
(To be fair, table columns share this characteristic.)
The table columns order is meaningless, but the triggers order is very relevant.
``` INSERT INTO foo SELECT * FROM bar ```
The harder problem however, is order in composite types.
Just using a BSON column for such attributes hits the Pareto 80/20 sweet spot, _and_ I can query against them much more easily than in an EAV schema (far fewer joins).
They're great for when the schema is mostly normalized, but there's just a little bit of denormalized stuff that doesn't warrant completely architecting the schema around it.
And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE:
CREATE INDEX ON publishers((info->>'name'));
It would be horribly painful to stick to _just_ SQL/JSON for manipulations, and I have absolutely zero idea what the coverage is in other platforms, but portability isn't completely off the table like it used to be
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
Other folks have mentioned :: instead of CAST AS (which is really nice when you need to write it a bunch of times) but it's mostly the diverse built-in function set and constraint support that keeps me an enthusiastic Postgres supporter.
We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.
Sadly for me when I tried to make a PoC of Postgres for our eCommerce platform I found that we had violated our constraints and lost data.
That was the day I went from MySQL agnostic to MySQL hating.
It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.
Everyone agrees with this. That's why they changed the default nearly 6 years ago.
Yes, the old default was terrible. But there's no way to change the past, so why complain about it for years and years?
All versions with this bad default have hit end-of-life, what more can be done?
https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
It doesn't sound too bad. The incompabilities seem to be mostly stuff like not being able to generally use replication from MySQL to MariaDB or vice versa. But I would guess that's not a very common case anyway: most users are either developers that want their software to work with both MariaDB and MySQL (which can be achieved by sticking to the very large intersection of supported features), or they are users that want to switch from MySQL to MariaDB (which seems well-supported). Running a mix of MariaDB and MySQL servers and expecting to be able to replicate between them seems like a particularly uncommon setup (though maybe it's useful during a migration).
Arguably MariaDB made the correct decision to fix some MySQL 5.7 bugs that we were unwittingly relying upon, but the different behavior caused issues for us all the same.
Rather than worry about MySQL vs MariaDB and whether the one I need is packaged and available for my distro/OS of choice, I reach for Postgres on new projects.
Anecdata: I've been using mariadb as a drop in mysql replacement for over 5 years in production, and it's been working seamlessly.
I'm no DB expert, and perhaps my use case isn't complex enough or written in a highly mysql dependent way - but I felt like someone should chime in since there's not much positivity toward mariadb in this thread so far. I'd be interested to hear from some other people with actual mariadb experience.
I guess any actual incompatibility is in fringe cutting edge features, which is where both databases have diverged.
I recall a Bryan Cantrill talk about how Oracle is this completely amoral machine that just wants to make money, and how that drives their every decision [0]. Is this no longer the case? Or is it, and if so, how does improving MySQL and giving it away for free making Oracle money? Or was it always hyperbole and Oracle is improving MySQL because they want to be nice?
[0] https://www.youtube.com/watch?v=-zRN7XLCRhc&t=2047s, that link starts at the beginning of what's popularly known as "the lawnmower rant", it's delightful.
In Bryan's defense, the lawnmower rant is pretty true- they are a simple company. They focus heavily on making money and abandoning things they aren't in a good position to do so from.
Selling per-cpu-core licenses and support contracts, great! Experimenting on or converting small projects into new markets? Not great. Fleshing out OpenSolaris into a product that could rival linux? Dead end.
Maybe it could have worked out, but the lawnmower didn't care about a product that had too few users to make money from. OTOH, even Facebook uses MySQL... All they need to do is invest enough to maintain market share to get support contracts.
Bringing MaximeVM out of research labs into the market as GraalVM.
First RDMS to support Perl and Java as stored procedures.
Join venture with Sun for Network Computer thin clients.
> Fleshing out OpenSolaris into a product that could rival linux?
Every OpenSolaris sales is one sale less from Unbreakable Linux, so in a day and age where UNIX === Linux, a company like Oracle has chosen what makes more sense monetarly.
I doubt that IBM is getting lots of new AIX customers.
And in the end, except for a timid offer from IBM, no one cared to rescue Sun.
But I guess they must have had at least sane management that tried to keep the existing mindshare of acquired projects (as far as I know many people went from Sun to Oracle directly for example), as well as finding new talent. And also, the acquisitions made sense and were done at a comparatively good price so I guess they are good lawnmowers with long-term plans (but terrible PR)?
The talk was in 2011, Oracle purchased Sun in 2010. So basically their continuous work on OpenJDK has nothing to do with the talk at all.
and yes, Oracle is 110% focused on the bottom line. Preferably this quarter
* Permission management on Postgres is much more painful. * The need for an external connection pooler makes postgres more annoying to set up. * Better quality docs on performance tuning MySQL.
but way more flexible and powerful
I only work in the Java world where all application servers have the connection pooling built in, so I never had the need for an external connection pooler.
I believe that developers back in the 90's wanted a way to "drop in" various low and higher cost DBMS systems to their client-server based apps without the need of hiring full-time database sysops.
It can be useful if your program is simple enough to work with both MySQL and SQLite, but often you should just stick with one DB and get to use all its features.
(arguably SQL itself is/was supposed to be that common layer in the first place, but db vendors always want to be able to present users with new features that are not yet standardized)
mysql and MariaDB diverged from versions 5.6 and 10 respectively, although MariaDB can and does still merge some fixes from mysql; oracle don’t do that.
MariaDB and the original InnoDB developers are now doing heavy rewrites with big performance gains, replication options like multi-source, spider for sharding and galera are all built in and actively maintained.
Oracle attempted to extort anyone trying to licence a mysql based product(rip infobright), so infinidb and clustrix, now respectively called columnstore and xpand, have new homes with MariaDB and are core parts of the system. Online schema changes, easy horizontal scale (huge numbers that make db2 and oracle sweat), s3 backed tables, non blocking backups, flashback, versioned tables.
The list goes on, and many industry veterans from ibm, oracle, sybase etc are finding a home there and helping MariaDB grow.
There are new public developer resources and the connectors are feature rich and well loved. There have been a few free online conferences during the pandemic, and MariaDB now has its own place at fosdem not just bundled in with mysql.
If you go enterprise, there isn’t much out there as accessible as maxscale for transaction failover!
MySQL 5.7 supports multi-source replication: https://dev.mysql.com/doc/refman/5.7/en/replication-channels...
> spider for sharding and galera are all built in and actively maintained.
Galera is available in MySQL-compatible binaries from Percona (Percona-XtraDB-Cluster): https://www.percona.com/software/mysql-database/percona-xtra...
MySQL also has their own Paxos-compliant clustering solution (called "Innodb cluster"), first available in 5.7, but much better in 8.0:
https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster...
I played with Spider in MariaDB 10.3, and it didn't satisfy the requirements we had, and it doesn't seem to have progressed significantly since then.
Yes, it's neat that MariaDB merged it and is keeping it working, but there don't seem to be many use cases where it ends up being significantly better than e.g. multi-source replication (the use cases where you're going to run out of disk, in most cases spider's un-implemented push-down join etc. make it too slow to be practical either).
> Online schema changes,
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op...
(including "instant column add")
> easy horizontal scale (huge numbers that make db2 and oracle sweat), s3 backed tables, non blocking backups,
MySQL has had "MySQL Enterprise Backup" for a long time. Percona wrote a similar implementation (XtraBackup), which works for MySQL, Percona, and MariaDB. AFAIK MariaDB copied XtraBackup as mariabackup (not criticising them for doing this, just pointing out that Percona did the original heavy lifting on this).
> flashback, versioned tables
Yep, versioned tables are a nice feature that I don't think are available in any other MySQL-like DB.
> If you go enterprise, there isn’t much out there as accessible as maxscale for transaction failover!
Not even ProxySQL ( https://proxysql.com/ )?
To be clear, I like MariaDB, but Oracle isn't doing a bad job on MySQL. MySQL 5.7 is pretty solid, and they have been improving defaults (while leaving the option to enable compat settings which are necessary when migrating a large deployment between versions), and MySQL 8.0 has some really nice improvements. For some of the best features from each, Percona (either standard Percona 8.0, which gives features like MariaDB's thread-pool, more comprehensive encryption including redo-logs etc., or XtraDB-Cluster to go with Galera instead of Innodb Cluster) offers quite a few benefits/improvements (and is contributing a lot of bugfixes/improvements upstream).
(My team runs quite a big installation, currently running Percona 5.7, and migrating to MariaDB probably won't be practical or justifiable considering replication compatibility which is a requirement to do no-downtime migrations)
Which is a pity, Netbeans is my favourite IDE and still has features hardly replicated in other Java IDEs, including the InteliJ Ultimate edition.
JetBrains would rather sell two licenses than allow for integrated development and debugging of native methods.
I disagree with this
It is fair to say Oracle did not invest in ease of Oracle->MySQL migration but they have been making a steady progress on making MySQL better database for modern applications