March 2015 – Master MySQL

March 12, 2015

Making the case to support +2 version upgrades

In the MySQL team, we have always had a requirement to support upgrades from one major version. For example:

Upgrading from MySQL 5.5 to 5.6 is supported.
Upgrading from MySQL 5.1 to 5.6 is not supported.

Downgrades are also supported for one major version. For example, if a user upgrades to 5.6 but discovers that it is not working as expected, they have the safety knowing that there is a way to step back to MySQL 5.5. This may come with some limits; for example when new features (such as new row formats or page checksums) are enabled, this may no longer be possible.

Today I wanted to discuss a current non-requirement. We do not support skipping major versions, such as upgrading from MySQL 5.1 to 5.6. Justin however makes the case that despite not being supported, it has often worked (at least when used with mysqldump).

We believe that Justin’s bug report has a lot of merit, and are considering extending our requirements to support a +2 version upgrade (i.e. 5.5 to 5.7). This will have a noticeable impact on our QA team, and the effort required to expand testing to handle additional upgrade scenarios will have to be carefully evaluated.

We are seeking feedback from our users in the community in response to BUG #76264! The specific questions we would like to ask you are:

Are you currently running MySQL 5.5 and planning to upgrade directly to MySQL 5.7?
If so; are there any constraints that make it too to be able to step through 5.6 as part of the upgrade process?
Would it be acceptable if a 5.5 to 5.7 upgrade was only supported via mysqldump?
Do you have a requirement for a >+2 version upgrade?

Please leave a comment, or get in touch!

March 10, 2015

Heads up! MySQL 5.7 DMR6 contains a (small) known bug

MySQL 5.7 DMR6 was been released today! By my crude measurement, it is a big release with a number of new features and bug fixes:
morgo@Rbook:~$ for V in 1 2 3 4 5 6; do curl --silent http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-$V.html | wc -l; done; 2543 4914 <-- DMR2 2282 2940 4118 4121 <-- DMR6

The release notes have one important known bug to note:

This bug has been fixed in 5.7.7.

March 9, 2015

Installing MySQL 5.6 on Ubuntu 14.10 (Utopic Unicorn)

Installing ‘mysql’ on Ubuntu 14.10 will default to version 5.5, but also included is MySQL 5.6 is an option.

These are the steps I ran through to install it on a fresh Digital Ocean droplet. On a local machine, you will need to add sudo:

