Sunday, December 30, 2012

Don't forget to check your partitions!

As it's the end of the year it might be time to check your partition definitions.

If you forget to add a new partition in time partitions with no MAXVALUE might start to throw errors:
mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200), 
    -> primary key(`event_id`,`edate`)) 
    -> partition by range( edate ) (
    -> partition p2010 VALUES LESS THAN (2011), 
    -> partition p2011 VALUES LESS THAN (2012), 
    -> partition p2012 VALUES LESS THAN (2013) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten'); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2011','twenty eleven');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2012','twenty twelve');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
ERROR 1526 (HY000): Table has no partition for value 2013

And partitions with a MAXVALUE might start to contain data of multiple years, and that might not be how it was intended.

So check your partitioning if you partition by year. It might also be a good moment to think about archiving old data.

You could use the common_schema.sql_range_partitions view to create the next partition.

mysql> select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye';
| sql_add_next_partition                                                              |
| alter table `test`.`nye` add partition (partition `p_2014` values less than (2014)) |
1 row in set, 22 warnings (0.08 sec)

mysql> call eval("select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye'");
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
Query OK, 1 row affected (0.00 sec)

O, and your sharding definitions might also contain a reference to a year.

And with the right partition definitions it's just:

mysql> SELECT TIMEDIFF(NOW(),'2013-01-01 00:00:00') AS 'Countdown';
| Countdown |
| -27:19:07 |
1 row in set (0.00 sec)

Sunday, December 9, 2012

A difficult XtraBackup restore

There was one MySQL server with a Adaptec Raid controller and 4 disks. One of the disks was having media errors and caused the whole SCSI bus to become unavailable.

This resulted in a corrupted InnoDB table.

Luckily we did have backups. A full backup and incrementals.

So to restore the backups I installed XtraBackup and MySQL 5.5 on another server.

Then the first step was to 'prepare' the backup. This worked okay for the full backup (redo only).

The second step to add the incremantals failed for the first incremental. This was easily resolved by specifying the full paths instead of relative paths.

Then the backup was fully prepared using the redo logs and undo logs.

As XtraBackup doesn't backup your my.cnf we copied the my.cnf from another server and adjusted it for this server. The my.cnf in your backup only contains everything needed for a restore, and some of those settings are Percona Server specific and will result in an error when used with MySQL.

So far everything went as expected.

Then we started MySQL with the newly restored backup by executing "service mysql start", this failed.

Then I tried to start it with a "mysqld_safe &", this worked. So I expected the init script to use wrong parameters. So I executed "bash -x /etc/init.d/mysql start" and expected the error to show, but MySQL started okay this time. Using "/etc/init.d/mysql stop" and then "/etc/init.d/mysql start" resulted in an error again.
Then I added some echo statements to the script to check which parameters it used for mysqld_safe. Then I tried to start mysqld_safe with the same parameters, this worked.

So mysqld_safe failed to start and didn't log anything to the error log when executed from the init script. It does work when called directly.

Then it hit me. The SELinux context for the MySQL files was wrong (ls -lZ).

This is something that must not be changed with chcon as that won't survive a relabel action. I changed the context for the datadir (not /var/lib/mysql in this case) and location for the socket and pid files with semanage. The I used restorecon to apply it to the files. And then MySQL started fine. This is explained in in detail in this blog post.

  • XtraBackup works great (and fast, disk I/O is quickly a bottleneck for restores)
  • Use full paths instead of relative paths for XtraBackup
  • Make sure to backup your my.cnf (or put in a CM tool like puppet)
  • If you use SELinux, don't forget to make sure the file context is correct after a restore.

Sunday, December 2, 2012

Wednesday, November 7, 2012

Scale with MySQL

Today there was the 'Scale with MySQL' event in the new Oracle building in Utrecht. There were sessions about the MySQL 5.6 RC and about MySQL Cluster. It was nice to meet so many other MySQL users. It was interesting too hear about what MySQL is used for and in which kind of environments.

