Lately, I worked on several queries which started returning wrong results after upgrading MySQL Server to version 5.7 The reason for the failure was derived merge optimization which is one of the default

optimizer_switch

  options. Issues were solved, though at the price of performance, when we turned it

OFF

 . But, more importantly, we could not predict if any other query would start returning incorrect data, to allow us to fix the application before it was too late. Therefore I tried to find reasons why

derived_merge

  can fail. Analyzing the problem

In the first run, we turned SQL Mode

ONLY_FULL_GROUP_BY

on, and this removed most of the problematic queries. That said, few of the queries that were successfully working with

ONLY_FULL_GROUP_BY

  were affected.

A quick search in the …

Thirteen years ago today (September 25th, 2005) I joined MySQL AB.

At the time they were just about hitting 10 years old themselves, celebrated at the first conference I attended for MySQL – The Open Source Database Conference, in Frankfurt in November 2005. That was around the time “Free as in Free Beer” was popular (and we drank lots of it).

I was introduced to Salmiakki at that time too – still don’t like it.

Less than a couple of weeks later on October 7th 2005 Oracle announced it was buying InnoDB. I remember thinking at the time how my new job could be cut short so quickly, and the tumultuous times we went through before we got to where we are today. I never imagined I’d reach 13 years at the time.

I joined as a regular Support Engineer, recruited by Dean Ellis, who at the …

MySQL binary logs make possible to implement point-in-time recovery and greatly reduce Recovery Point Objective. In this post I will show how to copy MySQL binary logs with TwinDB Backup. MySQL binary logs was an easy way to implement replication. Whenever the server executes a DML query it also writes the query to a local […]

The post How to Backup MySQL Binary logs appeared first on TwinDB.

To use Percona XtraBackup with keyring_vault plugin enabled you need to take some special measures to secure a working backup. This post addresses how to backup Percona Server for MySQL with keyring_vault plugin enabled. We also run through the steps needed to restore the backup from the master to a slave.

This is the second of a two-part series on setting up Hashicorp Vault with Percona Server for MySQL with the keyring_vault plugin. First part is Using the keyring_vault plugin with Percona Server for MySQL 5.7.

Backing up from the master

First you need to install the latest Percona XtraBackup 2.4 package, in this tutorial I used this version:

[root@mysql1 ~]# xtrabackup --version
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysqld-bin …

MySQL has locking capabilities, for example table and row level locking, and such locks are needed to control data integrity in multi-user concurrency. Deadlocks—where two or more transactions are waiting for one another to give up locks before the transactions can proceed successfully—are an unwanted situation. It is a classic problem for all databases including MySQL/PostgreSQL/Oracle etc. By default, MySQL detects the deadlock condition and to break the deadlock it rolls back one of the transactions.

For a deadlock example, see InnoDB deadlocks

Some misconceptions

There are some misconceptions about deadlocks:

a) Transaction isolation levels are responsible for deadlocks. The possibility of deadlocks is not affected by isolation level. Isolation level changes the behavior of read …

While writing about problematic Oracle MySQL features previously I concentrated mostly on InnoDB problems that I have to fight with really often and deliberately skipped replication from even the preliminary list of features to study in details for that blog post. First of all, I mostly work with MariaDB users now, and implementation of many replication features in MariaDB is notably different already (and has its own list of known problems). But this happened also because (asynchronous) replication plays a key role in most MySQL environments and deserves a detailed study in a separate post.
…

In another post, I explained how to use the MySQL Query Re-write Plugin to manipulate data that didn’t exactly match SQL standards of MySQL. In this post, I am going to give you another example on how to use a trigger to parse non-conforming data so the data can be written to a MySQL database.

A customer came to me with a problem. They were using third-party software which produced multiple rows of comma-separated data (like a .csv file), but all of the data wasn’t on a single row. Instead, the data consisted of multiple rows of data, separated by commas and with line feeds after each row of data. Here …

There are several possibilities to end up with inconsistent MySQL replication, This could be accidental or intentional. In this blog I would like to discuss on how to identify the inconsistent slaves with master and fix them. I used here pt-table-checksum (to find the difference between master and slave) and pt-table-sync (to sync. between MySQL master and slave) from Percona Toolkit, The detailed documentation of Percona toolkit is available here for your reference. I expect / recommend you to be careful (as I mentioned above, sometimes records are inserted / deleted on MySQL slave intentionally) before using pt-table-checksum to sync. slave with master because rollbacking this task is even more expensive. The objective of this blog is to show you how to find differences between master and …

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

This week is clearly what I’d call a “MariaDB week” — plenty of announcements coming from MariaDB Corporation and MariaDB Foundation.

It started with Alibaba Cloud and MariaDB Announce the Launch of ApsaraDB RDS for MariaDB TX, which makes Alibaba Cloud the first public cloud to offer the enterprise offering of MariaDB, MariaDB TX 3.0. It is not available yet as of this announcement for rolling out from the interface, but I expect it will be soon. Exciting, as you can already get MariaDB Server on …

Prometheus 2 time series database (TSDB) is an amazing piece of engineering, offering a dramatic improvement compared to “v2” storage in Prometheus 1 in terms of ingest performance, query performance and resource use efficiency. As we’ve been adopting Prometheus 2 in Percona Monitoring and Management (PMM), I had a chance to look into the performance of Prometheus 2 TSDB. This blog post details my observations.

Understanding the typical Prometheus workload

For someone who has spent their career working with general purpose databases, the typical workload of Prometheus is quite interesting. The ingest rate tends to remain very …