undefined | Better HN

0 pointsmdaniel9mo ago0 comments

> and has most of PostgreSQL's feature set while being faster and simpler to use for the average user.

I would read that blog post, because I am firmly in the "mysql/mariadb is for people who like mongo" camp but I like learning new things

While I was delighted to see that 11 no longer just straight-up throws input data in the trash, I get a lot of mileage out of transactional DDL which seems to still be a PG feature

  docker run -d --name my -e MARIADB_ROOT_PASSWORD=sekrit docker.io/library/mariadb:11.4.7

  docker exec -i my mariadb -psekrit mysql <<SQL
  BEGIN;
  CREATE TABLE just_kidding (pk int);
  CREATE TABLE onoz (migrations are hard, yo);
  ROLLBACK;
  SQL

  docker exec -i my mariadb -psekrit mysql <<SQL
  SELECT count(1) FROM just_kidding;
  SQL
  count(1)
  0

While digging into its stored-proc story, I found these two gems

https://mariadb.com/kb/en/sql_modemssql/

https://mariadb.com/kb/en/sql_modeoracle/

which I would enjoy exploring more

0 comments

evanelias9mo ago

> I was delighted to see that 11 no longer just straight-up throws input data in the trash

You're essentially proving the upthread commenter's point here... the relevant setting is strict sql_mode, which has been available as an option for literally 20 years, and has generally been used by any serious MySQL/MariaDB shop for that whole time. Long ago it wasn't enabled by default out of the box, but it has been since MySQL 5.7 (released 10 years ago) and MariaDB 10.2 (released over 8 years ago).

> I get a lot of mileage out of transactional DDL which seems to still be a PG feature

Correct, MySQL and MariaDB do not support transactional DDL, and maybe never will. That's not a unique shortcoming though, as Oracle and SQLite don't support it either. MS SQL Server does support it, but if I recall correctly there are caveats depending on the isolation level in use.

Postgres clearly wins out on that feature, but as with everything in computing, it comes with serious trade-offs: a rather sub-par MVCC implementation [1], and lack of DDL support in logical replication [2].

I'm biased because I work in this space, but IMO it's easy to live without transactional DDL in MySQL/MariaDB if you pair a good schema management system (which allows you to test and lint DDL) with an online schema change tool (which allows you to throw away the shadow table if something goes wrong). And generally you shouldn't be running DDL by hand directly in prod anyway...

[1] https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...

[2] https://www.postgresql.org/docs/current/logical-replication-...

mdanielOP9mo ago

Well, I did say that I'd enjoy learning more, because I'll join in any good rant about autovacuum being some "oh gawd"

But, let's be real talk here: what kind of RDBMS ships with a flag named "strict sql mode" that's available to be set to something? Its reputation wasn't born from uncharitable twitter take downs, it was the kind of thing I had to try in order to know if it still did crazypants things like `CREATE TABLE foo (d DATE); INSERT INTO foo VALUES ('lololo')`. So, sure, I hear you about "it hasn't been stupid for 10 years" but don't lose track of the first part of that qualifier

I hear rumors that Mongo isn't insaney pants anymore, either, but I for damn sure ain't running that shit and will quit places that do

evanelias9mo ago

> what kind of RDBMS ships with a flag named "strict sql mode" that's available to be set to something?

How about some of the most widely deployed database software on earth? For example SQLite doesn't even enforce column types by default today, and has only had the option to do so for less than four years!

In MySQL/MariaDB's case, yes they should have changed that default much earlier, but they historically over-indexed on backwards compatibility concerns around that time period.

j / k navigate · click thread line to collapse

0 pointsmdaniel9mo ago0 comments

> and has most of PostgreSQL's feature set while being faster and simpler to use for the average user.

I would read that blog post, because I am firmly in the "mysql/mariadb is for people who like mongo" camp but I like learning new things

While I was delighted to see that 11 no longer just straight-up throws input data in the trash, I get a lot of mileage out of transactional DDL which seems to still be a PG feature

  docker run -d --name my -e MARIADB_ROOT_PASSWORD=sekrit docker.io/library/mariadb:11.4.7

  docker exec -i my mariadb -psekrit mysql <<SQL
  BEGIN;
  CREATE TABLE just_kidding (pk int);
  CREATE TABLE onoz (migrations are hard, yo);
  ROLLBACK;
  SQL

  docker exec -i my mariadb -psekrit mysql <<SQL
  SELECT count(1) FROM just_kidding;
  SQL
  count(1)
  0

While digging into its stored-proc story, I found these two gems

https://mariadb.com/kb/en/sql_modemssql/

https://mariadb.com/kb/en/sql_modeoracle/

which I would enjoy exploring more

0 comments

evanelias9mo ago

> I was delighted to see that 11 no longer just straight-up throws input data in the trash

> I get a lot of mileage out of transactional DDL which seems to still be a PG feature

[1] https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...

[2] https://www.postgresql.org/docs/current/logical-replication-...

mdanielOP9mo ago

Well, I did say that I'd enjoy learning more, because I'll join in any good rant about autovacuum being some "oh gawd"

I hear rumors that Mongo isn't insaney pants anymore, either, but I for damn sure ain't running that shit and will quit places that do

evanelias9mo ago

> what kind of RDBMS ships with a flag named "strict sql mode" that's available to be set to something?

In MySQL/MariaDB's case, yes they should have changed that default much earlier, but they historically over-indexed on backwards compatibility concerns around that time period.

j / k navigate · click thread line to collapse