Chris on MySQL

Syndicate content
Tips and Solutions for MySQL®, MariaDB®, and other MySQL-related Topics
Updated: 57 min 46 sec ago

MariaDB 10.0.13 Overview and Highlights

August 12, 2014 - 7:22pm GMT

MariaDB 10.0.13 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.13/

This is the fourth GA release of MariaDB 10.0, and 14th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. InnoDB upgraded to 5.6.19.
  2. XtraDB upgraded to 5.6.19-67.0.
  3. TokuDB upgraded to 7.1.7.
  4. Performance_Schema upgraded to 5.6.20.
  5. Connect engine supports partitioning.
  6. Many plugins have had their maturity level increased (from beta to gamma or from gamma to stable).
  7. filesort-with-small-limit-optimization is now visible through the slow query log and a new status variable, sort_priority_queue_sorts.
  8. New variables aria_pagecache_file_hash_size and key_cache_file_hash_size for determining the number of hash buckets for open and changed files for Aria and MyISAM respectively.

You can read more about the 10.0.13 release here:

https://mariadb.com/kb/en/mariadb-10013-release-notes/

And if interested, you can review the full list of changes in 10.0.13 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10013-changelog/

Hope this helps.

 

MariaDB 5.5.39 Overview and Highlights

August 6, 2014 - 3:57pm GMT

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

https://downloads.mariadb.org/mariadb/5.5.39/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

If interested, the official MariaDB 5.5.39 release notes are here:

https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5539-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.39 can be found here:

https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5539-changelog/

Hope this helps.

 

MariaDB 5.5.39 Overview and Highlights

August 6, 2014 - 12:40am GMT

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

https://downloads.mariadb.org/mariadb/5.5.39/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

If interested, the official MariaDB 5.5.39 release notes are here:

https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5539-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.39 can be found here:

https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5539-changelog/

Hope this helps.

 

MySQL 5.6.20 Overview and Highlights

August 5, 2014 - 4:53pm GMT

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.

 

Troubleshooting TokuDB Corruption

August 2, 2014 - 3:42am GMT

I recently ran across some TokuDB table corruption, which was not easily identifiable at first, and the error log entry was not too verbose either, so I wanted to share that experience here.

Basically, TokuDB crashed, and then mysqld had problems restarting afterward. Just for reference, the error log had the following in the stack trace:

/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71c48)[0x7fb25be75c48]
/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71cbd)[0x7fb25be75cbd]
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z29toku_deserialize_bp_from_disk...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z23toku_ftnode_pf_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z30toku_cachetable_pf_pinned_pair..
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z24toku_ft_flush_some_child...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z28toku_ftnode_cleaner_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_ZN7cleaner11run_cleaner...
/usr/lib64/mysql/plugin/ha_tokudb.so(+0xcfeee)[0x7fb25bed3eee]
/lib64/libpthread.so.0(+0x79d1)[0x7fb39310b9d1]
/lib64/libc.so.6(clone+0x6d)[0x7fb391828b5d]

The crash was not really the problem here; that is another story.

But rather mysqld not starting back up was an issue.

Given this crash, and mysqld not starting back up, we needed to check every fractal tree file in the data directory.

I’m not an expert with this, but what I did, and you may find helpful too, is to iteratively check each fractal tree file with “tokuftdump –nodata tokutablename”.

If it does not return anything, then there is a problem with the table – not too scientific – but was effective in this case.

If you have just a couple files to check, this is easy enough to run per file. If there are a lot to check, then a simple shell loop with the check (and output the name at the time) can help.

Any corrupt keys should be then dropped and recreated.

That should get you back going.

On a side note, when looking more into “tokuftdump –nodata”, I did run across these 2 commands (one-liners), which allow you to check the compression format of the tables also using tokuftdump –nodata. Thus, it illustrates the use of this command for another feature, and it’s easily modifiable if you’re interested:

Pre-MariaDB 5.5.37:

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM
information_schema.tokudb_file_map WHERE dictionary = "main"
OR dictionary LIKE "key-%"'
|perl -F'\t' -ane '@out=qx(tokuftdump --nodata --rootnode /var/lib/mysql/$F[1]);
foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/)
{ print $F[0]."\t".$1."\n"; last } }'

