That’s not to downplay the complexity of providing that, I’m sure it’s technically difficult, but this is basically the worst debugging experience I’ve had of any language.
"you've got a syntax error at around here: [the entire query]. go consult the manual or something idk"
I downloaded DataGrip after reading your comment, and damn, the autocomplete is just so good. It even shows me the arguments of Postgresql extensions like TimescaleDB.
This almost reads like an ad, but I just wanted to thank you for changing my life :P
Been migrating a 5 year old Percona MySQL galera cluster over to MariaDB galera. Galera multi-master clusters, for my use case, are a huge win. A month ago I had started down the path of replacing it with CockroachDB, but Cockroach only supports UTF-8 and my use case requires LATIN-1, sadly.
With MariaDB I'm using the digitalocean repos and I guess we'll see how stable those are.
Galera has been great, have used it for 5+ years and only a couple hiccups. This is a rather small database, but nearly mission critical. A couple times the cluster failed to start and I had to figure out the most recent node and manually bootstrap from it. I built some small shell tools to tell me if the cluster was happy, and bootstrap, because I had to do it so in frequently I could never remember the queries and files involved, and didn't want to have to read through my docs when the cluster was down.
Our use case is a small e-mail relay, so the win with galera is each e-mail server also runs a database node, and since they are master/master we can take one node down at a time for maintenance without having to do any failovers or the like.
In my current set-up (inherited), the unit tests run using H2 while running code talks to MySQL, so I have two databases that give unhelpful generic error messages.
But Oracle usually does point out the exact character/token in the SQL statement where the error originated from.
Thanks MySQL team!
Did anyone choose it over pg, and if so, why?
I did pick MySQL over Postgres for a old project that I'm currently working on again, the primary reason was easy multi-master replication via galera. I know that Postgres has long had many cluster stories, but I always struggled to even figure out which one was the right choice to make. Most of the clusters I've deployed ended up using DRBD and heartbeat rather than one of the Postgres native options.
As far as advance features, quite a few years ago I was doing this exercise to generate reports on some sample payroll dataset. The expectation was clearly that you would run a query, massage that data in some code, then run a bunch of other queries in that code to further correlate the data. I forget the exact problem that was posed.
I'm not super strong in database advanced features, but in the end I was able to come up with some fairly simple SQL, using materialized views or CTEs or similar, that produced the report entirely using SQL, in a fairly short runtime.
This was my first real experience with using MySQL beyond the basic features it had in decades past, and I was quite impressed with it.
Which JSON type are you missing? I store and query JSON extensively (probably too extensively) in MySQL and most things work great.
MySQL has had a full-featured native JSON column type since MySQL 5.7 (Oct 2015). Here's the current manual page for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/json.html
MariaDB does not have a JSON column type, instead aliasing it to LONGTEXT. MariaDB takes an alternative approach of providing JSON manipulation functions which work with stringy types. https://mariadb.com/kb/en/json-data-type/
Note that in some Linux distributions, when you try to install "mysql" packages you instead get "mariadb", so you may need to query the server to see which one it really is!
I plan to use CockroachDB as a distributed data store since it's protocol compatible with Postgres. Still in testing phase, but the deployment is very easy.
My blocker was that I'm using it in part for Postfix maps, and the Postfix pgsql module can't be configured to us anything but LATIN-1 encoding, and Cockroach only supports UTF-8.
MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups.
I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.
> MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.
Because of course it's very reasonable to accept a foreign key specification but completely ignore it.
At least the MariaDB folks try to fix the problems that are passed down to them. MySQL just seems content to tick the box, "Yeah we have that feature too, please don't switch to PostgreSQL!" and leave it at that. MySQL is okay for typical CRUD workloads with old-fashioned scalar types, but don't expect much with respect to the newer, fancier features.
> At least the MariaDB folks try to fix the problems that are passed down to them
This assertion simply does not make sense. MariaDB hard-forked its codebase from MySQL's a full decade ago. They were able to use/port some open source MySQL code through ~5.7 but that seems to have stopped in recent years with MySQL 8, probably due to its major architectural changes.
These days MySQL and MariaDB each have some nice features that the other one lacks.
With respect to what is "passed down", by whom and to whom: the creator of MySQL is literally the chief architect at MariaDB, and many other early MySQL AB engineers work at MariaDB. Meanwhile most of MySQL's poor reputation today is a lingering effect of its early versions, largely before the Oracle acquisition of Sun. In this context, I am continually confused as to why some Postgres users have a higher opinion of MariaDB than MySQL.
Edit to add: I failed to mention that I do absolutely concede that for a new application with heavy geospatial component, Postgres+PostGIS is absolutely the correct choice. InnoDB's geospatial support is comparatively much newer and less widely used.
These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.
Tryin to find Postgres tooling like that hasn’t been very productive. Thanks for the tip just for this one.
Our job is to be aware of shortcomings and work around it. This rule applies to any software, any language. I'm sorry that using MySQL has made you so mad, but deliberate spreading false knowledge simply because you're angry helps no one.
If you don't use MySQL, it doesn't mean you're aware of who does, how they use it and what's the revenue produced.
It'd be great if you could expand your post and add details, perhaps even some (anecdotal) evidence that sheds more light into your point of view.