mysql 5.6 blob performance_changes of mysql 5.6.20-阿里云开发者社区

New option

【Rev:5936】【Rev:6045】【Rev:6049】The new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error. For backward compatibility, the default for binlog_impossible_mode is IGNORE_ERROR, meaning the server logs the error, halts logging, and continues updates to the database. Setting this variable to ABORT_SERVERmakes the server halt logging and shut down if it cannot write to the binary log

Innodb

【Rev:5958】Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

【Rev:5992】【Rev:5994】【Rev:5995】【Rev:5996】【Rev:6015】Opening a parent table that has thousands of child tables could result in a long semaphore wait condition.(Bug #18806829)

【Rev:5991】On mysqld start, specifying multiple data files using the innodb_data_file_path option would return a Space id in fsp header error after data is written to the second file

【Rev:5922】The fix for Bug#16418661 added superfluous buf_flush_list() logic to InnoDB startup code. (Bug #17798076, Bug #70899)

【Rev: 6004】【Rev:6016】【Rev:6017】【Rev:6018】Due to differences in memory ordering on different processor types, some mutex and read-write lock flags were not read consistently.(bug#47213)

Replication

【Rev:5967】【Rev:5990】A DROP TABLE statement may be divided into multiple statements before it is sent to the binary log if it contains regular (not temporary) tables and temporary tables, or if it contains temporary tables using both transactional and non-transactional storage engines. Now, when using GTIDs, DROP TABLEstatements affecting these combinations of tables are no longer allowed unless the value of the gtid_nextsystem variable is AUTOMATIC. This is because, with GTIDs enabled on the server, issuing a DROP TABLE in the cases just described while having only one GTID associated with each statement (the SQL thread does this following SET gtid_next=’uuid:number‘) causes problems when there are not enough GTIDs for assignment to all the resulting statements following the division of the original DROP TABLE.

【Rev:5934】【Rev:5935】When a slave worker thread tried to execute a statement that was too large, the resulting error caused a crash. Now in such cases, the error is truncated to fit the size of the buffer. (Bug #18563480)

【Rev:5997】When using row-based replication, updating or deleting a row on the master that did not exist on the slave led to failure of the slave when it tried to process the change. This problem occurred with InnoDB tables lacking a primary key. (Bug #18432495, Bug #72085)

【Rev:5938】Quotation marks were not always handled correctly by LOAD DATA INFILE when written into the binary log. (Bug #18207212, Bug #71603)

【Rev:5918】Beginning in MySQL 5.6.20, when a user specified AUTO_INCREMENT value falls outside of the range between the current AUTO_INCREMENT value and the sum of the current and number of rows affected values it is replicated correctly. In previous versions, an error was generated by the slave even if the user specified AUTO_INCREMENT value fell outside of the range. (Bug #17588419, Bug #70583)

【Rev: 5927】【Rev:5928】【Rev:6058】A group of threads involved in acquiring locks could deadlock when the following events occurred:

#Dump thread reconnects from slave; on master, a new dump thread tries to kill zombie dump threads; having acquired the thread’s LOCK_thd_data, it is about to acquire LOCK_log.

#Application thread executing show binary logs, having acquired LOCK_log and about to acquire LOCK_index.

#Application thread executing PURGE BINARY LOGS; having acquired LOCK_index, it is about to acquireLOCK_thread_count.

#Application thread executing SHOW PROCESSLIST (or SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST), having acquired LOCK_thread_count and about to acquire the zombie dump thread’s LOCK_thd_data.

This leads to the 4 threads deadlocking in the same order which the threads have been listed here.

【Rev:5929】Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. This was caused by the fractionalTIMESTAMP format added in MySQL 5.6.4 not being supported in earlier versions. You can now replicate aTIMESTAMP in either format correctly. (Bug #70124, Bug #17532932)

Server layer/optimizer

【Rev:6009】When a SELECT included a derived table in a join in its FROM list and the SELECT list included COUNT(DISTINCT), the COUNT() returned 1 even if the underlying result set was empty

【Rev:5972】Enabling optimizer trace could cause a server exit for queries with a subquery in a HAVING clause.

【Rev:5939】If a query had both MIN()/MAX() and aggregate_function(DISTINCT) (for example, SUM(DISTINCT)) and was executed using Loose Index Scan, the result values of MIN()/MAX() were set improperly.

【Rev:5926】For UNION statements, the rows-examined value was calculated incorrectly. This was manifest as too-large values for the ROWS_EXAMINED column of Performance Schema statement tables (such asevents_statements_current).

【Rev:6024】For a view defined on a UNION, the server could create an invalid view definition. (Bug #65388, Bug #14117018, Bug #72018, Bug #18405221)

【Rev:4635】If there was a predicate on a column referenced by MIN() or MAX() and that predicate was not present in all the disjunctions on key parts earlier in the compound index, Loose Index Scan returned an incorrect result. (Bug #71097, Bug #17909656)

【Rev:5157】【Rev:5162】【Rev:5163】【Rev:5166】【Rev:5950】【Rev:5953】【Rev:6009】With big_tables enabled, queries that used COUNT(DISTINCT) on a simple join with a constant equality condition on a non-duplicate key returned incorrect results

DDL

【Rev:6012】【Rev:6013】ALTER TABLE on a partitioned table could result in the wrong storage engine being written into the table’s .frmfile and displayed in SHOW CREATE TABLE

【Rev:5167】【Rev:6025】An assertion could be raised when creating a index on a prefix of a TINYBLOB or GEOMETRY column in an InnoDBcolumn

【Rev:6038】Deadlock could occur if three threads simultaneously performed INSTALL PLUGIN, SHOW VARIABLES, andmysql_change_user(). (Bug #71236, Bug #18008907, Bug #72870, Bug #18903155)

【Rev:5915】 To uninstall the master-side plugin, there must be no semisynchronous slaves. To uninstall the slave-side plugin, there must be no semisynchronous I/O threads running. (Bug #70391, Bug #17638477)

Innodb Full-text

【Rev: 5985】【Rev:5988】【Rev:6037】For single item full-text searches, deleted documents were included in inverse document frequency (IDF) calculations

【Rev:5961】A DELETE operation on a table with full-text search indexes raised an assertion

【Rev:5943】【Rev:5945】A regression introduced in MySQL 5.6.5 would cause full-text search index tables to be created in the system tablespace (space 0) even though innodb_file_per_table was enabled.

【Rev:4887】A race condition in fts_get_next_doc_id resulted in Duplicate FTS_DOC_ID and Cannot find index FTS_DOC_ID_INDEX in InnoDB index translation table errors (bug#70311)

【Rev:5933】For full-text queries on InnoDB tables, attempts to access deleted document IDs could lead to a server exit

Memcached Plugin

【Rev:5963】When calling the memcached flush_all command, InnoDB attempts to initialize a connection and a transaction. If the transaction is in TRX_STATE_NOT_STARTED state, InnoDB would fail to set CONN_DATA->CRSR_TRX to NULL, resulting in a serious error

【Rev:5937】The InnoDB memcached plugin would call plugin_del without acquiring the lock_plugin mutex. This bug fix also addresses a race condition in ib_cursor_delete_row

Partition table

Selecting from a table having multiple columns in its primary key and partitioned by LIST COLUMNS(R), where R was the last (rightmost) column listed in the primary key definition, returned an incorrect result.(bug#71095)