MariaDB 5.5.37+ (b/c “dictionary” column changed to “table_dictionary_name”):

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM
information_schema.tokudb_file_map WHERE table_dictionary_name = "main"
OR table_dictionary_name LIKE "key-%"'
|perl -F'\t' -ane '@out=qx(tokuftdump --nodata --rootnode /var/lib/mysql/$F[1]);
foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/)
{ print $F[0]."\t".$1."\n"; last } }'

For reference, these are documented here.

Hope this helps.

 

Troubleshooting TokuDB ERROR 1126 – API Version Mismatch or bitmap_free

August 2, 2014 - 3:40am GMT

When setting up TokuDB, you may encounter error 1126.

I’ve seen 2 recent invocations of it, so I wanted to share them both here in case you run into this issue:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library
'/usr/lib/mysql/plugin/ha_tokudb.so' (errno: 2, undefined symbol: bitmap_free)
MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library 'ha_tokudb.so'
(errno: 8, API version for STORAGE ENGINE plugin TokuDB not
supported by this version of the server)

The latter is a bit more descriptive, but the former is fairly cryptic.

Given the latter, as you may have guessed it, if you run into either of these, you have the wrong version of ha_tokudb.so in your plugin directory – that is, it is not the correct version for your MariaDB/MySQL.

In both cases, 10.0.11 was used. In the first case, the ha_tokudb.so was from 5.5.37. In the second case, the ha_tokudb.so was from 10.0.12.

Hope this helps.

 

Disabling Transparent Hugepages for TokuDB

August 2, 2014 - 1:37am GMT

If you want to use TokuDB with MariaDB, MySQL, or Percona Server, you will need to disable support for transparent hugepages in Linux.

Fortunately, this is very easy to check, and to change.

An easy way to check is with:

cat /sys/kernel/mm/transparent_hugepage/enabled

This will return something like:

[always] madvise never

Note the word surrounded by “[]” is what this option is set to. So the above is set to “always”. To disable it, we want it set to “never”.

I’ve found the easiest way to change/set this is to add the below to your /etc/rc.local file (and then reboot your system):

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

There is another method listed here if you use grub to boot your kernel.

If you try to enable TokuDB without first changing this, then from the command line, you would see the following error:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1123 (HY000): Can't initialize function 'TokuDB';
Plugin initialization function failed.

Or, in the error log, you would see this *slightly* more clear message:

TokuDB: Transparent huge pages are enabled, according to
/sys/kernel/mm/transparent_hugepage/enabled.  TokuDB will be disabled.
To use TokuDB disable huge pages in your kernel or, for testing, set
the environment variable TOKU_HUGE_PAGES_OK to 1
140714 17:25:57 [ERROR] TokuDB:  Huge pages are enabled, disable them before continuing
140714 17:25:57 [ERROR] Plugin 'TokuDB' init function returned error.
140714 17:25:57 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

If interested in how to enable TokuDB in MariaDB, please visit here or here.

Or, if you’re interested in why TokuDB requires hugepages to be disabled, PeterZ wrote a nice post about that here.

Hope this helps.

 

Enabling TokuDB in MariaDB is a Breeze

August 2, 2014 - 1:37am GMT

TokuDB is gaining more and more popularity and many people are finding it very helpful for certain cases.

Using/enabling it in some distributions can be quite a pain, but enabling it in MariaDB is easy as 1-2-…, well, only 1-2, since that’s all there is to it!

1. Ensure you have the correct MariaDB version:

  • MariaDB 5.5.36+
  • MariaDB 10.0.9+
  • Note: Linux 64-bit systems only – specific packages include: Ubuntu, Debian, Fedora, CentOS, Red Hat
  • Note: If using the Linux tarball – it must be the version built with glibc 2.14+

2. Run this command:

INSTALL SONAME 'ha_tokudb';

or update my.cnf file with:

[mysqld]
plugin-load=ha_tokudb

There is one requirement from TokuDB, which is to have transparent hugepages disabled on Linux, so some folks may indeed have a 3rd step, if you have this enabled. Luckily, this is very easy to check and change as well.

When you have it enabled correctly, you will see it listed when invoking SHOW ENGINES:

MariaDB [(none)]> show engines;
+--------+---------+------------------------------------------------...
| Engine | Support | Comment                                        ...
+--------+---------+------------------------------------------------...
| ...
| TokuDB | YES     | Tokutek TokuDB Storage Engine with Fractal Tree...
| ...

