Percona is happy to announce the first experimental binary of Percona XtraDB Cluster 8.0 on October 1, 2019. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This release combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.

Improvements and New Features

Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:

• Streaming replication supports large transactions
• The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
• More granular and improved error logging. wsrep_debug is now a multi-valued variable to assist in controlling the logging, and logging messages have been significantly improved.
…

Overview

Clustering provides high availability and disaster recovery, along with the ability to read-scale both locally and globally. Some clusters even provide active/active capabilities, which others have a single master.

Real time database replication is a must for clustering and other key business purposes, like reporting. There are a number of replication technologies available for MySQL, and some are even bundled into various solutions. When choosing a replication methodology, it is paramount to understand just how the data moves from source to target. In this blog post, we will examine how asynchronous, synchronous, and “semi-synchronous” replication behave when used for clustering. Also, we will explore how …

MySQL has had an interesting optimization for years now1, which has popped up from time to time: in certain circumstances, it may choose to use an index that is index-wise less efficient, but provides the resulting rows in order, to avoid a filesort of the result.

What does this typically look like in production? A query that seems simple and easy takes much longer than it should, sometimes. (Perhaps in production, the query gets killed by pt-kill or exceeds the max_execution_time provided.) The query could be very simple indeed:

SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1

There’s an index on other_id, and running the query with an appropriate USE INDEX, the query is fast. Even weirder, changing the query to use LIMIT 10 causes it to …

As follow up to the previous blog announcement we would like to inform you about where you can find MySQL & MySQL Community Team during October & November 2019. Please find the list below:

October 2019:

• PerconaLive, Amsterdam, Holland, September 30-October 2, 2019

  • For detailed information see the blog post from last week.
• OpenTech Summit, Bangkok, …

Author: Robert Agar

One of the tasks that a database team needs to be prepared to perform is a MySQL migration. In the deep, dark past of the early 21st Century, this would have involved moving a MySQL database from one server to another one located within your company’s data center. This might have been done to take advantage of better hardware with which to provide optimal service to the database’s end users. As the IT needs of a business change, systems are often shuffled around to make the best use of computing resources.

Besides enjoying the faster performance the main impact of migration on your database team is the requirement to use a different IP address to connect to the server hosting the databases they support. No big deal. The assumption would be that all tools and monitoring platforms in place before the migration will continue to function correctly. Once the dust settles, all is well in …

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

1. You set a session variable on a single line with the following …

When building network libraries for Node.js, as we do at work, one quite quickly comes by Node's Buffer type. A Buffer gives access to a memory region in a quite raw form, allowing to handle raw data and allowing to interpret binary streams. The Buffer interface predates ES6 TypedArrays and has some optimizations.

Two optimisations are notable:

For one the slice() method does not copy data, but returns a view on the underlying data. This makes it quite efficient to work on a window of the data, but when writing one has to be careful. Simple example:

const buffer = Buffer.from("hello"); …

MySQL Shell is a pretty nice piece of software. Is not just another mysql client but it is also a tool that offers scripting capabilities for JavaScript and Python. And one of the coolest things you can do with it is to check if your MySQL 5.7 server is ready for an upgrade or not. Enter: Upgrade Checker Utility.

MySQL Shell Upgrade Checker Utility

So what is it? It is a script that will check your MySQL 5.7 instance for compatibility errors and issues with upgrading. It’s important to notice the word “check”. It doesn’t fix. Just check. Fix is on you, friendly DBA (or we can happily assist with it).

But isn’t there something that already does that? Close, but no. The mysqlchk program and the –check-upgrade parameter does something similar: Invokes the CHECK TABLE …. FOR UPGRADE command. The Upgrade …

Multiplexing Background

Historically it’s a technique used in networking to integrate multiple analog and digital signals via a shared medium. The goal of multiplexing over the network was to enable signals to be transmitted more efficiently for a given communication channel, thus achieving cost efficiency. 

Since the term Multiplexing comes from telecommunications, the industry has heavily used a device called Multiplexers – aka Mux. There was even a term called muxing where signals were often analog and digital to be combined in a single line. 

The technique was developed in the early 1870s, it’s origins to be found in telegraphy, and it has become a standard for digital telecommunications in the 20th Century. 

Following multiplexing methods are currently available: 

• Frequency Division Multiplexing (FDM)
• Time Division Multiplexing (TDM) …

MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, column_name, data_type, column_type from information_schema.columns where table_name = 't'; The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ →