How to set up and use MariaDB Connector C

I just wanted to provide quick tutorial on using MariaDB’s Connector C.

I downloaded the latest Connector C (3.0.2), running MariaDB 10.1.28, and was able to get it to work fine with a simple C program using the following commands:

1. Downloaded the Connector C .msi file (32-bit, since my VS is 32-bit), extracted, and installed, which placed it at:

C:\Program Files (x86)\MariaDB\MariaDB Connector C\lib

2. You need to add the directory that contains libmaria.dll to your $PATH Environment LIB PATH variable. In my case, it was:

Control Panel -> System -> Advanced System Settings -> Environment Variables -> Choose "LIB" from under "System variables" -> then add the Connector C lib path, like:
C:\Program Files (x86)\MariaDB\MariaDB Connector C\lib

3. Then just run the following command, where my c/c++ program name is “mysql1.c”:

cl /I "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\include" /I "C:\Program Files (x86)\mariadb-10.1.25\include\mysql" /I "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Include" /MD "C:\Program Files (x86)\MariaDB\MariaDB Connector C\lib\libmariadb.lib" mysql1.c

Note the first path is to include all normal C headers like stdio.h, the second for mysql.h, and the third is for windows.h, and the last for the Connector C .lib.

Here is the actual session output:

C:\chris\mysql1> cl /I "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\include" /I "C:\Program Files (x86)\mariadb-10.1.25\include\mysql" /I "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Include" /MD "C:\Program Files (x86)\MariaDB\MariaDB Connector C\lib\libmariadb.lib" mysql1.c

Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.40219.01 for 80x86
Copyright (C) Microsoft Corporation. All rights reserved.

mysql1.c
Microsoft (R) Incremental Linker Version 10.00.40219.01
Copyright (C) Microsoft Corporation. All rights reserved.

/out:mysql1.exe
C:\chris\mysql1\libmariadb.lib
mysql1.obj

4. If all is successful, as the above, you can invoke your newly created c/c++ program, mysql1.exe, in this case (mine just obtains the version number from the instance):

C:\chris\mysql1>mysql1

MySQL Version = 100128

For reference, here is my mysql1.c code:

#include 
#include 
#include 
 
MYSQL *conn;
int version = 1;

int main ( int argc, char *argv[] )
{
    conn = mysql_init ( NULL );
    mysql_real_connect ( conn, "localhost", "root",
            "xxxxx", "test", 3316, NULL, 0 );
	version = mysql_get_server_version( conn );
	printf("\nMySQL Version = %d\n",version);
    mysql_close ( conn );
    return 0;
}

Previous related posts, if interested:

  1. Creating a basic C/C++ Program to Interact with MySQL and MariaDB
  2. Common Errors and Resolutions for Building your own MySQL or MariaDB C/C++ Program on Windows
  3. Setting Up Connector/C and SkySQL C Connector for MySQL and MariaDB

I hope this helps.

An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 2

Okay, so you’ve read the first post on enabling MariaDB’s data at rest encryption, and now you are ready to create an encrypted table.

And just to get it out of the way for those interested, you can always check your encrypted (and non-encrypted) table stats via:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;

ENCRYPTION_SCHEME=1 means the table is encrypted and ENCRYPTION_SCHEME=0 means they are not.

But let’s get into some specific examples.

I find the following 4 tables interesting, as the first 3 essentially all create the same table, and the 4th shows how to create a non-encrypted table once you have encryption enabled.

CREATE TABLE t10 (id int) ENGINE=INNODB;
CREATE TABLE t11 (id int) ENGINE=INNODB ENCRYPTED=YES;
CREATE TABLE t12 (id int) ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=18;
CREATE TABLE t13 (id int) ENGINE=INNODB ENCRYPTED=NO;
MariaDB> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t1%';
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
| SPACE | NAME     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
|    48 | dare/t10 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
|    49 | dare/t11 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
|    50 | dare/t13 |                 0 |                  0 |               0 |                   1 |                     NULL |                         NULL |              1 |
|    51 | dare/t12 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |             18 |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+

