I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6.  There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned.  Here’s a quick summary:

You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc

This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies.  This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:

mysql> SELECT password_expired
-> FROM mysql.user
-> WHERE user = 'root' AND host = 'localhost';
+------------------+
| password_expired |
+------------------+
| N                |
+------------------+
1 row in set (0.00 sec)

mysql> SET @sql = 'ALTER USER …

I’ve noted previously that the new transportable tablespaces for InnoDB in MySQL 5.6 are a big step forward, and newly-released 5.6.9-rc makes importing tablespaces a bit easier.  In previous versions, you had to have a .cfg file to import the InnoDB tablespace.  That file is produced during FLUSH TABLE <tbl> FOR EXPORT operations, and contains InnoDB metadata that’s not contained in the .ibd tablespace file itself.  I filed a feature request requesting the .cfg file be made optional, and Sunny implemented it:

mysql> create table tt (a INT PRIMARY KEY, b VARCHAR(10), KEY(b)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.33 sec)

mysql> …

In a previous post, I had demonstrated how subquery materialization, introduced in MySQL 5.6.5, improves the performance of certain queries, like query Q16 of DBT3. Such improvement was easily explained:

• Subquery materialization has a high start up cost (it needs to create and fill the temporary table).
• But afterwards it has fast lookups (temporary table has a hash index, no duplicates, and is in memory).
• In other words, compared to EXISTS, the first evaluation of the IN predicate is slow (high start up cost) and all following evaluations are fast (just a hash lookup).
• In the DBT 3 setup, one outer table (named "part") has 200,000 rows, so there are 200,000 evaluations of …

Not much to add really to the bug I’ve filed here: bug#67159.

Again this GTID stuff looks good, but seems to prevent changes in the configuration of performance_schema, which I think is not appropriate, especially as P_S now has lots of extra goodies and after 5.6 will surely have even more.

Not much to add really to the bug I’ve filed here: bug#67099.

I personally can think of some very nasty consequences of applying this on the slaves I manage, and the reason I’m posting the bug is that while I guess this is too late to fix in 5.6 as it’s effectively a new feature, I’m sure many sites may bump into this and be somewhat disappointed if they want to use the new GTID feature and have several slaves.  Hence, if the fix/feature has to go into MySQL 5.7 then I hope it goes in sooner rather than later. We will see.

Updated: 2013-09-19

I probably should have updated this earlier but it does seem that Oracle have taken these comments on board. See: WL6559.  It looks like they plan to do this for 5.7 which is good news. I’m really hoping …

A colleague, Kris, commented recently on a recent find I made on 5.6.7-rc1 which while not the end of the world does seem to severely limit the use of the new GTID features, or if nothing else make it a pain to use.

Today I found another related issue, when trying to check the syntax of one of the MySQL commands. That’s now reported as bug#67073.

All of these are sort of edge cases but do make 5.6 look a little rough round the edges, when the rest of the product is actually really good and a great improvement from The MySQL 5.5 GA version that everyone uses.  That’s really a shame.

I did report the Original problem as SR 3-6270525721 : MySQL 5.6.7-rc1, grants, replication and GTID cause problems, but of course that’s …

While the index merge access types certainly are useful for a number of queries, there has been some frustration expressed both from customers and the community about how it...

1. is not used when it should have been
2. is used when ref access is obviously better
3. merges suboptimal indexes
4. is too restricted in which conditions can be used

I could come up with numerous examples of related bugs and feature requests dating back more than six years. To list a few: 17673, 30151, 23322, 65274, …

Sitting at the Hilton in San Francisco, listening to the MySQL Connect opening Keynotes, sparks interesting thoughts. Thoughts about the future of MySQL, the Oracle stewardship, the MariaDB alternative and the advantage of the solutions provided by the MySQL Ecosystem.
But there is something missing. I know something is not part of this picture, but I can't figure out what it is.
At the end of keynotes and pane,l I turn to the exhibitors area, an approx 800 sq ft., 50% occupied by Oracle. At the bottom left corner stands my answer. 
The small yellow cubic hive of AWS was at the top of the rollup that reminds of MySQL and RDS, admittedly the most used version of MySQL in the cloud so far. And here is the missing part. 

New great features in 5.6 Release Candidate and in MySQL Cluster 7.3
The new features …

Sitting at the Hilton in San Francisco, listening to the MySQL Connect opening Keynotes, sparks interesting thoughts. Thoughts about the future of MySQL, the Oracle stewardship, the MariaDB alternative and the advantage of the solutions provided by the MySQL Ecosystem.
But there is something missing. I know something is not part of this picture, but I can't figure out what it is.
At the end of keynotes and pane,l I turn to the exhibitors area, an approx 800 sq ft., 50% occupied by Oracle. At the bottom left corner stands my answer. 
The small yellow cubic hive of AWS was at the top of the rollup that reminds of MySQL and RDS, admittedly the most used version of MySQL in the cloud so far. And here is the missing part. 

New great features in 5.6 Release Candidate and in MySQL Cluster 7.3
The new features packed into …

If you’re a user of MySQL Workbench then you may have noticed a pocket knife icon appear in the top right hand corner – click on that and a terminal opens which gives you access to the MySQL utilities. In this post I’m focussing on the replication utilities but you can also refer to the full MySQL Utilities documentation.

What I’ll step through is how to uses these utilities to:

• Set up replication from a single master to multiple slaves
• Automatically detect the failure of the master and promote one of the slaves to be the new master
• Introduce the old master back into the topology as a new slave and then promote it to be the master again

Tutorial Video

Before going through the steps in detail here’s a demonstration of the replication utilities in action…

To get full …