As per a related post from last week, we’re looking forward to Percona Live 2016 in Santa Clara, the go-to conference for MySQL & MongoDB users in particular, and open source database enthusiasts in general.

This year we’ve been given the opportunity to conduct a one-day tutorial based on our popular blog series ‘Become a MySQL DBA’. We’re reaching our 20th post in this series (the latest one is on trouble-shooting with pt-stalk) and are excited to be able to share that content and knowledge during this one day session in Santa Clara this month.

Art van Scheppingen

Our colleagues Art van Scheppingen and Ashraf Sharif will be walking participants through the content and practical exercises for about 6 hours spread across day 1 at Percona Live. The tutorial is scheduled to take place in Ballroom H. For …

See Bill Nye the Science Guy at Percona Live Data Performance Conference, and help an excellent cause!

The best science is built on solid data. As a world-renown icon in tech and geek circles everywhere, Bill Nye fights to raise awareness of the value of science, critical thinking, and reason. He hopes that the data he brings will help inspire people everywhere to change the world. And seeing as the open source community is full of science-minded individuals, he is excited to speak to everyone at Percona Live!

Since his early days as a comedian, to creating his well-known …

The optimizer in MySQL 5.7 leverages generated columns. Generated columns will physically store data in two cases: Either the column is defined as STORED or you create an index on a virtual column. The optimizer will leverage such an index automatically if it encounters the same expression in a statement. Let's see an example:

mysql> DESC squares;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| dx    | int(10) unsigned | YES  |     | NULL    |       |
| dy    | int(10) unsigned | YES  |     | NULL    |       |
…

Update: Starting with MySQL 8.0.16 we do have CHECK constraints implemented in SQL! See here.

During our recent TechTour event the idea came up to implement JSON document validation not necessarily via foreign keys (as I have shown here) but to define the generated column as NOT NULL. The generation expression must be defined in a way that it returns NULL for invalid data.
DISCLAIMER: This has already been explored by yoku0825 in his blogpost. He deserves all credit!

Let's do a short test:

mysql> CREATE TABLE checker ( 
    i int, 
    …

When MySQL released performance_schema it made a lot of new useful information available, but it wasn't particularly user-friendly. The introduction of the MySQL SYS schema made it a lot easier for developers and DBAs to get useful insights from performance_schema. I was talking to some brogrammers the other day, and while they admitted that SYS schema is "totally legit", they wished that it "spoke their language".

To that end, I am happy to announce MySQL BRO schema. It's a natural complement to SYS schema, and makes it easier for brogrammers to work with performance_schema.

Read on for a few examples of the BRO schema in action.

The SYS schema has a view named …

It is not uncommon that we need to filter out some DBs or Tables while setting up replication. It is important to understand how MySQL evaluates/process the replication filtering rules to avoid the conflicting or confusion while we setting them up.The purpose of this blog is to illustrate the rules and provide some suggestions for best practice.

MySQL provides 3 levels of filters for setting up replication: Binary log, DB and Table. The binlog filters apply on the master to control how to log the changes. Since MySQL replication is based on the binlog, it is the first level filter and has the highest priority. While the DB-level and Table-level filters apply on the slaves, since each table belongs to a schema, the DB-level filters have higher priority than the Table-level ones. Inside the Table-level filters, MySQl will evaluate the options in the order of: …

In last week’s post, Part 1 of 3, we took a look at the JSON data type support added in MySQL 5.7.8. This week, I wanted to look at some of the document-oriented patterns that MongoDB uses so we can see how we might accomplish the same in MySQL.

The goal is not to 100% replicate what Mongo does, but to see which patterns will make the best use of JSON support in MySQL, allowing us to get the best of an SQL store and a document-oriented one.

I also want to tee up next week’s post, where we’ll take a look at how to implement models that make use of the JSON data type in Laravel, as well as how to work with them using the query builder and other mechanisms.

Warning: This one’s a bit of a long read. Here’s a high-level TOC.

1. …

Percona is glad to announce the first GA release of Percona XtraBackup 2.4.2 on April 1st, 2016. Downloads are available from our download site and from apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as …

Historical MySQL Bug#2, opened 12 Sep 2002, states that MySQL Connector/J doesn’t make toast. It hasn’t been fixed for more than 14 years. I’ve finally created a patch for it.

First of all: why only fix this for MySQL Connector/J? We should make sure the server can do this for any implementation! With this fix, now MySQL server (starting with version 5.1) can make toast.

There are few dependences though (see assembled setup picture):

1. Raspberry Pi + PiFace shield
2. Power switch relay (I’ve used an  …

It's almost a year old, but this post on the Pinterest engineering blog is really great advice: https://engineering.pinterest.com/blog/learn-stop-using-shiny-new-things-and-love-mysql

My favorite advice from there: "Keep it simple. No matter what technology you’re using, it will fail."

Happy reading.