4 ms·

PostgreSQL: "your date 2020-02-31 isn't a date, fix that" MySQL: "2020-02-31? Whatever man, I'll just enter something..."

by corty 6y ago

consp 6y ago
Considering the US uses a weird date format, I definitely prefer the former in combination with input sanitation forcing you to thing about your actions before assuming the database will fix it for you.
- corty 6y ago
  Agreed. As with strong typing in programming languages, I do prefer a database to be strict in rejecting invalid inputs. MySQL does two bad things here: It accepts an invalid input plus it interprets it creatively, producing something the user most probably didn't intend. In that respect, MySQL is almost as bad as Excel creatively "interpreting" dates. Another example of a database doing improper things would be Oracle mixing up the empty string with NULL. In Oracle, both are the same... MySQL has a few more of those gotchas, e.g. regarding broken charsets (UTF-8 isn't 'utf8', it is 'utf8mb4', 'utf8' is an alias for 'utf8mb3' which is a broken subset). I wouldn't use MySQL for any data that was important to get back consistently. However, since Uber seems to be using some schemaless "we don't care"-layer anyways, that point is moot for the original article.
  - bbarnett 6y ago
    https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo... Much of your MySQL complaint is not a MySQL issue, but a config issue. And yes, powerful config options are good, not bad.
    - BitPirate 6y ago
      One can still complain about mysqls dumb defaults. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.ht... "Hey, let's just not act transactional on a timeout by default"
      - evanelias 6y ago
        That default is sensible. You're making it sound like this setting applies to all "timeouts" regardless of type. That is not the case. This setting applies to lock-wait timeouts. The default value allows applications to decide on the correct course of action: either re-try just the statement that timed out (without having to re-do the previous parts of the transaction), or rollback the transaction. The application still receives an error on the timeout regardless of this setting. The database doesn't automatically commit the previous statements in the transaction regardless of this setting.
  - vinger 6y ago
    utf8mb3 isn't a broken subset. It includes characters in the basic multilingual plane which is fine for 99% of cases. uft8mb4 uses more bytes than necessary for most. You get 255 varchar limits with uft8mb3, I think you only get 192 characters with uft8mb4.
    - Alexendoo 6y ago
      uft8mb4 does not use more bytes than utf8mb3, for anything representable by utf8mb3 the size in uft8mb4 is identical. Anything that would be 4 bytes in uft8mb4 is not able to be stored in utf8mb3 utf8mb3 is definitely a broken subset, it's deprecated at the very least
isoprophlex 6y ago
That doesn't exactly sell me on MySQL, sounds like a recipe for disaster
- corty 6y ago
  There is more, however not everything is still valid or valid for every table type in MySQL: https://sql-info.de/mysql/gotchas.html https://sql-info.de/mysql/gotchas.html
  - evanelias 6y ago
    "This page deals with issues related to MySQL 4.1 and earlier, not 5.0" MySQL 5.0 came out over 15 years ago.
martimarkov 6y ago
Oh yeah old complication of sanitising input... It’s like asking you for an int and you entering 2.358 and saying that’s just simple.
chris_wot 6y ago
That's not decreasing complexity, that's just shifting it out of the database layer. See https://news.ycombinator.com/item?id=26272084 https://news.ycombinator.com/item?id=26272084
rubyist5eva 6y ago
The mysql behavior terrifies me because eventually you're going to end up with something you didn't expect and it's going to be a pain in the butt to track it down.
exikyut 6y ago
Ooh, sounds like PHP 5!
evanelias 6y ago
MySQL's default settings reject invalid dates for over 5 years now, since MySQL 5.7. How long are people going to keep repeating this complaint? Literally every version of MySQL and MariaDB that allows invalid dates by default (MySQL 5.6 and older, MariaDB 10.1 and older) has reached end-of-life for upstream support from the vendor!
- deleted 6y ago
  [deleted]

