MySQL Shamallû

Thursday, March 24, 2016

Saturday, April 11, 2015

MySQL and SSL on Amazon RDS

Warning, this is a longer post. Feel free to skip chunks as dictated by your level of ADHD.

Amazon's relational database service recently had to rotate SSL certificates for all their RDS instances when the existing certificate expired, as they all eventually do. This got me a little curious since I remembered that MySQL can make use of SSL certificates to both secure connections and also to authenticate users.

I decided to see if I could use client-based certificates to authenticate clients in RDS so I spun up a quick RDS instance (perhaps I'll write a post about that later) and started to tinker. However, I forgot to fully consider the problem and focused instead on what I had set out as the requirement for my project - simply making client certificates work in RDS.

First, a little theory...

What is this SSL thing anyway?

In a word, encryption.
In a few more words it is encryption that includes a method of both doing the key exchange through use of asymmetric ciphers such as RSA as well as the more efficient block cipher for actual transport once a key and cipher-suite has been negotiated. Now, it's not just that (the Confidentiality by means of encryption) but also includes the HMAC (hashed message authentication code, or a digest to verify the validity of the message) for the Integrity part and also a means of Authentication by means of verifying identities using a trusted issuer of the certificates that signs them so you know they are who they say (provided you remember to check the signature and can also trust that third party).
If you want more than that there is this awesome site called Wikipedia that has many more words and some diagrams and nerdy stuff. It is not my intention to explain how awesome RSA is, cause it's cool.
Here's the high-level of how it all fits together, though.
First, there is this entity called a Certificate Authority. This is someone everybody involved can trust. For example, when you're dealing with websites you don't know this might be VeriSign or DigiCert or someone like that. Your web browser typically comes with some Certificate Authority certificates built-in so that when you browse to a site that has a certificate that is signed by or issued by one of those authorities your browser can check the signature and tell that it's a valid certificate and should be trusted.
Next, one party that wants to be trusted too will generate a key plus a certificate signing request. He or she will keep the key private and never share that, then send the certificate signing request that has all the details about him or her to the certificate authority to sign. The most important information in the request is the "Subject" line which contains details about the who, where, etc of the person / server.
The Certificate Authority (CA) then decides if it's willing to trust and certify that person or server who made the request (if they are a commercial CA like VeriSign then they usually want some money to convince them that they trust you). So, thinking over the CA whips out that private key it keeps tightly tucked away for just such occasions and signs the request. Now it has not just the Subject line but an Issuer line of information included in it. This is given back to the requester who now has a proper pair of keys, private and public (called a key and a cert here).

Now, how does MySQL use these in a normal setup?

On the server side you have the CA cert and a key/cert pair for the server itself, configured with the following parameters:
--ssl-ca=ca.pem
--ssl-cert=server-cert.pem
--ssl-key=server-key.pem

So, here is where I should point out that you need those in "PEM" format instead of some other weird thing like PKCS#7 or something that nobody cares about.

At this point you're good to go from the server side and your client can connect. Now, the MySQL client will currently ignore the fact that the server has a certificate configured unless you explicitly tell it to care (now changed in 5.7.7) so you don't have to worry about something terrible happening to all your normal connections. However, you also can now connect to the server with an SSL encrypted connection! All you need is a copy of the public CA cert from the server. So, let's try it -

# mysql -uroot --ssl-ca=ca.pem
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Ok, cool, it didn't error, but how do we know we're using SSL here? The simple method is to run the 'status' command

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.24-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 22 min 12 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.009
--------------

See that lovely orange line that's terribly hard to miss when it's highlighted? Yep, that tells us that we're using SSL and what the negotiated cipher-suite is for this connection.

But wait, there's more! In a standard MySQL setup and following that manual, we can also give one of these nifty certs to the client. Why? So not only can we verify the server is trusted but so that the server can verify that we the client are trusted, too. Just repeat the process of creating the server's key/cert pair before except call it the client key/cert. Then make a user and require one of three things from the user as part of the create user / grant statement. Now use some more flags on the command line when connecting:

GRANT SELECT ON *.* TO 'bjim'@'localhost' REQUIRE X509;

# mysql -ubjim --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.24-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

"Require X509" just means that the client should have a properly formatted cert signed by the CA specified. Kind of like having a company badge. Nobody ever looks at the details, but if you have on that looks like the company made it then you're good to go.

The other options are to require either the Issuer or Subject string to match. Remember those from a few paragraphs ago? You can do things like this:

GRANT SELECT ON *.* TO 'bjim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE ISSUER '/C=US/ST=Texas/L=Dallas/O=Banana Corp/CN=CA/emailAddress=ca@example.com';

GRANT SELECT ON *.* TO 'bjim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE SUBJECT '/C=US/ST=Texas/L=Dallas/O=Banana Stand/CN=client/emailAddress=client@example.com';

Those just match the properties of the certificate and they can be combined. Cool, right? There is something deceptive here that is simply not stated but which leads you to make a terrible assumption which is where I went wrong which we'll see later...

Why do we want to secure MySQL connections?

Well, now that's an excellent question. Basically, it's mostly to encrypt the network traffic to prevent the dangers of someone snooping on the traffic or playing the fun little Man-in-the-Middle game. Most of the time you rely on other options for "securing" the network aspects of MySQL such as keeping your databases in a private network in their own VLAN and allowing access from certain controlled, or seemingly controlled, servers such as your web / app servers or some bastion servers you might hop through for administrative or monitoring purposes with connections to that being secured by means of a VPN or SSH. So what does SSL bring to the table past that? Well, not a ton, in my opinion, just the option to have something akin to 2-factor authentication.

Enough talk, let's do this!

How to generate all the certs

We're basically going to follow the standard method of generating a CA cert, then using it to sign our own server and client certs which we will also generate. Anyone who has ever done this for a web server is already familiar with the process. Just to make sure we're doing it the MySQL way we'll follow the manual.

You fill in your own blanks when prompted
# make a CA key
openssl genrsa 2048 > ca-key.pem
# make the CA cert
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
# make the Server's key and CSR
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
# Fix some formatting (necessary for newer versions of OpenSSL)
openssl rsa -in server-key.pem -out server-key.pem
# sign the Server's CSR and generate the Cert
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# make the Client's key and CSR
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
# Fix the formatting on this one, too
openssl rsa -in client-key.pem -out client-key.pem
# Sign the client's CSR and produce the client's cert
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Verify the issuer and subject (here's what mine say):
# openssl x509 -subject -noout < client-cert.pem
subject= /C=US/ST=TX/L=Dallas/O=Hopscotch Pros/OU=Analytics/CN=client1.example.com/emailAddress=analysts@example.com
# openssl x509 -issuer -noout < client-cert.pem
issuer= /C=US/ST=TX/L=Dallas/O=examples cert authority/OU=mr it man/CN=ca.example.com/emailAddress=root@example.com

Cool, now we have a bunch of files, we plug them in the blanks, create some users to match against the subject, and poof!

So I make the user:

GRANT SELECT ON *.* TO 'jim'@'localhost' IDENTIFIED BY 'pass123' REQUIRE SUBJECT '/C=US/ST=TX/L=Dallas/O=Hopscotch Pros/OU=Analytics/CN=client1.example.com/emailAddress=analysts@example.com';

# mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Yay!

Errors!?!

Ok, ok, I got this! Now let's create the same user on my RDS instance (except with the host part as % instead of localhost) and try with the same certs:

# mysql -ujim -ppass123 -h myinstance.myid.us-west2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Wait, what? What does that even mean?

After some digging around I found that there are some issues when using newer versions of OpenSSL because by default it uses a slightly different header on the pem file. Solution is to convert from pem to pem, which is what we did in our creation steps as you may now note. So that wasn't it.

Some more digging and newer versions of OpenSSL also default to sha256 instead of sha1 (for decent reasons) as the MAC on the certificate. Ok, so I check and yes, the CA cert here has sha1 and my generated certs are using sha256. That could be causing a problem (though, honestly, you'd think each cert could be validated independently, regardless of actual format of the cert itself)

# openssl x509 -text -noout < rds-combined-ca-bundle.pem | grep sha

Signature Algorithm: sha1WithRSAEncryption

# openssl x509 -text -noout < client-cert.pem | grep sha

Signature Algorithm: sha256WithRSAEncryption

Trying again... and again...

Ok, I can fix this. Redo the whole cert generation process with the additional option passed to OpenSSL each time '-sha1'. Now my certs use the same old hashing algorithm sha1 as the CA and RDS's server certs. Let's try again:

# mysql -ujim -ppass123 -h myinstance.myid.us-west2.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Son of a caterpillar! What is going on here? More googling. Couple of bugs, nothing helpful.

At this point I try locally (see above working solution) and it works fine. Huh. Then it dawns on me - the one thing that's pretty different here between what I'm doing locally and against RDS is that I am generating the keys from the CA cert on the server (it's all done with the same CA cert locally). With the RDS instance I don't have that option since I don't have access to the CA key. Still, I should at least be able to generate a cert with a new CA and try locally. So, I create a new CA key/cert pair different from the one used in the my.cnf file and use that to create and sign a new client key/cert pair. Testing that locally, but which CA cert are you supposed to reference on the CLI? Manual says it's the one for the server's cert. Ok.

# mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

Well, that doesn't help. What if I use the new CA cert?

# mysql -ujim -ppass123 --ssl-ca=ca2.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem

ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

Alright. This isn't working. Maybe it's because I'm using OpenSSL to generate these certs and MySQL is using yaSSL (default in all community builds). The Enterprise Edition of MySQL uses OpenSSL still for some reason (Still curious about that difference, Oracle...) so I install that for testing and try it.

mysql -ujim -ppass123 --ssl-ca=ca.pem --ssl-cert=client-cert2.pem --ssl-key=client-key2.pem
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Oh, now I see why yaSSL is nice. OpenSSL based builds give even more cryptic errors.

More googling, even less useful stuff.

The "well duh" moment

What the manual forgets to mention but what is obvious when you stop to think about it as I was forced to do when I hit this wall and had to slow down and use my own head for a few minutes is this: It needs to be the same CA for both server and client certs. Yes, needs to be. The whole point is trust. MySQL allows you to specify an Issuer as a requirement which led me to initially assume that you could let any old client cert work regardless of origin. However, as you've seen above you can actually input ANYTHING into those fields when generating a CA cert then sign stuff. Regardless of actual validity you can spoof any Issuer line. You can use any self-made CA cert to sign any self-generated client certificate as well thus spoofing any Subject line, too. This becomes really kind of pointless when it comes to security. As user 'bob' on the instance I can just do a SHOW GRANTS FOR 'jim'@'%'; command and see what to spoof in the Subject line if that's what is required.

Conclusion - a single CA certificate must be used to sign both the server's certificate AND the client's certificate or it will not work.

What this means for RDS.

Since RDS comes with a certificate provided by AWS and it is not a setting that can be changed, in other words, you cannot upload your own certs) and everyone has the same CA cert it means that what you're verifying when you specify the RDS CA cert is that you're connecting to an RDS MySQL instance. Since you don't have the CA key for AWS you cannot create the matching client certificates to use. Generating your own does you no good. In the end, if authentication is part of your goal for MySQL then you must use the passwords and you should really be using network level access control by means of VPC / EC2 Security Groups.

Moral of the story

There are three real lessons I take away from this (with a bit of additional experience added in):
1 - If the RDS manual doesn't explicitly say you can do something with a MySQL feature you can probably assume that it either doesn't work or has some caveats that you'll come across eventually.
2 - Just because you can't enjoy all the security benefits of SSL doesn't mean you can't enjoy some of them. If you like nicely encrypted connections then go for it.
3 - The most important lesson - think about what you're doing and why you're doing it. Does it accomplish what you are asking? Does what you're asking to do even make sense? I fear too often that we paint ourselves into intractable corners because we skip this. I know I'm guilty of it as I've shown with my headlong attempt to do something nobody was doing without first really thinking about why.

The point of this story, and indeed this entire blog, is learning. If I had to learn it then someone else will have to learn it, too. I hope this saves someone some frustration.

That said, if someone is able to make this work with two separate CA's for server and client and disprove my conclusion I am excited to hear about it. Please let me know. I am certainly not above being wrong.

Tuesday, September 30, 2014

JSON UDF's for MySQL in Labs - Take 2

About a year ago I wrote a little on the MySQL JSON UDF's from the MySQL Labs. You can find the original article and testing steps here.

I decided with the most recent Labs release (0.3.2) that Sveta put out I would try this again and see how far we've come. For reference, the work is documented also here on Sveta's blog - https://blogs.oracle.com/svetasmirnova/

I repeated my exact same steps to create the table and load it full of the most recent zips.json data straight from the MongoDB folks. After running a handful of documents through a quick external validation via the JSONLint website (quick plug, I love this thing, especially when I have to edit IAM policies for AWS stuff) I set about loading them in again using the perl 1.5 liner I used last time.

To start with, the installation and documentation are much improved. I was impressed with just how simple it was and how flushed out that README file is becoming, two things I had previously commented on that needed some small improvement.

Now, the fun part.

Data loaded in normally, cleanly, nicely using the same steps as before.

wget -q http://media.mongodb.org/zips.json

vim json_insert.pl

#!/usr/bin/perl
while (<>) {chomp; printf("INSERT INTO test.jtest (jdoc) VALUES(\'%s\');\n", $_);}

CREATE DATABASE IF NOT EXISTS test;

USE test;

CREATE TABLE `jtest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`jdoc` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

cat zips.json | ./json_insert.pl | mysql

Ok, so far so good. Now let's try the queries from last time again:

Hmm, that looks familiar... Ok, maybe we're still working with the more formal definition of JSON here and I need to quote both key and value again. I'll check with that whole 'json_valid' UDF here:

mysql> SELECT json_valid(jdoc) isValid, COUNT(*) FROM jtest GROUP BY isValid;

+---------+----------+

| isValid | COUNT(*) |

+---------+----------+

| 1 | 29353 |

+---------+----------+

1 row in set (0.42 sec)

Hmm, a few more zip codes than last time, but otherwise it claims the column contains nice, valid JSON documents. Last time to check functionality I went ahead and replaced the geographical coordinates with just the word "blank" in quotes to satisfy the requirement of the fully quoted key and value pairs, also removing any list as a value. This time, repeating the process nets me a familiar:

SELECT json_extract(jdoc, "city") city, json_extract(jdoc, "pop") pop FROM test.jtest LIMIT 10;

So, that brings us right back where we started. I haven't played too much with the other functions yet, I admit. I keep getting stuck with this one, which at least to me and for my uses is the big one (if it works, obviously json_append(), json_replace(), and json_set() will be big ones to use for me)

Playing around a little more I am finding the same thing again here. This is still promising and I still hope it works, but it is not working the way I would expect it to and it is not working in any of the other variations I have concocted so far. I think I am next going to play with the built-in PostgreSQL functionality for JSON in 9.3 and see how it compares. Stay tuned for that in the next few days, I hope.

In the meantime, Sveta - I love your work, I think I'm following the examples in the README just fine, but what am I missing here? I'd love to show this working here.

Tuesday, July 22, 2014

Copying and Pasting SQL

Lesson learned today - Do not trust auto-formatting on websites / instant messaging apps / e-mail.

When copying and pasting queries be careful about auto-formatting. Many editors want to replace an n-dash '-' with an m-dash '–' . Some (or possibly most) SQL shells interpret these differently. Often, only the first is synonymous with subtraction.

It took an embarrassing amount of time today for me to catch that one. I hope someone is spared a few minutes by my wasted time.

Thursday, May 15, 2014

Older MySQL Poems

By unpopular demand, here are some of the ones from the Percona Live 2013 presentation. I make no claims of quality:

deadlock detected

we rollback transaction two

err one two one three

- a MySQL Haiku

In the style of Edgar Allan Poe's "The Raven"...

Once upon a SQL query
While I joked with Apple's Siri
Formatting many a logical volume on my quad core
Suddenly there came an alert by email
as of some threshold starting to wail
wailing like my SMS tone
"Tis just Nagios", I muttered,
"Sending alerts unto my phone,
Only this - I might have known..."

Ah distinctly I remember
as I documented for each member
of the team just last November
in the wiki that we keep -
write and keep and nothing more...
When my query thus completed
Fourteen duplicate rows deleted
All my replicas then repeated
repeated the changes as before.
I dumped it all to a shared disk,
kept as a backup forever more.

There once was a small database program
It had InnoDB and MyISAM
One did transactions well,
and the other would crash like hell.
Between the two they used all of my RAM

- A MySQL Limerick -

Round and round the disk drive spins
but SSD sits still and grins.
It is randomly fast
for data current and past.
My database upgrade begins.

Flush local tables

Make and LVM snapshot

Backup with rsync

- A Haiku on simple backups -

Oracle is red,
IBM is blue,
I like stuff for free -
MySQL will do.

There was a dba who lived in a shoe,
He had so many tables he didn't know what to do
He optimized some and analyzed a few
then truncated them all and left for the day

And a throwback to Dr. Seuss...

Do you like MyISAM?
I do not like it, Sam-I-am.
I do not like MyISAM.

Would you use it here or there?
I would not use it here or there.
I would not use it anywhere.
I do not like MyISAM.
I do not like it, Sam-I-am.

Would you like it in an e-commerce site?
Would you like it with in the middle of the night?
I do not like it for an e-commerce site.
I do not like it in the middle of the night.
I would not use it here or there.
I would not use it anywhere.
I do not like MyISAM.
I do not like it Sam-I-am.

Would you could you for foreign keys?
Use it, use it, just use it please!
You may like it, you will see
Just convert these tables three…
Not for foreign keys, not for those tables three!
I will not use it, you let me be!

The DBA's Creed

Alright, it's time for another cheesy one, but here goes. Feel free to modify it in the comments if you have better:

This is my database. There are many like it, but this one is mine.

My database is my best friend. It is my life. I must master it as I master my life.

My database, without me, is useless. Without my database, I am useless.

I must query my database well. I must make queries respond faster than my customers who are trying to kill me. I must tune the database before he shoots me. I will.

My database and I know what COUNT(*) means. It's the JOIN's in the query, the temp tables on disk, or the IOPS used. We know they should be sargable. We will index properly...

My database is code, even as I, because it is my life. Thus, I will learn it as a brother. I will learn its weaknesses, its strength, its changelogs, its plugins, its views and its triggers.

I will keep my buffer_cache filled and ready, even and my dirty writes are flushed. We will become part of each other. We will...

Before Codd, I swear this creed. My database and I are the defenders of my company's data. We are the masters of our queries. Backups are the saviors of my life.

So be it, until there are no slow queries and all is normalized, and cached.

Friday, November 22, 2013

Data Poem Time!

(Inspired / modified from Robert Frost's "Nothing Gold Can Stay" and Elizabeth Barrett Browning's "How Do I Love Thee")

Nothing Old Can Stay (A poem about data archiving and purging)

Website's first orders are cold,

Its hardest rows to query.

The original hits were a joy;

But only until the second deploy.

Then buffer pages are not marked new,

As the data grew and grew,

Till the web traffic of today.

Nothing old can stay.

How Do I Join Thee

How do I join thee? Let me count the rows.

I join thee to the maximum join size

My join buffer will permit in bytes

For after that it is written on disk,

Though performance is less brisk.

I join thee first on the left with sighs

I join thee then on the inner sides.

I join thee not only so I can join other tables

But to sum and aggregate over thy labels.

I join thee using thy reference column

I join thee in my reports so solemn.

Select, Delete, Update, or Insert:

I shall join thee until I cause an alert.

Tuesday, September 24, 2013

New JSON UDF's for MySQL in Labs

One of the more common complaints I hear from developers about MySQL when compared to other databases like PostgreSQL or MongoDB is a lack of decent JSON support. Consequently, we usually end up storing JSON blobs in the database.

I know what you're thinking, "Why don't you extract the information and put it into a nice, normalized set of tables?" to which the response is usually "Because that takes more time and effort."

So, assuming you don't want to store your JSON in MongoDB where you can index it, query it in complex ways, and be totally web-scale what can you do? Well, for a while now there have been JSON related functions in common-schema by Shlomi Noach. There are both the 'extract_json_value' and 'json_to_xml' procedures that can be used. While these do help a little they are, fundamentally, still just brute-forcing their way through these blobs of text. There have also been a few attempts at UDF's for this over the years with the most extensive, in my opinion, being lib_mysqludf_json by Arnold Daniels. There is some pretty good documentation on this and, admittedly, I have not used it extensively.

However, as I discovered quite by chance, we now have some additional UDF's from the fine folks at MySQL up since a few days ago on labs.mysql.com. I have not heard any mention of them yet in any of the normal places (if they were mentioned at MySQL Connect this past weekend I did not hear about it). In this article I'll talk a bit more about them.

To start with you have to download and install the UDF's which is a fairly straightforward process and instructions are given in the README inside the download, though the name of the library file "libmy_json.so" inside the README does not match the name of the provided library file itself "libmy_json_udf.so" so you have to account for that when creating the functions. A small matter, sure, but odd that the meager documentation would get that part wrong. However, it's in Labs, not GA yet so not a big deal.

It should be noted before moving on that we are still storing JSON in normal MySQL types (varchar, text, blob) rather than having a new data-type for this, which, in my opinion, is fine. We basically have to do the same thing for the XML stored in there.

There are a number of functions provided:

json_valid - gives you a 1 or a 0 depending on the validity of the JSON document
json_search - Find stuff in your JSON blobs.
json_extract - Pull out individual sections / values based on keys
json_replace - just what it sounds like and, probably, the most exciting feature since you don't have to now return the whole blob to your application, do your JSON operation, then store the whole thing back again.
json_append - cool for similar reasons as above.
json_remove - again, similar to the above.
json_set - according to the documentation a sort of 'INSERT... ON DUPLICATE KEY UPDATE' function which checks and parses the JSON (but not the whole document in this version for some reason).
json_merge - What it sounds like, though I'm not sure you would use this on any sorts of writes, though maybe as a sort of aggregation method when querying the data.
json_contains_key - sort of like 'IFNULL'.

I have yet to try out all of them but a few of them are quite useful so far. Just to show you a bit of how it works let's insert some stuff and query it.

First, let's create a table, I'm using test.jtest for this:

CREATE TABLE `jtest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`jdoc` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Next, I need sample data. I'm using the zips.json samples from the fine folks at MongoDB:

wget -q http://media.mongodb.org/zips.json

This contains a lot of documents of the following format:

{"city": "ACMAR", "loc": [-86.51557, 33.584132], "pop": 6055, "state": "AL", "_id": "35004"}

Now, let's insert them (I know you can do a perl one-liner for this but I chose to write a quick 2 line script instead since I'll likely be reusing this a few times:

vim json_insert.pl
#!/usr/bin/perl
while (<>) {chomp; printf("INSERT INTO test.jtest (jdoc) VALUES(\'%s\');\n", $_);}

cat zips.json | ./json_insert.pl | mysql

We need to verify that our data made it:

SELECT COUNT(*) FROM test.jtest;
+----------+
| count(*) |
+----------+
| 29472 |
+----------+
1 row in set (0.01 sec)

Anyway, let's move on and query the data:

SELECT json_extract(jdoc, "city") city, json_extract(jdoc, "pop") pop FROM test.jtest LIMIT 10;

+------+------+
| city | pop |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+------+------+
10 rows in set (0.00 sec)

Aw... it doesn't work. Why not? It looks like valid JSON to my eye (maybe I'm just used to the MongoDB style) but it isn't for the UDF's we're using. The JSON UDF functions only operate on valid JSON documents. See:

SELECT json_valid(jdoc) isValid, COUNT(*) FROM jtest GROUP BY isValid;
+---------+----------+
| isValid | count(*) |
+---------+----------+
| 0 | 29472 |
+---------+----------+
1 row in set (0.84 sec)

Ok, so, that's disappointing. I can't just pull out of MongoDB and drop directly in there (yet). After a lot of playing around it seems the working definition of valid JSON here requires key-value pairs to have both sides properly quoted (yes, that is most correct, but it is disappointing for numbers, coordinates, etc). So how do we have fun here? We need to butcher our JSON a bit. In this case I'm taking out the coordinates for "loc" and quoting the numbers of the populations:

vim json_insert.pl
#!/usr/bin/perl
while ($line = <>) {
chomp $line ;
$line =~ s/\[.*\]/\"blank\"/g;
$line =~ s/pop\"\: (\d+)/pop\"\: \"$1\"/g;
printf("INSERT INTO test.jtest (jdoc) VALUES(\'%s\');\n", $line);
}

Now, let's reset and try again:

TRUNCATE TABLE test.jtest;

cat zips.json | ./json_insert.pl | mysql

Now let's check again:

SELECT json_valid(jdoc) isValid, COUNT(*) FROM jtest GROUP BY isValid;

+---------+----------+

| isValid | COUNT(*) |

+---------+----------+

| 1 | 29470 |

+---------+----------+

1 row in set (0.11 sec)

Woohoo! Can we do things now?

SELECT json_extract(jdoc, "city") city, json_extract(jdoc, "pop") pop FROM test.jtest LIMIT 10;

+------------+-------+

| city | pop |

+------------+-------+

| ACMAR | 6055 |

| ADAMSVILLE | 10616 |

| ADGER | 3205 |

| KEYSTONE | 14218 |

| NEW SITE | 19942 |

| ALPINE | 3062 |

| ARAB | 13650 |

| BAILEYTON | 1781 |

| BESSEMER | 40549 |

| HUEYTOWN | 39677 |

+------------+-------+

10 rows in set (0.00 sec)

We can!

Conclusion: I don't think we're quite there yet, but it is a definite start.

Thursday, August 1, 2013

Random Cool Stuff

It's been a while without activity here but I have not been entirely idle. I have spent rather a lot of time working lately (entirely too much). Sometimes I find myself reading on various topics or stumbling across interesting bits as I attempt to research answers to things. I figured I'd share a few of the interesting reads, projects, or ideas I've come across lately.

http://www.php.net/manual/en/intro.mysqlnd-ms.php - Definitely has potential, makes me excited, but needs a bit more intelligent read/write splitting abilities, still.

http://mysqldba.blogspot.com/2012/02/asyncronous-shard-queries-in-php-using.html - some bits around asynchronous reads from a sharded set of databases.

http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html - Switching from MyISAM to InnoDB on some tables I have had to field a few questions from perplexed developers around their auto-increment values. Sometimes there are massive gaps now. Why? In part they are doing a lot of 'INSERT IGNORE' queries which effectively reserve an auto-inc value (unless you are specifying it manually) in the event they can insert. You don't get those back if the result is "ignore". You can, however, influence this a bit with the 'innodb_autoinc_lock_mode' variable. In short, you should not be relying on the auto-inc column being gap free.

http://www.percona.com/doc/percona-server/5.5/reliability/crash_resistant_replication.html - This one is fun. I have not benchmarked the cost of enabling this yet but I have experienced the problems it is trying to solve. Note that MySQL 5.6 also fixes this problem and the Percona 5.6 option is replaced by the Oracle MySQL solution.

Since it doesn't come up often I sometimes have to refer folks to this pretty good explanation and solution to the many-to-many relationship problem that isn't entirely valid in a normal RDBMS - http://www.tonymarston.net/php-mysql/many-to-many.html . Good read.

I have been conned into supporting PostgreSQL as well, lately. There is a bit to get used to like permissions. Permissions are wildly different and, I dare say, not well documented in the manual or many other places I found on the wide internet. Remember to grant usage on the schema itself when needed and remember to check for expired accounts. http://stackoverflow.com/questions/6799224/pgsql-grant-every-single-right-to-a-user-on-a-schema

http://www.tokutek.com/2011/07/indexing-the-director’s-cut/ - Note really a read so much as an excellent presentation. Make some popcorn, put it on the big-screen, enjoy.

http://www.npr.org/blogs/health/2013/07/18/203298244/worlds-biggest-virus-may-have-ancient-roots - Not related at all to databases, but still crazy cool.

http://www.oracle.com/us/corporate/press/1967380 - Yeah, it's Oracle, not MySQL but hey, they are doing some really nifty things, too. Pluggable (portable) databases, data heat-maps for auto-tiering storage, compression policies, detailed heuristics for index stats, and MapReduce features. I do always wish MySQL would do index stats more intelligently. For example, just because I have three possible values for a column doesn't mean 33% of the column is value 'a'.

Did I mention I passed the first part of the Oracle OCA exam? Yep, I now know the basics of Oracle SQL. It is, in a word, very different than MySQL SQL. So are many of the principles, like aliasing rules, whether or not you should use subqueries (obviously, the optimizer is a bit different, you can nest "unlimited" numbers of subqueries in some parts of the query and up to 256 deep in the where clause if you're crazy enough). Next step the actual Administration part. There is a lot of material for someone who doesn't use it hardly ever, but soon...

In case you have a lot to delete out of a very large table (let's just assume you "sharded it" or don't care and are trying to clean up old garbage) you may notice that a simple "DELETE FROM tbl WHERE col1='value';" is a bit too slow to be done in production, particularly with InnoDB if col1 isn't a primary key. Ideally, you want to do small, incremental deletes by primary key reference if possible to avoid hosing everything in the process. Here is a nice explanation of how to do it http://mysql.rjweb.org/doc.php/deletebig

Other neat things: https://code.google.com/p/flexviews/ and https://sdm.lbl.gov/fastbit/ and http://www.arubin.org/blog/2012/07/16/mysql-pam-ldap-authentication-module-configuration/ and https://github.com/mcafee/mysql-audit

I'll leave you searching for the reference with the following table, keeping in mind that one must never attempt to normalize Lewis Carroll:

CREATE TABLE OtherThings (
Speaker varchar(100) not null default "Walrus",
Listener varchar(100) not null default "Carpenter",
Topic enum('shoes', 'ships', 'sealing wax', 'cabbages', 'kings'),
SeaIsHost boolean,
PigsHaveWings boolean
) ENGINE=BLACKHOLE;

I'll try to pick a more particular topic for the next post...

Monday, June 3, 2013

MySQL Integer Types and Sizes

Just a quick note on integer type sizing in MySQL since I am coming across some very interesting column definitions in some odd corners of my inherited database schemas.

First, a cute picture to soften everybody up:

We'll start with a question - What is the difference between a column of type INT(8) and a column of type INT(11) or even INT(64) ?

Think about it for a second… Think you know the answer? Scroll down to find out!

The max unsigned value for an INT column is 2^32 -1 regardless of what sits in the parentheses. That is 4294967295. You may notice the number of characters in that is 10 (incidentally, the same for signed).

Now, let's create a quick table and see what happens with it:

CREATE TABLE `myints` (

`a` int(4) unsigned NOT NULL,

`b` int(8) unsigned NOT NULL,

`c` int(10) unsigned NOT NULL,

`d` int(16) unsigned NOT NULL,

`e` int(32) unsigned NOT NULL,

`f` int(64) unsigned NOT NULL,

PRIMARY KEY (`a`)

)

Let's try a nice 4 digit number:

insert into myints values (1024, 1024, 1024, 1024, 1024, 1024);

Query OK, 1 row affected (0.00 sec)

Let's try another one with 8 digits:

insert into myints values (10242048, 10242048, 10242048, 10242048, 10242048, 10242048);

Query OK, 1 row affected (0.00 sec)

Let's try again with 12 digits:

insert into myints values (102420484096, 102420484096, 102420484096, 102420484096, 102420484096, 102420484096);

ERROR 1264 (22003): Out of range value for column 'a' at row 1

Aw, failure… Yes, those are bigger than the max value of a 32 bit integer. Let's back off a bit, maybe just column 'a' didn't like it since it's an int(4), after all:

insert into myints values (4294967295, 4294967295, 4294967295, 4294967295, 4294967295, 4294967295);

Query OK, 1 row affected (0.00 sec)

Max value works, let's try incrementing the 'f' column, remember we said int(64) there, and see:

insert into myints values (4294967295, 4294967295, 4294967295, 4294967295, 4294967295, 4294967296);

ERROR 1264 (22003): Out of range value for column 'f' at row 1

So, doesn't seem to limit what values you can insert. What about when you select values out?

mysql> select * from myints;

+------------+------------+------------+------------+------------+------------+

| a | b | c | d | e | f |

+------------+------------+------------+------------+------------+------------+

| 1024 | 1024 | 1024 | 1024 | 1024 | 1024 |

| 10242048 | 10242048 | 10242048 | 10242048 | 10242048 | 10242048 |

| 4294967295 | 4294967295 | 4294967295 | 4294967295 | 4294967295 | 4294967295 |

+------------+------------+------------+------------+------------+------------+

Nope. Not there either.

So, in the words of Yzma from Emperor's New Groove "Kronk! Why do we even have that lever?!?"

For one and only one particular use. Formatting when you enable the ZEROFILL option on the column. However, a note From the manual:

"If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column."

Now, a demonstration.

mysql> alter table myints modify column a int(4) unsigned zerofill not null;

mysql> alter table myints modify column b int(8) unsigned zerofill not null;

mysql> alter table myints modify column c int(10) unsigned zerofill not null;

mysql> alter table myints modify column d int(16) unsigned not null zerofill;

We'll leave 'e' and 'f' alone for now for the sake of sane formatting.

mysql> select * from myints;

+------------+------------+------------+------------------+------------+------------+

| a | b | c | d | e | f |

+------------+------------+------------+------------------+------------+------------+

| 1024 | 00001024 | 0000001024 | 0000000000001024 | 1024 | 1024 |

| 2048 | 00002048 | 0000002048 | 0000000000002048 | 2048 | 2048 |

| 10242048 | 10242048 | 0010242048 | 0000000010242048 | 10242048 | 10242048 |

| 4294967295 | 4294967295 | 4294967295 | 0000004294967295 | 4294967295 | 4294967295 |

+------------+------------+------------+------------------+------------+------------+

That's what ZEROFILL does. That's the only actual use for the number inside the parentheses with an integer type in MySQL. Without ZEROFILL it is effectively meaningless. You can either leave it off or just put it at whatever you like.

Please feel free to read more details in the documentation.

So, did you guess right? If so the next picture is for you:

Monday, April 29, 2013

Reflections on the Percona Live Conference

I think I had more fun at Percona Live this year than last year. In part, I mostly knew what to expect, but even more I was able to meet up with many folks From both last year and previous jobs. MySQL definitely hasn't gone away for them (although one company still has the database detractors who believe PostgreSQL is the best thing since Y Combinator, and I honestly do hope they make heavy use of it for at least a few apps and let me know what their ultimate conclusion is after living with it for a year or so).

I definitely enjoyed the tutorials this year and spent the day doing the Percona XtraDB Cluster training with Jay Janssen from Percona. This was the right kind of format for me and a good pace. I did forget to bring my laptop to that one and ended up building out a three node cluster using a single, remote virtual server from my iPad. Overall, harder, to be sure, but the thrill of setting it up from scratch and keeping up was fun.

Just a few more notable thoughts from the rest of the time...

The Oracle hosted MySQL Community Reception (aka, the 5.6 very post launch party) was a bit lacking in structure, bumpin tunes, free-verse database poetry, or even social interaction. However, the ice-dolphin was a pretty nice touch.

Tuesday morning saw a great keynote by Thomas Ulin of Oracle who did an excellent job of showing why Oracle is bringing a maturity and stability to MySQL which it had not previously seen. I definitely applaud what they are doing there (yes, we all have bugs we wish they'd get around to fixing, but I think that is a different story, personally). To follow-up on that he led an important Birds of a Feather session that evening on 5.6 upgrade experiences and heard many pain points, gripes, and questions. I was quite impressed that he brought a number of key engineers with him and easily took control of the situation, fielding hard questions and then turning to his guys and asking what was being done to address the particular issue, when they expected to have fixes, what the difficulties really were, and what we could all expect. While I'm sure it was less fun to be one of those engineers for that hour this was a major win for transparency and demonstrated a serious commitment to improvement. My hat really goes off to Oracle for trying hard with us as a community (yes, we beggars who use free software are also very choosy). It is also clear that they are basically just nerds like the rest of us. :) This all stood in stark contrast to the session on MariaDB 10.0 with Monty which went into some additional detail, although discovering much it as they went, about their methodology and goals. It is clear that the two camps are diverging at greater rates in most conceivable ways.

Wednesday I attended a talk by Paul Vallee entitled "Mission Critical, not Mission Impossible". This may have been one of the more inspiring talks at the conference on just how much room for improvement there is in the execution portion of IT these days. Eye opener and, hopefully, life changer. I would really like to hear that one again.

Thursday saw some great moment from the Facebook guys on how they manage to run efficiently at scale and also from Jermy Cole and Davi Arnaut on InnoDB file strucutres. A lot of hard work went into that one and it is evident that the Facebook team has done an amazing job at architecting a solid setup that most of us will only really dream about.

At any rate, these are my favorites, the items that really stuck out. Add to that the open-sourcing of the TokuDB engine and the demonstration of how easy things can / should be from Robert Hodges of Continuent and this really was a great place to spend the week.

However... As a nerd who likes his food I really have to take a quick jab at the Hyatt catering team. I do not understand what you folks were doing or thinking with those menus and the dishes served. Sometimes they were ok, mostly they elicited a desire to stoop to eating Taco Bell and if there had been one near I definitely would have done so. There is normal food there as well, I saw it for the other conferences being held in other parts of the center, just not for us. Oh well. Perhaps, I will just brown-bag it next year.

Tuesday, April 23, 2013

Linux Filesystems and MySQL

Percona Live 2013 is underway in Santa Clara, CA. I presented tonight on Linux Filesystems and MySQL.

I think, overall, it went well, though I may have underestimated the technical nature of my audience a little judging by their questions. Admittedly, it was geared toward the somewhat newer DBA / System Administrator who has been pressed into MySQL responsibilities. As a result, I chose to cover a lot of theory that I think made the presentation a bit "boring" for the first half for those folks who already have down the basics of SAS vs SATA, IO Scheduler differences, and filesystem differences. This is, of course, a tribute to the rather bright nature of most attendees at this conference. The caliber is, on average, a good bit higher than other technical conferences I've been to in the past.

As part of it, I decided to lighten the mood a bit with some bad poetry sprinkled throughout. In a few days Percona will upload the slides and I'll update this with the link.

I will do a more detailed review of the conference at the end but so far everything is good except the food the hotel is serving.

[Update] Slides for the talk can be found here .

Wednesday, April 10, 2013

Percona Live 2013 is coming soon

I will be speaking at Percona Live 2013 later this month in Santa Clara, CA. It is day 1 of the sessions in the evening and the title is "Linux Filesystems and MySQL". It has been all kinds of fun doing the research for this and I've learned more about benchmarking than I had guessed I would as I attempted to generate the numbers for it.

I hope to see a lot of people there, some of them from last year and I hope to meet quite a few more.

Ammon

Tuesday, April 9, 2013

MySQL Duplicate Table Aliases

So it turns out that MySQL will, in certain circumstances, allow for duplicate table aliases to be used, eg:

SELECT
t1.a,
t2.b
FROM
test1.t1 AS tbl
JOIN test2.t2 AS tbl
WHERE
t1.a = 5
AND t2.b = 'foo';

If you look at what the parser sees this as (using a 'show warnings;' immediately after an 'explain extended') then you will see that it actually replaces those aliases, so it kind of works. However, this is still ugly and makes me cringe a little. I would never have thought it would be allowed until I came across it in a live query yesterday.

Ultimately, it may be documented in some dark corner as a feature but I filed a bug report on it anyway.

So the rules appear to be these:
1 - The two tables must be in different databases.
2 - There may not be any duplicate column names referenced in any part of the query.

Ammon

Saturday, April 6, 2013

Shamallû

For those who have not had the privilege of pouring through ancient Babylonian texts I need to explain the title of this blog for a moment. Shumallû is the Akkadian (Babylonian and Assyrian) word for "Student" or "Pupil", typically with regards to a scribe in training. While the word is Akkadian the written form most often goes to the older Sumerian version, being LÚ.SHAB.TUR.

I choose this as my title because I am still learning MySQL. While I have been using it for years and was certified in it several years back I find that with every new setup and every new challenge I have much more to learn and there are those who are far more knowledgeable than I am. I hope this blog to be a place where I can share some of the random knowledge I have collected for weird problems, observations, or testing especially for the things I could not easily find on Google. Perhaps, others who know more can continue to instruct or correct me in comments or feedback as I go and I will continue to learn from the wealth of experience among my peers.

Also, sometimes I like to have fun with MySQL, languages, and food. There may appear some mixing of those subjects for which I make no apologies.

Ammon