So when configuered as above, then we see t10, t11, and t12 were all encrypted no matter whether we specified ENCRYPTED=YES in the CREATE TABLE. We also see t10 and t11 used KEY_ID #1 since we did not specify ENCRYPTION_KEY_ID in the CREATE TABLE, whereas t12 did use KEY_ID #18 since we specified ENCRYPTION_KEY_ID=18 in the CREATE TABLE.

We see that t13 was not encrypted, since we did specify ENCRYPTED=NO in the CREATE TABLE. This is possible when you use innodb_file_per_table, the default.

If we attempt to create a table specifying ENCRYPTION_KEY_ID to a value that is not in the keys.txt file, then you’ll see an error like the following:

MariaDB> CREATE TABLE t15 (id int) ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=17;
ERROR 1005 (HY000): Can't create table `dare`.`t15` (errno: 140 "Wrong create options")

You will see the same if you try to ALTER an existing table from one ENCRYPTION_KEY_ID to another that does not exist.

Examples with above settings:

Let’s encrypt a non-encrypted table (t13) to be encrypted:

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 0 |
+----------+-------------------+

MariaDB> ALTER TABLE t13 ENCRYPTED=YES;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 1 |
+----------+-------------------+

Now let’s perform the reverse, un-encrpyt an encrypted table:

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 1 |
+----------+-------------------+

MariaDB> ALTER TABLE t13 ENCRYPTED=NO;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 0 |
+----------+-------------------+

Let’s convert an ENCRYPTION_KEY_ID:

MariaDB> SELECT NAME, CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t11';
+----------+----------------+
| NAME     | CURRENT_KEY_ID |
+----------+----------------+
| dare/t11 |              1 |
+----------+----------------+

MariaDB [dare]> ALTER TABLE t11 ENCRYPTION_KEY_ID=18;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t11';
+----------+----------------+
| NAME     | CURRENT_KEY_ID |
+----------+----------------+
| dare/t11 |             18 |
+----------+----------------+

Now change innodb-encrypt-tables=FORCE:

With this setting, if you attempt to create a table with ENCRYPTED=NO, then the command will fail:

MariaDB [dare]> CREATE TABLE t23 (id int) ENGINE=INNODB ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `dare`.`t23` (errno: 140 "Wrong create options")

Similarly, if you attempt to change an encrypted table to a non-encrypted table with FORCE in effect, you’ll see an error like the below:

MariaDB> CREATE TABLE t20 (id int) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

MariaDB> ALTER TABLE t20 ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `dare`.`#sql-29b4_2` (errno: 140 "Wrong create options")

Now revert the FORCE back to 1 for innodb-encrypt-tables, and drop innodb-encryption-threads from 4 to 0 because of high CPU usage issue – if you need to avoid it (and also revert innodb-encrypt-tables back to 1 instead of FORCE)

MariaDB> CREATE TABLE t30 (id int) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

MariaDB> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t3%';
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
| SPACE | NAME     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
|    63 | dare/t30 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+

I did this test since it could be necessary that to set innodb-encryption-threads=0 if the high CPU utilzation issue requires you to do so, and you do not need the background threads to perform key rotation (discussed more on the manual page listed above).

Also, I did this because I’ve heard that if you set innodb-encryption-threads=0, then newly created tables where you do not explicitly set ENCRYPTED=YES will not be encrypted. I do not see that behavior, as you can see above that the table is encrypted. So I do want to dispell this notion whilst I’m at it.

Misc:

I did notice that one you set an ENCRYPTION_KEY_ID for a table, it remains with that CREATE TABLE output forever, even if you un-encrypt the table. Even an null ALTER TABLE does not remove it. It is harmless, but soemthing to be aware of:

MariaDB> ALTER TABLE t3 ENCRYPTED=NO;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning |  140 | InnoDB: Ignored ENCRYPTION_KEY_ID 18 when encryption is disabled |
+---------+------+------------------------------------------------------------------+

MariaDB> ALTER TABLE t3 ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning |  140 | InnoDB: Ignored ENCRYPTION_KEY_ID 18 when encryption is disabled |
+---------+------+------------------------------------------------------------------+

