MySQL在今天同時發表MySQL Community Server 5.7.20與MySQL Community Server 5.6.38。按照慣例我還是繼續使用舊系列的MySQL Community Server 5.6.38。因為新的MySQL Community Server 5.7.20問題太多!從底下的變更記錄就可以知道,從5.7.19到5.7.20修正了非常的問題點!
Audit Log Notes
Event-matching filter rules for the audit_log plugin now support an abort element, which can be used to prevent qualifying events from executing. For more information, see Audit Log Filtering. This capability can be used, for example, to augment the capabilities of MySQL Enterprise Firewall, which blocks SQL statements on a per-user basis, by writing audit filtering rules that match statements and block them based on characteristics of the statements themselves.
Deprecation and Removal Notes
Previously, the --transaction-isolation and --transaction-read-only server startup options corresponded to thetx_isolation and tx_read_only system variables. For better name correspondence between startup option and system variable names, transaction_isolation and transaction_read_only have been created as aliases for tx_isolation andtx_read_only. The tx_isolation and tx_read_only variables are now deprecated and will be removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation and transaction_read_only instead.
The query cache is now deprecated and is removed in MySQL 8.0. Deprecation includes these items:
The FLUSH QUERY CACHE and RESET QUERY CACHE statements.
The SQL_CACHE and SQL_NO_CACHE SELECT modifiers.
These system variables: have_query_cache, ndb_cache_check_time, query_cache_limit, query_cache_min_res_unit,query_cache_size, query_cache_type, query_cache_wlock_invalidate.
These status variables: Qcache_free_blocks, Qcache_free_memory, Qcache_hits, Qcache_inserts,Qcache_lowmem_prunes, Qcache_not_cached, Qcache_queries_in_cache, Qcache_total_blocks.
The mysql client by default strips comments in statements sent to the server, and this behavior is controlled using --skip-comments (strip comments), and --comments (preserve comments).
Comment stripping is now deprecated. This feature and the options to control it will be removed in a future MySQL release.
These syntax constructs for table and column references are now deprecated and will be removed in a future version of MySQL. Instances of these constructs should be changed to remove the leading period.
.col_name
.tbl_name
.tbl_name.col_name
Installation Notes
For platforms that use systemd (see Managing MySQL Server with systemd), the data directory is initialized if empty at server startup. This might be a problem if the data directory is a remote mount that has temporarily disappeared: The mount point would appear to be an empty data directory, which then would be initialized as a new data directory. It is now possible to suppress this automatic initialization behavior. specify the following line in the /etc/sysconfig/mysql file (create the file if it does not exist):
NO_INIT=true
(Bug #26595288, Bug #87287)
Packaging Notes
mysqlcheck was missing in the MySQL Server Docker image, which prevented mysql_upgrade from running. (Bug #26400146, Bug #86968)
Security Notes
Certificates automatically generated by mysqld and mysql_ssl_rsa_setup now use X509 v3 rather than v1. (Bug #26521654)
The keyring_okv plugin now supports password-protecting the key file used for secure connections. See Using the keyring_okv KMIP Plugin.
X Plugin Notes
A Mysqlx.Connection.CapabilitiesGet request using MySQL X Protocol did not return the complete list of available authentication mechanisms. (Bug #26044113)
For mixed case or uppercase schema names, the statement list_objects could incorrectly report a collection as a table. (Bug #25769683)
The X Plugin was omitted from the list of plugins to include for testing data directory permissions. (Bug #24823999)
Functionality Added or Changed
Replication: In previous versions issuing STOP GROUP_REPLICATION stopped the plugin but the server still accepted transactions. This meant the transactions were not transmitted to the group. To make STOP GROUP_REPLICATION safer, now super_read_onlyis set to ON immediately upon issuing STOP GROUP_REPLICATION, which ensures no transactions are accepted. (Bug #25495067, Bug #84795)
Replication: The group_replication_member_weight variable has been added which enables you to control the election of new primaries in single-primary mode. In previous versions primary election was based on the member's UUID, with the lowest UUID elected as the new primary in the event of fail over. Use this variable to assign numeric weights to members to ensure that specific members are elected, for example during scheduled maintenance of the primary or to ensure certain hardware is prioritised.
Bugs Fixed
InnoDB: Invalid error handling code was removed from a function related to tablespace import. (Bug #26595476)
InnoDB: A check for discarded partitions during a DML operation only checked the first partition. Failure to check for other discarded partitions caused an assertion failure. (Bug #25942592)
InnoDB: Replication lag occurred on slave instances during large update operations on tables with many partitions. (Bug #25687813, Bug #85352)
InnoDB: A failure occurred during an end range comparison. (Bug #25669686)
InnoDB: Enabling the innodb_buffer_pool_load_now setting failed in readonly mode. The event that signals the buffer pool load thread was not initialized. (Bug #25586766)
InnoDB: Test-related code intended to simulate a random read on a non-existent page raised an invalid assertion. (Bug #25479538)
References: This issue is a regression of: Bug #25053705.
InnoDB: A long wait for a dictionary operation lock held by a full-text search synchronization operation caused a server exit. (Bug #24938374)
Partitioning: In certain cases when fetching heap records a partition ID could be set to zero. (Bug #86255, Bug #26034430)
Partitioning: Queries involving NULL were not always handled correctly on tables that were partitioned by LIST. (Bug #76418, Bug #20748521)
References: See also: Bug #86255, Bug #26034430.
Replication: MySQL internal administration commands that update replication-specific repository tables, for example during a replication synchronization check using the mysqlrplsync utility, can now bypass read locks. This enables such commands to execute regardless of the settings for the read_only and super_read_only system variables and the autocommit mode. (Bug #26414532, Bug #86224)
Replication: When the Group Replication plugin Delayed_initialization_thread failed to start due to unavailable resources, a locked mutex was being kept behind which would cause issues on Delayed_initialization_thread destructor. The fix ensures that the mutex is unlocked when the thread fails to start. (Bug #26394678)
Replication: If hostname resolution was not working for a member in Group Replication, the error returned when attempting to connect was referring to credentials. The error message has been improved to describe the problem with hostname resolution. (Bug #26368004)
Replication: If the options file contained Group Replication related settings the server could stop unexpectedly on start up. (Bug #26314472)
Replication: FLUSH LOGS attempted to send an OK message after having already sent an error response during the commit phase. (Bug #26272158)
Replication: With some workloads that contained large inserts, the XCOM transaction cache could consume a large amount of memory. The fix limits the size of the cache to reduce the impact to memory usage. (Bug #26241291)
Replication: Group Replication partition threads were not visible in the Performance Schema tables. (Bug #26241008)
Replication: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE was set to an incorrect value whengroup_replication_recovery_complete_at="transactions_certified" on a recovering member. (Bug #26180350)
Replication: The values of the group_replication_recovery_use_ssl andgroup_replication_recovery_ssl_verify_server_cert variables were not being updated when configured for the Group Replication recovery channel. (Bug #26142801)
Replication: When replicating a partitioned table with an index, on a replication slave where HASH_SCAN was specified as part of theslave_rows_search_algorithms setting, the slave I/O thread sometimes stopped with an error HA_ERR_KEY_NOT_FOUND. (Bug #26137159)
Replication: group_replication_force_members could be used in situations where the group was working properly, in other words a majority was reachable. This incorrect use could cause instability in the group. Therefore, its use has been restricted to the scenario for which it was created, for forming a new membership from a subset of a previous group's membership when a majority of the members are unreachable. (Bug #26093967)
Replication: The delayed initialization mechanism used for server starts has been improved. Now, it only blocks connections until the server is in read mode. (Bug #26037344)
References: See also: Bug #84731, Bug #25475132.
Replication: It was possible to set server_uuid to the same value as group_replication_group_name. Doing so could result in unexpected behavior because GTIDs are identified by a UUID. Now it is not possible to set server_uuid to the same value asgroup_replication_group_name. (Bug #26035931)
Replication: The system variable pseudo_slave_mode, which is for internal server use, sometimes raised an assertion when it was changed inside a transaction. The server no longer changes this variable inside a transaction. (Bug #26034192, Bug #86250)
Replication: When a primary member, for example the primary in single-primary group or in a multi-primary group, which also had asynchronous replication channels feeding data into it was stopped, the asynchronous channels would continue applying changes. Although super_read_only was being set when STOP GROUP_REPLICATION was issued, this did not stop any running asynchronous replication channels which were already running. This meant that changes could be made locally on the member, and that the asynchronous replication channels had to be stopped manually. Now when Group Replication stops, either due to an error or when STOP GROUP_REPLICATION is issued, all asynchronous replication channels are stopped. (Bug #26024253)
Replication: When write sets are used for parallelization by a replication slave (as specified by thebinlog_transaction_dependency_tracking system variable), empty transactions are now ignored, and the handling of relay log rotation has been optimized. (Bug #25982097)
Replication: A misleading warning was issued when the command FLUSH LOGS or PURGE LOGS BEFORE was used on a binary log file with an expiry time set, and the binary log file was in use. The warning related to the file being in use, and implied that a purge attempt had taken place, even if the expiry time had not yet been reached. Now, MySQL checks the expiry time of the binary log file first, and only then checks whether the file is in use. So the warning is only issued for an in-use binary log file that is old enough to be purged. (Bug #25973525)
Replication: Group Replication stopped unexpectedly when more than 1024 file descriptors were in use. (Bug #25892493)
Replication: Now that XA transactions are prepared and committed in two parts, an issue with statement-based replication has been identified. If two XA transactions committed on the master are being prepared on the slave in the inverse order, locking dependencies can occur that cannot be safely resolved. The issue is not present with row-based replication.
XA transactions are therefore now considered unsafe for statement-based replication.
When binlog_format = STATEMENT, a warning is issued for DML statements inside XA transactions, and replication might fail with deadlock on slaves.
When binlog_format = MIXED, DML statements inside XA transactions are logged using row-based replication.
When binlog_format = ROW, DML statements inside XA transactions are logged as before.
(Bug #25786490, Bug #85639)
Replication: If the Group Replication plugin was uninstalled while a group was running, the plugin was marked for uninstall. This could cause the server shutdown to not complete successfully. The fix ensures that the Group Replication plugin cannot be uninstalled if the majority of the group cannot be reached. In such a case it is necessary to issue STOP GROUP_REPLICATION before uninstalling the plugin. (Bug #25673788)
Replication: Replication clients no longer enable LOCAL capability for LOAD DATA statements, because they do not use LOAD DATA LOCAL statements. (Bug #24763131)
Replication: In case of a failure while creating multiple slave applier worker threads, some threads would be left orphaned and their resources would not be collected. Thanks to Laurynas Biveinis for his contribution to fixing this bug. (Bug #24679056, Bug #82980)
Replication: The binlog_checksum option cannot be changed within a transaction. MySQL cannot log this statement, as would be required inside a transaction, while the requested function is being performed on the binary log. (Bug #22914463)
Replication: The logging of Group Replication has been improved. Now logging includes information when a member joins or leaves, when the view changes, and so on. (Bug #84798, Bug #25495393)
Compiling with -DWITHOUT_SERVER=1 resulted in my_symlink.c compilation failure due to missing #include for my_dir.h. Thanks to Christian Hesse for the patch. (Bug #26495816, Bug #87137)
yaSSL could incorrectly perform TLS cipher negotiation. (Bug #26482173)
The Linux RPM spec file for RHEL6 and higher is updated with comments that recommend installing the redhat-rpm-configpackage to add convenience macros that make rebuilding the RPM package easier. Thanks to Simon Mudd for the patch. (Bug #26474153, Bug #87098)
If the error log was misconfigured and the server could not start, no output indicating the problem was produced. (Bug #26447825, Bug #87087)
mysqld_multi was modified to use mysqld --initialize rather than the deprecated mysql_install_db. Thanks to Zhan Shi for the patch. (Bug #26446321, Bug #87080)
Adding an ORDER BY to a query that included an outer join and a subquery caused a constant value defined for a column in the subquery to be incorrectly promoted to a constant value in the case when the subquery returns 0 rows. (Bug #26432173)
The server failed to check the maximum path length for partition names. (Bug #26390632)
Identifiers containing a mixture of backslashes and backticks could be parsed incorrectly. (Bug #26372491)
Source packages for Debian platforms contained prebuilt debug binaries, causing build failures on any architectures other than the one on which those binaries were built. (Bug #26186911)
The mysqld_pre_systemd script in RPM packages found the error log setting in option files if specified as log-error but not aslog_error, though both are permitted. (Bug #26148391, Bug #86466)
When running mysqlbinlog with the --read-from-remote-server option, rewrite rules specified using the --rewrite-db option were ignored, so data was not written to the target database. (Bug #26117735, Bug #86288)
REFERENCES privilege checking could use the incorrect database in some cases. (Bug #26106655)
Uninstalling the daemon_memcached plugin caused a serious error. (Bug #25909540)
The rpl_diff.inc test case file did not find the data difference between servers. Thanks to Yura Sorokin for the patch. (Bug #25860138, Bug #85838)
An ngram fulltext parser search query returned incorrect results and raised an assertion. (Bug #25851975)
Selecting from a view could yield different results with materialization enabled versus materialization disabled. (Bug #25782811, Bug #85622)
The Performance Schema failed to check the maximum host length for client connections. (Bug #25510805)
mysqlpump displayed incorrect progress information about the number of tables dumped. (Bug #25432850)
Some mysqldump warnings went to the standard output rather than the standard error output and consequently were written to the dump file. (Bug #25380000, Bug #82992)
LOAD DATA failed to accept multibyte characters that followed an escape sequence. (Bug #25147988, Bug #83950, Bug #25865525)
A server error occurred when a full text search result exceeded the innodb_ft_result_cache_limit setting. The patch for this bug also backports a related patch (Bug #21140111). (Bug #25033538)
If a stored function was considered a constant by the optimizer, calling it from a subquery in a NOT IN condition in the WHERE clause could cause a server exit. (Bug #23577867)
A mysqldump memory leak was fixed. Thanks to Yura Sorokin for the patch. (Bug #23531150, Bug #81714)
Incorrect results or a server exit could result when a query used Batched Key Access optimization and a virtual generated column was part of the join buffer. (Bug #23169112)
If a session rolled back to a savepoint and then was killed, the statements up to the point of the savepoint could be committed. (Bug #22350047, Bug #79596)
For clients that used Connector/Python and authenticated using the sha256_password plugin, the server could handle connections incorrectly. (Bug #21421642)
Packaging Notes
mysqlcheck was missing in the MySQL Server Docker image, which prevented mysql_upgrade from running. (Bug #26400146, Bug #86968)
Bugs Fixed
InnoDB: Replication lag occurred on slave instances during large update operations on tables with many partitions. (Bug #25687813, Bug #85352)
InnoDB: A failure occurred during an end range comparison. (Bug #25669686)
InnoDB: Enabling the innodb_buffer_pool_load_now setting failed in readonly mode. The event that signals the buffer pool load thread was not initialized. (Bug #25586766)
InnoDB: A long wait for a dictionary operation lock held by a full-text search synchronization operation caused a server exit. (Bug #24938374)
Partitioning: Queries involving NULL were not always handled correctly on tables that were partitioned by LIST. (Bug #76418, Bug #20748521)
References: See also: Bug #86255, Bug #26034430.
Replication: When replicating a partitioned table with an index, on a replication slave where HASH_SCAN was specified as part of theslave_rows_search_algorithms setting, the slave I/O thread sometimes stopped with an error HA_ERR_KEY_NOT_FOUND. (Bug #26137159)
Replication: Replication clients no longer enable LOCAL capability for LOAD DATA statements, because they do not use LOAD DATA LOCAL statements. (Bug #24763131)
Replication: In case of a failure while creating multiple slave applier worker threads, some threads would be left orphaned and their resources would not be collected. Thanks to Laurynas Biveinis for his contribution to fixing this bug. (Bug #24679056, Bug #82980)
MSI packages for Windows failed to detect when Microsoft Visual C++ 2010 Redistributable Package was installed. (Bug #26501092, Bug #87139)
yaSSL could incorrectly perform TLS cipher negotiation. (Bug #26482173)
The server failed to check the maximum path length for partition names. (Bug #26390632)
Identifiers containing a mixture of backslashes and backticks could be parsed incorrectly. (Bug #26372491)
Source packages for Debian platforms contained prebuilt debug binaries, causing build failures on any architectures other than the one on which those binaries were built. (Bug #26186911)
Uninstalling the daemon_memcached plugin caused a serious error. (Bug #25909540)
Selecting from a view could yield different results with materialization enabled versus materialization disabled. (Bug #25782811, Bug #85622)
The Performance Schema failed to check the maximum host length for client connections. (Bug #25510805)
A server error occurred when a full text search result exceeded the innodb_ft_result_cache_limit setting. The patch for this bug also backports a related patch (Bug #21140111). (Bug #25033538)
If a stored function was considered a constant by the optimizer, calling it from a subquery in a NOT IN condition in the WHERE clause could cause a server exit. (Bug #23577867)
A mysqldump memory leak was fixed. Thanks to Yura Sorokin for the patch. (Bug #23531150, Bug #81714)
If a session rolled back to a savepoint and then was killed, the statements up to the point of the savepoint could be committed. (Bug #22350047, Bug #79596)
For clients that used Connector/Python and authenticated using the sha256_password plugin, the server could handle connections incorrectly. (Bug #21421642)
NAME_CONST() can return null if incorrect arguments are given. In some cases, this was not handled and could cause a server exit. (Bug #19143243, Bug #26361149)
因為MySQL到現在都沒有「升級版本」功能!下載的安裝檔案,永遠都只能安裝一個完全空白的全新資料庫!
幸好insoler現在有三台規格、配備完全相同的Mac mini Server,所以就可以用這樣的方式來完成升級MySQL版本,事實上是重新安裝新的MySQL的動作:
1. 利用備用的Mac mini主機,先把現在運作中的MySQL資料庫完整匯出、備份,再匯入到備用的Mac mini主機,
2. 把insoler的Web Server主機的資料庫IP改成備用的Mac mini主機的IP位址。
3. 關閉正式運作的MySQL資料庫。安裝一個全新的空白MySQL Community Server 5.6.38。安裝時也只能安裝新的空白資料庫,無法直接升級目前使用的MySQL 5.6.37的資料庫!
4. 重新建立需要的管理者帳號、密碼。
5. 匯入備份的MySQL資料庫。
6. 重新啟動新版的MySQL Community Server 5.6.38資料庫。
7. 修改Web Server主機的資料庫IP,改成正式的Mac mini主機的IP位址。
8. 備用的MySQL主機也必須重複3〜6的步驟來完成從MySQL 5.6.37升級到MySQL 5.6.38的動作,這樣才能確保正式主機與備用主機的MySQL版本相同。
MySQL 5.6.37已經可以連續運作超過10天都沒有發生「嘗試失敗」與「中斷連線」的問題。
連線 | # | ø 每小時 | % |
---|---|---|---|
最大同時連線數 | 5 | --- | --- |
嘗試失敗 | 0 | 0 | 0% |
中斷連線 | 0 | 0 | 0% |
總計 | 3,353 | 3,361.4 | 100.00% |
而且每天都會定時自動執行mysqldump來備份整個MySQL資料庫,而且不像舊版常常會產生許多「中斷連線」的錯誤,一直都可以保持「嘗試失敗」與「中斷連線」都是0的情況,現在的版本,終於已經比以前好很多。
但是我相信MySQL Community Server 5.7.20仍舊還有很多問題沒有解決!因為MySQL在網站上已經有預定要發表Changes in MySQL 5.7.21 (Not yet released, General Availability),但還沒有發表時間。所以在MySQL 5.7.20絕對還有很多問題需要解決,所以我還是繼續使用舊版的MySQL 5.6.38。猜想應該可以與MySQL 5.6.37一樣穩定運作!
經過2個多星期時間觀察,很不幸的,這個版本可能修正了一些Bug但是根本沒有MySQL Community Server 5.6.37穩定!所以我又重新安裝MySQL 5.6.37,不使用MySQL 5.6.38。等到MySQL 5.6.40的時候,再測試看看。