Visit the MySQL Events page to see all other location for the 'Scale with MySQL' sessions.

And there are more options for meeting other MySQL users in the Netherlands: The first meetup for the MySQL User Group NL is on Friday November 16th.

Sunday, October 28, 2012

First meeting for MySQL User Group NL announced

Kinderdijk windmills 

With almost 40 member the MySQL User Group NL is already a success.

I've now scheduled the first meeting. It will be on Friday November 16th in Amsterdam. Please signup using the page.

Agenda (See for latest updates)
18:00 Welcome
18:30 First presentation
19:30 Food
20:00 Second presentation

I'm looking for speakers, so feel free to suggest a speaker or to volunteer to speak.

More resources for this user group:

Saturday, October 20, 2012

MySQL AutoTuner

After reading a blog post about MySQL Tuning scripts I thought about the possibility of a fully Automatic MySQL Tuner.

This is how it would work:
A daemon which would connect to your database server and then fetch status variables, just like mysqltuner and such. Then the daemon could decide that a parameter would need to be adjusted and then run "SET GLOBAL …" and write a /etc/mysql/ file which should be included in your my.cnf.

It should have a min/max setting for each option and some thresholds.

  • Not everyone is a DBA
  • It's could better than the default settings is most cases. Luckily many defaults are updated in 5.6.
  • You're not using, are you?
  • It could help when there are changing workloads
  • It might be sufficient for a developer environment
  • MySQL might be embedded in a 'virtual appliance' which can be deployed on may different kinds of hardware.
Why not?
  • The risk of it taking a wrong decision is too high
  • It might be better then the defaults, but still worse than a trained DBA
  • A few important settings (InnoDB Buffer Pool Size and InnoDB Logs) require a restart. I don't like anything to restart MySQL automatically (does your puppet config do automatic restarts?)
  • It might generates differences between servers with the same role. This might not be an issue when the autotuner would use P2P/multicast to communicate with other autotuners, but that makes it even more scary
  • I don't like bolted-on solutions
The conclusion is that I don't think this is a good solution. 

Thursday, September 20, 2012

Automated MySQL Master Failover

After the GitHub MySQL Failover incident a lot of blogs/people have explained that fully automated failover might not be the most optimal solution.

Fully automated failover is indeed dangerous, and should  be avoided if possible. But a complete manual failover is also dangerous. A fully automated manually triggered failover is probably a better solution.

A synchronous replication solution is also not a complete solution. A split-brain situation is a good example of a failure which could happen. Of course most clusters have all kinds of safe guard to prevent that, but unfortunately also safe guards can fail.

Every failover/cluster should be considered broken unless:
  1. You've tested the failover scripts and procedures
  2. You've tested the failover scripts and procedures under normal load
  3. You've tested the failover scripts and procedures under high load
  4. You've tested it since the last change in the setup and/or application
  5. Someone else tested the failover scripts and procedures
  6. Everyone working with the cluster/failover setup is properly trained
Just like they do on the Mythbusters show: first verify if it's true and then do everything you can to make it go BOOM!

And a failover/cluster solution is not a backup nor a complete disaster recovery solution.

Just to name a few of the failures with HA setups I've seen:
  • Master and Standy both failing with a full disk because of binlogs
  • Network hicups causing failovers to a standby with a cold cache
  • Failovers triggered by human mistakes (quite often)
  • Failovers because of bugs in the cluster software
  • Failovers due to someone removing the table which was monitored by the loadbalancer
  • Failovers due to a run-away monitoring check from the loadbalancers
  • Failovers due to failover software on a lower level taking 'smart' decisions (Veritas Cluster Server on VMware ESX with VMware HA)

Saturday, August 25, 2012

Fun with Performance Schema

I'm using a very small MariaDB instance as a datastore for my YouLess energy monitor, my own mail server (postfix, roundcube). It's a virtual machine from a commercial VPS provider.

All data fits in memory and the overhead of running with performance_schema on is not an issue.

While I was reading a blog post about performance_schema by Mark Leith I wanted to see what P_S could tell me about my own server.

