Today’s post features an interview with Morgan Tocker, MySQL community manager at Oracle. Morgan is an old friend of Percona, having worked here as director of MySQL training from 2009 to 2011. He’s also done stints at MySQL, Sun Microsystems and InPowered. You can follow his adventures at his blog, “Master MySQL.”  You can also connect with him face-to-face at the Percona Live MySQL Conference and Expo this April 1-4 in Santa Clara, Calif.  Use the promotional code “SeeMeSpeak” on the registration page and save 10 percent.

…

Over the past few years, we’ve seen MySQL technology advance in leaps and bounds, especially when it comes to scalability. But by focusing on the internals of the storage engine for so long, MySQL has fallen behind regarding support for advanced SQL features.

SQLite, another popular open-source SQL database, just released version 3.8.3, including support for recursive SQL queries using the WITH RECURSIVE syntax, in compliance with SQL:1999.

Why is this significant? It means that MySQL is now the only widely-used SQL implementation that does not support recursive queries. Fifteen years after it was defined in the SQL standard, almost every other SQL database of note has supported this feature:

• …

I wish more discussion happened on the internals mailing list, but if you’re interested in finding out what’s upcoming/changing in MySQL 5.7, so far the best resources I’ve found are:

• The video (1h41m is worth a watch), and blog post from Todd Farmer
• A presentation from Morgan Tocker (I wonder if we’ll see this at FOSDEM too)
• All of Morgan Tocker’s posts tagged community
• The 5.7 release …

I recently upgraded an instance to 5.7.3 the other day, and ran into an error, so I wanted to share the resolution for it here.

In my case, I was upgrading 5.7.1 to 5.7.3. However, this will apply to anyone wanting to upgrade from pre-5.7.2 (including 5.6/5.5) to 5.7.2+.

I performed the upgrade, in-place, and restarted mysqld. This was fine. However, then I attempted to connect via the command-line, and received the following error:

shell> mysql -uroot -ppass -P3310
ERROR 1130 (HY000): Host 'localhost' is not allowed
to connect to this MySQL server

Searching the net, you’ll mostly find RTM replies, which were all accurate as far as I could tell. In all of those prior reported cases, the issues were expected behavior and the issues were ultimately user error.

Of course I double-checked my config and data files. I knew I didn’t change anything in the user table, or any system table, for that matter. …

Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later).  We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date.  A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me.  The event was streamed and recorded, and you can view the full presentation on YouTube.  The slide deck can be found here.

The discussion around proposed deprecation was good, and this blog serves to document my own notes about what …

Todd Farmer’s MySQL 5.7 talk can been seen at http://www.youtube.com/user/sfmysql. For those few of you who did not stay up to watch Todd live, you get TWO slide decks in just over ninety minutes — it is almost the next best as being at the San Francisco Users Group in person.

In MySQL 5.5 metadata locks were introduced to ensure consistency for some operations that previously could cause problems. For example if a transaction SELECTed from a table and another connection changed the structure of the table before the transaction was committed; in that case the two operations would be written in reverse order to the binary log which could prevent replaying the binary log.

However there was no way to see who held metadata locks. SHOW PROCESSLIST would show who were waiting for metadata locks, and often you could guess who held it, but it wasn’t so always. Consider for example the following:

mysql> SELECT Id, db, Command, Time, State, Info FROM information_schema.PROCESSLIST; …

When MySQL 5.7 DMR3 was released, I couldn’t wait to try out the new UNION ALL optimization that no longer requires data to be materialized in a temporary table.

This new optimization can be seen via EXPLAIN, but it is also nice to run it through SHOW PROFILES, which breaks down query execution step by step. However, this feature is now deprecated (since it overlaps with performance_schema), and will be removed in a future version.

So today I wanted to show you a combination of: * What a UNION ALL statement looks like in MySQL 5.6 (EXPLAIN, SHOW PROFILES). * How it is improved in MySQL 5.7 (EXPLAIN, SHOW PROFILES). * How you can easily emulate the SHOW PROFILES feature with performance_schema + ps_helper

…

The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity.

There are three possible values, as documented in the manual:

As Giuseppe has written about previously, writing notes or “informational events” can create debugging problems because they reduce the …

On Friday (22/11/2013), we hosted webinar covering the new replication features in the MySQL 5.7 DMR as well as in MySQL Labs. You can now view the webinar replay here.

It’s a very exciting time for MySQL Replication! MySQL 5.6 contains numerous new replication features and Oracle recently announced…

• The second Development Milestone Release of MySQL 5.7, introducing yet again new replication features and enhancements including optimized multi-threaded slave, better consistency with semi-synchronous replication, and improved monitoring with new Performance Schema tables.
• Early access to multi-source replication via labs.mysql.com …