
MySQL 5.7.6 is out - porker
http://datacharmer.blogspot.com/2015/03/mysql-5.html
======
morgo
Community Manager for MySQL here!

A better overview link for what's new in 5.7.6 specifically is this one:
[http://mysqlserverteam.com/the-mysql-5-7-6-milestone-
release...](http://mysqlserverteam.com/the-mysql-5-7-6-milestone-release-is-
available/)

To understand what's new in 5.7 overall:
[http://dev.mysql.com/doc/refman/5.7/en/mysql-
nutshell.html](http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html)

Specific to this blog post. Here is the page on upgrading:
[http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-
previo...](http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-
series.html)

I'm happy to answer any questions, please feel free to ask away.

~~~
DonHopkins
"The old syntax was meant to be only deprecated, but it was accidentally
completely removed."

How could Oracle have possibly "accidentally completely removed" the old
syntax for setting a password even though it "was meant to be only
deprecated", without being completely incompetent?

I can not in my wildest imagination come up with a scenario in which a
competent developer could possibly "accidentally remove" something like that.
If they made such a huge "accident" with that security feature, what other
terrible negligent "accidents" lurk just beneath the surface of this new
release of MySQL?

How can you "LOL" away such an incompetent "accident" like that?

~~~
datacharmer
"LOL away?" What gave you the impression that I was making fun of it? I am the
one who found the bug and reported it, and this is the explanation I got.
Since the person I talked to is someone I trust, I don't have reason to think
otherwise. I can elaborate further and tell you why this particular error was
likely to happen: it's because since the beginning MySQL was built with root
user without password, and most of the tests were running in such conditions.
That was the culture of rapid growth that gave MySQL its popularity and the
developers are paying now the technical debt that has left so many features
with poor testing behind. What Oracle is doing now is trying to redress that
situation by strengthening the defaults, for which I commend them. Being a QA
developer, I am less pleased with the quality of tests that let such mistake
pass, but I know that such things happen, and it does not necessarily mean
that there is a can of worms behind this bug.

~~~
DonHopkins
It's not my impression that you're making fun of it, but that you're trying to
excuse it as an inconsequential "accident" instead of incompetence. Accidents
like that don't happen without causes like incompetence or negligence or
malice. Accidents like that are a symptom that something is deeply wrong.

------
taf2
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 to compliant mysql. Including redis and
elasticsearch. Both of these tools IMO compliant MySQL.

I'm no fan of Oracle by any means but at least MySQL is still an open source
database. Perhaps when I have free time before the next startup I'll give
Postgres a good look. For now I'll probably try it out with AWS redshift...

~~~
pilif
_> 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.

~~~
kyrra
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
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
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-...](http://stackoverflow.com/questions/17147920/postgres-window-
function-lag-equivalent-query-in-mysql)

~~~
ldng
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_test...](https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/)

------
joshstrange
MySQL doesn't excite me at all anymore. It used to be my DB of choice but as
of late I've been using postgres and other than tools being somewhat lacking
(this is getting better all the time, there are now GUI browsers for postgres
that rival sequel pro). Nothing was really keeping me on MySQL other than it
was generally the prefered DB for most OS projects and I had used it in
professional settings multiple times. Postgres's JSON store is very
interesting and I've read a number of articles on other cool things in
Postgres all while MySQL was more or less silent. I'd love to setup a postgres
server at work and benchmark it against our MySQL DB, the one thing really
holding me back is our use of ENUMs but I don't think that's going to be a big
barrier and I hate them anyways.

~~~
pilif
_> the one thing really holding me back is our use of ENUMs but I don't think
that's going to be a big barrier and I hate them anyways_

Postgres has proper support for ENUM as defined in the SQL Standard. You have
to create a new type and declare it as being an ENUM type:

