I know it was officially announced Monday, but I just wanted to take a moment and let everyone know MariaDB Enterprise 1.0 is now available, in case you missed the previous article.

What does MariaDB Enterprise consist of?

“MariaDB Enterprise is composed of several components including MariaDB Manager, which is a set of management tools and an API with which you can easily provision, monitor, and manage a highly available MariaDB Galera Cluster for multi-master, synchronous replication. Galera is a powerful technology that can eliminate single points of failure for your database infrastructure, but it is relatively new and can be a challenge to configure for administrators who aren’t familiar with it.”

https://mariadb.com/..MariaDB Enterprise – Getting Started …

We are considering enabling the SQL mode ONLY_FULL_GROUP_BY by default in MySQL 5.7. Here is a quick example to demonstrate how this change will impact you:

Sample Data:

CREATE TABLE invoice_line_items (id INT NOT NULL PRIMARY KEY auto_increment, 
invoice_id INT NOT NULL,
description varchar(100)
);

INSERT INTO invoice_line_items VALUES 
 (NULL, 1, 'New socks'),
 (NULL, 1, 'A hat'),
 (NULL, 2, 'Shoes'),
 (NULL, 2, 'T shirt'),
 (NULL, 3, 'Tie');

Default behaviour in MySQL 5.6 and below:

mysql> SELECT id, invoice_id, description 
FROM invoice_line_items GROUP BY invoice_id;
+----+------------+-------------+
| id | invoice_id | description |
+----+------------+-------------+
|  1 |          1 | New socks   |
|  3 |          2 | Shoes       |
|  5 |          3 | Tie         |
+----+------------+-------------+
3 rows in set (0.00 sec)

Proposed default behaviour in MySQL 5.7:

mysql> SELECT id, …

Since union queries were introduced in MySQL 4.0.0, they have been processed by executing each part of the union and adding the result to a temporary table. Then a final query block is executed that, if needed, filters out duplicates, do global ordering and limit the number of output rows. The good thing about this method is that it works for all union queries. The bad thing is that it always uses a temporary table, even in the really simple cases, and there’s always an extra query block to execute, even if there’s no sorting and no duplicate filtering.

As of MySQL 5.7.3, UNION ALL doesn’t use temporary tables unless needed for sorting. The result of each part of the union will be sent directly back to the client, without waiting in a temporary table or passing through an extra query block. When 5.7.3 came out, Facebook (who filed the feature request for …

Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.

The basic idea

The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the data. I’ll call this column a tenant id. We’ll start with a basic example: a todo app. Let’s say you write a program that you’re going to use on your own to …

There are many blog posts already written on topics related to “MySQL server memory usage,” but nevertheless there are some who still get confused when troubleshooting issues associated with memory usage for MySQL. As a Percona support engineer, I’m seeing many issues regularly related to heavy server loads – OR OOM killer got invoked and killed MySQL server due to high Memory usage… OR with a question like: “I don’t know why mysql is taking so much memory. How do I find where exactly memory is allocated? please help!”

There are many ways to check memory consumption of MySQL. So, I’m just trying here to explain it by combining all details that I know of in this post.

• Check memory related Global/Session variables.

If you are using …

Percona is glad to announce the release of Percona XtraBackup 2.1.7 on January 24th, 2014. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.1 series. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking …

If you’re having frequent MySQL Workbench crashes in Mac OS X, check whether you don’t have some Subversion plugin installed, such as SCFinderPlugin or SCToolbarButton. Apparently they are outdated and do not work well in recent versions of OS X, causing certain other innocent applications to crash. Removing these plugins should fix the problem in affected apps, including Workbench.

See Bug #71119 or Bug #68080

To check whether this is your problem, look for a line similar to the following in the Mac OS X crashlogs generated for Workbench:

0xbe29000 -  0xbe2dff7 +org.tigris.scfinderplugin (169) <BBADB6CA-61AE-24D5-4E0A-EBCCAD3F0D92> /Library/Contextual Menu Items/SCFinderPlugin.plugin/Contents/MacOS/SCFinderPlugin

To uninstall that plugin, delete the following …

By default, for every client connection the MySQL server spawns a separate thread which will process all statements for this connection. This is the ‘one-thread-per-connection’ model. It’s simple and efficient until some number of connections N is reached. After this point performance of the MySQL server will degrade, mostly due to various contentions caused by N threads that are trying to access shared resources: either system ones like CPU, IO, memory or MySQL specific: structures/locks/etc. To keep the system stable and avoid degradation in the performance we need to limit the number of active threads, and at the same time we do not want to limit number of the client connections. The ‘Thread Pool’ model helps us to achieve that. It allows mapping N client connections to M number of active threads (actually performing work) while demonstrate a smooth and stable throughput for the MySQL server.

There are several implementations of …

As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.

Note: all PostgreSQL examples were taken from PostgreSQL documentation.
First topic is syntax sugar.

Configuring the character set for MySQL is confusing. It is so confusing that there are roughly 25 different places to configure a character set. Don’t believe me? Add them up. The real number may be closer to 30. I realize a lot of this is due to the age of MySQL and the extent of it’s character set support. MySQL does support character set configuration in many different places which is usually a good thing.

I often complain about defaults that make no sense like lock_wait_timeout=1 year. In this case there is a default that makes absolutely no sense to me. The manual says that select into outfile is the complement of load data infile. It isn’t completely true. They differ in one key aspect, the default character set!. By default select into outfile now does the right thing by using binary character set and dumping the raw bytes to the file. Load data infile defaults to the value of the character_set_database variable which defaults …