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> INSE...

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 f...

SANS ISC reported a number of zeroday's for MySQL today. * CVE-2012-5611 MySQL (Linux) Stack based buffer overrun PoC Zeroday http://seclists.org/fulldisclosure/2012/Dec/4 https://bugzilla.redhat.com/show_bug.cgi?id=882599 * CVE-2012-5612 MySQL (Linux) Heap Based Overrun PoC Zeroday http://seclists.org/fulldisclosure/2012/Dec/5 https://bugzilla.redhat.com/show_bug.cgi?id=882600 * CVE-2012-5613 MySQL (Linux) Database Privilege Elevation Zeroday Exploit http://seclists.org/fulldisclosure/2012/Dec/6 https://bugzilla.redhat.com/show_bug.cgi?id=882606 * CVE-2012-5614 MySQL Denial of Service Zeroday PoC http://seclists.org/fulldisclosure/2012/Dec/7 https://bugzilla.redhat.com/show_bug.cgi?id=882607 * CVE-2012-5615 MySQL Remote Preauth User Enumeration Zeroday http://seclists.org/fulldisclosure/2012/Dec/9 https://bugzilla.redhat.com/show_bug.cgi?id=882608   Source: http://seclists.org/oss-sec/2012/q4/387

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.

  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 meetup.com page . Agenda (See meetup.com 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: LinkedIn Google+ Twitter Facebook

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/autotuner.cf file which should be included in your my.cnf. It should have a min/max setting for each option and some thresholds. Why? 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 my-huge.cf, 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 migh...

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: You've tested the failover scripts and procedures You've tested the failover scripts and procedures under normal load You've tested the failover scripts and procedures under high load You've tested it since the last change in the setup and/or application Someone else tested the failover scripts an...

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; +--------------------------------------+------------+-------------+--------------------------+---------------------------+ | EVENT_NAME | COUNT_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_READ | SUM_NUMBER_OF_BYTES_WRITE | +--------------------------------------+------------+-------------+--------------------------+---------------------------+ | wait/io/file/sql/FRM | 25387 | ...

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...

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...

After reading www.postgresq.org 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 mysql.com percona.com askmonty.org postgresql.org oracle.com sqlite.org code.openark.org skysql.com drizzle.org 156.151.63.6    mysql.com 74.121.199.234  percona.com 173.203.202.13  askmonty.org 2a02:16a8:dc51::50 postgresql.org 137.254.16.101  oracle.com 67.18.92.124    sqlite.org 69.89.31.240    code.openark.org 94.143.114.49   skysql.com 173.203.110.72  drizzle.org So only postgresql.org 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!

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 lulu.com 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 refere...

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 sec...

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 127.0.0.1 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 ba...

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 planet.mysql.com.

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 accessi...