secondary – Master MySQL

MySQL is 20 years old tomorrow!

According to Wikipedia, the initial release of MySQL was 23 May 1995. This places it at 20 years ~~old~~ young tomorrow.

2015 is actually a year of anniversaries. As well as MySQL reaching the big two-oh, we are also celebrating 10 years of InnoDB under the stewardship of Oracle, and five years of MySQL @ Oracle.

Please make sure toast to MySQL this weekend!

(Photo from Percona Live community dinner 2015.)

MySQL 5.6.24 Community Release Notes

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.24.

In particular:

Thank you to Simon Mudd for reporting that ALTER TABLE did not take advantage of fast alterations if the table contained temporal columns found to be in pre-5.6.4 format. Bug #72997.
Thank you to Doug Warner for reporting that a TRUNCATE TABLE operation on a temporary table raised an assertion. Bug #72080.
Thank you to Elena Stepanova for reporting that an InnoDB full-text phrase search returned incorrect results. Bug #75755.
Thank you to Miljenko Brkic for reporting that the exptime set with the memcached API set command was ignored. Bug #70055.
Thank you to Nilnandan Joshi for reporting a regression where the mysqld server could not shutdown while the memcached plugin was active. Bug #74956.
Thank you to Zhai Weixiang for reporting that several mutexes on InnoDB dummy tables caused contention, and could be relaxed safely. Bug #73361.
Thank you to Zhai Weixiang for reporting that append operations using the InnoDB memcached API could cause a segfault. Bug #75200.
Thank you to Ramesh Sivaraman and Roel Van de Paar for reporting that a number of ALTER TABLE statements that attempt to add partitions, columns, or indexes to a partitioned table while a write lock was in effect for this table were not handled correctly. Bug #74451, Bug #74478, Bug #74491, Bug #74560, Bug #74746, Bug #74841, Bug #74860, Bug #74869.
Thank you to Santosh Praneeth Banda for reporting that when gtid_mode=ON and slave_net_timeout was set to a low value, the slave I/O thread could appear to hang. Bug #74607.
Thank you to Tsubasa Tanaka and Daniël van Eeden for independently reporting that a server warning error referred to an obsolete table_cache system variable. Thank you also to Daniël for providing a patch. Bug #73373, Bug #75081.
Thank you to Simon Mudd for reporting that Performance Schema statement events tables incorrectly referenced system variables (@ @ instead of @@). Bug #71634.
Thank you to Yao Deng for reporting that mysql_real_connect() could close a file descriptor twice if the server was not running. Bug #69423.
Thank you to Olle Nilsson for reporting that notification of events for the general log were received by the audit log plugin only of the general query log was enabled. Bug #60782.
Thank you to Daniël van Eeden for reporting a security issue.

Thank you again to all the community contributors listed above. If I missed a name here, please let me know!

– Morgan

MySQL 5.6.23 Community Release Notes

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.23.

In particular:

Thank you to Inaam Rana for reporting an issue with the InnoDB purge thread state and tablespace export operations. Bug #75298.
Thank you to Inaam Rana for reporting that the INNODB_METRICS adaptive_hash_searches_btree counter failed to report counter data. Bug #74511.
Thank you to Shahriyar Rzayev and David Bennett for suggesting improvements to error messages from InnoDB on non-Windows platforms David also provided a suggested patch. Bug #73365.
Thank you to Sadao Hiratsuka and Piotr Jurkiewicz for independently reporting that integer columns failed to increment and decrement correctly when using the memcached plugin. Piotr also provided a suggested patch to fix the issue. Bug #69415, Bug #74874.
Thank you to Roel Van de Paar for reporting a security issue. Bug #74292.
Thank you to Roel Van de Paar for reporting a security issue. Bug #74597.
Thank you to Santosh Praneeth Banda for reporting a situation where a slave could have more GTIDs than a master, and data inconsistency could occur. Bug #72635.
Thank you to Domas Mituzas for reporting that when using SHOW SLAVE STATUS to monitor replication performance, Seconds_Behind_Master sometimes displayed unexpected lag behind the master. Bug #72376.
Thank you to Matt Swanson for reporting that InnoDB full-text boolean searches incorrectly handled + combined with parentheses. Bug #74845.
Thank you to Roel Van de Paar for reporting a security issue. Bug #74447.
Thank you to Sean Pringle for reporting that an internal temporary table could cause problems if the file was orphaned and the name was used later for other queries. Bug #32917.

Thank you again to all the community contributors listed above. If I missed a name here, please let me know!

– Morgan

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.

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:

