Joro wrote recently about MySQL 5.6.17‘s new support for AES-256 encryption, and it’s a great improvement for people need to encrypt their data at rest. The new session block_encryption_mode variable controls what variant of AES is used, and for backwards compatibility, the default behavior remains 128-bit key length with ECB block cipher mode. If you’re happy with that level of encryption, nothing changes – your existing code will work the same on 5.6.17 as it has on earlier versions (note that users of statement-based replication will experience new warnings). There are good examples of how to use …[Read more]
The MySQL 5.6 certification exams (Developer and DBA) are now GA! A big thanks to all those who sat for the extended length beta exams – and subsequently waited for several months for your results. Your feedback and the data collected from many beta testers helped ensure the final exams are high quality. A huge thanks also goes to the MySQL staff at Oracle who produced, edited and verified exam questions, in addition to their normal job responsibilities. In particular, these exams would have never materialized …[Read more]
I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.
Isolating the problem
As always with a slow query, finding the execution plan with
EXPLAIN is the 1st step to understand where time is
spent. Here the plan was very good with almost all joins using
the primary key or a unique key, but perhaps the most interesting
part was that
EXPLAIN was very slow as well. This
indicates that the optimizer takes a lot of time finding the
optimal execution plan. To double check, we can run
mysql> set @@profiling = 1; mysql> SELECT …[Read more]
Megabytes of text had been written already on InnoDB locking and
deadlocks. Still, even very simple cases of deadlocks while
working with a table having only one row sometimes make people
wonder what happened and why.
Today I want to check if this topic is explained well in the manual and existing blog posts and understood properly. So, it's an exercise for my dear readers and those who like to report bugs as much as I do.
Let's consider a very simple example. In session #1 with default transaction isolation level execute the following:
CREATE TABLE `tt` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tt values(1,1); -- insert a row there
select * from tt; -- check that we have row (1,1)
select * from tt …
MySQL 5.6.16 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:
As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.
There were 2 minor functionality changes:
- Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
- CMake now supports a -DTMPDIR=dir_name option to specify the default …
Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.16.
- Thanks to Honza Horak for suggesting we make tmpdir configurable at build-time, and for providing a patch. Bug #68338.
- Thanks to Hartmut Holzgraefe for reporting a memory leak when using the InnoDB memcached API and replication. Bug #70757.
- Thanks to Justin Swanhart for reporting that InnoDB reported an incorrect operating system error code when it failed to initialize. Bug #70867.
- Thanks to Yoshinori Matsunobu who reported …
As you should already know, Oracle had released MySQL 5.6.16
officially on January 31, 2014. You can find all the details in
official release notes. In this post I decided
to concentrate on important fixes for bugs reported by community
in 4 most important categories: InnoDB, replication, partitioning
Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself is closed without any specific comment on what exactly was fixed.
Over the last few days, I integrated the MySQL 5.6 GTID version of the Percona Replication Manager (PRM) work of Frédéric Descamps, a colleague at Percona. The agent supports the GTID replication mode of MySQL 5.6 and if the master suffers a hard crash, it picks the slave having applied the highest transaction ID from the dead master. Given the nature of GTID-based replication, that causes all the other slaves to resync appropriately to their new master which is pretty cool and must yet be matched by the regular PRM agent.
For now, it is part of a separate agent, mysql_prm56, which may be integrated with the regular agent in the future. To use it, download the agent with the link above, the pacemaker configuration is similar to the one of the regular PRM agent. If you start from scratch, have a look …[Read more]
Oracle has released the Critical Patch Update (CPU) advisory for January
The affected MySQL products are:
- Oracle MySQL Enterprise Monitor, versions 2.3, 3.0
- Oracle MySQL Server, versions 5.1, 5.5, 5.6
So this means that you should consider updating MySQL. For MySQL
Enterprise the updates should be available on My Oracle Support
and for the Community version the new versions are on the regular
download locations. I guess the official repositories are
For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73
If you use the MySQL release from your distribution …
A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?
For this test, we will use these 2 tables (same structure as in Peter’s post):
CREATE TABLE t1000merge ( id int not null auto_increment primary key, i int(11) NOT NULL, j int(11) NOT NULL, val char(10) NOT NULL, KEY i (i), KEY j (j) ) ENGINE=InnoDB; CREATE TABLE t1000idx2 ( id int not null auto_increment primary key, i int(11) NOT NULL, j int(11) NOT NULL, val char(10) NOT NULL, KEY ij (i,j) ) ENGINE=InnoDB;
Tables were …[Read more]