root@ubuntu1410:~# apt-get install mysql-server-5.6 Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-3.16.0-21 linux-headers-3.16.0-21-generic linux-image-3.16.0-21-generic linux-image-extra-3.16.0-21-generic Use 'apt-get autoremove' to remove them. The following extra packages will be installed: libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6 mysql-client-core-5.6 mysql-common mysql-common-5.6 mysql-server-core-5.6 Suggested packages: libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx tinyca The following NEW packages will be installed: libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6 mysql-client-core-5.6 mysql-common mysql-common-5.6 mysql-server-5.6 mysql-server-core-5.6 0 upgraded, 12 newly installed, 0 to remove and 3 not upgraded. Need to get 21.8 MB of archives. After this operation, 165 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://mirrors.digitalocean.com/ubuntu/ utopic/main libaio1 amd64 0.3.110-1 [6,454 B] Get:2 http://mirrors.digitalocean.com/ubuntu/ utopic-updates/main mysql-common all 5.5.41-0ubuntu0.14.10.1 [13.5 kB] Get:3 http://mirrors.digitalocean.com/ubuntu/ utopic-updates/main libmysqlclient18 amd64 5.5.41-0ubuntu0.14.10.1 [603 kB] Get:4 http://mirrors.digitalocean.com/ubuntu/ utopic/main libdbi-perl amd64 1.631-3build1 [770 kB] Get:5 http://mirrors.digitalocean.com/ubuntu/ utopic/main libdbd-mysql-perl amd64 4.028-2 [88.6 kB] Get:6 http://mirrors.digitalocean.com/ubuntu/ utopic/main libterm-readkey-perl amd64 2.32-1build1 [25.0 kB] Get:7 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-client-core-5.6 amd64 5.6.19-1~exp1ubuntu2 [4,061 kB] Get:8 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-client-5.6 amd64 5.6.19-1~exp1ubuntu2 [5,654 kB] Get:9 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-server-core-5.6 amd64 5.6.19-1~exp1ubuntu2 [4,777 kB] Get:10 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-server-5.6 amd64 5.6.19-1~exp1ubuntu2 [5,710 kB] Get:11 http://mirrors.digitalocean.com/ubuntu/ utopic/main libhtml-template-perl all 2.95-1 [65.5 kB] Get:12 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-common-5.6 all 5.6.19-1~exp1ubuntu2 [13.5 kB] Fetched 21.8 MB in 4s (5,436 kB/s) Preconfiguring packages ... Selecting previously unselected package libaio1:amd64. (Reading database ... 115597 files and directories currently installed.) Preparing to unpack .../libaio1_0.3.110-1_amd64.deb ... Unpacking libaio1:amd64 (0.3.110-1) ... Selecting previously unselected package mysql-common. Preparing to unpack .../mysql-common_5.5.41-0ubuntu0.14.10.1_all.deb ... Unpacking mysql-common (5.5.41-0ubuntu0.14.10.1) ... Selecting previously unselected package libmysqlclient18:amd64. Preparing to unpack .../libmysqlclient18_5.5.41-0ubuntu0.14.10.1_amd64.deb ... Unpacking libmysqlclient18:amd64 (5.5.41-0ubuntu0.14.10.1) ... Selecting previously unselected package libdbi-perl. Preparing to unpack .../libdbi-perl_1.631-3build1_amd64.deb ... Unpacking libdbi-perl (1.631-3build1) ... Selecting previously unselected package libdbd-mysql-perl. Preparing to unpack .../libdbd-mysql-perl_4.028-2_amd64.deb ... Unpacking libdbd-mysql-perl (4.028-2) ... Selecting previously unselected package libterm-readkey-perl. Preparing to unpack .../libterm-readkey-perl_2.32-1build1_amd64.deb ... Unpacking libterm-readkey-perl (2.32-1build1) ... Selecting previously unselected package mysql-client-core-5.6. Preparing to unpack .../mysql-client-core-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ... Unpacking mysql-client-core-5.6 (5.6.19-1~exp1ubuntu2) ... Selecting previously unselected package mysql-client-5.6. Preparing to unpack .../mysql-client-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ... Unpacking mysql-client-5.6 (5.6.19-1~exp1ubuntu2) ... Selecting previously unselected package mysql-server-core-5.6. Preparing to unpack .../mysql-server-core-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ... Unpacking mysql-server-core-5.6 (5.6.19-1~exp1ubuntu2) ... Processing triggers for man-db (2.7.0.2-2) ... Setting up mysql-common (5.5.41-0ubuntu0.14.10.1) ... Selecting previously unselected package mysql-server-5.6. (Reading database ... 115966 files and directories currently installed.) Preparing to unpack .../mysql-server-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ... Unpacking mysql-server-5.6 (5.6.19-1~exp1ubuntu2) ... Selecting previously unselected package libhtml-template-perl. Preparing to unpack .../libhtml-template-perl_2.95-1_all.deb ... Unpacking libhtml-template-perl (2.95-1) ... Selecting previously unselected package mysql-common-5.6. Preparing to unpack .../mysql-common-5.6_5.6.19-1~exp1ubuntu2_all.deb ... Unpacking mysql-common-5.6 (5.6.19-1~exp1ubuntu2) ... Processing triggers for man-db (2.7.0.2-2) ... Processing triggers for ureadahead (0.100.0-16) ... Setting up libaio1:amd64 (0.3.110-1) ... Setting up libmysqlclient18:amd64 (5.5.41-0ubuntu0.14.10.1) ... Setting up libdbi-perl (1.631-3build1) ... Setting up libdbd-mysql-perl (4.028-2) ... Setting up libterm-readkey-perl (2.32-1build1) ... Setting up mysql-client-core-5.6 (5.6.19-1~exp1ubuntu2) ... Setting up mysql-client-5.6 (5.6.19-1~exp1ubuntu2) ... Setting up mysql-server-core-5.6 (5.6.19-1~exp1ubuntu2) ... Setting up mysql-server-5.6 (5.6.19-1~exp1ubuntu2) ... 2015-03-09 09:55:55 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. 2015-03-09 09:55:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). mysql start/running, process 19834 Setting up libhtml-template-perl (2.95-1) ... Setting up mysql-common-5.6 (5.6.19-1~exp1ubuntu2) ... Processing triggers for libc-bin (2.19-10ubuntu2.3) ... Processing triggers for ureadahead (0.100.0-16) ...

