6 ms·

I recently moved an application off mysql to postgresql after hitting this error. After over a decade of mysql doing things their way and it always being a pain

by kimmel 13y ago

I recently moved an application off mysql to postgresql after hitting this error. After over a decade of mysql doing things their way and it always being a pain in the ass for everyone else when is enough, enough? I no longer develop software to run on mysql because I like my sanity.

willvarfar 13y ago
Well, funnily enough MySQL are now owned by Oracle and I've wasted a couple of days trying to get some sane semblance of `LIMIT` working for me on Oracle... so don't hold your breath, Oracle have an even longer history of damning their customers... I've been keeping a list of MySQL gotchas, but steering clear of the MySQL take on the SQL standard itself: http://williamedwardscoder.tumblr.com/post/25080396258/oh-mysql-i-hate-you http://williamedwardscoder.tumblr.com/post/25080396258/oh-my... I still contemplate MySQL for new projects, because better the enemy you know and all that... OMG, I'm mad! Postgres rah rah rah!
- fendale 13y ago
  Oracle does seems to make limit queries much harder than they need to be. I wrote an article about limit and paging queries in oracle ages ago that may interest you, but you probably have it all figured out now. http://betteratoracle.com/posts/18-limiting-query-results-top-n-and-window-queries http://betteratoracle.com/posts/18-limiting-query-results-to...
  - sendob 13y ago
    This actually made me think of: Oracle's "UTF8" vs "AL32UTF8" which I consider to be a gotcha, even though it is reasonable in terms of the evolution of the standard, a modern land mine perhaps. The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )
    - chris_wot 13y ago
      Oracle historically has poor support. Here's an old example with an ADO bug, they never fixed it even though the support ticket was open for almost 2 years! http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-oracle-ado-bug.html http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-or...
      - sendob 13y ago
        and unfortunately, they are the only ones who can offer support or implement a patch for their software! With postgres and mysql you can find many companies competing to offer great service, and who can contribute their fixes and improvements directly should they so choose. The stronger the output of these communities and the organizations that support them, the more pressure for even the companies like Oracle to improve ( or perish :))
- AlisdairO 13y ago
  You should be able to do LIMIT in Oracle using http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html http://www.oracle.com/technetwork/issue-archive/2006/06-sep/... . If you want a standards-compliant way to do it, you can use the ROW_NUMBER() window function. This might not be as fast, but will work on virtually every DB except MySQL...
  - willvarfar 13y ago
    Yes I'm deep beyond correctness and now want to make it fast... Oracle is like COBOL; the only techies who like it are those whose job security depends upon it ;)
rimantas 13y ago
Interesting, pretty much every system has insane things in it. Is postgresql an exception?
- sitharus 13y ago
  Certainly not! However, its WTFs per minute count is far lower than MySQL. I started off my career as a PHP/MySQL developer. PostgreSQL is definitely years ahead of MySQL. It used to be that MySQL had far superior read performance for simple queries, but that hasn't been true since around 2006. Also Postgres' SQL optimiser is really good.
  - rimantas 13y ago
    > PostgreSQL is definitely years ahead of MySQL I guess this does not apply to replication, upserts, etc. Well, I think I will just grab some popcorn and wait. It will be fun to watch how those who never bothered to learn MySQL (and by this I don't only mean its peculiarities) will get bitten for not bothering to learn PostgreSQL.
    - gbog 13y ago
      Why? Because you think everything is equal, everything has its own gotchas and not one is better than the other? That is against all history, not only in software. Postgres is obvectively better (safer, better engineered, easier to use, etc.) than MySql, just as Python is objectively better than PHP, and an AK47 was better than most other assault rifles of its time.
      - the_mitsuhiko 13y ago
        The lack of upserts is a problem in postgres, as much as some people say they can get away without them. There are objective concurrency issues with the lack of it, that have been acknowledged by postgres developers when they started work on upserts.
        13y ago
codeka 13y ago
I basically chose MySQL because of familiarity for the most part, but I think I'll definitely consider an alternative for my next project...
- kalleboo 13y ago
  For me it was because AWS RDS didn't support Postgres until recently, although that has changed now.
grey-area 13y ago
Yep, I switched to Postgresql after seeing a client lose data to corrupted databases - they had rebooted their server, and mysql refused to restart as a result and had to be manually recovered. Every time I hear another mysql horror story like this (truncation without warning was another favourite), it makes me thankful I no longer use it.
- sendob 13y ago
  if it matters, must have backups! ( +replication +geographical distribution etc as needs must ) Even a perfect database, should such a thing ever exist, is not sufficient to protect us from a determined disaster
  - grey-area 13y ago
    Sure, I came in after their disaster to clean up, and their issue was their backups weren't up to date and they had no replication etc (it was a small site, no sysadmin, they rebooted their server during a busy time and boom! All the other services came up ok, but not mysql). The fact that Mysql failed and ended up with corrupted dbs on a simple reboot (not power failure) doesn't fill me with confidence - that really shouldn't happen. That's just an anecdote of course, but I think I prefer working with psql anyway.

by kimmel 13y ago

willvarfar 13y ago

Well, funnily enough MySQL are now owned by Oracle and I've wasted a couple of days trying to get some sane semblance of `LIMIT` working for me on Oracle... so don't hold your breath, Oracle have an even longer history of damning their customers... I've been keeping a list of MySQL gotchas, but steering clear of the MySQL take on the SQL standard itself: http://williamedwardscoder.tumblr.com/post/25080396258/oh-mysql-i-hate-you http://williamedwardscoder.tumblr.com/post/25080396258/oh-my... I still contemplate MySQL for new projects, because better the enemy you know and all that... OMG, I'm mad! Postgres rah rah rah!

fendale 13y ago
Oracle does seems to make limit queries much harder than they need to be. I wrote an article about limit and paging queries in oracle ages ago that may interest you, but you probably have it all figured out now. http://betteratoracle.com/posts/18-limiting-query-results-top-n-and-window-queries http://betteratoracle.com/posts/18-limiting-query-results-to...
- sendob 13y ago
  This actually made me think of: Oracle's "UTF8" vs "AL32UTF8" which I consider to be a gotcha, even though it is reasonable in terms of the evolution of the standard, a modern land mine perhaps. The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )
  - chris_wot 13y ago
    Oracle historically has poor support. Here's an old example with an ADO bug, they never fixed it even though the support ticket was open for almost 2 years! http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-oracle-ado-bug.html http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-or...
    - sendob 13y ago
      and unfortunately, they are the only ones who can offer support or implement a patch for their software! With postgres and mysql you can find many companies competing to offer great service, and who can contribute their fixes and improvements directly should they so choose. The stronger the output of these communities and the organizations that support them, the more pressure for even the companies like Oracle to improve ( or perish :))