Changing my blog to use MySQL 5.7 proposed defaults

In my recent blog post, I announced a set of proposed changes to defaults for MySQL 5.7.

I’m now using these settings on my WordPress blog, which is powered by MyQL 5.7.5. The supplimental config file comes from my GitHub project called mysql-compatibility-config.

Here were the steps required:

[root@mysqldb2 ~]# cd /etc
[root@mysqldb2 etc]# wget https://raw.githubusercontent.com/morgo/mysql-compatibility-config/master/mysql-57/mysql-57-proposed.cnf
--2015-01-23 12:29:00--  https://github.com/morgo/mysql-compatibility-config/blob/master/mysql-57/mysql-57-proposed.cnf
Resolving github.com (github.com)... 192.30.252.128
Connecting to github.com (github.com)|192.30.252.128|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘mysql-57-proposed.cnf’

    [ <=>                                                                                                                                        ] 34,049      --.-K/s   in 0.01s

2015-01-23 12:29:00 (3.26 MB/s) - ‘mysql-57-proposed.cnf’ saved [34049]

[root@mysqldb2 etc]# vi /etc/my.cnf
# Add a line to !include /etc/mysql-57-proposed.cnf
[root@mysqldb2 etc]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

MySQL 5.6.22 Community Release Notes

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.22.

In particular:

Thank you to Ramesh Sivaraman for reporting that setting the innodb_limit_optimistic_insert_debug debug configuration option to 1 caused an infinite B-tree page split. Ramesh also provided a minimal testcase to reproduce. Bug #74605.
Thank you to Jeremy Cole for reporting that InnoDB pages with a checksum value of zero were incorrectly treated as empty pages. Bug #73689.
Thank you to Zhai Weixiang for reporting that the MySQL server would fail to shutdown when started with innodb-force-recovery = 6. Bug #73341.
Thank you to Bart Butler for reporting that InnoDB would shutdown unclean on systems with lower_case_table_names=2 and containing tables using uppercase. Bug #72043.
Thank you to Laurynas Biveinis for reporting two assertions that could occur in InnoDB using debug builds. Bug #71343, Bug #68116.
Thank you to Stewart Smith for reporting that InnoDB was missing a memory barrier when setting up asynchronous I/O (AIO). Stewart was also kind enough to provide a patch addressing the issue. Bug #72809.
Thank you to Jean-François Gagné for reporting an out of memory situation when using row-based replication and having different data types on the slave. Jean-François also provided an example shell script, which saved us a lot of time in reproducing the issue. Bug #72610.
Thank you to Jean-François Gagné for reporting that relay log recovery did not correctly handle the case where Relay_Master_Log_File could be empty. Bug #73039.
Thank you to Santosh Praneeth Banda for reporting that replication masters did not correctly handle the case when slaves requested GTIDs which had already been purged. Bug #73032.
Thank you to Domas Mituzas for reporting that mysqlbinlog incurred significant kernel mutex contention by calling localtime() for every event read. We have since modified mysqlbinlog to use localtime_r instead. Bug #72701.
Thank you to Jeremy Cole for reporting that the variable SQL_LOG_BIN having global scope is dangerous. We agree with Jeremy and have modified the behaviour so that it may only be set on a session basis. Bug #67433.
Thank you to Jean-François Gagné for reporting that the mysqld startup script would fail on CentOS when the socket option was relative. Bug #74111.
Thank you to Eugene Zheganin for reporting that MySQL would not compile with DTrace on Solaris 11.2. Bug #73826.
Thank you to Nikola Glavinceski for reporting that certain queries for which subquery materialization or UNION DISTINCT was used together with a hash index on a temporary table could produce incorrect results or cause a server exit. Bug #73368.
Thank you to Hartmut Holzgraefe for reporting that the ENABLED_LOCAL_INFILE cmake option was incorrectly enabled by default. Bug #72106.
Thank you to Jason Rider for reporting that the use of ODBC-format date literals could produce incorrect query results. Bug #69233.
Thank you to Nicholas Bamber for reporting that mysql_setpermission failed to properly quote user names in SQL statements that it generated. Bug #66317.

Thank you again to all the community contributors listed above. In addition, the MySQL team would like to thank the community for their feedback on Bug #67433.

If I missed a name here, please let me know!

– Morgan

Meet the MySQL Team at Percona Live London

The MySQL team will be presenting three sessions at Percona Live in 2 weeks:

The MySQL SYS Schema by Mark Leith
MySQL 5.7: Performance and Scalability Benchmark by Dimitri Kravtchuk
MySQL Replication: What’s New in 5.7 and Beyond by Luis Soares and Andrew Morgan