Some additional notes:

You will be prompted for a password for the root user. You can change it later, but best to think of something meaningful and write it down.
The two WARNINGS that you see are quite safe. The default Ubuntu configuration uses a deprecated name for a configuration setting (Bug #1362393), and MySQL is warning that the behavior of TIMESTAMP columns may change in the future.
The MySQL server should start automatically on boot. If you can not connect to MySQL, please check the file /var/log/messages. The most common reason it will not start is that the default configuration requires a server with 1GB of RAM. I have an you can use if you need it to start with less RAM.

March 4, 2015

Plan to remove sql-bench from MySQL 5.7

In the MySQL team, we are planning to remove sql-bench from the MySQL 5.7 sources.

As the README file denotes, SQL Bench is a benchmark utility that supports MySQL 3.20 and 3.21, PostgreSQL 6.#, mSQL 2.# and Solid Server 2.2. It has not been well maintained for recent versions, and we are no longer using it internally as part of our testing process.

Our aim in making this change is to:

Improve the manageability of our git repository by reducing its size.
By keeping unmaintained code bundled with MySQL, it can lead users into negative impressions of our product. We have worked very hard at improving stability, and want to remove lurking pitfalls for our users.

The source code to sql-bench will remain in the 5.6 and earlier source trees.

Will you be affected by this change? Please get in touch!

March 3, 2015

Proposal to deprecate "compatibility" SQL Modes

In the MySQL team, we are currently discussing deprecating several of the SQL mode options which are used by mysqldump to change the output format. From the mysqldump command:

$ mysqldump --help
..
--compatible=name
 Change the dump to be compatible with a given mode. By
 default tables are dumped in a format optimized for
 MySQL. Legal modes are: ansi, mysql323, mysql40,
 postgresql, oracle, mssql, db2, maxdb, no_key_options,
 no_table_options, no_field_options. One can use several
 modes separated by commas. Note: Requires MySQL server
 version 4.1.0 or higher. This option is ignored with
 earlier server versions.

To explain the rationale for this proposal:

The options mysql323, mysql40 are designed to allow mysqldump to create an output format that can be restored on a MySQL Server of version 3.23 or 4.0. While we aim to support the upgrade case from these versions, supporting a downgrade is not something we support, as restoring data to a 10 year old release poses a number of challenges.
The options postgresql, oracle, mssql, db2, maxdb are ‘special’ SQL modes, in that they are not really modes themselves but aliases to switch on other SQL modes. For example:
```
mysql> set sql_mode = 'mssql';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
1 row in set (0.00 sec)
```
Having aliases creates a number of interesting challenges:
- It complicates training and support as users may have multiple options in order to achieve the same outcome (either listing all sql modes individually or via one of the aliases). In a similar example we removed unique option prefixes in MySQL 5.7 to reduce this confusion.
- The options that we enable for each alias imply (but have not measurably offered) compatibility with the other database that is mentioned. Furthermore, as other databases will release newer versions, the singularity of the alias name does not account for this.
- Related to the above; if newer versions of other database products desire new sql modes enabled, it is more flexible (and future proof) to have the list of which behaviour options that should be enabled for each other database in documentation or client programs rather than the server itself. This allows us to not change behavior in a server GA release.

The options no_key_options, no_field_options and no_table_options remove MySQL-specific meta data which I have highlighted below in bold:

# no_key_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# no_field_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# no_table_options
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note that no_field_options did not remove the character set attribute for the column b and thus is incomplete in its current implementation. no_field_options also disables the same meta-data which is disabled by no_key_options.

The no_key_options and no_table_options remain useful, although it should be noted that information_schema may better support a custom view of schema to match the capabilities of the destined database. We are working on making information_schema queries much faster in the future, via our native data dictionary project.

To summarize this proposal:

Our plan is to deprecate the following options in MySQL 5.7, for removal in a later version:
mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb.
We are also seeking input from those that use the following SQL modes, as we may decide to deprecate these in the future:
no_field_options, no_key_options, no_table_options.

Will these changes impact you? Please leave a comment or get in touch!

March 3, 2015

My voices of the ElePHPant Interview :)

Here is an interview of me talking about EXPLAIN FORMAT=JSON, optimizer trace, cost model, MySQL Workbench and even a little Performance Schema.

Some links that I recommend checking out: