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 transaction." So if would like to know all locks held by a transaction this doesn't help. There is lots of information in the output of " SHOW ENGINE INNODB STATUS\G " in the section about TRANSACTIONS. ------------ TRANSACTIONS ------------ Trx id counter 8991 Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle History list length 50 ...

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 third solution is to change the transaction isolation level of the blocking transaction to READ COMMITTED. mysql [test] > set transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql [test] > start transaction; Query OK, 0 rows affected (0.00 sec) mysql [test] > insert into t2 select * from t1; Query OK, ...

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. The first session: mysql [test] > create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql [test] > insert...

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 | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect old_passwords value: '1' | +---------+------+----------------------------------------------+ 1 r...

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 the A ntelope format. As both MySQL versions support A ntelope and B arracuda this shoud be fine. I don't know why Oracle didn't introduce a new C heeta format with the introduction of the fulltext indexes. The i...

I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL. The benchmark document helps you to ensure you have a secure configuration for MySQL. Center for Internet Security (CIS) is a nonprofit organization. The Call For Participation is now open. If you are already a CIS member then you can download the document here . If you don't have an account you can register for free.

To get more familiar with docker and to create a test setup for MySQL Cluster I created docker images for the various components of MySQL Cluster (a.k.a. NDB Cluster) At first I created a Fedora 20 container and ran all components in one container. That worked and is quite easy to setup. But that's not how one is supposed to use docker. So I created Dockerfile 's for all components and one base image. The base image: contains the MySQL Cluster software has libaio installed has a mysql user and group  serves as a base for the other images The management node (ndb_mgmd) image: Has ndb_mgmd as entrypoint Has a config.ini for the cluster config Should be started with " --name=mymgm01 " The data node (ndbmtd) image: Has ndbmtd as entrypoint Uses the connect string: " host=${MGM01_PORT_1186_TCP_ADDR}:1186 " Should be started with " --link mymgm01:mgm01 " to allow it to connect to the management node. You should create 2 container...