MariaDB 10.1.6 Overview and Highlights

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

https://downloads.mariadb.org/mariadb/10.1.6/

This is the 4th beta, and 7th overall, release of MariaDB 10.1. There were not many major changes in this release, but a few notable items, as well as many overall bugs fixed (I counted 156, down ~50% from 10.1.5).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights:

  • RESET_MASTER is extended with TO # clause which allows one to specify the number of the first binary log. (MDEV-8469)
  • Added support for binlog_row_image=minimal for compatibility with MySQL.
  • New status variables: log_bin_basename, log_bin_index and relay_log_basename.
  • New status variables: innodb_buf_dump_status_frequency for determining how often the buffer pool dump status should be printed in the logs.
  • New status variables: Com_create_temporary_table and Com_drop_temporary_table for tracking the number of CREATE/DROP TEMPORARY TABLE statements.
  • Connect updated to 1.04.0001.
  • Mroonga updated to 5.04 (earlier versions of Mroonga did not work in 10.1).

Of course it goes without saying that do not use this for production systems since it is still only beta. However, I definitely recommend installing it on a test server and testing it out. And if you happen to be running a previous version of 10.1, then you should definitely upgrade to this latest release.

You can read more about the 10.1.6 release here:

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

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

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

Hope this helps.

MySQL 5.6.26 Overview and Highlights

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

For this release, there are 3 “Functionality Added or Changed” items, 1 “Security Fix”, and 36 other bug fixes.