MariaDB> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encryption_key_id`=18 `encrypted`=NO

Lastly, I should mention that if you want to export a certain table to another instance, if it is encrypted, it will not work as-is. You will need to un-encrypt the table first, then perform the discard/import tablespace on the destination server, and then re-encrypt the table on the source server.

I hope this has helped covered the basics and a number of use cases to help you get your data at rest encrypted.

An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 1

Encryption is becoming more and more prevalent and increasingly necessary in today’s world, so I wanted to provide a good overall “getting started” article on using MariaDB’s data at rest encryption (DARE) for anyone out there interested in setting this up in their environment.

MariaDB’s data encryption at rest manual page covers a lot of the specifics, but I wanted to create a quick start guide and also note a few items that might not be immediately obvious.

And due to the number of my examples, I’m splitting this into two posts. The first will focus solely on setting up encryption so you can use it. The second will focus on using it with a number of examples and common use cases.

Also, I feel that I should mention from the outset that, currently, this data at rest encryption only applies to InnoDB/XtraDB tables and Aria tables (that are created with ROW_FORMAT=PAGE, the default), and other limitations are listed on the above page.

1. First off, you will need to create a keys.txt file. The example on the above page works fine for this, but you may want to eventually create your own:

1;770A8A65DA156D24EE2A093277530142
18;F5502320F8429037B8DAEF761B189D12F5502320F8429037B8DAEF761B189D12

Save that as keys.txt (or whatever you want) and I’ll place it in the datadir for the sake of this example.

The first number is the encryption key identifier (a 32-bit number), and the latter the actual hex-encoded encryption key (which can be 128, 192, or 256-bit). The two values are to be separated by a semi-colon. You only need the first key (i.e., #1), but you can add as many keys as you like, and use various keys for various tables if you desire. The number 1 key is also the default.

2. Add the following lines to your configuration file (these are the basics from the manual page above):

plugin-load-add=file_key_management.dll
file-key-management
file-key-management-filename = "C:/Program Files/MySQL/mariadb-10.1.21/data/keys.txt"
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

Of course if on Linux, change the “.dll” to “.so”. Or you can always load it dynamically, but when you also need to set related variables, I prefer this way.

  • The first three lines load the key_management plugin, enable file-key-management, and point to the file-key file you created.
  • The fourth is what enables encryption. You can set to 0 (off), 1 (on), or FORCE (to always force every single table to be encrypted).
  • The fifth tells the server to enable encryption for the log files. From what I understand, this is for the InnoDB log files. There is a separate option to encrypt binary logs (- -encrypt-binlog). The manual recommends to enable this if you are encrypting tables since the data in the logs would not be encrypted otherwise, and thus viewable.
  • The sixth specifies how many background encryption threads to startup and use. These threads perform background key rotation and scrubbing. Note that this will add some overhead, and these background threads can cause higher CPU utilization since they are running and checking tables all of the time. More on this topic can be found here: https://jira.mariadb.org/browse/MDEV-11581

3. Restart your instance, and check that all looks normal.

Verify that the plugin loaded properly:

MariaDB> SHOW PLUGINS;
+-------------------------------+----------+--------------------+-------------------------+---------+
| Name                          | Status   | Type               | Library                 | License |
+-------------------------------+----------+--------------------+-------------------------+---------+
...
| file_key_management           | ACTIVE   | ENCRYPTION         | file_key_management.dll | GPL     |
+-------------------------------+----------+--------------------+-------------------------+---------+

And if using the above, you will see the 4 encryption threads start up in the error log:

2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #1 thread id 33908 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #2 thread id 33924 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #3 thread id 38532 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #4 thread id 29060 total threads 4.

4. Now you are ready to create an encrypted table. More on this in part #2 of this blog post.

Click here to read An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 2.

Hope this helps.

Treating NULLs as not less than zero in ORDER BY Revisited

In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.

To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.

So a query like the following returns the NULLs first (expected behavior):

SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC;
+--------+------+
| col1   | col2 |
+--------+------+
| apple  | NULL |
| apple  |    5 |
| apple  |   10 |
| banana | NULL |
| banana |    5 |
| banana |   10 |
+--------+------+

The trick I mentioned in my post is to rewrite the query like:

SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;

The difference is that we added a minus sign (-) in front of the column we want sorted and we change ASC to DESC.

Now this query returns what we’re looking for:

+--------+------+
| col1   | col2 |
+--------+------+
| apple  |    5 |
| apple  |   10 |
| apple  | NULL |
| banana |    5 |
| banana |   10 |
| banana | NULL |
+--------+------+

I could not really find this behavior documented at the time, and thus did some more digging to find out if this is intended behavior and if it should continue to work in the future (i.e., can we rely on it).

The answer is yes to both, it is intended, and it will continue to work.

Now, why does this work this way?

  • It is known that sorting in ascending (ASC) order NULLs are listed first, and if descending (DESC) they are listed last.
  • It is known that minus sign (-) preceding the column followed by DESC is the same as ASC (essentially the inverse). This is because if a > b, then -a < -b.
  • And the last bit of the puzzle is that NULL == -NULL.

So since -NULL == NULL, and we are now using DESC, the NULLs will be last. And then the remainder of the INT values will be in ASC order, since we effectively converted them to negative values and the changed order to DESC, effectively putting those INTs in ASC order.

Hope this helps clarify for anyone out there interested.

Treating NULLs as not less than zero in ORDER BY

I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).

However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.

We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.

To illustrate, a normal query just sorting in ASC order returned:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | yellow | NULL |
| apple  | red    |    5 |
| apple  | green  |   10 |
| banana | brown  | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
+--------+--------+------+

But of course we want the NULLs last in each respective group, so we first added an ISNULL() check, which does return what we need:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, ISNULL(col3) ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

However, ideally, we wanted to eliminate the ISNULL() call.

The solution was to use a little know trick (at least to me, and I also don’t see it documented) where you add a minus sign (“-“) in front of the column you want to sort and also change the ASC to DESC. The minus sign is essentially the inverse, hence the change from ASC to DESC, but it has the added benefit (in this case) of now listing NULLs last.

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, -col3 DESC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

Hope this helps.

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.

MariaDB 10.0.20 Overview and Highlights

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

https://downloads.mariadb.org/mariadb/10.0.20/

This is the eleventh GA release of MariaDB 10.0, and 21st overall release of MariaDB 10.0.

There were no major functionality changes, but there was one security fix, 6 crashing bugs fixed, some general upstream fixes, and quite a few bug fixes, so let me cover the highlights:

  • Security Fix: Client command line option –ssl-verify-server-cert (and MYSQL_OPT_SSL_VERIFY_SERVER_CERT option of the client API) when used together with –ssl will ensure that the established connection is SSL-encrypted and the MariaDB server has a valid certificate. This fixes CVE-2015-3152.
  • Crashing Bug: mysql_upgrade crashes the server with REPAIR VIEW (MDEV-8115).
  • Crashing Bug: Server crashes in intern_plugin_lock on concurrent installing semisync plugin and setting rpl_semi_sync_master_enabled (MDEV-363).
  • Crashing Bug: Server crash on updates with joins still on 10.0.18 (MDEV-8114).
  • Crashing Bug: Too large scale in DECIMAL dynamic column getter crashes mysqld (MDEV-7505).
  • Crashing Bug: Server crashes in get_server_from_table_to_cache on empty name (MDEV-8224).
  • Crashing Bug: FreeBSD-specific bug that caused a segfault on FreeBSD 10.1 x86 (MDEV-7398).
  • XtraDB upgraded to 5.6.24-72.2
  • InnoDB updated to InnoDB-5.6.25
  • Performance Schema updated to 5.6.25
  • TokuDB upgraded to 7.5.7

Given the security fix, you may want to consider upgrading if that particular CVE is of concern to you. Also, please review the crashing bugs to see if they might affect you, and upgrade if so. Also, if running TokuDB, XtraDB, InnoDB, or Performance Schema, you may also want to benefit from those fixes, as well as the new MariaDB fixes (139 in all).

You can read more about the 10.0.20 release here:

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

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

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

Hope this helps.