If interested, there are more details and specifics on enabling TokuDB in MariaDB here. (For instance, if you’re running 5.5.33-5.5.35 or 10.0.5-10.0.8, you may also be able to enable TokuDB, but in a slightly different manner. But there are a few more other details as well.)

Hope this helps.

 

MySQL 5.5.39 Overview and Highlights

August 1, 2014 - 9:27pm GMT

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.

 

MariaDB 10.1.0 Overview and Highlights

July 19, 2014 - 12:37am GMT

MariaDB 10.1.0 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.0/

This is the first alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, which cover a wide variety of areas such as: Performance, InnoDB/XtraDB, WebScaleSQL, Optimizer, Security, Storage Engine functionality, & Administration Improvements.

These are 9 of the most notable changes in MariaDB 10.1.0 (but do check out the release notes and changelogs below for the full list of changes):

  1. InnoDB: Allow > 16K pages on InnoDB – InnoDB now allows page size to be configured as 16K, 32K and 64K. Note that single row size must be < 16K. This feature will allow especially more blob columns to be created.
  2. InnoDB: Force PK option – Added a new dynamic configuration variable innodb_force_primary_key default off. If option is true, create table without primary key or unique key where all keyparts are NOT NULL is not accepted. Instead an error message is printed. Variable value can be changed with set global innodb_force_primary_key = .
  3. Security: Enhance security using special compilation options – MariaDB is now compiled with security hardening options by default. It is an additional protection layer that makes new, yet unknown, security vulnerabilities more difficult to exploit.
  4. Optimizer: ANALYZE $stmt – Check how close the optimizer’s estimates about the query plan are to the reality. (more about this here also)
  5. WebScaleSQL: Added numerous improvements from WebScaleSQL (including MDEV-6314, MDEV-6315, MDEV-6323, MDEV-6325, MDEV-6329, and more).
  6. Performance: Disable Performance Schema by default.
  7. Performance: Merge 10.0.10-FusionIO to 10.1 – Atomic writes, multi-threaded flushing and page compression are available for Fusion-IO devices in 10.1.0.
  8. Storage Engine: Don’t create frm files for temporary tables – Temporary tables no longer create frm files on disk. Which means that if the temporary table is created in the MEMORY engine, it will not touch the disk at all.
  9. Administration: GUI-friendly cmake options to enable/disable plugins – MariaDB now uses PLUGIN_xxx cmake options to enable or disable plugins, not a combination of WITH_xxx, WITHOUT_xxx, WITH_PLUGINX_xxx, WITHOUT_PLUGIN_xxx, WITH_xxx_STORAGE_ENGINE, WITHOUT_xxx_STORAGE_ENGINE.

You can read more about the 10.1.0 release here:

https://mariadb.com/kb/en/mariadb-1010-release-notes/

And if interested, you can review the full list of changes in 10.1.0 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1010-changelog/

Hope this helps.

 

MariaDB 10.0.12 Overview and Highlights

June 20, 2014 - 7:06pm GMT

MariaDB 10.0.12 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.12/

This is the third GA release of MariaDB 10.0, and 13th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated Spider storage engine to version 3.2.4.
  2. Updated Connect engine.
  3. Updated pcre to version 8.35.
  4. Exists-to-In optimization is now enabled by default.
  5. performance_schema is now disabled by default.

You can read more about the 10.0.12 release here:

https://mariadb.com/kb/en/mariadb-10012-release-notes/

And if interested, you can review the full list of changes in 10.0.12 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10012-changelog/

Hope this helps.

 

MySQL 5.5.38 Overview and Highlights

June 13, 2014 - 2:28am GMT

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.

 

MariaDB 5.5.38 Overview and Highlights

June 12, 2014 - 5:11am GMT

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

https://downloads.mariadb.org/mariadb/5.5.38/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

  • Includes all bugfixes and updates from MySQL 5.5.38
  • XtraDB updated to the version from Percona Server 5.5.37-35.0
  • TokuDB updated to version 7.1.6
  • MariaDB on Power: With this release of MariaDB we’re introducing binary tarball builds created on a Power7 box running Red Hat 6. They’re listed on the downloads page in the “OS/CPU” column as “Linux ppc64″. We welcome your feedback on these new packages!

