4 ms·

>1. It's reliable/durable I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept an

by pilif 12y ago

>1. It's reliable/durable I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept and alter invalid data really don't strike me as neither reliable nor durable. Your other points are valid reasons for sticking with MySQL though. If you're happy and the tool does what you need, that's fine. But once you've seen what you can do as you gain access to the more advanced SQL features and once you've burned by MySQL not starting up because of data corruption, then you might want to investigate other options. Postgres happens to be a very good alternative with good tools, predictable performance, very advanced SQL features, but also really good reliability and durability.

morgo 12y ago
In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.
- tsuraan 12y ago
  So, MySQL, as of 5.7, no longer accepts dates with year 0, and it also stopped converting unrecognized date strings into the date 00-00-0000? That would be a very positive change.
  - FireBeyond 12y ago
    It has "not supported" them for a long time. But it wasn't the default, due to backward compatibility (of course the decision that lead to this in the first place is no less suspect).
  - morgo 12y ago
    Yes. See slide 43 for examples of statements that now produce errors by default: http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 This will break some applications that are upgrading. I have a whitelist/blacklist suggestion on how to transition on my blog: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...
- equoid 12y ago
  "The default sql mode has changed." So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.
  - morgo 12y ago
    Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar). Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.
kyrra 12y ago
Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption. What "advanced SQL features" do other DB products have that you cannot do with MySQL?
- pilif 12y ago
  CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.
  - kbenson 12y ago
    It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
    - ldng 12y ago
      I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...
FireBeyond 12y ago
Though "you shouldn't have to", my first recommendation for anyone setting up a MySQL installation is to go to the Percona Configuration Wizard and build a much more appropriate configuration file (with strict and enforcing defaults, better performance and a whole bunch of other things).
IndianAstronaut 12y ago
I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.

by pilif 12y ago

morgo 12y ago

In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.

tsuraan 12y ago
So, MySQL, as of 5.7, no longer accepts dates with year 0, and it also stopped converting unrecognized date strings into the date 00-00-0000? That would be a very positive change.
- FireBeyond 12y ago
  It has "not supported" them for a long time. But it wasn't the default, due to backward compatibility (of course the decision that lead to this in the first place is no less suspect).
- morgo 12y ago
  Yes. See slide 43 for examples of statements that now produce errors by default: http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 http://www.slideshare.net/morgo/upcoming-changes-in-mysql-57 This will break some applications that are upgrading. I have a whitelist/blacklist suggestion on how to transition on my blog: http://www.tocker.ca/2014/09/01/suggestions-for-transitioning-into-strict-sql-mode.html http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...
equoid 12y ago
"The default sql mode has changed." So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.
- morgo 12y ago
  Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar). Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.

kyrra 12y ago

Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption. What "advanced SQL features" do other DB products have that you cannot do with MySQL?

pilif 12y ago
CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.
- kbenson 12y ago
  It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
  - ldng 12y ago
    I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...

FireBeyond 12y ago

Though "you shouldn't have to", my first recommendation for anyone setting up a MySQL installation is to go to the Percona Configuration Wizard and build a much more appropriate configuration file (with strict and enforcing defaults, better performance and a whole bunch of other things).

IndianAstronaut 12y ago

I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.