Out of those other 36 bugs, 13 are InnoDB, 1 Partitioning, 3 Replication, and 19 misc. (including 3 potentially crashing bug fixes, and 1 performance-related fix) Here are the ones of note:

  • Functionality Added/Changed: Replication: When using a multi-threaded slave, each worker thread has its own queue of transactions to process. In previous MySQL versions, STOP SLAVE waited for all workers to process their entire queue. This logic has been changed so that STOP SLAVE first finds the newest transaction that was committed by any worker thread. Then, it waits for all workers to complete transactions older than that. Newer transactions are not processed. The new logic allows STOP SLAVE to complete faster in case some worker queues contain multiple transactions. (Bug #75525)
  • Functionality Added/Changed: Previously, the max_digest_length system variable controlled the maximum digest length for all server functions that computed statement digests. However, whereas the Performance Schema may need to maintain many digest values, other server functions such as MySQL Enterprise Firewall need only one digest per session. Increasing the max_digest_length value has little impact on total memory requirements for those functions, but can increase Performance Schema memory requirements significantly. To enable configuring digest length separately for the Performance Schema, its digest length is now controlled by the new performance_schema_max_digest_length system variable.
  • Functionality Added/Changed: Previously, changes to the validate_password plugin dictionary file (named by the validate_password_dictionary_file system variable) while the server was running required a restart for the server to recognize the changes. Now validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a restart. In addition, two new status variables are available. validate_password_dictionary_file_last_parsed indicates when the dictionary file was last read, and validate_password_dictionary_file_words_count indicates how many words it contains. (Bug #66697)
  • Security-related: Due to the LogJam issue (https://weakdh.org/), OpenSSL has changed the Diffie-Hellman key length parameters for openssl-1.0.1n and up. OpenSSL has provided a detailed explanation at http://openssl.org/news/secadv_20150611.txt. To adopt this change in MySQL, the key length used in vio/viosslfactories.c for creating Diffie-Hellman keys has been increased from 512 to 2,048 bits. (Bug #77275)
  • InnoDB: Importing a tablespace with a full-text index resulted in an assertion when attempting to rebuild the index.
  • InnoDB: Opening a foreign key-referenced table with foreign_key_checks enabled resulted in an error when the table or database name contained special characters.
  • InnoDB: The page_zip_verify_checksum function returned false for a valid compressed page.
  • InnoDB: A failure to load a change buffer bitmap page during a concurrent delete tablespace operation caused a server exit.
  • InnoDB: After dropping a full-text search index, the hidden FTS_DOC_ID and FTS_DOC_ID_INDEX columns prevented online DDL operations. (Bug #76012)
  • InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure. (Bug #70214)
  • Partitioning: In certain cases, ALTER TABLE … REBUILD PARTITION was not handled correctly when executed on a locked table.
  • Replication: If flushing the cache to the binary log failed, for example due to a disk problem, the error was not detected by the binary log group commit logic. This could cause inconsistencies between the master and the slave. The fix uses the binlog_error_action variable to decide how to handle this situation. If binlog_error_action=ABORT_SERVER, then the server aborts after informing the client with an ER_BINLOGGING_IMPOSSIBLE error. If binlog_error_action=IGNORE_ERROR, then the error is ignored and binary logging is disabled until the server is restarted again. The same is mentioned in the error log file, and the transaction is committed inside the storage engine without being added to the binary log. (Bug #76795)
  • Replication: When using GTIDs, a multi-threaded slave which had relay_log_recovery=1 and that stopped unexpectedly could encounter a relay-log-recovery cannot be executed when the slave was stopped with an error or killed in MTS mode error upon restart. The fix ensures that the relay log recovery process checks if GTIDs are in use or not. If GTIDs are in use, the multi-threaded slave recovery process uses the GTID protocol to fill any unprocessed transactions. (Bug #73397)
  • Replication: When two slaves with the same server_uuid were configured to replicate from a single master, the I/O thread of the slaves kept reconnecting and generating new relay log files without new content. In such a situation, the master now generates an error which is sent to the slave. By receiving this error from the master, the slave I/O thread does not try to reconnect, avoiding this problem. (Bug #72581)
  • Crashing Bug: Incorrect cost calculation for the semi-join Duplicate Weedout strategy could result in a server exit.
  • Crashing Bug: For large values of max_digest_length, the Performance Schema could encounter an overflow error when computing memory requirements, resulting in a server exit.
  • Crashing Bug: GROUP BY or ORDER BY on a CHAR(0) NOT NULL column could lead to a server exit.
  • Performance-related: When choosing join order, the optimizer could incorrectly calculate the cost of a table scan and choose a table scan over a more efficient eq_ref join. (Bug #71584)

Conclusions:

So while there were no major changes, and not too many overall bug fixes, the security fix could be an issue if you run the latest RHEL/CentOS with SSL connections + a DHE SSL cipher specifed with –ssl-cipher=DHE-RSA-… Also, some of those InnoDB bugs are nasty, especially the fulltext bugs, thus if you use InnoDB’s fulltext, I’d recommend planning for an upgrade.

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

Hope this helps. 🙂

MySQL 5.5.45 Overview and Highlights

MySQL 5.5.45 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” items this time, 1 “Security Fix”, and just 9 bugs overall fixed.

Out of the 9 bugs, there were 3 InnoDB bugs, 1 security-related bug, and 1 potential crashing bug. Here are the ones worth noting:

  • InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure.
  • InnoDB: An assertion was raised when InnoDB attempted to dereference a NULL foreign key object.
  • InnoDB: On Unix-like platforms, os_file_create_simple_no_error_handling_func and os_file_create_func opened files in different modes when innodb_flush_method was set to O_DIRECT. (Bug #76627)
  • Security-related: Due to the LogJam issue (https://weakdh.org/), OpenSSL has changed the Diffie-Hellman key length parameters for openssl-1.0.1n and up. OpenSSL has provided a detailed explanation at http://openssl.org/news/secadv_20150611.txt. To adopt this change in MySQL, the key length used in vio/viosslfactories.c for creating Diffie-Hellman keys has been increased from 512 to 2,048 bits. (Bug #77275)
  • Crashing Bug: GROUP BY or ORDER BY on a CHAR(0) NOT NULL column could lead to a server exit.

I don’t think I’d call any of these urgent for all (unless you run the latest RHEL/CentOS with SSL connections + a DHE SSL cipher specifed with –ssl-cipher=DHE-RSA-…), but if running 5.5, especially if not a very recent 5.5, you should consider upgrading.

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

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

Hope this helps.

LDAP Authentication with MariaDB PAM Plugin

This is getting more and more common, so I wanted to provide the steps required to get LDAP authentication working with MariaDB PAM plugin.

Unless you’re already familiar with setting up the MariaDB PAM plugin, I’d first recommend getting this to work with a standard Linux user (steps 1-4), then once all is working fine, progress to the LDAP users (steps 5-10). (And if you do not want to test this for the Linux user account, then you may skip steps #2 and #3.)

  1. Enable plugin by running the following from the command line client:
    INSTALL SONAME 'auth_pam';

    You should see an entry like this afterward in SHOW PLUGINS:

    | pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
  2. Create the mysql user account (note it does not have a password, as it will obtain this from your Linux user, and eventually the LDAP account) and provide it with the GRANTS you want it to have:
    CREATE USER 'chris'@'localhost' IDENTIFIED VIA pam USING 'mariadb';
    GRANT ALL ON db1.* TO 'chris'@'localhost';

    Note “mariadb” is the PAM service name I’ve specified. It is good to specify this so you don’t overwrite the existing default policy (in case it is being used).

  3. Create PAM policy in “/etc/pam.d/mariadb” (ensure readable and ensure the file name, “mariadb”, matches the PAM service name you specified for your user in the above step):
    auth required pam_unix.so
    account required pam_unix.so

    (Restart MariaDB instance afterward.)

    Then, you should be able to connect via the command line with (assuming you have a Linux user ‘chris’):

    mysql -u chris -p

    This should allow you to login. Now you can move on to integrating LDAP.

  4. Verify the LDAP user exists with:
    shell> id chris

    It should return uid, gid, groups, etc.

  5. If using the MySQL client, you’ll need to enable the clear text plugin:
    [mysqld]
    pam_use_cleartext_plugin

    If you need to do this, it is recommended you begin using SSL connections, if not already.

    Also, you’ll need to reboot after this change, but wait until after step #6.

  6. We need to edit the PAM policy in “/etc/pam.d/mariadb” to:
    auth required pam_ldap.so
    account required pam_ldap.so

    (We’re basically just replacing “pam_unix.so” with “pam_ldap.so”.)

    Now, restart MariaDB.

  7. Next, you need to ensure that you have libpam-ldap/openldap installed (so you have “pam_ldap.so”, that is the key). You can install this on RedHat/CentOS with the following:
    # yum install openldap openldap-clients
  8. After that, you’ll need to configure /etc/ldap.conf. Here is a sample configuration:
    debug 10 # set debug level only during the initial configuration
    base dc=corp,dc=company_name,dc=com
    binddn cn=service_account,OU=Service Accounts,OU=US Security,DC=corp,DC=company_name,DC=com
    bindpw <password>
    timelimit 120
    idle_timelimit 3600
    uri ldaps://<LDAP URL>:<LDAP PORT>

    And if using Active Directory, you should also add these lines:

    pam_login_attribute samaccountname
    pam_member_attribute member
    nss_map_objectclass posixAccount user
    nss_map_objectclass shadowAccount user
    nss_map_attribute uid sAMAccountName
    nss_map_attribute homeDirectory unixHomeDirectory
    nss_map_attribute shadowLastChange pwdLastSet
    nss_map_objectclass posixGroup group
    nss_map_attribute uniqueMember member
    pam_login_attribute sAMAccountName
    pam_filter objectclass=User
    pam_password ad

    Note I obtained the sample ldap.conf from this Alexander Rubin post.

  9. After that, make sure you can connect to ldap and that you can search ldap with ldapsearch, which you can verify with:
    shell> telnet <ldap server> <ldap password> (this should report "connected")
    shell> ldapsearch –w <password for bind user> -x –D 'cn=USER,OU=People...' "(&(ObjectClass=user)(cn=USERNAME))"
  10. After this, things should be all set up, as the plugin is installed properly, the user has been created in MariaDB, we’ve installed pam_ldap.so, we’ve updated /etc/pam.d/mariadb to use the pam_ldap.so instead of the pam_unix.so, and created the appropriate ldap.conf. Thus you should be able to login with the following (this time assuming “chris” is an LDAP user account):
    mysql -u chris -p
  11. If you want to know more about user mapping, you should read this post by Geoff Montee as well as this post by Alexander Rubin.

    I hope this helps.