The latest from the MySQL community

Starting with the new release MySQL 5.7 there is support to store JSON documents in a column. During our recent Tech Tour events we got questions about document validation, so ensuring that a JSON document has a certain structure. (Funny. It all started with the idea to be schema-free. Now people seem to need schema enforcement.)
I have two ideas how to implement a schema validation for JSON columns. The first one is by leveraging generated columns together with a foreign key. The second idea is by implementing a trigger. Today I want to focus on the generated columns and foreign keys.
When defining foreign keys with generated columns there are two limitations we need to be aware of:

• Foreign keys require indexes. JSON columns cannot be indexed. We need to leverage other types.
• Only STORED generated columns are supported for foreign keys.

So here is an example of an address table that …

Of the different types of clustered MySQL environments, NDB Cluster is among the ones that involves more effort and resource to administer. And unless you are a command line freak, you would want to use a tool that gives you a full view of what is going on in your cluster. 

It is possible to install ClusterControl and monitor your existing MySQL Cluster (NDB). In this blog post, we are going to show you on how to add two existing MySQL Clusters (production and staging) into ClusterControl. 

• ClusterControl: 192.168.55.170
• Cluster #1:
  • Management/API node: mgmd-api1 - 192.168.55.71
  • Management/API node: mgmd-api2 - 192.168.55.72
  • Data node: data1 …

Indexes are usually built by way of a data structure; typically, that structure takes the form of a “tree.” Most commonly, the structure of choice is a B-Tree, which is a hierarchical organization defined by the arrangement and interactions of its nodes.

High in a B-Tree’s hierarchy, you find a root node. Each of the items in that root node points to a collection of items in a child node. This can go on for multiple levels. Eventually, you reach the leaf nodes, where the data itself is stored. Those leaf nodes point back to the rows in the main table. To be technology specific, for instance, in MyISAM, those leaf nodes have offsets in the main data file – the main data file in a MyISAM table is just a sequential collection of rows.

The purpose of an index is to allow a user to very quickly traverse just such a structure – whatever form that structure or algorithm might take – and to locate and examine rows or ranges of …

In this blog post, we are going to show you some examples of things that can go wrong in Galera - inexplicable node crashes, network splits, clusters that won’t restart and inconsistent data. We’ll take a look at the data stored in log files to diagnose the problems, and discuss how we can deal with these.

This builds upon the previous post, where we looked into log files produced by Galera (error log and innobackup.* logs). We discussed how regular, “normal” activity looks like - from initialization of Galera replication to Incremental State Transfer (IST) and State Snapshot Transfer (SST) processes, and the respective log entries. 

This is the seventeenth installment in the ‘Become a MySQL DBA’ blog series. Our previous posts in the DBA series include:

• …

Recently my colleague (by Percona) Yves Trudeau and colleague (by industry) Marco Tusa
published their materials on Amazon Aurora. Indeed, Amazon Aurora is a hot topic these days, and we have a stream of customer inquiries regarding this technology. I’ve decided to form my own opinion, and nothing is better than a personal, hands-on experience, which I am going to share.

The materials I will refer to:

• [1] Presentation from Amazon Re:Invent: …

ScaleDB 15.10 is out. Some users have downloaded it and tested it and we have received pretty positive feedback, but also some requests to have more info and help on how to start. I will try to condense here the basic steps to install and test ScaleDB ONE for the first time.
First of all, some terminology. We have two versions: ScaleDB ONE and ScaleDB Cluster. ScaleDB ONE is meant to be used on a single machine (ONE = One Node Edition), whether it is a VM, a cloud instance or a physical server, whilst ScaleDB Cluster is the full size, multi-node cluster that everybody expects to run for mission critical applications. This means that the typical use cases for ScaleDB ONE are testing and development, data marts and streaming data collection and analysis that are limited by a single server (although you can always replicate your data to another server using the standard MySQL Replication). ScaleDB Cluster instead, is highly …

Summary from last blog:

• Tip: An alias for Get-CimInstance is GCim, for Select-Object it's Select and for Get-WmiObject is GWmi.
• There are Raw and Formatted counters. Watch out for formula converting Raw samples to Formatted values.

NAMESPACE organizationThe general organization of namespaces is as follows:
  Category (Class if you prefer)
    Counter(s)
      Instance(s)
Every Category has Counters but not all of the Counters have Instances. The full path to the desired value is called a __PATH:

PS > GWmi Win32_PerfFormattedData_PerfOS_Processor | Select __Path

__PATH
------
      namespace …

Recently we released an updated version of our Percona Toolkit with an important update for pt-online-schema-change if you use MySQL 5.6. A new parameter has been added, analyze-before-swap. What does it do? Let me explain it in more detail.

A bug not a bug

A customer contacted us because pt-online-schema-change caused hundred of queries to get stuck after the ALTER was done. All those queries were trying read from the altered table but for some reason the queries never finished. Of course, it caused downtime. The reason behind this is this “bug not a bug”:

http://bugs.mysql.com/bug.php?id=78289

As a summary, if you are running 5.6 with persistent stats enabled (which it is by default), the optimizer in some …

Nginx is well-known for its ability to act as a reverse-proxy with small memory footprint. It usually sits in the front-end web tier to redirect connections to available backend services, provided these passed some health checks. Using a reverse-proxy is common when you are running a critical application or service that requires high availability. It also distributes the load equally among the backend services.

Recently, nginx 1.9 introduced support for TCP load balancing - similar to what HAProxy is capable of. The one major drawback is that it does not support advanced backend health checks. This is required when running MySQL Galera Cluster, as we’ll explain in the next section. Note that this limitation is removed in the paid-only edition called NGINX Plus. 

In this blog post, we are going to play around with nginx as a reverse-proxy for MySQL Galera Cluster services to achieve higher availability. We had a Galera cluster …

Nginx is well-known for its ability to act as a reverse-proxy with small memory footprint. It usually sits in the front-end web tier to redirect connections to available backend services, provided these passed some health checks. Using a reverse-proxy is common when you are running a critical application or service that requires high availability. It also distributes the load equally among the backend services.

Recently, nginx 1.9 introduced support for TCP load balancing - similar to what HAProxy is capable of. The one major drawback is that it does not support advanced backend health checks. This is required when running MySQL Galera Cluster, as we’ll explain in the next section. Note that this limitation is removed in the paid-only edition called NGINX Plus. 

In this blog post, we are going to play around with nginx as a reverse-proxy for MySQL Galera Cluster services to achieve higher availability. We had a Galera cluster …