This is the output from the first query:
mysql> select * from file_summary_by_event_name order by count_read desc,count_write desc limit 10;
| wait/io/file/sql/FRM                 |      25387 |         548 |                  8795589 |                    117014 |
| wait/io/file/myisam/dfile            |      20102 |         760 |                 35053140 |                     32720 |
| wait/io/file/aria/MAI                |      17392 |      208795 |                  7163684 |                   7756451 |
| wait/io/file/aria/MAD                |       8696 |           0 |                 71237632 |                         0 |
| wait/io/file/sql/binlog              |       6624 |       41964 |                 54217518 |                  58900518 |
| wait/io/file/myisam/kfile            |       2666 |         196 |                   619930 |                      6817 |
| wait/io/file/sql/file_parser         |       2435 |           8 |                  4360082 |                     14128 |
| wait/io/file/innodb/innodb_data_file |       1054 |      243380 |                 17063936 |                7016056832 |
| wait/io/file/innodb/innodb_log_file  |          6 |      220950 |                    69632 |                 799742976 |
| wait/io/file/innodb/innodb_temp_file |          6 |           6 |                  6291456 |                   6291456 |
10 rows in set (0.00 sec)

I'm using InnoDB for most (if not any) table. The data fits in memory so it shouldn't need to read a lot from disk.

The second result is for wait/io/file/myisam/dfile. I know that the tables in the 'mysql' database use MyISAM. By why is is so high in the list? Did I accidentally create some MyISAM tables?

The second query:
> select * from file_summary_by_instance order by count_read desc,count_write desc limit 5;
| FILE_NAME                                                            | EVENT_NAME                | COUNT_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_READ | SUM_NUMBER_OF_BYTES_WRITE |
| /opt/mariadb-5.5.XX-linux-x86_64/data/mysql/proc.MYD                 | wait/io/file/myisam/dfile |      19411 |           4 |                  3207486 |                      2244 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/serverX-bin.000022             | wait/io/file/sql/binlog   |       6618 |       41739 |                 54184750 |                  58809577 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/table1.frm             | wait/io/file/sql/FRM      |        630 |          19 |                    81268 |                      4971 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/view1.frm              | wait/io/file/sql/FRM      |        618 |           0 |                   375532 |                         0 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/view2.frm              | wait/io/file/sql/FRM      |        617 |           0 |                   433493 |                         0 |
5 rows in set (0.00 sec)

So the mysql.proc table is the one that's the issue. I do use some stored procedures for reporting so that's easy to explain.

As we all know Linux takes care of this by eating RAM.

So performance_schema can easily give detailed information about your server. I wouldn't have guessed that stored procedures were in the top 10 read I/O.

This shows some opportunity for improving MySQL on lightly loaded and idle servers. Is that really needed? Yes, it could make MySQL more enery efficient by not waking sleeping disks and leaving the CPU(s) in a sleep state. Drizzle has shown us that it's possible to get rid of the FRM's. If I have some time I might do some tests with Intel's PowerTOP.

I did also run the queries for write I/O, but that goes to InnoDB datafiles, logfiles, binlogs, etc. and is all how you expect them to be.

Sunday, June 24, 2012

That's not my name! A story about character sets

When computers were still using large black text oriented screens or no screens at all, a computer only knew how to store a limited set of characters. Then it was normal to store a name with the more complicated characters replaced by more basic characters. The ASCII standard was used to make communication between multiple systems (or applications) easier. Storing characters as ASCII needs little space and is quite strait forward.

Then DOS used CP850 and CP437 and so on to make it possible to use language /location specific characters.
Then ISO8859-1, ISO8859-15 and more of these character sets were defined as standard.

And now there is Unicode: UTF-8, UTF-16, UCS2, etc. which allow you to store many different kinds of characters in the same character set.

But all those character sets only work correctly if you configure all applications correctly. Many of the character sets are very similar and seem to work correctly even if one of the systems is not correctly configured. If this happens most characters will be correct except the special ones.  And my name does contain a 'special' character, the 'ë'.