In addition, Tomas will be presenting a not-to-be missed keynote on how MySQL just keeps getting better.

Please stop by and introduce yourself! We would love to hear feedback from users who have downloaded and tested our latest 5.7 DMR5 and labs releases.

MySQL 5.6.21 Community Release Notes

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.21.

In particular:

Thank you to Yoshinori Matsunobu who reported that during recovery GTID-based replication would need to scan all binary logs. We have since introduced an option for simplified recovery in the case a large number of binary log files exist. Bug #69097.
Thank you to Michael Newton for reporting that mysqldump should hexdump geometry data types when invoked with the --hex-blob command line option. Although we did not use his patch, we also thank Martin Hradil for contributing to the bug report. Bug #43544.
Thank you to Yoshinori Matsunobu for reporting that a large number of partitions could consume much more memory than previous versions of MySQL. This regression was introduced from an earlier bug fix. Bug #70641.
Thank you to Tianzhou Chen for reporting a security issue. Bug #73650.
Thank you to Qixiu Xiao for reporting that retrieving multiple values (gets) via InnoDB’s memcached interface would return incorrect results. The InnoDB memcached interface does not currently support retrieval of multiple values at once, and has since been changed to produce an error instead. Bug #72453.
Thank you to David Schwartz for reporting that performing operations on a timed out key would fail using InnoDB’s memcached interface. Bug #72586.
Thank you to Jeremy Cole for reporting that a failed in-place ALTER TABLE operation would leave behind a non-unique temporary file, preventing future modifications. Jeremy also provided a testcase demonstrating how to easily reproduce the issue. Bug #72594.
Thank you to Inaam Rana for reporting inaccurate handling of srv_activity_count in the InnoDB source code. Inaam also contributed a patch to fix the issue. Bug #72137.
Thank you to Jeremy Cole for providing an example where InnoDB will drastically under fill pages. Thank you also to Davi Arnaut, who commented on Jeremy’s bug report, and demonstrated a simpler test-case to be able to reproduce. Bug #67718.
Thank you to Sadao Hiratsuka for reporting that mysqlbinlog could not safely concatenate files with GTID-based replication. Bug #71695.
Thank you to Yoshinori Matsunobu for reporting that with semi-sync replication, setting a slave to a binary log position in the future would cause an assertion on the master. We have changed the behaviour to produce an error. Bug #70327.
Thank you to Alexander Du for reporting that when an SQL thread which was waiting on a lock is killed, it could cause transactions to be skipped on a slave. Bug #69873.
Thank you Zhai Weixiang for reporting that a server could hang on shutdown when semi-sync replication is in use. Bug #71047.
Thank you to Santosh Praneeth Banda for reporting that starting/stopping the sql thread on a slave could cause a transaction to be logged with a different GTID than on the master. Bug #72313.
Thank you to Hartmut Holzgraefe for reporting that having two slaves with the same server_uuid resulted in a misleading error message. Based on Hartmut’s feedback, the slave error now identifies the duplicate server UUID and the master error identifies the zombie thread that has been killed. Bug #72578.
Thank you to Jean-François Gagné for reporting an out of memory scenario when using RBR and slave having different data types. Bug #72610.
Thank you to Rolf Martin-Hoster for reporting that MySQL did not correctly handle the general or slow query log as a FIFO or socket file. Bug #67088.
Thank you to Laurynas Biveinis for reporting that A simultaneous OPTIMIZE TABLE and online ALTER TABLE on the same InnoDB table could result in deadlock. Laurynas also provided a test-case and example patch. Bug #71433.
Thank you to Elena Stepanova for reporting that the query cache was not correctly invalidated when a CASCADE DELETE or CASCADE UPDATE constraint was specified, and the table names contained special characters. Bug #72547.

Thank you again to all the community contributors listed above. In particular, the MySQL team would like to call out the names that appear more than once in this release:

Yoshinori Matsunobu (3), Laurynas Biveinis (2), Jeremy Cole (2)

If I missed a name here, please let me know!

– Morgan

MySQL 5.7 DMR5 Release Notes – what we know so far

The release notes for MySQL 5.7 DMR5 are starting to take shape on dev.mysql.com.

For me, what is one of the most exciting changes is that “Strict SQL mode is now enabled by default for transactional storage engines”. This aligns very nicely with a post I wrote just recently about transitioning changes using initially a whitelist and then a blacklist.

User’s with legacy applications still be able to opt-out of STRICT mode, but importantly new applications won’t be accidentally trapped into sub-optimal defaults.