AlisdairO 13y ago
You should be able to do LIMIT in Oracle using http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html http://www.oracle.com/technetwork/issue-archive/2006/06-sep/... . If you want a standards-compliant way to do it, you can use the ROW_NUMBER() window function. This might not be as fast, but will work on virtually every DB except MySQL...
- willvarfar 13y ago
  Yes I'm deep beyond correctness and now want to make it fast... Oracle is like COBOL; the only techies who like it are those whose job security depends upon it ;)

rimantas 13y ago

Interesting, pretty much every system has insane things in it. Is postgresql an exception?

sitharus 13y ago
Certainly not! However, its WTFs per minute count is far lower than MySQL. I started off my career as a PHP/MySQL developer. PostgreSQL is definitely years ahead of MySQL. It used to be that MySQL had far superior read performance for simple queries, but that hasn't been true since around 2006. Also Postgres' SQL optimiser is really good.
- rimantas 13y ago
  > PostgreSQL is definitely years ahead of MySQL I guess this does not apply to replication, upserts, etc. Well, I think I will just grab some popcorn and wait. It will be fun to watch how those who never bothered to learn MySQL (and by this I don't only mean its peculiarities) will get bitten for not bothering to learn PostgreSQL.
  - gbog 13y ago
    Why? Because you think everything is equal, everything has its own gotchas and not one is better than the other? That is against all history, not only in software. Postgres is obvectively better (safer, better engineered, easier to use, etc.) than MySql, just as Python is objectively better than PHP, and an AK47 was better than most other assault rifles of its time.
    - the_mitsuhiko 13y ago
      The lack of upserts is a problem in postgres, as much as some people say they can get away without them. There are objective concurrency issues with the lack of it, that have been acknowledged by postgres developers when they started work on upserts.
      - 13y ago

codeka 13y ago

I basically chose MySQL because of familiarity for the most part, but I think I'll definitely consider an alternative for my next project...

kalleboo 13y ago
For me it was because AWS RDS didn't support Postgres until recently, although that has changed now.

grey-area 13y ago

Yep, I switched to Postgresql after seeing a client lose data to corrupted databases - they had rebooted their server, and mysql refused to restart as a result and had to be manually recovered. Every time I hear another mysql horror story like this (truncation without warning was another favourite), it makes me thankful I no longer use it.

sendob 13y ago
if it matters, must have backups! ( +replication +geographical distribution etc as needs must ) Even a perfect database, should such a thing ever exist, is not sufficient to protect us from a determined disaster
- grey-area 13y ago
  Sure, I came in after their disaster to clean up, and their issue was their backups weren't up to date and they had no replication etc (it was a small site, no sysadmin, they rebooted their server during a busy time and boom! All the other services came up ok, but not mysql). The fact that Mysql failed and ended up with corrupted dbs on a simple reboot (not power failure) doesn't fill me with confidence - that really shouldn't happen. That's just an anecdote of course, but I think I prefer working with psql anyway.