Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL 5.7.6 is out

datacharmer.blogspot.com

Post reply on HN
Print view View original

41–50 of 70 posts

Re: MySQL 5.7.6 is out

#41

Post by porker »

pilif wrote: post #24
kyrra wrote: post #20

Earlier quoted context omitted.

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?

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 too…

> CTEs

Coming from a MySQL background I didn't understand the fuss over CTEs ...until I worked with a SQL Server guy. That completely opened my eyes to how _sane_ things could be, vs my MySQL work-arounds.

Made me wish I'd chosen a Microsoft route!

Re: MySQL 5.7.6 is out

#42

Post by FireBeyond »

tsuraan wrote: post #33
morgo wrote: post #19

Earlier quoted context omitted.

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

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.

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).

Re: MySQL 5.7.6 is out

#43

Post by FireBeyond »

pilif wrote: post #11
taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

>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 acces…

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).

Re: MySQL 5.7.6 is out

#44

Post by IgorPartola »

morgo wrote: post #38
IgorPartola wrote: post #34

Earlier quoted context omitted.

Thanks. I'll add one more and maybe this is already in the works: last I checked you couldn't rebuilt an index of an InnoDB table without re-inserting all the rows into a table. This meant that adding an index to a huge table was very slow. I didn't know about a lot of these improvements, and am happy that they are happening. I moved away from MySQL around 2012-2013 because of the above reasons, so glad that things a…

MySQL 5.6 supports Online DDL. Most operations are online, you can see a list at: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-o... (The table 14.5 column "Allows Concurrent DML?" is the interesting one.)

Awesome, thanks. I am actually really happy about this.

Re: MySQL 5.7.6 is out

#45

Post by morgo »

tsuraan wrote: post #33
morgo wrote: post #19

Earlier quoted context omitted.

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

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.

Yes. See slide 43 for examples of statements that now produce errors by default: 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-transitionin...

Re: MySQL 5.7.6 is out

#46

Post by JohnBooty »

taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

  > 3. It's performance is predictable.  99% of issues 
  > with performance are easily solved with the right index
This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be.

And then when you do have everything indexed correctly, MySQL is significantly dumber (edit: looks like I'm significantly dumber! see correction below) about using those indexes than MSSQL or Postgres.

For example: those two can do index-only SELECTS. If table "foo" has columns A, B, C, D, E, and F and I've created an index on A and B, why can't "select A,B from foo" be served directly from the index? Postgres and MSSQL do it, and it's a huge performance boost in those two.

Postgres also lets you do some amazing things with partial indexes, indexes on computed values, etc.

MySQL might not be horrible here, but out of the 3 major relational databases I've used, it's clearly the weakest at this so it's funny to hear it touted as a strength.

Edit: Good news - I was wrong; MySQL does support index-only selects (covering indexes). User morgo also pointed out the improved JSON format for explain as well.

Re: MySQL 5.7.6 is out

#47

Post by parfe »

meritt wrote: post #40
pilif wrote: post #24

Earlier quoted context omitted.

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 too…

As a long-time and fairly technical MySQL user and only light experience with PostgreSQL, can you suggest the best reading material (in addition to the user manual) on the subject? Comparatively, for MySQL, my recommendation would be 'High Performance MySQL'.

The postgres manual. Seriously. It's all you need to forget mysql existed.

http://www.postgresql.org/docs/9.4/interactive/index.html

Re: MySQL 5.7.6 is out

#48

Post by equoid »

morgo wrote: post #19
pilif wrote: post #11

Earlier quoted context omitted.

>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 acces…

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

"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.

Re: MySQL 5.7.6 is out

#49

Post by JohnBooty »

porker wrote: post #41
pilif wrote: post #24

Earlier quoted context omitted.

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 too…

> CTEs Coming from a MySQL background I didn't understand the fuss over CTEs ...until I worked with a SQL Server guy. That completely opened my eyes to how _sane_ things could be, vs my MySQL work-arounds. Made me wish I'd chosen a Microsoft route!

Yeah, once you've used CTEs it's impossible to go back. Same with window functions.

Good news is that Postgres has great CTE support and it was ahead of MSSQL in window functions last I checked!

Re: MySQL 5.7.6 is out

#50

Post by j_s »

morgo wrote: post #37
jordigh wrote: post #35

Earlier quoted context omitted.

> Community Manager for MySQL here! I always found this title a bit weird. How does one exactly manage a community? :-) https://plus.google.com/+AaronSeigo/posts/DHEx1tu5dSK

Please see my comments on this thread: https://news.ycombinator.com/item?id=8318199 I agree it's a silly name, but it's not like we created it :)

If you've not heard of the term 'tummler' this might be helpful:

To build a strong community, stop “community managing”, be a Tummler instead.

http://dangerouslyawesome.com/2014/04/community-management-t...

Post reply on HN