Below is a picture of two letters I received in the mail recently:

So what went wrong?

This is called Mojibake.

The first one:

Query OK, 0 rows affected (0.01 sec)

| Variable_name            | Value                                              |
| character_set_client     | utf8                                               |
| character_set_connection | utf8                                               |
| character_set_database   | latin1                                             |
| character_set_filesystem | binary                                             |
| character_set_results    | utf8                                               |
| character_set_server     | latin1                                             |
| character_set_system     | utf8                                               |
| character_sets_dir       | /home/dveeden/mysql/5.5.22-mariadb/share/charsets/ |
8 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES('Daniël van Eeden');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
| v1                |
| Daniël van Eeden  |
1 row in set (0.00 sec)

mysql> set session character_set_client=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set session character_set_connection=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES('Daniël van Eeden');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
| v1                  |
| Daniël van Eeden    |
| Daniël van Eeden   |
2 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES('Daniël van Eeden');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
| v1                  |
| Daniël van Eeden    |
| Daniël van Eeden   |
| Daniël van Eeden   |
3 rows in set (0.00 sec)

So we can reproduce this issue by setting the client and connection charset to latin1.

mysql> SELECT v1,HEX(v1) FROM t1;
| v1                  | HEX(v1)                                |
| Daniël van Eeden    | 44616E69C3AB6C2076616E20456564656E     |
| Daniël van Eeden   | 44616E69C383C2AB6C2076616E20456564656E |
| Daniël van Eeden   | 44616E69C383C2AB6C2076616E20456564656E |
3 rows in set (0.00 sec)

| ë                           | ë                            |
1 row in set (0.00 sec)

The ë is stored as C383 C2AB and is rendered as two latin1 characters.

unicode C3 83 (Ã) is latin1 C3
unicode C2 AB («) is latin1 AB
C3AB = unicode ë

ëC3 ABEB89
ÃC3 83C3C7
ÙC3 99D9EB

mysql> SELECT CONVERT(X'C3AB' USING latin1);
| CONVERT(X'C3AB' USING latin1) |
| ë                            |
1 row in set (0.00 sec)

The sender of the first letter is probably storing UTF-8 from the web application in a database, but uses Latin1 when sending the letters.

The second letter renders the ë as Ù.

After some tries it seems like the sender is storing the ë as EB (latin1) and rendering it as CP850.

| CONVERT(X'EB' USING cp850) |
| Ù                          |
1 row in set (0.00 sec)

Anders Karslsson recently wrote a nice introduction about Unicode. And Ronald Bradford wrote a blogpost about UTF-8 With MySQL and LAMP.

Sunday, June 3, 2012

XA Transactions between TokuDB and InnoDB

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:
mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
| id | name   |
|  3 | demo01 |
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA COMMIT 'demo01';
Query OK, 0 rows affected (0.00 sec) 
This show a transaction with a transaction ID of 'demo01'.    

XA START Starts the transaction and puts it in ACTIVE state.  
XA END Ends the transaction and puts it IDLE state.
XA PREPARE Will prepare the transaction and puts it in PREPARED state.  
Then XA COMMIT can be used to COMMIT the transaction or XA ROLLBACK can be used to rollback the transaction.    

Demo 2: XA Transaction between 2 nodes:
mysql55-tokudb6> XA START 'tr01';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA START 'tr01';
Query OK, 0 rows affected (0.01 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)
mysql56-innodb> INSERT INTO xatest(name) VALUES('tr01');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA END 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA PREPARE 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA COMMIT 'tr01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
| id | name |
|  1 | tr01 |
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
| id | name |
|  1 | tr01 |
1 row in set (0.00 sec) 
Demo 3: XA Transaction with rollback:
mysql55-tokudb6> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA START 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)
mysql56-innodb> INSERT INTO xatest(name) VALUES('tr02');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA END 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA PREPARE 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)
mysql56-innodb> XA ROLLBACK 'tr02';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> SELECT * FROM xatest;
| id | name |
|  1 | tr01 |
1 row in set (0.00 sec)

