In a recent labs release a new feature was introduced by Oracle, or actually two very related new features were introduced. The first new feature is Common Table Expressions (CTEs), which is also known as WITH . The second feature is recursive CTEs, also known as WITH RECURSIVE . An example of WITH : WITH non_root_users AS (SELECT User, Host FROM mysql.user WHERE User<>'root') SELECT Host FROM non_root_users WHERE User = ? The non-CTE equivalent is this: SELECT Host FROM (SELECT User, Host FROM mysql.user WHERE User<>'root') non_root_users WHERE User = ? This makes it easier to understand the query, especially if there are many subqueries. Besides using regular subqueries or CTEs you could also put the subquery in a view, but this requires more privileges. It is also difficult to change the views later on as other quieries might have started to use them. But views are still very useful. You can make it easier for others to query data or you can ...

As the maximum storage size for a LONGBLOB in MySQL is 4GB and the maximum max_allowed_packet size is 1GB I was wondering how it is possible to use the full LONGBLOB. So I started testing this. I wrote a Python script with MySQL Connector/Python and used MySQL Sandbox to bring up an instance of MySQl 5.6.25. One of the first settings I had to change was max_allowed_packet, which was expected. I set it to 1GB. The next setting was less expected, it was innodb_log_file_size. The server enforces that the transaction has to fit in 10% of the InnoDB log files. So I had to set it to 2 files of 5G to be able to insert one record of (almost) 1GB. So that worked for a row of a bit less that 1GB, this is because there is some overhead in the packet and the total has to fit in 1GB. For the next step (>1GB) I switched from Python to C so I could use mysql_stmt_send_long_data() which allows you to upload data in multiple chunks. I expected that to work, but it didn't. This is bec...

One of the latest labs releases of Oracle MySQL brings multi source replication. This lifts the limitation found in earlier releases that a MySQL slave can only have one master. To be fair, there were other ways of doing this already: Using a time based switch as described in MySQL High Availability Using the multi source feature in the yet-to-be released MariaDB 10 Using Tungsten Replicator There are many good uses of multi source replication. You could use it to combine data from multiple shards or applications. If MySQL is used with a loadbalancer the most easy to build setup is a 2-way multi master. This makes it possible to use the InnoDB storage engine. Using MySQL Cluster is another alternative, but MySQL Cluster uses the NDB storage engine, and might not be a supported option for your application. A MySQL Cluster setup also needs at least 4 machines to be fully redundant and MySQL Multi Master only needs two machines. There is little intelligence required in the load...

There is a new feature in the MariaDB 10 Beta which caught my eye: support for returning a result set on delete . With a 'regular' DELETE operation you only get to know the number of affected rows. To get more info or actions you have to use a trigger or a foreign key. Anoter posibility is doing a SELECT and then a DELETE and with the correct transaction isolation a transactional support this will work. With the support for the RETURNING keyword this has become easier to do and it will probably bennefit performance and save you a few roundtrips and a few lines of code. There is already support for RETURNING in PostgreSQL. And PostgreSQL has an other nifty feature for which RETURNING really helps: CTE or common table expressions or the WITH keyword. I really hope to see CTE support in MySQL or MariaDB some day. An example from RETURNING and CTE in PostgreSQL: demo=# select * from t1; id | name ----+------- 1 | test1 2 | test2 3 | test3 4 | test1 5 | test2 ...

The last MySQL User Group NL meeting was last Friday. It's always nice to learn about MySQL and meet other MySQL users. There were two presentations: one about MySQL User Defined Functions (UDF's) and one about MySQL TCO. The slides are available from the meetup page . There are already a number of MySQL events announced for the next few months. I'll only list events in the Netherlands and Virtual events. MySQL Virtual Developer Days This is a virtual event which will take place on March 19 (EMEA region, NA event is on March 12). There are many interesting topics: Performance Schema, New 5.6 Features, Replication, MySQL Enterprise Monitor The eVite SkySQL and MariaDB roadshow 21 March 2013 in Amsterdam This event  have presentations by Monty Widenius, Seppo Jaakola and Anders Karlsson. The main topics are MariaDB and Galera. The event page MySQL User Group NL, Q2 Meeting 31 May 2013 in Geldermalsen The event page

I've created a graph about the MySQL version history. It's mysql-graph-history on github. Please let me know if this is correct or if I'm forgetting some versions.

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

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

For MySQL it's a best practice to store IP addresses in a INT column rather than in a VARCHAR. This allows for more compact storage. This best practice is only for MySQL as PostgreSQL does have data types like inet and cidr . MySQL is equiped with the INET_ATON() and INET_NTOA() functions since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and INET6_NTOA() functions were addes to finaly add IPv6 address support. To make it easier to work with IPv4 and IPv6 addresses the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6() functions were added. The difference between the new and old functions is that the old functions use INT UNSIGNED to store IP addresses and the new function uses VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. Here are some examples about how you could do nifty things with IP addresses in MySQL. Store hosts and networks in MySQL CREATE TABLE `hosts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `hostname` varchar(255) DEFA...