If interested, there is more about the 5.5.38 release here:

https://mariadb.com/kb/en/mariadb-5538-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.38 can be found here:

https://mariadb.com/kb/en/mariadb-5538-changelog/

Hope this helps.

 

MySQL 5.6.19 Overview and Highlights

June 11, 2014 - 5:26pm GMT

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

June 11, 2014 - 3:23am GMT

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/.

 

What exactly does log_warnings=2 log?

June 6, 2014 - 7:29pm GMT

Have you ever wondered exactly does log_warnings=2 log? Well, I have, and finally decided to check the code. (The manual used to mention setting this to 2 for diagnosing some connection-related problems, but I didn’t run into that comment in my most recent search.)

Basically, in recent 5.6 source code, we find “log_warnings > 1″ in 7 files. In 5.5 source, it is only in 5 files. Here are the 7 files in 5.6:

filesort.cc (line 460)
log_event.cc (lines 4873, 10020, 11209)
rpl_master.cc (line 912)
rpl_rli_pdb.cc (lines 1538, 1596, 1735, 2066)
rpl_slave.cc (lines 3585, 4684, 5405, 5436)
sql_acl.cc (lines 9591, 9613, 11351)
sql_connect.cc (line 791)

Long story short, the main (most common) ones are when a filesort fails (filesort.cc) or a failed login occurs (sql_acl.cc). Then there are some replication-specific instances where it logs extra info, such as master/slave/binlog info, “ignored” errors, and some summary stats for multi-threaded slave worker threads (rpl_master.cc, rpl_rli_pdb.cc, rpl_slave.cc) (All of the extra replication logging was new to 5.6, fwiw.). If it fails to close a connection that it should close, it logs some info (sql_connect.cc). And lastly, another new option in 5.6 is password expiration support. So if that is enabled and expired for the current user (and log_warnings > 1), then a note is logged to the error log as well (sql_acl.cc line 11351).

Long story long, … I debated posting the relevant code snippets, but after seeing how boring that was going to be, I’ve omitted them.

Fwiw, the max log_warnings in 5.6 on 32-bit is 4294967295 and for 64-bit is 18446744073709547520. However, at least for now anyway, it seems there is no difference between setting this to 2 or 18446744073709547520, as the only current references/actions are for “log_warnings > 1″.

In 5.7, there is some new info added to the log_warnings definition, mostly because of the new 5.7 variable log_error_verbosity. Here is what is says about “log_warnings=2 (or higher)”:

“Setting log_warnings=2 (or higher) is equivalent to log_error_verbosity=3 (errors, warnings, notes), and the server sets log_warnings to 2 if a larger value is specified.”

Hope this helps.

 

MaxScale 0.7.0 Overview and Highlights

June 6, 2014 - 6:24pm GMT

MaxScale 0.7.0 was recently released (it is the 4th alpha, with the beta on the near horizon), and is available for download here.

The release contains a number of new enhancements as well as 8 bugs fixes.