mysql56-innodb> SELECT * FROM xatest;
| id | name |
|  1 | tr01 |
1 row in set (0.00 sec)

These transactions are between two MySQL instances: One with InnoDB and one with TokuDB. It's possible to run TokuDB and InnoDB both in one database instance, but separating them between instances (and hosts) might be needed for performance or some other reason.

It's possible to run a transaction with TokuDB and a PostgreSQL database:
mysql55-tokudb6> XA START 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# BEGIN;

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('tr03');
Query OK, 1 row affected (0.00 sec)

mysql55-tokudb6> DELETE FROM xatest WHERE name='tr02';
Query OK, 0 rows affected (0.00 sec)

xatest=# INSERT INTO xatest(name) VALUES('tr03');
mysql55-tokudb6> XA END 'tr03';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> XA PREPARE 'tr03';
Query OK, 0 rows affected (0.00 sec)
xatest=# PREPARE TRANSACTION 'tr03';
mysql55-tokudb6> XA COMMIT 'tr03';
Query OK, 0 rows affected (0.00 sec)

xatest=# COMMIT PREPARED 'tr03';

For PostgreSQL this only works if max_prepared_transactions is set to a non-zero value.

Transactions can not only run between databases but applications, filesystems and many other components can also be a member of the transactions. They need to support two-phase commit.

An XA Transaction is coordinated by a transaction coordinator which can be an application on a application server.

Monday, May 28, 2012

IPv6 on database websites

After reading now active over IPV6 by default I quickly tried some other host to see what the current state of IPv6 is for some known database websites.

$ getent hosts

So only supports IPv6 right now. On the MySQL side Facebook is one of the known IPv6 users.

Are there any IPv6  database websites I forgot to include?

The World IPv6 Launch is June 6 2012, so there are still a few days left to enable IPv6!

Thursday, April 26, 2012

Books vs. e-Books for DBA's

As most people still do I learned to read using books. WhooHoo!

Books are nice. Besides reading them they are also a nice decoration on your shelf. There is a brilliant TED talk by Chip Kidd on this subject.

