Archive for the ‘MySQL’ Category

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:

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:

Hope this helps.


MariaDB 10.0.13 Overview and Highlights

Tuesday, August 12th, 2014

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

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:

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

Hope this helps.


MariaDB 5.5.39 Overview and Highlights

Wednesday, August 6th, 2014

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

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:

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

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.


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):

Hope this helps. :)


Troubleshooting TokuDB Corruption

Friday, August 1st, 2014

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:


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

Friday, August 1st, 2014

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/' (errno: 2, undefined symbol: bitmap_free)
MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library ''
(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 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 was from 5.5.37. In the second case, the was from 10.0.12.

Hope this helps.


Disabling Transparent Hugepages for TokuDB

Friday, August 1st, 2014

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
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag

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

Friday, August 1st, 2014

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:


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

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:

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:

Hope this helps.


MariaDB 10.1.0 Overview and Highlights

Friday, July 18th, 2014

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

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:

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

Hope this helps.


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty