MySQL 5.6.26 Overview and Highlights

MySQL 5.6.26 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there are 3 “Functionality Added or Changed” items, 1 “Security Fix”, and 36 other bug fixes.

Out of those other 36 bugs, 13 are InnoDB, 1 Partitioning, 3 Replication, and 19 misc. (including 3 potentially crashing bug fixes, and 1 performance-related fix) Here are the ones of note:

  • Functionality Added/Changed: Replication: When using a multi-threaded slave, each worker thread has its own queue of transactions to process. In previous MySQL versions, STOP SLAVE waited for all workers to process their entire queue. This logic has been changed so that STOP SLAVE first finds the newest transaction that was committed by any worker thread. Then, it waits for all workers to complete transactions older than that. Newer transactions are not processed. The new logic allows STOP SLAVE to complete faster in case some worker queues contain multiple transactions. (Bug #75525)
  • Functionality Added/Changed: Previously, the max_digest_length system variable controlled the maximum digest length for all server functions that computed statement digests. However, whereas the Performance Schema may need to maintain many digest values, other server functions such as MySQL Enterprise Firewall need only one digest per session. Increasing the max_digest_length value has little impact on total memory requirements for those functions, but can increase Performance Schema memory requirements significantly. To enable configuring digest length separately for the Performance Schema, its digest length is now controlled by the new performance_schema_max_digest_length system variable.
  • Functionality Added/Changed: Previously, changes to the validate_password plugin dictionary file (named by the validate_password_dictionary_file system variable) while the server was running required a restart for the server to recognize the changes. Now validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a restart. In addition, two new status variables are available. validate_password_dictionary_file_last_parsed indicates when the dictionary file was last read, and validate_password_dictionary_file_words_count indicates how many words it contains. (Bug #66697)
  • Security-related: Due to the LogJam issue (https://weakdh.org/), OpenSSL has changed the Diffie-Hellman key length parameters for openssl-1.0.1n and up. OpenSSL has provided a detailed explanation at http://openssl.org/news/secadv_20150611.txt. To adopt this change in MySQL, the key length used in vio/viosslfactories.c for creating Diffie-Hellman keys has been increased from 512 to 2,048 bits. (Bug #77275)
  • InnoDB: Importing a tablespace with a full-text index resulted in an assertion when attempting to rebuild the index.
  • InnoDB: Opening a foreign key-referenced table with foreign_key_checks enabled resulted in an error when the table or database name contained special characters.
  • InnoDB: The page_zip_verify_checksum function returned false for a valid compressed page.
  • InnoDB: A failure to load a change buffer bitmap page during a concurrent delete tablespace operation caused a server exit.
  • InnoDB: After dropping a full-text search index, the hidden FTS_DOC_ID and FTS_DOC_ID_INDEX columns prevented online DDL operations. (Bug #76012)
  • InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure. (Bug #70214)
  • Partitioning: In certain cases, ALTER TABLE … REBUILD PARTITION was not handled correctly when executed on a locked table.
  • Replication: If flushing the cache to the binary log failed, for example due to a disk problem, the error was not detected by the binary log group commit logic. This could cause inconsistencies between the master and the slave. The fix uses the binlog_error_action variable to decide how to handle this situation. If binlog_error_action=ABORT_SERVER, then the server aborts after informing the client with an ER_BINLOGGING_IMPOSSIBLE error. If binlog_error_action=IGNORE_ERROR, then the error is ignored and binary logging is disabled until the server is restarted again. The same is mentioned in the error log file, and the transaction is committed inside the storage engine without being added to the binary log. (Bug #76795)
  • Replication: When using GTIDs, a multi-threaded slave which had relay_log_recovery=1 and that stopped unexpectedly could encounter a relay-log-recovery cannot be executed when the slave was stopped with an error or killed in MTS mode error upon restart. The fix ensures that the relay log recovery process checks if GTIDs are in use or not. If GTIDs are in use, the multi-threaded slave recovery process uses the GTID protocol to fill any unprocessed transactions. (Bug #73397)
  • Replication: When two slaves with the same server_uuid were configured to replicate from a single master, the I/O thread of the slaves kept reconnecting and generating new relay log files without new content. In such a situation, the master now generates an error which is sent to the slave. By receiving this error from the master, the slave I/O thread does not try to reconnect, avoiding this problem. (Bug #72581)
  • Crashing Bug: Incorrect cost calculation for the semi-join Duplicate Weedout strategy could result in a server exit.
  • Crashing Bug: For large values of max_digest_length, the Performance Schema could encounter an overflow error when computing memory requirements, resulting in a server exit.
  • Crashing Bug: GROUP BY or ORDER BY on a CHAR(0) NOT NULL column could lead to a server exit.
  • Performance-related: When choosing join order, the optimizer could incorrectly calculate the cost of a table scan and choose a table scan over a more efficient eq_ref join. (Bug #71584)

Conclusions:

So while there were no major changes, and not too many overall bug fixes, the security fix could be an issue if you run the latest RHEL/CentOS with SSL connections + a DHE SSL cipher specifed with –ssl-cipher=DHE-RSA-… Also, some of those InnoDB bugs are nasty, especially the fulltext bugs, thus if you use InnoDB’s fulltext, I’d recommend planning for an upgrade.

The full 5.6.26 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-26.html

Hope this helps. 🙂

MySQL 5.6.25 Overview and Highlights

MySQL 5.6.25 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there are 2 “Functionality Added or Changed” items of note:

  • Functionality Added/Changed: MySQL distributions now include an innodb_stress suite of test cases. Thanks to Mark Callaghan for the contribution. (Bug #76347)
  • Functionality Added/Changed: my_print_defaults now masks passwords. To display passwords in cleartext, use the new –show option.

In addition to those, there were 55 other bug fixes:

  • 10 InnoDB
  •   8 Replication
  •   3 Partitioning (one overlaps w/ an InnoDB bug fix)
  • 35 Miscellaneous (and 6 of those were specifically for “MySQL Enterprise Firewall”)

The highlights for me are 5 of the replication bugs, 1 partitioning bug, 1 performance-related bug, 1 wrong results bug, and 9 crashing bugs:

  • Replication: When using semisynchronous replication performance was degrading when the number of threads increased beyond a certain threshold. To improve performance, now only the thread which is committing is responsible for deleting the active transaction node. All other operations do not touch this active transaction list. (Bug #75570)
  • Replication: When binary logging was enabled, using stored functions and triggers resulting in a long running procedure that inserted many records caused the memory use to increase rapidly. This was due to memory being allocated per variable. The fix ensures that in such a situation, memory is allocated once and the same memory is reused. (Bug #75879)
  • Replication: If an error was encountered while adding a GTID to the received GTID set, the log lock was not being correctly released. This could cause a deadlock. (Bug #75781)
  • Replication: When master_info_repository=TABLE the receiver thread stores received event information in a table. The memory used in the process of updating the table was not being freed correctly and this could lead to an out of memory error. The fix ensures that after an event is flushed to the relay log file by a receiver thread, the memory used is freed. (Bug #72885, Bug #69848)
  • Replication: Using mysqlbinlog to process log events greater than 1.6GB failed with an out of memory error. This was caused by an internal error converting the length variable. The fix upgrades the length variable to avoid overflow in both encoding and decoding functions. (Bug #74734)
  • Partitioning: Executing an ALTER TABLE on a partitioned table on which a write lock was in effect could cause subsequent SQL statements on this table to fail. (Bug #74288).
  • Performance-related: Certain queries for the INFORMATION_SCHEMA TABLES and COLUMNS tables could lead to excessive memory use when there were large numbers of empty InnoDB tables. (Bug #72322)
  • Incorrect Results: Queries that included a HAVING clause based on nondeterministic functions could produce incorrect results. (Bug #69638)
  • Crashing Bug: For small values of the read_rnd_buffer_size system variable, internal caching of temporary results could fail and cause query execution failure.
  • Crashing Bug: A failed FLUSH PRIVILEGES statement followed by statements to create or drop accounts could cause a server exit.
  • Crashing Bug: SHOW VARIABLES mutexes were being locked twice, resulting in a server exit.
  • Crashing Bug: For join queries with a large number of tables, the server could exit converting the join to a semi-join.
  • Crashing Bug: Deleting rows from mysql.user following by granting privileges to a new account could result in a server exit.
  • Crashing Bug: Within a stored procedure, access to view columns after DDL or FLUSH TABLES statements in the procedure could cause a server exit.
  • Crashing Bug: Execution of certain BINLOG statements while temporary tables were open by HANDLER statements could cause a server exit.
  • Crashing Bug: For a prepared statement with an ORDER BY that refers by column number to a GROUP_CONCAT() expression that has an outer reference, repeated statement execution could cause a server exit.
  • Crashing Bug: Specifying –general_log_file= (with an empty value) at server startup caused the server to fail and exit.

Conclusions:

So while there were no major changes, the partitioning fix could fix a potentially serious issue if you think you might encounter it (some partitioning use-cases involve frequent ALTERs), the replication fixes could potentially be important for you, and the numerous crashing (and performance-related & wrong results) bugs are important if you’re performing the operations that trigger them. So read through these, and if you’ll be affected by any of the above, or think you might be, then I’d recommend upgrading.

On a side note, there are several serious “MySQL Enterprise Firewall” bug fixes in this release, which I omitted above since the general public doesn’t have access to it, but if you are using it, you should upgrade due to the number of potentially serious bugs that exist in prior versions.

The full 5.6.25 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-25.html

Hope this helps. 🙂

MySQL 5.6.24 Overview and Highlights

MySQL 5.6.24 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there are 4 “Functionality Added or Changed” items:

  • Functionality Added/Changed: CMake support was updated to handle CMake version 3.1.
  • Functionality Added/Changed: The server now includes its version number when it writes the initial “starting” message to the error log, to make it easier to tell which server instance error log output applies to. This value is the same as that available from the version system variable. (Bug #74917)
  • Functionality Added/Changed: ALTER TABLE did not take advantage of fast alterations that might otherwise apply to the operation to be performed, if the table contained temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). Instead, it upgraded the table by rebuilding it. Two new system variables enable control over upgrading such columns and provide information about them:
    • avoid_temporal_upgrade controls whether ALTER TABLE implicitly upgrades temporal columns found to be in pre-5.6.4 format. This variable is disabled by default. Enabling it causes ALTER TABLE not to rebuild temporal columns and thereby be able to take advantage of possible fast alterations.
    • show_old_temporals controls whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format. Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS table is affected similarly. This variable is disabled by default.
  • Functionality Added/Changed: Statement digesting as done previously by the Performance Schema is now done at the SQL level regardless of whether the Performance Schema is compiled in and is available to other aspects of server operation that could benefit from it. The default space available for digesting is 1024 bytes, but can be changed at server startup using the max_digest_length system variable.

In addition to those, there were 50 other bug fixes:

  • 15 InnoDB
  •   4 Replication
  •   1 Partitioning
  • 30 Miscellaneous

The highlights for me are the Partitioning bug and 2 of the Replication bugs (of the 15 InnoDB bugs, 5 were related to full-text search, and 6 were related to Memcached plugin, and the other 4 were mostly obscure):

  • Partitioning: A number of ALTER TABLE statements that attempted to add partitions, columns, or indexes to a partitioned table while a write lock was in effect for this table were not handled correctly.
  • Replication: When gtid_mode=ON and slave_net_timeout was set to a low value, the slave I/O thread could appear to hang. This was due to the slave heartbeat not being sent regularly enough when the dump thread found many events that could be skipped. The fix ensures that the heartbeat is sent correctly in such a situation.
  • Replication: When replicating from a MySQL 5.7.6 or later server to a MySQL 5.6.23 or earlier server, if the older version applier thread encountered an Anonymous_gtid_log_event it caused an assert. The fix ensures that these new log events added in MySQL 5.7.6 and later do not cause this problem with MySQL 5.6.24 and later slaves.

Conclusions:

So while there were no major changes, the partitioning fix covered a number of bugs, the replication fixes could potentially be important for you, and the numerous InnoDB full-text and Memcached fixes would be important if you’re using either of those. Thus if you rely on any of this, I’d consider upgrading.

The full 5.6.24 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-24.html

Hope this helps. 🙂

 

MySQL 5.7.7 Overview and Highlights

MySQL 5.7.7 was recently released (it is the latest MySQL 5.7, and is the first “RC” or “Release Candidate” release of 5.7), and is available for download here and here.

As for the fixes/changes, there are quite a few again, which is expected in an early RC release.

The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):

  • Optimizer Note: It is now possible to provide hints to the optimizer within individual SQL statements, which enables finer control over statement execution plans than can be achieved using the optimizer_switch system variable. Optimizer hints are specified as /*+ … */ comments following the SELECT, INSERT, REPLACE, UPDATE, or DELETE keyword of statements or query blocks. Hints are also permitted in statements used with EXPLAIN, enabling you to see how hints affect execution plans.
  • Security Note: The C client library now attempts to establish an SSL connection by default whenever the server is enabled to support SSL. This change affects these standard MySQL client programs: mysql, mysql_config_editor, mysql_install_db, mysql_plugin, mysql_secure_installation, mysql_upgrade, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlshow, and mysqlslap. It will also affect new releases of MySQL Connectors that are based on the C client library: Connector/C, Connector/C++, and Connector/ODBC.
  • Spatial Data Support: The ST_Buffer(), ST_Difference(), ST_Distance(), ST_Intersection(), ST_IsSimple(), ST_SymDifference(), and ST_Union() functions have been reimplemented to use the functionality available in Boost.Geometry. The functions may raise an exception for invalid geometry argument values when the previous implementation may not have.
  • InnoDB: The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.
  • InnoDB: The innodb_large_prefix default value was changed to ON. The previous default was OFF. When innodb_file_format is set to Barracuda, innodb_large_prefix=ON allows index key prefixes longer than 767 bytes (up to 3072 bytes) for tables that use a Compressed or Dynamic row format.
  • InnoDB: The innodb_strict_mode default value was changed to ON. The previous default was OFF. When innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior).

    The configuration parameter default changes described above may affect replication and mysqldump operations. Consider the following recommendations when using the new default settings:

    • When replicating or replaying mysqldump data from older MySQL versions to MySQL 5.7.7 or higher, consider setting innodb_strict_mode to OFF to avoid errors. Target settings should not be more strict than source settings.
    • When replicating from MySQL 5.7.7 or higher to older slaves, consider setting innodb_file_format=Barracuda and innodb_large_prefix=ON on the slave so that the target and source have the same settings.
  • InnoDB: To address a scalability bottleneck for some workloads where LOCK_grant is locked in read-mode, LOCK_grant locks are now partitioned. Read lock requests on LOCK_grant now acquire one of multiple LOCK_grant partitions. Write locks must acquire all partitions. To address another scalability bottleneck, the server no longer performs unnecessary lock acquisitions when creating internal temporary tables. (Bug #72829)
  • Replication: The XA implementation in MySQL has been made much more compatible with the XA specification.
  • Replication: The defaults of some replication related variables have been modified. The following changes have been made:
    • binlog_gtid_simple_recovery=TRUE
    • binlog-format=ROW
    • binlog_error_action=ABORT_SERVER
    • sync_binlog=1
    • slave_net_timeout=60

Again, there are numerous enhancements and many bug fixes, so please check out the full changelogs. If you’re running some 5.7 version, then you should definitely upgrade. (But this should not be used for production systems yet, of course.)

You can view the full 5.7.7 changelogs here:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html

Hope this helps.

 

MySQL 5.7.6 Overview and Highlights

MySQL 5.7.6 was recently released (it is the latest MySQL 5.7, and is the “m16” or “Milestone 16” release), and is available for download here and here.

As for the fixes/changes, there are quite a few (the official release was again split into 3 separate emails), which is expected in a “milestone” release.

The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):

  • Incompatible Change: The CREATE USER and ALTER USER statements have additional account-management capabilities. Together, they now can be used to fully establish or modify authentication, SSL, and resource-limit properties, as well as manage password expiration and account locking and unlocking. … A new statement, SHOW CREATE USER, shows the CREATE USER statement that creates the named user. The accompanying Com_show_create_user status variable indicates how many times the statement has been executed.
  • Configuration Note: mysqld now supports a –daemonize option that causes it to run as a traditional, forking daemon. This permits the server to work with operating systems that use systemd for process control.
  • Installation Note: The mysqld server and mysql_upgrade utility have been modified to make binary (in-place) upgrades from MySQL 5.6 easier without requiring the server to be started with special options. The server checks whether the system tables are from a MySQL version older than 5.7 (that is, whether the mysql.user table has a Password column). If so, it permits connections by users who have an empty authentication plugin in their mysql.user account row, as long as they have a Password value that is empty (no password) or a valid native (41-character) password hash.
  • Performance Schema Notes: The Performance Schema now allocates memory incrementally, scaling its memory use to actual server load, instead of allocating all the memory it needs during server startup. Consequently, configuration of the Performance Schema is easier; most sizing parameters need not be set at all. A server that handles a very low load will consume less memory without requiring explicit configuration to do so.
  • Incompatible Change: A new C API function, mysql_real_escape_string_quote(), has been implemented as a replacement for mysql_real_escape_string() because the latter function can fail to properly encode characters when the NO_BACKSLASH_ESCAPES SQL mode is enabled.
  • InnoDB: InnoDB system tablespace data is now exposed in the INNODB_SYS_TABLESPACES and INNODB_SYS_DATAFILES Information Schema tables.
  • InnoDB: Numerous (7) buffer pool flushing-related enhancements were added.
  • InnoDB: The default setting for the internal_tmp_disk_storage_engine option, which defines the storage engine the server uses for on-disk internal temporary tables, is now INNODB. With this change, the Optimizer uses the InnoDB storage engine instead of MyISAM for internal temporary tables.
  • InnoDB: InnoDB now supports native partitioning.
  • InnoDB: InnoDB now supports the creation of general tablespaces using CREATE TABLESPACE syntax. Tables are added to a general tablespace using CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name syntax.
  • InnoDB: InnoDB now supports 32KB and 64KB page sizes. For both page sizes, the maximum record size is 16KB.
  • InnoDB: Replication-related support was added to InnoDB which enables prioritization of slave applier transactions over other transactions in deadlock scenarios. This transaction prioritization mechanism is reserved for future use.
  • InnoDB: The Performance Schema now instruments stage events for monitoring InnoDB ALTER TABLE and buffer pool load operations.
  • Replication: MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. See MySQL Multi-Source Replication. As part of MySQL Multi-Source Replication, replication channels have been added. Replication channels enable a slave to open multiple connections to replicate from, with each channel being a connection to a master. See Replication Channels.

Again, there are numerous enhancements and hundreds of bug fixes, so please check out the full changelogs. If you’re running some 5.7 version, then you should definitely upgrade. (But this should not be used for production systems yet, of course.)

You can view the full 5.7.6 changelogs here:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html

Hope this helps.

 

MySQL 5.6.23 Overview and Highlights

MySQL 5.6.23 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 3 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

  1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1j to version 1.0.1k. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
  2. Functionality Changed: Support for the SSL 2.0 and SSL 3.0 protocols has been disabled because they provide weak encryption. (Bug #19820550, Bug #19921150)
  3. Functionality Changed: yaSSL was upgraded to version 2.3.7. (Bug #19695101, Bug #20201864)
  4. Functionality Changed: The valid date range of the SSL certificates in mysql-test/std_data has been extended to the year 2029. (Bug #18366947)

In addition to those, there were 37 other bug fixes:

  • 13 InnoDB
  •   5 Replication
  • 18 Miscellaneous
  •   1 Partitioning

The highlights for me are the Partitioning bug, 1 of the Replication bugs, and 8 of the InnoDB bugs, as 1 was a regression bug (crashing/corruption) and the others include bugs that raise invalid assertions, server halts, break replication, and so forth, though all in all, I wouldn’t say any of these are very common and require immediate attention:

  1. InnoDB: If a DROP DATABASE statement failed on the master, mismatched tables could be left on the slave, breaking replication. This was caused by the DROP TABLE statement being binary logged if at least one table was deleted during the DROP DATABASE operation. The fix ensures that in such a situation the DROP TABLE statement is binary logged with the IF EXISTS option. (Bug #74890, Bug #20041860)
  2. InnoDB: A tablespace export operation set the purge state to PURGE_STATE_STOP but the purge thread did not check the purge state until the current purge operation was completed. In the case of a large history list, the tablespace export operation was delayed, waiting for the current purge operation to finish. The purge state is now checked with every purge batch. (Bug #20266847, Bug #75298)
  3. InnoDB: An ALTER TABLE … ADD INDEX operation raised an assertion due to assertion code that did not allow an online index status of ONLINE_INDEX_ABORTED_DROPPED. The assertion code has been relaxed. (Bug #20198726)
  4. InnoDB: DML operations on a table with full-text search indexes raised an invalid assertion. (Bug #19905246) References: This bug is a regression of Bug #19314480.
  5. InnoDB: A multiple-table delete operation caused the server to halt. (Bug #19815702)
  6. InnoDB: A FLUSH TABLES operation raised an assertion. (Bug #19803418)
  7. InnoDB: With change buffering enabled, a buffered sequence of operations that should not have been buffered resulted in an Unable to purge a record error. (Bug #19528825, Bug #73767)
  8. InnoDB: A slow shutdown (innodb_fast_shutdown=0) after crash recovery raised an assertion. Slow shutdown did not wait for background rollback operations to finish before proceeding. (Bug #16862810)
  9. Partitioning: A failed ALTER TABLE … TRUNCATE PARTITION statement or a failed TRUNCATE TABLE statement against a partitioned table sometimes left inconsistent metadata in the table cache; subsequent SQL statements reusing this metadata failed, and could in some cases also lead to a failure of the server. (Bug #74292, Bug #19786861)
  10. Replication: When using SHOW SLAVE STATUS to monitor replication performance, Seconds_Behind_Master sometimes displayed unexpected lag behind the master. This was caused by Previous_gtids log events being written to the slave’s relay log with a timestamp behind the master, and then being used to calculate the Seconds_Behind_Master. This fix ensures that events generated on the slave that are added to the relay log and are not used when calculating Seconds_Behind_Master. (Bug #72376, Bug #18622657)

Conclusions:

So while there were no major changes, those 8 InnoDB bugs, especially in total, are of concern, so I’d consider upgrading if I were running InnoDB on a prior version of 5.6.

And with the yaSSL updates, if you use SSL connections, you may want to consider upgrading as well.

The full 5.6.23 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-23.html

Hope this helps. 🙂

 

MySQL 5.6.22 Overview and Highlights

MySQL 5.6.22 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 2 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

  1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1h to version 1.0.1j. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
  2. Functionality Changed: Replication: The variable binlogging_impossible_mode has been renamed binlog_error_action. binlogging_impossible_mode is now deprecated. (Bug #19507567)
  3. Functionality Changed: Security: yaSSL was upgraded to version 2.3.5. (Bug #19695101)
  4. Compilation Notes: These are all rather minor, so I’ll spare the full entries here. However, if you build MySQL from source, it would be worth several minutes to read the 5 notes in the changelogs.

In addition to those, there were 46 other bug fixes:

  • 17 InnoDB
  •   9 Replication
  • 19 Miscellaneous
  •   1 Partitioning

The highlights for me are 6 of the InnoDB bugs, as 2 were regression bugs (crashing/corruption), 2 potentially corruption causing, another crashing, and 1 halting:

  1. InnoDB: An ALTER TABLE operation raised an assertion. When a foreign key object was removed from the dictionary cache, an incorrect foreign key object was removed from the rb-tree. (Bug #19908343) References: This bug is a regression of Bug #18806829.
  2. InnoDB: Pages with a checksum value of zero were incorrectly treated as empty pages. A page should only be considered empty if its checksum value and LSN field values are zero. (Bug #19500258, Bug #73689) References: This bug is a regression of Bug #17335427.
  3. InnoDB: The dict_set_corrupted() function attempted to update the clustered index of the SYS_INDEXES data dictionary table incorrectly. (Bug #19584379).
  4. InnoDB: The InnoDB data dictionary was not updated when a ALTER TABLE … CHANGE COLUMN operation changed the case of the column name. (Bug #19465984).
  5. InnoDB: A memory access violation caused fts_optimize_thread and mysqld to terminate. (Bug #19314480).
  6. InnoDB: A procedure, called from a function to perform an operation on a temporary table, caused the server to halt/stall. (Bug #19306524)

Conclusions:

So while there were no major changes, those 6 InnoDB bugs (2 being regression bugs) are definitely of concern, so I’d be sure to review these to see if you’re running an affected version, and consider upgrading if so.

And with the yaSSL updates, if you use SSL connections, you may want to consider upgrading as well.

The full 5.6.22 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-22.html

Hope this helps. 🙂

 

MySQL 5.7.5 Overview and Highlights

MySQL 5.7.5 was recently released (it is the latest MySQL 5.7, and is the “m15” or “Milestone 15” release), and is available for download here and here.

As for the fixes/changes, there are quite a few (the official release was split into 3 separate emails), which is expected in such an early milestone release.

The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):

  • InnoDB: The innodb_buffer_pool_size parameter is now dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable. For more information, see Resizing the InnoDB Buffer Pool Online.
  • Replication: When replicating from a master running a version earlier than MySQL 5.6.0 [Read “5.5” or “5.1”] to a slave running MySQL 5.6.0 or later, the slave requires the master_uuid value, which is the server_uuid value from the master. The master_uuid value is unsupported on the older master, and in such a replication situation could become invalid on the newer slave. A check for empty master_uuid now ensures that the slave uses an empty value for master_uuid. (Bug #18338203)
  • Incompatible Change: mysql_install_db has been rewritten from Perl into C++. This enables it to be provided as an executable binary and eliminates its dependency on having Perl installed.
  • MySQL builds on Windows using Visual Studio now require Visual Studio 2013 or later. The previous requirement was Visual Studio 2010 or later. (Bug #18404381)
  • Now, MYSQL_MAINTAINER_MODE is on by default when compiling debug builds with GCC, and MYSQL_MAINTAINER_MODE enbles -Werror regardless of whether GCC or Clang is used.
  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel. If DTrace is present, server builds will detect it with no special CMake options required.
  • Incompatible Change: A new log record type (MLOG_FILE_NAME) is used to identify file-per-table tablespaces that have been modified since the last checkpoint. This enhancement simplifies tablespace discovery during crash recovery and eliminates scans on the file system prior to redo log application. For more information about the benefits of this enhancement, see Tablespace Discovery During Crash Recovery. This enhancement changes the redo log format, requiring that MySQL be shut down cleanly before upgrading to or downgrading from MySQL 5.7.5.
  • Incompatible Change: The InnoDB storage engine can no longer be disabled. The –skip-innodb option is deprecated and has no effect, and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (–innodb=OFF, –disable-innodb, and so forth). A new innodb_lock_no_retry flag for the –debug option is now available.
  • Incompatible Change: The Performance Schema now provides a user_variables_by_thread table that exposes user-defined variables. For more information, see Performance Schema Connection Attribute Tables. In consequence of this change, the server now limits user-defined variable names to a maximum of 64 characters, the length of the VARIABLE_NAME column in the table. Previously, the server did not enforce a limit.
  • The optimizer computes more accurate costs for semi-join materialization. (Bug #18558561)
  • To generate execution plans, the optimizer uses a cost model that is based on estimates of the cost of various operations that occur during query execution. The optimizer has a set of compiled-in default “cost constants” available to it to make decisions regarding execution plans. The optimizer now has in addition a database of cost estimates to use during execution plan construction. These estimates are stored in the server_cost and engine_cost tables in the mysql system database and are configurable at any time: Any non-NULL cost estimate stored in the cost model tables overrides the corresponding compiled-in default estimate. Any NULL estimate indicates to the optimizer to use the compiled-in default. Implementation and testing is ongoing to make it safe for DBAs to change these values. Currently, changing them should be considered at your own risk. If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the mysql database.
  • The optimizer now uses more exact index statistics. Currently, the improved values are used by InnoDB, with these effects: 1) In many cases, better execution plans result for queries for which previously a less optimal join index or table join order was chosen. 2) The row estimates in EXPLAIN output are more accurate, as well as the filter values in some cases. 3) Cardinality estimates in the index statistics displayed by SHOW INDEX are more accurate for InnoDB tables.
  • During query execution plan construction, the optimizer now uses condition filtering to make better use of all conditions on a table in determining the estimate of qualifying rows that will be joined to the next table. For example, even though there might be an index that can be used to select rows, there might also be additional conditions in the WHERE clause that can further restrict the estimate for qualifying rows. Use of additional conditions is controlled by the condition_fanout_filter flag of the optimizer_switch system variable. This flag is on by default but can be disabled to suppress use of condition filtering (for example, for a query that is found to perform better without it).
  • Security Note: Incompatible Change: MySQL 5.6 deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords is now removed, which involves the following changes. Applications that use any feature no longer supported must be modified. The mysql_old_password authentication plugin is removed. The –secure-auth option to the server and client programs is the default, but is now a no-op. It is deprecated and will be removed in a future MySQL release. The –skip-secure-auth option to the server and client programs is no longer supported and using it produces an error.
  • Incompatible Change: Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.
  • InnoDB: SPATIAL indexes can now be used for InnoDB tables. InnoDB supports indexing of spatial data types, including use of ALTER TABLE … ALGORITHM=INPLACE for online operations (ADD SPATIAL INDEX). To support transaction isolation properties, InnoDB uses predicate locking. A predicate lock locks the minimum bounding rectangle (MBR) used for a query so that other transactions cannot insert or modify a row that would match the query condition.
  • Incompatible Change: Previously, mysql_upgrade performed an upgrade by invoking the mysql and mysqlcheck clients. mysql_upgrade has been reimplemented to generate the required SQL statements itself and execute them by communicating directly with server.
  • Incompatible Change: In MySQL 5.6.6, the YEAR(2) data type was deprecated. Support for YEAR(2) has now been removed. Once you upgrade to MySQL 5.7.5 or newer, any remaining YEAR(2) columns must be converted to YEAR(4) to become usable again. For conversion strategies, see YEAR(2) Limitations and Migrating to YEAR(4). For example, run mysql_upgrade after upgrading.
  • Incompatible Change: The GET_LOCK() has been reimplemented using the metadata locking (MDL) subsystem and its capabilities have been extended.
  • InnoDB: For optimal shutdown and recovery performance, shutdown and recovery phases are now supported by the multi-threaded page cleaner feature (innodb_page_cleaners) that was introduced in MySQL 5.7.4. (Bug #18805275)
  • InnoDB: Instead of inserting one index record at a time, InnoDB now performs a bulk load when creating or rebuilding indexes. This method of index creation is also known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes.
  • InnoDB: InnoDB memory allocations now are instrumented for the Performance Schema and will appear in the memory summary tables.
  • InnoDB: You can now truncate undo logs that reside in undo tablespaces. This feature is enabled using the innodb_undo_log_truncate configuration option. For more information, see Truncating Undo Logs That Reside in Undo Tablespaces.
  • InnoDB: Work was done to introduce the notion of attachable transactions in InnoDB (for AutoCommit / ReadOnly / ReadCommitted / NonLocking transactions). This is used to read from InnoDB Data Dictionary tables. Along with this, attachable transactions were exposed to the server. Data Dictionary access code will use them to read Data Dictionary data.
  • Replication: Retrying of transactions is now supported when multi-threading is enabled on a slave. In previous versions, slave_transaction_retries was treated as equal to 0 when using multi-threaded slaves. (Bug #16390504, Bug #68465)
  • Replication: Global transaction identifiers (GTIDs) are now logged in a MySQL system table whenever they are enabled on the server, which lifts a previous requirement to use binary logging when replicating with GTIDs. If binary logging is disabled, the server stores the GTID for each transaction in the mysql.gtid_executed table as the transaction is executed. If binary logging is enabled, then, whenever the binary log is rotated or the server is shut down, the server also writes into the new binary log the GTIDs for all transactions from the previous binary log.
  • Replication: The new variable simplified_binlog_gtid_recovery can be used to change the way binary log files are searched for previous GTIDs during recovery, speeding up the process when a large number of binary log files exist. (Bug #69097, Bug #16741603, Bug #74071, Bug #19686914)
  • Replication: Multi-threaded slaves can use the new slave_preserve_commit_order variable to ensure that the order which transactions were committed on the master is preserved on the slave. This prevents the slave from entering a state that the master was not in and is well suited to using multi-threaded slaves for replication read scale-out.
  • Replication: The new options binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count provide a way to configure the synchronization of the binary log. This enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group.
  • Replication: To make monitoring of a replication setup easier, various replication related variables have been moved to the performance_schema tables. This is particularly helpful for monitoring multi-source replication.
  • The mysqladmin flush-logs command now permits optional log types to be given, to specify which logs to flush. Following the flush-logs command, you can provide a space-separated list of one or more of the following log types: binary, engine, error, general, relay, slow. These correspond to the log types that can be specified for the FLUSH LOGS SQL statement. Thanks to Daniël van Eeden for the patch. (Bug #60878, Bug #12368203)
  • Scalability for InnoDB tables was improved by avoiding THR_LOCK locks. As a result of this change, DML statements for InnoDB tables that previously waited for a THR_LOCK lock will wait for a metadata lock. (Bug #42147, Bug #11751331)
  • The Boost.Geometry library now is required to build MySQL.

And that pretty much just covers the highlights of the “Functionality Added or Changed” section. I’m not even getting into the “Bugs Fixed” section, of which there were 296 (many InnoDB & Replication)! So there has been a lot going on in this release. If you’re running some 5.7 version, then you should definitely upgrade. (But this should not be used for production systems yet, of course.)

You can view the full 5.7.5 changelogs here:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html

Hope this helps.

 

MySQL 5.7.4 Overview and Highlights

MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14” or “Milestone 14” release), and is available for download here and here.

The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

In Memoriam:

“This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html

As for the fixes, there are quite a few, which is to be expected in such an early milestone release.

The main highlights for me were:

  1. The Performance Schema now instruments prepared statements (for both the binary and text protocols). Info is available in the prepared_statements_instances table, along with performance_schema_max_prepared_statements_instances system variable, and Performance_schema_prepared_statements_lost status variable.
  2. Incompatible Change: MySQL deployments installed using RPM packages now are secure by default (single root account, ‘root’@’localhost’, no anonymous-user accounts, no test database).
  3. Incompatible Change: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.
  4. Performance; InnoDB: InnoDB now supports multiple page_cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page_cleaner threads.
  5. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument
  6. InnoDB: InnoDB now supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.

Of course, there were many, many more fixes/updates (InnoDB being #1, Replication #2, and Partitioning #3 with most fixed bugs), so be sure to read through the full changelog. And if you are running a previous version of *5.7*, then definitely plan on upgrading to this latest 5.7.4.

Hope this helps.

 

MySQL, MariaDB, XtraDB, et al Downloads

Similar to my other catch-all pages on Server Variables, Changelogs, and Documentation, I’ve added a new ‘Downloads’ section.

Basically, it’s just a single source containing links to all of your favorite MySQLs and related products, such as MySQL, MariaDB, XtraDB, Xtrabackup, MySQL Proxy, Connectors and more:

http://www.chriscalender.com/?page_id=831

Hope this helps.