[http://www.postgresql.org/docs/9.4/static/datatype-
enum.html](http://www.postgresql.org/docs/9.4/static/datatype-enum.html)

~~~
joshstrange
Very interesting! Thanks for the link, even with that I'd rather convert the
ENUMs to just VARCHARs as our MVC framework doesn't support ENUMs but it "Just
works" because it sends strings to MySQL and MySQL stores them as INTs that
map to ENUM values. Because of this the test framework never played nice with
creating test DB's that used ENUMs.

------
mootothemax
There's one very important reason that I always return to MySQL:

\- If something goes wrong at 3am, I know how to fix it.

Whether it's performance, server setup, or weird edge cases, I have it
covered.

This being the sole reason, does anyone have any "when you're in trouble"
guides for Postgres or others?

------
jordigh
Dear lazyweb,

How active is the MariaDB development compared to MySQL's?

Should we still be concerned with Oracle trying to kill MySQL? How has that
whole debacle played out?

~~~
morgo
Edward Screven addresses this point in this video:
[https://www.youtube.com/watch?v=fzCpd4j72jA](https://www.youtube.com/watch?v=fzCpd4j72jA)

\- Oracle sees MySQL users as a distinct group of customers from the Oracle
Database

\- 5 years on, the engineering team on MySQL is 2x what it was when acquired.

By a simple metric (LoC), MySQL 5.6 has a larger delta than any previous
release: [https://www.flamingspork.com/blog/2013/03/05/mysql-code-
size...](https://www.flamingspork.com/blog/2013/03/05/mysql-code-size/)

Edit: See also this interview with Percona's CEO -
[http://www.zdnet.com/article/mysql-why-the-open-source-
datab...](http://www.zdnet.com/article/mysql-why-the-open-source-database-is-
better-off-under-oracle/)

~~~
jordigh
And I unlazied myself a little and found this:

[https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-
features/](https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/)

If marketing is to be believed, it does seem like MariaDB offers many points
of attraction over MySQL. The fact that Oracle does not provide test cases or
has proprietary extensions is a bit alarming from a software freedom
perspective.

------
spacemanmatt
For the effort to absorb "big changes" I would also weigh porting to
PostgreSQL.

~~~
VLM
Its clickbait headline, or maybe they're going for outright sarcasm given how
little is changing.

Technically its true that the install process is a little different but not
much, and theres some minor changes in user configuration. I've looked into it
and the most exciting "DBA level" changes are YEAR(2) is finally utterly
expunged, and there's some new reserved words "NONBLOCKING" and
"OPTIMIZERCOSTS". Thats about as exciting as it gets.

Its not transparent upgrade but its not like they're changing the default db
engine to an embedded nosql engine, or changing the default char encoding to
EBCDIC.

~~~
morgo
I would say the biggest change is that the default SQL mode will be the
equivalent to 5.6's
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER.

This means that a number of statements that produced warnings (but proceeded)
will now produce errors.

I have some slides on it here: [http://www.slideshare.net/morgo/upcoming-
changes-in-mysql-57](http://www.slideshare.net/morgo/upcoming-changes-in-
mysql-57) (see slide 33 onwards).

------
jkoudys
> The main effort of the team has been focused on speed, with performance
> reportedly improved from 2 to 3 times compared to previous releases.

Definitely good news, as MySQL came out during the peak of Moore's-law (or,
more appropriately, a misapplication of) fueled idiocy where nobody seemed
interested in investing in better performing software, since they all expected
they'd have faster hardware next year. Now everybody's deploying to VPSes, so
it's once again important that we get the most out of every single CPU cycle.
I haven't gone through all the diffs, but if they're promising 2x to 3x
improved performance, there must have been some pretty fundamental rewrites of
old code.

Coming from the Oracle + DB2 world, what I'd really like to see is some
improved performance analysis and query optimization tools. There have been
some incremental improvements to EXPLAIN (the new 'EXPLAIN on a named
connection' feature seems pretty cool for when I have 10+ application
instances all connecting to my DB), but it's still got a long way to go.

~~~
morgo
I totally hear you here :)

 __Visibility __is the most useful and always under-rated feature. If I can
clarify some of the recent enhancements here:

\- MySQL 5.6 introduced `EXPLAIN FORMAT=JSON`. MySQL Workbench uses this to
visualize query plans (important as they get complicated.)

\- Also in 5.6 was optimizer trace (find out why indexes were not used etc.)
and performance_schema enabled by default.

\- MySQL 5.7 adds cost information to `EXPLAIN FORMAT=JSON` (Workbench also
understands it.)

\- Workbench also has a set of performance views based on Performance_schema
(based on a project called SYS). You can install it as standalone from here
too: [https://github.com/MarkLeith/mysql-
sys](https://github.com/MarkLeith/mysql-sys)

\- The runtime performance data from SYS is very useful for making correct
optimization decisions. Being an SQL interface is also friendly for writing
your own scripts against it.

Also, to clarify on your first paragraph:

Yes, refactoring old code was one of the ways this was achieved. We picked
refactoring targets based on performance and because some of the older code is
not very testable.

------
hit8run
How stable is it? When is it supposed to be used in production?

~~~
morgo
5.7.6 is what we call a "Development Milestone Release", and is stated to be
of approximately release candidate quality.

We do not have a release date, but we're committed to a 2-3 year release
cycle, with 5.6 being the last release in Feb 2013.

------
dschiptsov
_multiple page cleaner threads_ \- this is nice.

