Posts Tagged ‘MySQL’

MySQL 5.7.5 Overview and Highlights

Tuesday, October 7th, 2014

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.6.21 Overview and Highlights

Friday, October 3rd, 2014

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

For this release, there was 1 “InnoDB Notes” and 1 “Functionality Added or Changed” bug fix (and 0 “Security Fix”), so not much there, but of course they should be noted:

  1. InnoDB Note: The –skip-innodb option is now deprecated 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).
  2. Functionality Added: Internally, spatial data types such as Geometry are represented as BLOB values, so when invoked with the –hex-blob option, mysqldump now displays spatial values in hex. (Bug #43544, Bug #11752369)

In addition to those, there were 43 other bug fixes (the “partitioning” bug is also an “InnoDB” bug):

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

The highlights for me were 3 InnoDB regression bugs fixed and an important InnoDB/Partitioning performance-related bug fix as well (there were also 2 InnoDB/memcached bugs, and 13 replication bugs, so if using memcached or replication, you should review the full changelogs), so check if they might have affected you:

  1. InnoDB: An ALTER TABLE … ADD FOREIGN KEY operation could cause a serious error. (Bug #19471516, Bug #73650, Bug #73761 – Public, & Percona Bug #1366073)
  2. InnoDB: During recovery, a segmentation fault would occur when marking a table as corrupt. (Bug #18942294)
  3. InnoDB: With a transaction isolation level less than or equal to READ COMMITTED, gap locks were not taken when scanning a unique secondary index to check for duplicates. As a result, duplicate check logic failed allowing duplicate key values in the unique secondary index. (Bug #19140907, Public Bug #73170) References: This bug is a regression of Bug #16133801 (Public Bug #68021).
  4. InnoDB; Partitioning: Large numbers of partitioned InnoDB tables could consume much more memory when used in MySQL 5.6 or 5.7 than the memory used by the same tables used in previous releases of the MySQL Server. (Bug #17780517, Bug #70641) References: This bug was introduced by Bug #11764622, Bug #57480.

Conclusions:

So while there were no major changes, those 3 InnoDB 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.

The last bug really only relates to partitioned InnoDB tables, so if you’re not using them, then it’s not really a big deal. However, of course, if you are and you are running an affected version, then I would consider upgrading due to the memory over-consumption.

Similarly, if running memcached, I’d recommend checking those fixes and affected versions to see if you might benefit from an upgrade also.

The full 5.6.21 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-21.html

Hope this helps. :)

 

MySQL 5.5.40 Overview and Highlights

Thursday, September 25th, 2014

MySQL 5.5.40 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://dev.mysql.com/downloads/mysql/5.5.html

This release, similar to the last 5.5 release, is mostly uneventful.

There were 0 “Functionality Added or Changed” bugs this time, and 18 bugs overall fixed.

Out of the 18 bugs, most seemed rather minor or obscure, but there are 3 I think are worth noting (all 3 are InnoDB-related, regressions, and serious if you encounter them, so best to be aware of them):

  • InnoDB: An ALTER TABLE … ADD FOREIGN KEY operation could cause a serious error. (Bug #19471516, Bug #73650)
  • InnoDB: With a transaction isolation level less than or equal to READ COMMITTED, gap locks were not taken when scanning a unique secondary index to check for duplicates. As a result, duplicate check logic failed allowing duplicate key values in the unique secondary index. (Bug #19140907) References: This bug is a regression of Bug #16133801.
  • InnoDB: During recovery, a segmentation fault would occur when marking a table as corrupt. (Bug #18942294)

Unfortunately, none of the bug reports are viewable. Even the one in public bug tracker has been marked “private”. So there is no telling what versions this affects. Bugs #73648 & 73648, the next closest viewable bugs were submitted 8/19/14 and 8/20/14, respectively. Thus it was a recently filed bug, and hopefully doesn’t affect too many previous versions. If I were running 5.5.39, using InnoDB with FKs, I think I’d upgrade. In fact, I might upgrade anyway because of the 3rd bug as well, because corruption is not that uncommon during recovery, and a segmentation fault at that time would just make a bad day worse.

For reference, the full 5.5.40 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-40.html

Hope this helps.

 

MySQL 5.6.20 Overview and Highlights

Tuesday, August 5th, 2014

MySQL 5.6.20 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 Fix”, 1 “InnoDB Important Change”, and 7 “Functionality Added or Changed” fixes, all of which should be read in case they might affect you (though for this release, these mostly appear to be minor – some [default] changes, build notes/changes, and deprecations):

  1. Security Fix: The linked OpenSSL library for the MySQL 5.6 Commercial Server has been updated from version 1.0.1g to version 1.0.1h. Versions of OpenSSL prior to and including 1.0.1g are reported to be vulnerable to CVE-2014-0224. This change does not affect the Oracle-produced MySQL Community build of MySQL Server 5.6, which uses the yaSSL library instead.
  2. InnoDB Important Change: 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. As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #69477)
  3. Replication: 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_SERVER makes the server halt logging and shut down if it cannot write to the binary log. (Bug #51014)
  4. CMake support was updated to handle CMake version 3.
  5. New Debian7, Ubuntu12.04, and Ubuntu14.04 distribution support that was introduced with 5.6.17 now comes with the platform-specific packaging source placed under the packaging directory, in the deb-precise, deb-wheezy, and deb-trusty directories.
  6. Support for LinuxThreads has been removed from the source code. LinuxThreads was superseded by NPTL in Linux 2.6. (Bug #72888)
  7. By default, mysql_install_db creates a my.cnf file in the installation base directory using a template. This may be undesireable for some deployments. To enable this behavior to be suppressed, mysql_install_db now supports a –keep-my-cnf option to preserve any existing my.cnf file and not create a new my.cnf file. (Bug #71600)
  8. The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL. Among the reasons for this: It works only for the MyISAM and ARCHIVE storage engines; it works on Unix but not Windows. Alternatives include mysqldump and MySQL Enterprise Backup.
  9. The timed_mutexes system variable has no effect and is deprecated.

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

  • 11 InnoDB
  • 09 Replication
  • 01 Partitioning
  • 31 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you:

  1. InnoDB: 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.
  2. 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.

Conclusions:

I’ve not too much to say about this release regarding whether one should upgrade or not. I will say that if you are running a *Commercial* version of MySQL 5.6, pre-5.6.20, and you use SSL, then you should upgrade. Aside from that, it more depends if you’re affected by one of these bugs or not.

The full 5.6.20 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-20.html

Hope this helps. :)

 

MySQL 5.5.39 Overview and Highlights

Friday, August 1st, 2014

MySQL 5.5.39 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.39

This release, similar to the last 5.5 release, is mostly uneventful.

There were two “Functionality Added or Changed” and 24 bugs fixed.

The “Functionality Added or Changed” changes are:

  • CMake support was updated to handle CMake version 3.
  • The timed_mutexes system variable has no effect and is deprecated.

Out of the 24 bugs, most seemed rather minor or obscure, but here are the ones I think are worth noting (crashing, security, wrong results, deadlock):

  • InnoDB: Opening a parent table that has thousands of child tables could result in a long semaphore wait condition.
  • Partitioning: 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)
  • Replication: 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 #72085)
  • Replication: A group of threads involved in acquiring locks could deadlock in a certain scenario. (Bug #69954)
  • ALTER TABLE on a partitioned table could result in the wrong storage engine being written into the table’s .frm file and displayed in SHOW CREATE TABLE.
  • MyISAM temporary files could be used to mount a code-execution attack.
  • An assertion could be raised when creating a index on a prefix of a TINYBLOB or GEOMETRY column in an InnoDB column.
  • Deadlock could occur if three threads simultaneously performed INSTALL PLUGIN, SHOW VARIABLES, and mysql_change_user(). (Bug #71236, Bug #72870)
  • MySQL did not compile with Bison 3. A workaround is to downgrade to Bison 2. (Bug #71250)

For reference, the full 5.5.39 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-39.html

Hope this helps.

 

MySQL 5.5.38 Overview and Highlights

Thursday, June 12th, 2014

MySQL 5.5.38 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.38

This release, similar to the last 5.5 release, is mostly uneventful.

Out of the mere 13 bugs, most were benign, but I thought there were two worth mentioning (just because one is a slight performance improvement for InnoDB, and the other relates to slave consistency, so always important to see if it could affect you):

  1. InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  2. Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)

For reference, the full 5.5.38 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-38.html

Hope this helps.

 

MySQL 5.6.19 Overview and Highlights

Wednesday, June 11th, 2014

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

I should actually call this post “5.6.18 and 5.6.19 Overview and Highlights”.

The 5.6 “Release Notes” Index provides an entry/changelog for 5.6.18 and says it was released 2014-04-11. However, it’s not available in the community download archives. This isn’t mentioned in the 5.6.18 changelogs, but it is in the 5.6.19 changelogs, where it says:

“There is no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL ‘Heartbleed’ issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.”

While Heartbleed did not affect the *community* versions of MySQL 5.6, it does affect the *commercial* versions of MySQL 5.6 (less than 5.6.18). This is because the *community* version of MySQL 5.6 was built using yaSSL (unaffected SSL library), whereas the *commercial* version of MySQL 5.6 was build using OpenSSL (vulnerable SSL library). So take note of this if you’re running *commercial* MySQL 5.6.

There is also an important “known limitation” of this release, which affects MySQL 5.6.10 through and including 5.6.18, which is:

“If you have InnoDB tables with full-text search indexes and you are upgrading from MySQL 5.6.10 to a MySQL version up to and including MySQL 5.6.18, the server will fail to start after the upgrade (Bug#72079). This bug is fixed in MySQL 5.6.19. As a workaround, remove full-text search indexes prior to upgrading and rebuild full-text search indexes after the upgrade is completed.”

So beware of this if you’re running 5.6.10+, using InnoDB full-text search indexes, and plan on upgrading to 5.6.11 – 5.6.18.

There was also one other bug fixed in the 5.6.18 release notes, but just a rather obscure one related to running a collated subquery on an ARCHIVE table containing an AUTO_INCREMENT column.

Now, as for 5.6.19, there are more fixes:

2 Functionality Added or Changed, which all seem quite minor:

  1. The obsolete and unmaintained charset2html utility has been removed from MySQL distributions. (Bug #71897, Bug #18352347)
  2. The mysqlbug, mysql_waitpid, and mysql_zap utilities have been deprecated and will be removed in MySQL 5.7.

And I counted 32 bug fixes (6 InnoDB, 7 Replication, and 19 misc. bugs).

I found the more important/noteworthy bug fixes to be:

  • InnoDB: After upgrading from 5.6.10 to MySQL versions up to and including MySQL 5.6.18, InnoDB would attempt to rename obsolete full-text search auxiliary tables on server startup, resulting in an assertion failure. (Bug #18634201, Bug #72079)
  • InnoDB: The fix for Bug#17699331 caused a high rate of read/write lock creation and destruction which resulted in a performance regression. (Bug #18345645, Bug #71708). This regression bug affects versions 5.6.16 – 5.6.18.
  • InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  • InnoDB: Enabling the InnoDB Table Monitor would result in a ib_table->stat_initialized assertion failure. (Bug #17039528, Bug #69641)
  • InnoDB: With innodb_max_dirty_pages_pct=0 buffer pool flushing would not be initiated until the percentage of dirty pages reached at least 1%, which would leave up to 1% of dirty pages unflushed. (Bug #13029450, Bug #62534)
  • Replication: Log rotation events could cause group_relay_log_pos to be moved forward incorrectly within a group. This meant that, when the transaction was retried, or if the SQL thread was stopped in the middle of a transaction following one or more log rotations (such that the transaction or group spanned multiple relay log files), part or all of the group was silently skipped. This issue has been addressed by correcting a problem in the logic used to avoid touching the coordinates of the SQL thread when updating the log position as part of a relay log rotation whereby it was possible to update the SQL thread’s coordinates when not using a multi-threaded slave, even in the middle of a group. (Bug #18482854)
  • Replication: In certain cases, the server mishandled triggers and stored procedures that tried to modify other tables when called by CREATE TABLE … SELECT. This is now handled correctly as an error. (Bug #18137535)
  • Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)
  • For indexes on prefixes or character string columns, index corruption could occur for assignment of binary data to the column due to improper character counting. (Bug #18359924)
  • mysqldump could create table definitions in the dump file that resulted in Too many columns errors when reloading the dump file. (Bug #17477959)
  • On Windows, REPAIR TABLE and OPTIMIZE TABLE failed for MyISAM tables with .MYD files larger than 4GB. (Bug #69683, Bug #17235179)

Conclusions:

  • If you’re running 5.6 *commercial*, and not running 5.6.18 or 5.6.19, you need to upgrade now.
  • If you’re running 5.6.10 through 5.6.18 and using InnoDB full-text indexes, and if you upgrade to a 5.6 version less than 5.6.19, you will encounter problems (though there is a work-around mentioned above).
  • The InnoDB fixes were somewhat specific, so I would only consider the regression bug that affected 5.6.16 – 5.6.18 to be an overall worrier, but read through them to be sure none of the others affect you.
  • Similarly, the couple replication fixes were also somewhat specific, so read through those to see if they could affect you.

The full changelogs can be viewed here (which has more details about all of the bugs listed above), 5.6.18 and 5.6.19 listed respectively:

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

Hope this helps. :)

 

MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7

Tuesday, June 10th, 2014

It was announced that MariaDB is now the default implementation of MySQL in the newly released Red Hat Enterprise Linux 7.

News of the Red Hat Enterprise Linux 7 can be found here.

And mention of MariaDB replacing MySQL as the default implementation is referenced in their release notes here.

The full change log entry reads:

MariaDB 5.5

  • MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7. MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL. MariaDB preserves API and ABI compatibility with MySQL and adds several new features; for example, a non-blocking client API library, the Aria and XtraDB storage engines with enhanced performance, better server status variables, and enhanced replication.

    Detailed information about MariaDB can be found at https://mariadb.com/kb/en/what-is-mariadb-55/.

 

MySQL 5.7.4 Overview and Highlights

Tuesday, April 15th, 2014

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 5.6.17 Overview and Highlights

Thursday, April 10th, 2014

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

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  1. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  2. Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES).
  3. InnoDB: MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, and ALTER TABLE … ENGINE=INNODB
  4. On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.
  5. mysql_install_db provides a more informative diagnostic message when required Perl modules are missing.
  6. The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).
  7. Incompatible Change: Old clients (older than MySQL 5.5.7) failed to parse authentication data correctly if the server was started with the –default-authentication-plugin=sha256_password option.

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

  • 21 InnoDB
  • 13 Replication
  • 01 Partitioning
  • 25 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you, and if so, then you’ll want to consider upgrading (*especially* note the 1st since it’s InnoDB and performance degradation)

  1. InnoDB: A regression introduced by Bug #14329288 (Oracle-internal) would result in a performance degradation when a compressed table does not fit into memory. (Bug #71436)
  2. Building MySQL from source on Windows using Visual Studio 2008 would fail with an identifier not found error due to a regression introduced by the patch for Bug #16249481 and Bug #18057449 (both Oracle-internal bugs).

Conclusions:

  1. If you use AES_ENCRYPT() and AES_DECRYPT(), I’d definitely investigate the changes, and plan for an upgrade (but test/check first and make and necessary changes first).
  2. Since there were 21 InnoDB bugs fixed, including 1 regression bug, I would recommend upgrading if you’re running a previous 5.6 version.
  3. Similarly, since there were 13 replication bugs, if you have a replication setup, I’d also plan to upgrade to take advantage of the fixes (and not be affected by the bugs).

The full 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-17.html

Hope this helps. :)

 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty