I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password
New password:secret
Confirm new password:secret
[vagrant@localhost ~]$ mysql -u root
ERROR 1045 (28000): Access denied for user …

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

Installation

If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema. …

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. Though an occasional deadlock is not something to worry about, frequent occurrences call for attention.

Before MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. But with Percona Toolkit’s pt-deadlock-logger you can have deadlock information retrieved from SHOW ENGINE INNODB STATUS at a given interval and saved to a file or table for late diagnosis. For more information on using pt-deadlock-logger, see …

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another …

In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema.

But what are the possible solutions?

The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.

The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution.

The …

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:

mysql [information_schema] > select trx_id,trx_state 

-> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974   | LOCK WAIT |
| 7962   | RUNNING   |
+--------+-----------+
2 rows in set (0.00 sec)

Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful.

Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the server.
…

It is possible to disallow users from using old_passwords=1. This can be done by adding 'maximum-old_passwords=0' to your my.cnf

This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier.

Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes.

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| …

MySQL 5.6.21 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there was 1 “InnoDB Notes” and 1 “Functionality Added or Changed” bug fix (and 0 “Security Fix”), so not much there, but of course they should be noted:

1. InnoDB Note: The –skip-innodb option is now deprecated and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (–innodb=OFF, –disable-innodb, and so forth).
2. Functionality Added: Internally, spatial data types such as Geometry are represented as BLOB values, so when invoked with the –hex-blob option, mysqldump now displays spatial values in hex. (Bug #43544, Bug …

Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax.

We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again.

There are two methods of downgrading:

• Dump/Restore with mysqldump. This is easy and reliable, but can take more time.

• In place (replace binaries, don't change data). This fast, but won't work if file formats have changed.

As expected this is documented in the MySQL Reference Manual.

I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for InnoDB. All tables used …

When you're testing out a new version of MySQL in a non-production environment there is a temptation to go wild and turn on all kinds of new features.  Especially if you're reading the changelogs or the manual and scanning through options.  You want to start with the most reasonable set of defaults, right?  Maybe you're even doing benchmarks to optimize performance using all the new bells and whistles.

Resist the temptation!  If your goal is to upgrade your production environment then what you really want is to isolate changes.  You want to preform the upgrade with as little to no impact as possible.  Then you can start turning on features or making changes one-by-one.

Why?  Anytime you're doing a major upgrade to something as fundamental as your core RDBMS, there are many ways things can go wrong.  Performance regressions & incompatible changes, client/server incompatibilities …