Sunday, October 25, 2015

A MySQL UDF written in Go

I was wondering if it is possible to write a MySQL User Defined Function (UDF) in Go.  
So I tried and I got a very basic UDF working.
mysql> SELECT udf_fileexists_go("/etc/hosts");
| udf_fileexists_go("/etc/hosts") |
|                               1 |
1 row in set (0.00 sec)

mysql> SELECT udf_fileexists_go("/nonexistend");
| udf_fileexists_go("/nonexistend") |
|                                 0 |
1 row in set (0.00 sec)

This is nowhere near production quality, so be careful.

The code is here:

Monday, September 7, 2015

Importing the Unicode Character Database in MySQL

In Python it is easily possible to findout the name of a Unicode character and findout some properties about that character. The module which does that is called unicodedata.

An example:

>>> import unicodedata

This module uses the data as released in the UnicodeData.txt file from the website.

So if UnicodeData.txt is a 'database', then we should be able to import it into MySQL and use it!

I wrote a small Python script to automate this. The basic steps are:

  • Download UnicodeData.txt
  • Create a unicodedata.ucd table
  • Use LOAD DATA LOCAL INFILE to load the data

This isn't difficult especially because the file doesn't have the actual characters in it. It is just an ASCII file with codepoints and the data related to it.

But it would be useful to have those characters in the database. So what I did is to create those with CONVERT(UNHEX(value) USING utf32.

What that does is this:

  • UNHEX: get the number of the codepoint
  • CONVERT that number from utf32. In utf32 all characters are 4-byte/32-bit and are mapped 1-on-1 to their codepoint. This is what ucs2 does with 2-byte/16-bit, but then we can't use the full range of characters. That is why UTF-16 replaced ucs2. UTF-16 uses a variable length, just like UTF-8.

So now I can do this:

mysql> USE unicodedata
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT name FROM ucd WHERE `char`='☺';
| name               |
1 row in set (0.00 sec)

Or this:

Screenshot of DOLPHIN character

To get the data (or just a dump of the database):

I'm speaking at Percona Live Amsterdam about MySQL and Unicode. And you can also cath me the booth (#205) or at the Community Diner.

Update 1

Set your encoding to utf8mb4 in your connection properties (e.g. --default-character-set=utf8mb4) or use SET NAMES utf8mb4 to switch to the utf8mb4 character set.

On Linux you might want to install google-noto-color-emoji-fonts, gdouros-symbola-fonts and/or google-android-emoji-fonts to see the emoji characters. For other groups of characters you might need to install additional fonts specific to that script.

Emoji fonts should be present on Windows 10 and might not be present on Windows 7 and earlier. Use the unicode enable mysql option in the start menu which is installed by MySQL Installer. This is to enable unicode for cmd.exe (Powershell or cmd.exe with the correct codepage set might also work)

Thanks to Peter Laursen for testing this.

Monday, July 20, 2015

Inserting large rows in MySQL and MariaDB

As the maximum storage size for a LONGBLOB in MySQL is 4GB and the maximum max_allowed_packet size is 1GB I was wondering how it is possible to use the full LONGBLOB.

So I started testing this. I wrote a Python script with MySQL Connector/Python and used MySQL Sandbox to bring up an instance of MySQl 5.6.25.

One of the first settings I had to change was max_allowed_packet, which was expected. I set it to 1GB.

The next setting was less expected, it was innodb_log_file_size. The server enforces that the transaction has to fit in 10% of the InnoDB log files. So I had to set it to 2 files of 5G to be able to insert one record of (almost) 1GB.

So that worked for a row of a bit less that 1GB, this is because there is some overhead in the packet and the total has to fit in 1GB.

For the next step (>1GB) I switched from Python to C so I could use mysql_stmt_send_long_data() which allows you to upload data in multiple chunks.

I expected that to work, but it didn't. This is because in MySQL 5.6 and up the max_long_data_size was replaced by max_allowed_packet. But max_allowed_packet can only be set to max 1GB and max_long_data_size can be set to 4GB.

So I switched from MySQL 5.6 to MariaDB 10.1, because MariaDB still has max_long_data_size. That worked, now I could upload rows of up to (almost) 4GB.

I also noticed InnoDB will complain in the error logs about large tuples
InnoDB: Warning: tuple size very big: 1100000025

So you can insert CD ISO images in your database. For small DVD images this could work if your connector uses the COM_STMT_SEND_LONG_DATA command.

But it's best to avoid this and keep the size of rows smaller.

The scripts I used for my tests (and references to the bugs I found):

Saturday, January 10, 2015

MySQL meetup in Amsterdam on January 15

The next meetup of the MySQL User Group NL is in Amsterdam in one of the offices of

• 18:30 - 19:00: Welcome
• 19:00 - 19:30: Easy Replication Hierarchy Management with Pseudo-GTID
 (by Shlomi Noach)
• 19:30 - 20:00: Food & Discussion • 20:00 - 20:30: Entry level Sphinx Search
 (by Art van Scheppingen)
 • 20:30 - 22:00: Discussion & Drinks

For more information and to RSVP you can visit the event page of MySQL User Group NL website

Monday, January 5, 2015

Different SSL setups for MySQL

In this blog post I will describe different ways of using SSL with the MySQL database server.

What does SSL give you?

You might use MySQL replication over the internet or connect to MySQL over the internet.

Another posibility is that you connect over an enterprise network to which just too many people have access. This is especially an issue if you use an BYOD network.

SSL helps here by encrypting the network traffic to prevent against evesdropping. It also validates that you're talking to the correct server to prevent man-in-the-middle attacks.

And you can also use SSL client certificates together with an password as two factor authentication.

SSL is not the only option, you could use SSH and many MySQL GUI clients like MySQL Workbench support this. But you can't easily use SSH with a python script or mysqldump.

Things that could go wrong

Using SSL is almost always better than not using SSL at all. So there is not much you could do wrong.

But there are a few things that could go wrong:

A false sense of security

You think you are protected by using SSL, but you forgot some option somewhere and the connection falls back to a non-SSL connection. Make sure you set SSL as required so this can't happen and use Wireshark or a similar tool to inspect the traffic to see if it is really encrypted.

Not renewing certificates in time

You should have some mechanism in place to notify you that your certificate is about to expire. This could be a nagios check, a note in your calendar or an email alert from your CA. If you let your certificates expire then your clients should stop with an error.


If performance is important then you should benchmark with and without SSL to see what the impact is. You could try a different cipher and try both OpenSSL and YaSSL to see what performs best.

Tools which use traffic inspection

If you are using a tool like VividCortex, pt-query-digest based on a pcap file then you should make sure these continue to work after you've deployed SSL. This can be done by giving them your key and use a cipher which doesn't use Diffie-Hellman (DH) or let them use a different source like performance_schema and/or your slow query log. But this depends on what is supported with that application. This might also includes some load balancer setups.

How SSL with MySQL is different to SSL in your browser

The browser by default has a big list of trusted CA's. MySQL doesn't trust anything by default. So that a big difference. This is more similar to how OpenVPN uses SSL.

For both a MySQL server and a Web server you have to enable SSL. And with both it's possible to require client certificates, but that's much more common with MySQL.

There are some other minor differences like protocol support: MySQL only uses TLS v1.0. And MySQL doesn't use hostname validation by default, so your certificate might be for and might be used on the host Your browser might use OCSP, CRL's or CRLsets to verify if a certificate is still valid. MySQL is only capable of using a CRL sice MySQL 5.6.

Setup 1: Server/Client with Internal CA

This is the most basic setup and where I created mysslgen for.

You should create a key for the server in PKCS#1 format as PKCS#8 won't work. And then create a CSR (Certificate Signing Request) which can be signed by your CA. The end result is a CA certificate a server certificate and a server key. I always use PEM formatted certificates.

What you need to configure on the server:

  • SSL server certificate and key (ssl-cert and ssl-key)
  • SSL CA certificate (ssl-ca)

What you need to configure on the client:

  • SSL CA certificate (ssl-ca)

To require the use of SSL for an account you should use REQUIRE SSL with your GRANT statement.

Setup 2: Server/Client with Internal CA and client certificate

For this you need to enable SSL on the server and create a certificate for the client.

On the client you need to specify the client certificate and key with ssl-cert and ssl-key.

Now you must use REQUIRE X509 to require a valid certificate. This won't work if you use the CA for more than this one MySQL server as other people might be able to get a valid certificate.

You might use REQUIRE SUBJECT '/' to limit the certificates which can be used.

In this setup each certificate is issued by your CA this gives you a lot of control about which certificates are issued. The result is that all certificates from your CA are trusted.

Setup 3: Server/Client with Public CA

This is similar to setup 2. But a public CA will issue much more certificates and will probably also revoke some of them.

Here you should use --ssl-verify-server-cert to make sure the server has a certificate which matches the hostname. Otherwise someone might use a certificate which is valid, but just not for that hostname to create a man in the middle attack.

Also you should use REQUIRE X509 and require a SUBJECT and optionally require a ISSUER for each user.

Setup 4: Server/Client with multiple public CA's

This looks almost like what webbrowsers use.

You could either use a certificate bundle (one file with multiple certificates) and specify this file with ssl-ca.

On Ubuntu this is made available by the ca-certificates package on /etc/ssl/certs/ca-certificates.crt

Another option is to specify a ssl-capath instead of using ssl-ca. Then you could set it to /etc/ssl/certs . If you want to use a custom dir with CA's then you need to run the c_rehash utility from OpenSSL on it to generate the correct symlinks. This only works if you use OpenSSL.

Setup 5: Replication

This works by setting MASTER_SSL_* parameters in the CHANGE MASTER TO command.

Make sure to use MASTER_SSL_VERIFY_SERVER_CERT if this is possible to enable hostname verification.

The only way to be really sure that the connection is using SSL is to set SSL as required for the replication user. The output of SHOW SLAVE STATUS output will only show if SSL is allowed, not that it's in active use.

One CA per server or a centralized CA?

You could use a CA per server and then issue a client certificate for each client. But then you will need a different client certificate for each server you want to connect to. Also you would have much more CA certificates to distribute.

So you might want to use one CA instead of a per-server CA.

What to do if things go wrong

If one of the private keys (client, server or CA) get's compromised there are a few things you can do:

  1. Nothing, just wait till the certificate expires. This only works if you are using short lived certificates (e.g. certificates which are valid for 7 days). This will still leave a window in which you are vulnerable. You might have another control in place which mitigates this risk. The problem with this is that you need to restart MySQL every 7 days and you need some mechanism to distribute the certificates in an automated fashion.

  2. Re-create all certificates and keys (including the CA). This might only be feasable if you have a very small CA with just a few users and one or more servers.

  3. Use a CRL This is a Certificate Revocation list. This only works if your certificates have proper serial numbers. Also if you use multiple CA's then you must have a CRL for each and then bundle them in one file and specify that with ssl-crl or in a directory with symlinks created by c_rehash and specified with ssl-crlpath (OpenSSL only). If you use CRL's then you need to use MySQL 5.6 or newer and have some method for updating the CRL, also for all clients.

Note: You must always re-create the private key if leaked, using the old CSR (Certificate Signing Request) is not enough.

Note on client configuration

The ssl-ca parameter might be set in the [client] section of your my.cnf. However mysqlbinlog then stops working. So use loose-ssl-ca in the client section instead.

The ssl-key and ssl-cert should be in the [client] section of your ~/.my.cnf and those file should be properly protected.

It is currently not possible to add SSL settings the a 'login-path' as created by my_config_editor.

Note on ciphers

With ssl-cipher you can set a certain cipher, but with YaSSL this is quite limited. When you use OpenSSL you could use a cipher suite, which is a certain set of ciphers with some filters and an order.

Note on YaSSL, OpenSSL and status variables

It's hard to determine if your MySQL server uses OpenSSL or YaSSL. There are some methods like using ldd /path/to/mysqld. By default MySQL Community Edition uses YaSSL and MySQL Enterprise Edition uses OpenSSL.

Depending on the SSL implementation some status variables might not get updated correctly.

To make sure you're fully protected against any SSL vulnerabilities you should follow the Oracle Critical Patch Update and keep your MySQL and OpenSSL packages up to date.

Note on running a CA

This is not as easy as it seams. It is easy to get started, but doing it right is hard. Make sure you understand how to issue a CRL, what X509 extensions are and which of them you must use.

One of the many websites which tells you how to setup your own CA: How to act as your own certificate authority (CA) - jamielinux

Also many of the public CA's offer to host your private CA for you.

Books on SSL/TLS

There is a book about SSL I can recommend: Bulletproof SSL and TLS

And there is a very entertaining book about the history of cryptography: The Code Book

Thursday, January 1, 2015

Using a CRL with MySQL

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn't belong...and there is no undelete.

First: Don't panic.
Often a password is required besides a certificate to connect to the server. So someone with the certificate can't use it without the password. The certificate itself might be protected by a password, but that's really rare. Also access to MySQL and/or your account should be limited to certain IP's.

The next step is to revoke the certificate. This is possible since MySQL 5.6.3 by using a Certificate Revocation List (CRL).
A CRL is a list of the serials of the revoked certificates and signed by the CA. So this will only work if the certificates have unique serials.

To get the serial of a certificate with OpenSSL:
$ openssl x509 -in client-cert.pem -noout -text | grep 'Serial Number' 
Serial Number: 3 (0x3)

To get the serial of a certificate with GnuTLS:
$ certtool --certificate-info --infile client-cert.pem | grep 'Serial Number' 
Serial Number (hex): 03

If all certificates have the same serial, then you can't revoke them and you have to create a new CA and all other certificates.

If you do have unique serials for your certificates then you can use GnuTLS to create the CRL:

$ certtool --generate-crl --load-ca-privkey=CAkey.pem \
--load-ca-certificate=CAcert.pem --outfile=CAcrl.pem

Then you will be asked in how many days the next CRL will be issued and what the CRL number is.

With OpenSSL you can use the 'ca' command with the -gencrl option, but that requires a bit of a setup.

If you have the CRL file then you can use --ssl-crl option for the server and clients to let them know which certificates are revoked. It looks like MySQL Workbench can't be configured to use CRL's, but it should work for most other tools and connectors.

The major drawback of using a CRL is that you have to update the file for all servers and clients if you revoke a certificate.

With OpenSSL you can use openssl crl -in CAcrl.pem -noout -text to view the CRL contents and with GnuTLS you can use certtool --infile=CAcrl.pem --crl-info

If you use a public CA for your MySQL certificates then the CA can revoke the certificate, but then you still need to get the latest CRL from your CA and distribute it to all servers and clients. This is because MySQL doesn't fetch the CRL from the 'CRL Distribution Points' URL's which may be embedded in the certificates.