But sometimes books have drawbacks. This is where I have to start the comparison with vinyl records (Yes, you're still reading a database oriented blog). Vinyl records look nice and are still being sold and yes I also still use them. The drawback is that car dealers start to look puzzeled if you ask them if your new multimedia system in your car is able to play your old Led Zeppelin records. The market for portable record players is small, and that's for a good reason.

The problem with books about databases is that they get old very soon. The MySQL 5.1 Cluster Certification Study Guide was printed by which made it possible to quickly update the material. This made sure that the material wasn't outdated when you bought it.

I like to use books as reference material, but I tend to use Google more often and the books stay on the bookshelf and are getting old and dusty. One of the reasons for this is that taking books with me just for reference is not an option judging by the weight of it.

At Percona Live UK I got a voucher from O'reilly to get a free e-Book. So I chose 'SQL and Relational Theory'. I started to read it on my laptop with FBreader and on my iPhone using Stanza. Both my phone and laptop are not really made for reading. So I bought an Sony Reader, which is made for reading.

Reading 'SQL and Relational Theory' on the Sony Reader is nice. The only annoyance is that the examples are like this:
FROM tbl1       | FROM tbl1
And with line wrapping it looks like this:
FROM tbl1       | 
FROM tbl1
Which is not very readable.
The book is very theoretical as you might expect, but nonetheless it's a very good read.

The Sony Reader is not very suitable for reading whitepapers in PDF format  as most whitepapers are in A4 or Letter format which is too big for the device. Of course software like Calibre can covert some of those.
(Oracle, Percona, others… please also publish your whitepapers in a format more suitable for an eReader)

The device itself is very nice. The battery time and e-Ink display are good (especially if you compare them with an tablet).

Unfortunately it doesn't increase my reading speed and it doesn't give me more time to read.

I'm looking forward to read some other database books in e-Book format.  I think the next one on my list is High Performance MySQL.

I planned to publish this post when I finished reading SQL and Relation Theory, but I thought now might be a better time as O'Reilly has a discount for that book and other books by C.J. Date.

The Sony Reader runs a modified Android (yes it's possible to root it to play angry birds on it). It also has a webbrowser, but itsn't well suited for reading Planet MySQL or Planet MariaDB. Using the webbrowser to download the MP3 for the OurSQL Podcast and then playing it does work flawlessly. I tried to download the EPUB file for the MySQL Reference Manual, but that failed so I used USB for that.

Sunday, April 22, 2012

SQL Injections, Again…

Last Friday the Dutch TV program Zembla aired part two of the "verzuimpolitie" series. The first part was mainly about how employers could access medical information about employees. There is a news article about the second part here (with google translate).

The second part is about the security of the IT system which is used to record medical information about employees. They give this information to the company to which the company they're working for is outsourcing everything related to workplace absenteeism.

After the first part of the series some viewer reported that the website contained SQL injections. The creators of the program verified this and tried to report it to VCD (The company which offers the software as a service). Then VCD called to police to remove them from the VCD office.

Then Zembla contacted the Radboud University and asked them to assist with this issue. The University verified the SQL Injection and confirmed that this was a serious security flaw. Then a VCD executive told Zembla that there wasn't a SQL Injection, someone just stole the passwords. This is strange because VCD reported to the University that they recorded a SQL Injection attack by the University.

The users of the VCD Humannet software were not informed. And when some of the companies using this SaaS service became aware of the security incident it took a lot of effort before the service was temporarily shutdown to prevent further harm.

This whole story reminded me of the situation around Comodo and DigiNotar. Comodo was hacked, stopped the issuing process, reported the issue and fixed it. Then DigiNotar was hacked, did not stop the issuing process. It also didn't report the issue. Then they became bankrupt.

The lessons learned for SQL Injections for DBA's and Application Developers:
1. Input validation. This is obvious.

2. Use prepared statements if possible.

3. Prepare for a security incident: make it easy to disable applications or parts of applications.
If all client companies are in the same database then it's very hard to shutdown the application for just one company. Using one database instance per client company might be a solution.

4. Use isolation
If there are 10 client companies and they all use different databases  as separation, then you should also use 10 application users with the correct permissions. Then a SQL injection for one customer won't affect other customers.

5. Use a database firewall.
This is not very common yet. You could use GreenSQL or McAfee (partly opensource). There are more solutions available, but these are at least partly opensource.

6. Use two factor authentication if dealing with sensitive data.
You don't have to buy expensive tokens. There are enough free or almost free solutions available. Yubikey is a possible solution.

7. Do not store passwords, store hashes.

8. Use encryption an function like AES_ENCRYPT() to encrypt sensitive data.
This could guard your data from 'curious' DBA's and other administrative users.
 Do not use a hardcoded password for this! Make sure that the AES_ENCRYPT doesn't end up in your binlogs, use a variable! And only use TLS secured connections. It might be better to encrypt the data in the application instead of in the database. It could even be possible to use client side encryption to encrypt the data in the browser.

9. Remove old authentication methods, login screens, etc.

The lessons learned for SQL Injections for management:
1. Security scans are mandatory. Companies like Madison Gurkha and Fox IT can offer this.

2. Don't only inclue your own services in security scans, but also the external services you use.

3. Make sure that there is a security breach notification requirement in the contracts for security sensitive services.

4. Make it easy to report security incidents.

5. Do shutdown the service if needed for security.

6. Do inform your customers about the security incident.

Sunday, April 15, 2012

Backup your sandbox with XtraBackup

Today I tried to make incremental backups of a MariaDB instance in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there is documentation about making incremental backups. 

MySQL sandbox makes it easy to run many different MySQL versions on one machine. It does this by changing the port number, data directory, UNIX socket location and a whole lot more.

So I first started with a full backup and after that I used that backup as a base for the incremental backups. To do that I had to specify the port number which is 5522 and the username and password for the msandbox account. As MySQL uses a UNIX socket instead of a TCP connection if the hostname is localhost I specified as hostname to force a TCP connection. That worked!

Then I created the incremental backup by using the --incremental option and the --incremental-basedir option to specify the location of the full backup. That also worked!

Then I tried to make a backup while putting some load on my database. I did use  "INSERT INTO test1(col1) SELECT col1 FROM test1" to do this.

The full and incremental backups still worked, or at least that's what the backup script told me. But the size of the incremental backups was quite small. And I noticed that the LSN was very small and not increasing. The xtrabackup_checkpoints file also told me that the backups where all for exactly the same LSN. As the LSN is only for InnoDB, I verified the table type for my test tables. And my test tables were in fact InnoDB. A "SHOW ENGINE INNODB STATUS\G" told me that the LSN was in fact increasing.

It turned out that XtraBackup was making backups of /var/lib/mysql instead of ~/sandboxes/msb_5_5_22-mariadb/data/. Adding "--defaults-file=~/sandboxes/msb_5_5_22-mariadb/my.sandbox.cnf" to the innobackupex command did correct this.

After specifying the correct config file I did try to make backups under load again. It failed due to the logfiles being too small. So I stopped the database, removed the ib_logfile's and started the database with a larger InnoDB logfile size.

Then It all worked flawlessly!

So you should make sure that your backup completes without errors AND that your backups is from the right database. Of course testing restores regularly would also detect this.


There is a nice demo of  MySQL Bug 13510739 on Eric Romang's blog

I've published this blog to make this content available on

Sunday, March 25, 2012

Why you should care about IPv6 in MySQL

Since MySQL 5.5 it's possible to use IPv6, but why should you care?

Since MySQL 5.5 you can configure MySQL to listen on an IPv6 address. The MySQL commandline utilities can connect to an IPv6 address.
Storing IPv6 addresses is simple, just store them as a 128 bit integer. You can convert IPv6 addresses to numbers and back in your application.

Your users will start to use IPv6

Your users might start to use IPv6 and you might need to store IPv6 addresses in your database. Since MySQL 5.6.3 you can use the INET6_ATON() and INET6_NTOA() functions. You could also store addresses as character string, but takes more storage and is less flexible.

You need to learn to use IPv6

In a traditional 3-tier web platform the database is normally not directly accessed by the end users as the application server is the one accessing the database. Direct database access is probably used only for administration and/or reporting. If the loadbalancer is IPv6 enabled then the website is accessible over IPv6. The connections between the loadbalancer, web server, application server and database server then can all use IPv4. The drawbacks for this setups are: You can't use direct server return options on the loadbalancer. IP addresses in logs and header might be the one from the loadbalancer.
So you don't need IPv6 for your database server, but you do need it elsewhere and within a number of years you might as well need it. So you should start to use IPv6 and start to learn how it works, how you should make it secure, how to tune it.

Note 1: You should use "bind-address = ::" to make MySQL Listen on IPv6 (Source: Bug #8836)
Note 2: If you use mysql-proxy directly or as part of MySQL Enterprise Monitor: It doesn't yet support IPv6. (Bug #60392)

Fine grained access control

One of the temporary solutions if you need IPv4 is carrier grade NAT. That means that all connections from 1 ISP or company might come from the same IPv4 address. If you want to grant access to somebody you could create the grant for the whole network or use VPN or SSH tunneling. If you use IPv6 every connection should come from a unique and more static IPv6 address. Then you can grant the access only for 1 IP address. Of course you should enable TLS/SSL for a secure connection.

Fixed in drizzle

Of course IPv6 is already fixed in drizzle. You can download the whitepaper about IPv6 in Drizzle here.

It doesn't yet seem to be fully fixed in MariaDB according to the Ask Monty Knowledgebase. But with MariaDB 5.5 this should change. I expect IPv6 support to be complete when MariaDB 5.6 is released. The virtual columns feature of MariDB could makes it easier and faster to work with IP addresses.