MySQL Server 8.0.18: Thanks for the Contributions

In my blog series about external contributions to MySQL 8 we have reached version 8.0.18 which was released Monday 14 October 2019. Again the community has contributed to make MySQL better. Thank you.

The contributions to MySQL 8.0.18 includes several patches from Facebook as well as patches from Gillian Gunson, Przemysław Skibiński (Percona), Daniel Black, and Satya Bodapati (also Percona). The contributions are:

  • MySQL now provides more control over the use of compression to minimize the number of bytes sent over connections to the server. Previously, a given connection was either uncompressed or used the zlib compression algorithm. Now, it is also possible to use the zstd algorithm (zstd 1.3), and to select a compression level for zstd connections. The permitted compression algorithms can be configured on the server side, as well as on the connection-origination side for connections by client programs and by servers participating in master/slave replication or Group Replication. For more information, see Connection Compression Control.

    Connection compression using the zstd algorithm requires that the server be built with zstd library support. The new WITH_ZSTD CMake option indicates whether to use the bundled or system zstd library.

    Legacy compression-control parameters, such as the --compress client option, are deprecated and will be removed in a future MySQL version.

    Thanks to Facebook for a contribution on which some of this work was based.
  • The sys.schema_unused_indexes view now filters out unique indexes. Thanks to Gillian Gunson for the contribution. (Bug #24798995, Bug #83257)
  • The new innodb_idle_flush_pct variable permits placing a limit on page flushing during idle periods, which can help extend the life of solid state storage devices. See Limiting Buffer Flushing During Idle Periods.

    Thanks to Facebook for the contribution. (Bug #27147088, Bug #88566)
  • Replication: The heartbeat interval for a replication slave, which is controlled by the MASTER_HEARTBEAT_PERIOD option of the CHANGE MASTER TO statement, can be specified with a resolution in milliseconds. Previously, the master’s binary log dump thread used a granularity of seconds to calculate whether a heartbeat signal should be sent to the slave, causing excessive heartbeat activity in the case of multiple skipped events. To remove this issue, all heartbeat-related calculations by the master and slave are now carried out using a granularity of nanoseconds for precision. Thanks to Facebook for the contribution. (Bug #29363787, Bug #94356)
  • When generating C source from SQL scripts, Some utf8-encoded characters were split across lines. Thanks to Przemysław Skibiński from Percona for the patch. (Bug #30152555, Bug #96449)
  • With strict SQL mode enabled, the STR_TO_DATE() function did not properly handle values with time parts only. Thanks to Daniel Black for the contribution. (Bug #18090591, Bug #71386)
  • InnoDB: A long running ALTER TABLE … ADD INDEX operation with concurrent inserts caused semaphore waits. Thanks to Satya Bodapati from Percona for the patch. (Bug #29008298)

If you have patches you would like to contribute you can do so from MySQL’s GitHub repository (requires signing the Oracle Contributor Agreement).

Thank you for the contributions.

MySQL Server 8.0.17: Thanks for the Contributions

MySQL 8.0.17 was released Monday and it includes great features such as the Clone feature and multi-valued indexes. There are also several nice contributions from the community. These are the changes that this blog is about.

The contributions to MySQL Server 8.0.17 include patches from Facebook, Daniël van Eeden, Mattias Jonsson, and Simon Mudd (all from Booking.com), Daniel Black, Yibo Cai (from Arm Technology), Josh Braden, and Zhou Mengkang. The larger contributions are:

  • The mysql client program now sends os_user and os_sudouser connection attributes, when available, to indicate the name of the operating system user running the program and the value of the SUDO_USER environment variable, respectively. For general information about connection attributes, see Performance Schema Connection Attribute Tables. Thanks to Daniël van Eeden for the contribution on which this feature was based. (Bug #29210935, Bug #93916)
  • The mysqldump option –set-gtid-purged controls whether or not a SET @@GLOBAL.gtid_purged statement is added to the mysqldump output. The statement updates the value of gtid_purged on a server where the dump file is reloaded, to add the GTID set from the source server’s gtid_executed system variable. A new choice –set-gtid-purged=COMMENTED is now available. When this value is set, if GTIDs are enabled on the server you are backing up, SET @@GLOBAL.gtid_purged is added to the output (unless gtid_executed is empty), but it is commented out. This means that the value of gtid_executed is available in the output, but no action is taken automatically when the dump file is reloaded. With COMMENTED, you can control the use of the gtid_executed set manually or through automation. For example, you might prefer to do this if you are migrating data to another server that already has different active databases. Thanks to Facebook for this contribution. (Bug #94332, Bug #29357665)
  • MySQL now uses open(O_TMPFILE) whenever applicable when creating a temporary file that is immediately unlinked. This is more efficient than previously and avoids the small possibility of a race condition. Thanks to Daniel Black for the contribution. (Bug #29215177, Bug #93937)
  • When clients were terminated for inactivity exceeding the wait_timeout value, the message written to the error log was unclear. Now ER_NET_WAIT_ERROR is written, which is more specific about the cause of the problem. Thanks to Mattias Jonsson for the contribution. (Bug #28940167, Bug #93240)
  • InnoDB: Insufficient memory barriers in the rw-lock implementation caused deadlocks on ARM. Thanks to Yibo Cai from Arm Technology for the contribution. (Bug #29508001, Bug #94699)
  • Replication: When events generated by one MySQL server instance were written to the binary log of another instance, the second server implicitly assumed that the first server supported the same number of binary log event types as itself. Where this was not the case, the event header was handled incorrectly. The issue has now been fixed. Thanks to Facebook for the contribution. (Bug #29417234)
  • Replication: When binary logging is enabled on a replication slave, the combination of the –replicate-same-server-id and –log-slave-updates options on the slave can cause infinite loops in replication if the server is part of a circular replication topology. (In MySQL 8.0, binary logging is enabled by default, and slave update logging is the default when binary logging is enabled.) However, the use of global transaction identifiers (GTIDs) prevents this situation by skipping the execution of transactions that have already been applied. The restriction on this combination of options has therefore now been removed when gtid_mode=ON is set. With any other GTID mode, the server still does not start with this combination of options. As a safeguard against creating the problem situation after the server has started, you now cannot change the GTID mode to anything other than ON on a running server that has this combination of options set. Thanks to Facebook for the contribution. (Bug #28782370, Bug #92754)
  • Replication: When a MEMORY table is implicitly deleted on a master following a server restart, the master writes a DELETE statement to the binary log so that slaves also empty the table. This generated event now includes a comment in the binary log so that the reason for the DELETE statement is easy to identify. Thanks to Daniël van Eeden for the contribution. (Bug #29157796, Bug #93771)

There are also a number of smaller patches that has helped improve the comments and messages in the MySQL source code. These are:

  • Bug 29403708 – CONTRIBUTION: FIX TYPO IN AUTHENTICATION METHODS DOCUMENTATION
    Thanks to Daniël van Eeden.
  • Bug 29428435 – CONTRIBUTION: FIX TYPOS IN MYSQLDUMP.CC
    Thanks to Josh Braden.
  • Bug 29262200 – CONTRIBUTION: FIX TYPOS IN COMMENTS FOR COM_XXX COMMANDS
    Thanks to Simon Mudd.
  • Bug 29468128 – CONTRIBUTION: UPDATE HANDLER.CC
    Thanks to Zhou Mengkang.

Thank you for your contributions. Feel free to keep submitting ideas to the MySQL bugs database with ideas how to improve MySQL.

MySQL Server 8.0.16: Thanks for the Contributions

MySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”.

Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are:

  • mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location for all chroot environments by multiple simultaneous mysql-test-run.pl instances. This enables those instances to avoid conflicts when reserving port numbers. Thanks to Facebook for the contribution. (Bug #29221085, Bug #93950)
  • Changes to the all_persisted_variables test. Thanks to the Facebook team for the contribution. (Bug #29013375, Bug #93478)
  • InnoDB: Write-ahead did not work as expected due to an incorrectly initialized variable. Thanks to Yuhui Wang for the contribution. (Bug #29028838, Bug #93442)
  • InnoDB: A Linux AIO handler function failed to check if completed I/O events succeeded. Thanks to Wei Zhao for the contribution. (Bug #27850600, Bug #90402)
  • InnoDB: A function called by a CREATE TABLE thread attempted to access a table object after it was freed by a background thread. Thanks to Yan Huang for the patch. (Bug #27373959, Bug #89126)
  • Replication: If the WAIT_FOR_EXECUTED_GTID_SET() function was used with a timeout value including a fractional part (for example, 1.5), an error in the casting logic meant that the timeout was rounded down to the nearest whole second, and to zero for values less than 1 second (for example, 0.1). The casting logic has now been corrected so that the timeout value is applied as originally specified with no rounding. Thanks to Dirkjan Bussink for the contribution. (Bug #29324564, Bug #94247)
  • Determination of the number of online CPUs available to the mysqld process is now more accurate. Thanks to Daniel Black for the contribution. (Bug #28907677, Bug #93144)
  • Made a comparison in the internal method Item_result::item_cmp_type() more efficient. Our thanks to Daniel Black for the contribution. (Bug #92784, Bug #28796107)

It sometimes happen that we receive a contribution that we really like, but – for one reason or another – choose not to use the patch. One such case is the new feature to allow reconfiguration of the SSL settings at runtime. Thank you Facebook for submitting a contribution for a similar feature even though we ended up not using the patch.

Thank you for your contributions. Feel free to keep submitting ideas to the MySQL bugs database with ideas how to improve MySQL

MySQL Server 8.0.14: Thanks for the Contributions

MySQL 8.0.14 was released earlier in the week, and again we (Oracle) received several contributions that either made it into the release or at least inspired a feature or bug fix. This blog will go through the the changelog notes for these changes. Thank you for the contributions.

Two of the contributions are new features with patches submitted by Facebook, one by Daniel Black, and one of Facebook’s patches from 8.0.13 has been updated:

  • A new system variable, log_slow_extra, if enabled, causes the server to write additional fields to slow query log lines that provide information about slow statements. In addition, SET lines written to the log to indicate statement timestamps now use the time from the beginning of statement execution, rather than the time at the end of execution. See The Slow Query Log. Thanks to Facebook for the contribution on which this feature is based. (Bug #27535580, Bug #89637)
  • InnoDB: To reduce the size of core files, the innodb_buffer_pool_in_core_file variable can be disabled to prevent InnoDB buffer pool pages from being written to core files. Thanks to Facebook for the contribution. (Bug #27724476, Bug #90144)
  • Previously, for command options that take a numeric value, the value could be given with a suffix of K, M, or G to indicate a multiplier of 1024, 1024^2 or 1024^3. Now a suffix can also be T, P, and E to indicate a multiplier of 1024^4, 1024^5 or 1024^6. Thanks to Daniel Black for the patch. (Bug #27306931, Bug #89017)
  • The code contributed by Facebook for the feature implemented by Bug#27855592 was updated. (Bug #28950397)

While it is great with new features, bug fixes are equally important. There are three bug fix contributions in 8.0.14:

  • Some typos in server source code were fixed. Thanks to Hyunwoo Park for the contribution. (Bug #26189673, Bug #86565)
  • On Ubuntu, the installed /etc/mysql/mysql.conf.d/default-auth-override.cnf file was mistakenly created with executable mode. Thanks to Evgeniy Patlan (from Percona) for the correction contribution. (Bug #28714840, Bug #92587)
  • Comparing log file names as strings using the memcmp() function resulted in uninitialized memory read errors. The comparison now uses the strncmp() function. Thanks to Zsolt Parragi and Laurynas Biveinis (both from Percona) for their contributions. (Bug #28178776, Bug #90238)

Sometimes it happens there is a contribution for a good idea, but in the end – for one reason or another – a different patch is implemented. I would still like to say thanks. I am aware of two such cases in MySQL 8.0.14:

  • Thanks to Facebook for suggesting the idea of a separate admin port – the new admin_address and admin_port options. (Bug #27847672, Bug #90395)
  • Thanks to Zhenghu Wen for reporting and submitting a patch for the bug that a member might not be able to move from the RECOVERING to the ONLINE state, when it joins a group. (Bug #89582, Bug #27511404)

Thanks to all involved. Keep up the good work. Even if your patch was not used, don’t get discouraged.

MySQL Server 8.0.13: Thanks for the 10 Facebook and Community Contributions

MySQL 8.0.13 was released this week. There are several exciting changes including functional indexes and using general expressions as the default value for your columns. So, I will recommend you to get MySQL 8.0.13 installed and try out the new features. You can read about changed in the release notes section of the MySQL documentation and in Geir’s release blog.

Thank you for the contributions to MySQL 8.0.13

However, what I would like to focus on in this blog is the external contributions that has been included in this release. There are five patches contributed by Facebook as well as five contributions from other MySQL users.

The patches contributed by Facebook are:

  • The MySQL client library now returns better error messages for OpenSSL errors. (Bug #27855668, Bug #90418)
  • The optimizer now supports a Skip Scan access method that enables range access to be used in previously inapplicable situations to improve query performance. For more information, see Skip Scan Range Access Method. (Bug #26976512, Bug #88103)
  • A new Performance Schema stage, waiting for handler commit, is available to detect threads going through transaction commit. (Bug #27855592, Bug #90417)
  • For mysqldump --tables output, file names now always include a .txt or .sql suffix, even for file names that already contain a dot. Thanks to Facebook for the contribution. (Bug #28380961, Bug #91745)
  • Failure to create a temporary table during a MyISAM query could cause a server exit. Thanks to Facebook for the patch. (Bug #27724519, Bug #90145)

Other contributions are:

  • Previously, file I/O performed in the I/O cache in the mysys library was not instrumented, affecting in particular file I/O statistics reported by the Performance Schema about the binary log index file. Now, this I/O is instrumented and Performance Schema statistics are accurate. Thanks to Yura Sorokin for the contribution. (Bug #27788907, Bug #90264)
  • Performance for locating user account entries in the in-memory privilege structures has been improved. Thanks to Eric Herman for the contribution. (Bug #27772506, Bug #90244)
  • InnoDB: A helper class was introduced to improve performance associated with reading from secondary keys when there are multiple versions of the same row. Thanks to Domas Mituzas for the contribution. (Bug #25540277, Bug #84958)
  • Replication: When the binlog_group_commit_sync_delay system variable is set to a wait time to delay synchronization of transactions to disk, and the binlog_group_commit_sync_no_delay_count system variable is also set to a number of transactions, the MySQL server exits the wait procedure if the specified number of transactions is reached before the specified wait time is reached. The server manages this process by checking on the transaction count after a delta of one tenth of the time specified by binlog_group_commit_sync_delay has elapsed, then subtracting that interval from the remaining wait time. If rounding during calculation of the delta meant that the wait time was not a multiple of the delta, the final subtraction of the delta from the remaining wait time would cause the value to be negative, and therefore to wrap to the maximum wait time, making the commit hang. The data type for the remaining wait time has now been changed so that the value does not wrap in this situation, and the commit can proceed when the original wait time has elapsed. Thanks to Yan Huang for the contribution. (Bug #28091735, Bug #91055)
  • Replication: In code for replication slave reporting, a rare error situation raised an assertion in debug builds, but in release builds, returned leaving a mutex locked. The mutex is now unlocked before returning in this situation. Thanks to Zsolt Parragi for the patch. (Bug #27448019, Bug #89421)

Thanks a lot for the contributions.