Enhancements:

  1. Galera Support: Enhanced support for Galera cluster to allow Galera to be used as a High Available Cluster with no write contention between the nodes. MaxScale will control access to a Galera Cluster such that one node is designated as the master node to which all write operations will be sent. Read operations will be sent to any of the remaining nodes that are part of the cluster. Should the currently elected master node fail MaxScale will automatically promote one of the remaining nodes to become the new master node.
  2. Multiple Slave Connections: The Read/Write Split query router has been enhanced to allow multiple slaves connections to be created. The number of slave connections is configurable via a parameter in the MaxScale configuration file. Adding multiple connections allows for better load balancing between the slaves and in a pre-requisite for providing improved fault tolerance within the Read/Write Splitter. The selection of which slave to use for a particular read operation can be controlled via options in the router configuration.
  3. Debug Interface Enhancements: A number of new list commands have been added to the debug interface to allow more concise tabular output of certain object types within the interface.
    MaxScale> help list
    Available options to the list command:
       filters    List all the filters defined within MaxScale
       listeners  List all the listeners defined within MaxScale
       modules    Show all currently loaded modules
       services   List all the services defined within MaxScale
       servers    List all the servers defined within MaxScale
       sessions   List all the active sessions within MaxScale
    MaxScale>

    Those objects that are defined in the configuration file can now be referenced by the names used in the configuration file rather than by using memory addresses. This means that services, servers, monitors and filters can all now be referenced using meaningful names provided by the user. Internal objects such as DCB’s and sessions, which are not named in the configuration file still require the use of memory addresses. Two modes of operation of the interface are now available, user mode and developer mode. The user mode restricts access to the feature that allow arbitrary structures to be examined and checks all memory address for validity before allowing access.

  4. Maintenance Mode for Servers: MaxScale now provides a maintenance mode for servers, this mode allows servers to be set such that no new connections will be opened to that server. Also, servers in maintenance mode are not monitored by MaxScale. This allows an administrator to set a server into maintenance mode when it is required to be taken out of use. The connections will then diminish over time and since no new connections are created, the administrator can remove the node from use to perform some maintenance activities.

    Nodes are placed into maintenance mode via the debug interface using the set server command.

    MaxScale> set server datanode3 maintenance

    Nodes are taken out of maintenance using the clear server command.

    MaxScale> clear server datanode3 maintenance
  5. Configurable Monitoring Interval: All monitor plugins now provide a configuration parameter that can be set to control how frequently the MaxScale monitoring is performed.
  6. Replication Lag Heartbeat Monitor: The mysqlmon monitor module now implements a replication heartbeat protocol that is used to determine the lag between updates to the master and those updates being applied to the slave. This information is then made available to routing modules and may be used to determine if a particular slave node may be used or which slave node is most up to date.
  7. Filters API: The first phase of the filter API is available as part of this release. This provides filtering for the statements from the client application to the router. Filtering for the returned results has not yet been implemented and will be available in a future version.

    Three example filters are including in the release

    1. Statement counting Filter – a simple filter that counts the number of SQL statements executed within a session. Results may be viewed via the debug interface.
    2. Query Logging Filter – a simple query logging filter that write all statements for a session into a log file for that session.
    3. Query Rewrite Filter – an example of how filters can alter the query contents. This filter allows a regular expression to be defined, along with replacement text that should be substituted for every match of that regular expression.
  8. MariaDB 10 Replication Support: The mysqlmon monitor module has been updated to support the new syntax for show all slaves status in MariaDB in order to correctly determine the master and slave state of each server being monitor. Determination of MariaDB 10 is automatically performed by the monitor and no configuration is required.
  9. API Versioning: The module interface has been enhanced to allow the API version in use to be reported, along with the status of the module and a short description of the module. The status allows for differentiation of the release status of a plugin to be identified independently of the core of MaxScale. plugins may be designated as “in development”, “alpha”, “beta” or “GA”.
    MaxScale> list modules
    Module Name     | Module Type | Version | API   | Status
    ----------------------------------------------------------------
    regexfilter     | Filter      | V1.0.0  | 1.0.0 | Alpha
    MySQLBackend    | Protocol    | V2.0.0  | 1.0.0 | Alpha
    telnetd         | Protocol    | V1.0.1  | 1.0.0 | Alpha
    MySQLClient     | Protocol    | V1.0.0  | 1.0.0 | Alpha
    mysqlmon        | Monitor     | V1.2.0  | 1.0.0 | Alpha
    readwritesplit  | Router      | V1.0.2  | 1.0.0 | Alpha
    readconnroute   | Router      | V1.0.2  | 1.0.0 | Alpha
    debugcli        | Router      | V1.1.1  | 1.0.0 | Alpha
    MaxScale>
  10. Linking: Following reported issues with incompatibilities between MaxScale and the shared library used by MySQL this version of MaxScale will be statically linked with the MariaDB 5.5 embedded library that it requires. This library is used for internal purposes only and does not result in MaxScale support for other versions of MySQL or MariaDB being affected.

Bug Fixes:

  1. mysql/galera monitors hang when backend fails (443)
  2. Read/Write Splitter closes connection without sending COM_QUIT (424)
  3. Internal thread deadlock (438)
  4. Sessions in invalid state (436)
  5. Router options for Read/Write Split module (359)
  6. Some automated tests have invalid SQL syntax (435)
  7. rwsplit.sh test script has incorrect bash syntax (431)
  8. MaxScale crashes after prolonged use (425)

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview (as well as read some details) here.

Hope this helps.

 

MaxScale 0.6.0 Overview and Highlights