PostgreSQL: "your date 2020-02-31 isn't a date, fix that" MySQL: "2020-02-31? Whatever man, I'll just enter something..."

by corty 6y ago

PostgreSQL: "your date 2020-02-31 isn't a date, fix that" MySQL: "2020-02-31? Whatever man, I'll just enter something..."

consp 6y ago

Considering the US uses a weird date format, I definitely prefer the former in combination with input sanitation forcing you to thing about your actions before assuming the database will fix it for you.

corty 6y ago
Agreed. As with strong typing in programming languages, I do prefer a database to be strict in rejecting invalid inputs. MySQL does two bad things here: It accepts an invalid input plus it interprets it creatively, producing something the user most probably didn't intend. In that respect, MySQL is almost as bad as Excel creatively "interpreting" dates. Another example of a database doing improper things would be Oracle mixing up the empty string with NULL. In Oracle, both are the same... MySQL has a few more of those gotchas, e.g. regarding broken charsets (UTF-8 isn't 'utf8', it is 'utf8mb4', 'utf8' is an alias for 'utf8mb3' which is a broken subset). I wouldn't use MySQL for any data that was important to get back consistently. However, since Uber seems to be using some schemaless "we don't care"-layer anyways, that point is moot for the original article.
- bbarnett 6y ago
  https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo... Much of your MySQL complaint is not a MySQL issue, but a config issue. And yes, powerful config options are good, not bad.
  - BitPirate 6y ago
    One can still complain about mysqls dumb defaults. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.ht... "Hey, let's just not act transactional on a timeout by default"
    - evanelias 6y ago
      That default is sensible. You're making it sound like this setting applies to all "timeouts" regardless of type. That is not the case. This setting applies to lock-wait timeouts. The default value allows applications to decide on the correct course of action: either re-try just the statement that timed out (without having to re-do the previous parts of the transaction), or rollback the transaction. The application still receives an error on the timeout regardless of this setting. The database doesn't automatically commit the previous statements in the transaction regardless of this setting.
- vinger 6y ago
  utf8mb3 isn't a broken subset. It includes characters in the basic multilingual plane which is fine for 99% of cases. uft8mb4 uses more bytes than necessary for most. You get 255 varchar limits with uft8mb3, I think you only get 192 characters with uft8mb4.
  - Alexendoo 6y ago
    uft8mb4 does not use more bytes than utf8mb3, for anything representable by utf8mb3 the size in uft8mb4 is identical. Anything that would be 4 bytes in uft8mb4 is not able to be stored in utf8mb3 utf8mb3 is definitely a broken subset, it's deprecated at the very least

isoprophlex 6y ago

That doesn't exactly sell me on MySQL, sounds like a recipe for disaster

corty 6y ago
There is more, however not everything is still valid or valid for every table type in MySQL: https://sql-info.de/mysql/gotchas.html https://sql-info.de/mysql/gotchas.html
- evanelias 6y ago
  "This page deals with issues related to MySQL 4.1 and earlier, not 5.0" MySQL 5.0 came out over 15 years ago.

martimarkov 6y ago

Oh yeah old complication of sanitising input... It’s like asking you for an int and you entering 2.358 and saying that’s just simple.

chris_wot 6y ago

That's not decreasing complexity, that's just shifting it out of the database layer. See https://news.ycombinator.com/item?id=26272084 https://news.ycombinator.com/item?id=26272084

rubyist5eva 6y ago

The mysql behavior terrifies me because eventually you're going to end up with something you didn't expect and it's going to be a pain in the butt to track it down.

exikyut 6y ago

Ooh, sounds like PHP 5!

evanelias 6y ago

MySQL's default settings reject invalid dates for over 5 years now, since MySQL 5.7. How long are people going to keep repeating this complaint? Literally every version of MySQL and MariaDB that allows invalid dates by default (MySQL 5.6 and older, MariaDB 10.1 and older) has reached end-of-life for upstream support from the vendor!

deleted 6y ago
[deleted]