June 5, 2014 - 2:34am GMT

MaxScale 0.6.0 was recently released (it is the 3rd alpha, with the beta on the near horizon), and is available for download here.

The particular release only contains 2 great additions and 2 important fixes (and note development continues with the 1.0 (GA) features, but these have not been put into this alpha version).

Important Additions:

  1. A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.
  2. New client-based features, such as a version string that provides compatibility with the major connectors, the ability to connect through the root user and the use of the Unix socket when MaxScale is co-located with a client application on the same server.

Important Bug Fixes:

  1. The new parameter “version_string” parameter has been added to service section. This allows a specific version string to be set for each service, this version string is used in the handshake from MaxScale to clients and is reported as the server version to clients.

    The version_string is optional, the default value will be taken from the embedded MariaDB library which supplies the parser to MaxScale.

  2. Statements are not routed to master if a transaction is started implicitly by setting autocommit=0. In such cases statements were previously routed as if they were not part of a transaction.

    This fix changes the behavior so that is autocommit is disabled, all statements are routed to the master and in case of session variable updates, to both master and slave.

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview here. (And read the 50-foot overview.)

Also, just to throw my 2 cents in, if you’re looking for a read-write splitting load balancer, then you should definitely check out MaxScale (though it is much more robust than that). But that is just one feature that many are already using successfully in production environments. And it will not suffer from the limitations that other read-write splitting load-balancing solutions have encountered which have prevented their wide-spread use. And as I noted, the beta is coming soon (and GA not too far behind that), with even more read-write splitting enhancements.

And if interested in some more about MaxScale, there are some great posts here (some with excellent step-by-step setup instructs):

Hope this helps.

 

MariaDB 10.0.11 Overview and Highlights

May 30, 2014 - 1:37am GMT

MariaDB 10.0.11 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.11/

This is the second GA release of MariaDB 10.0, and 12th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated TokuDB engine to version 7.1.6
  2. Updated Spider storage engine to version 3.2 (now Gamma)
  3. Updated XtraDB storage engine to version 5.6.17-65.0
  4. Updated InnoDB storage engine to version 5.6.17
  5. Updated performance_schema to version 5.6.17
  6. Updated Connect, and OQGraph engines.
  7. Online ALTER TABLE works for partitioned tables
  8. New system variable default_regex_flags. To make MariaDB RLIKE operator behave in a non-standard but backward compatible way use
    SET @@default_regex_flags='DOTALL';
  9. As per the MariaDB Deprecation Policy, this will be the last release of MariaDB 10.0 for both Ubuntu 12.10 “Quantal” and Mint 14 “Nadia”.

You can read more about the 10.0.11 release here:

https://mariadb.com/kb/en/mariadb-10011-release-notes/

And if interested, you can review the full list of changes in 10.0.11 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10011-changelog/

Hope this helps.

 

Another reason for Xtrabackup error “log block numbers mismatch”

May 7, 2014 - 2:15pm GMT

I ran into an issue the other day where Xtrabackup was not completing and threw the following error:

xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 134860907,
but got no. 176803931 from the log file.
xtrabackup: Error: xtrabackup_copy_logfile() failed.

When researching this error, I found that this error is generally caused when the following 2 conditions must are met, and this is generally when “xtrabackup cannot catch up with log writing activity on the server, so the log wraps around before xtrabackup can copy records before they are overwritten”:

  1. The log block numbers must wrap around, which only happens once per 1 GB of log writes.
  2. The wrap-around point must be between the last checkpoint and the current log tail at the time the backup starts.

Discussed here:
https://bugs.launchpad.net/percona-xtrabackup/+bug/1206309
https://bugs.launchpad.net/percona-xtrabackup/+bug/1050355

When this does occur as a result of the above (and not a bug), then it’s usually an I/O issue, either on the MySQL-side or the disk-side. One you can check with the /dev/null redirect of the actual data (i.e., if it then completes, perhaps you have some IO issue, so investigate that, and/or perform during a period of low-activity).

In this specific case, however, it was a new version of Xtrabackup, low-load, 6G InnoDB log files, …

So what was the problem?

Here the user was trying to use the “xtrabackup” binary to dump a MySQL 5.6 instance. Once switched to “xtrabackup_56″ binary, as described here, it worked as expected.

Hope this helps.