MySQL Galera Cluster 4.0 is the new kid on the database block with very interesting new features. Currently it is available only as a part of MariaDB 10.4 but in the future it will work as well with MySQL 5.6, 5.7 and 8.0. In this blog post we would like to go over some of the new features that came along with Galera Cluster 4.0.

Galera Cluster Streaming Replication

The most important new feature in this release is streaming replication. So far the certification process for the Galera …

SQL Left Function Example | LEFT() In SQL Server And MySQL is today’s topic. LEFT FUNCTION in SQL is used for extracting a substring starting from the left, followed by the number of characters. The LEFT() function extracts several characters from the string (starting from left).

SQL Left Function

The LEFT() function extracts a given number of characters from the left side of a supplied string.

The syntax of the LEFT() function is as follows.

SELECT LEFT (string, no_of_characters);

See the following parameters.

PARAMETERS:

1. String: An input string from which the substring will be extracted and can be a literal string, variable or a column.
2. No_of_characters: Number of characters you wish to extract from the string.

The LEFT() function returns a value of VARCHAR when …

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, …

EverData a leading data center and cloud solution provider in India has recently been writing quite a bit about Galera Cluster and it seems like we should highlight them. For one, they’ve talked about streaming replication in Galera Cluster 4, available in MariaDB Server 10.4. However, let us focus on their post: Galera Cluster vs Amazon RDS: A Comprehensive Review.

They compared MySQL with MHA, MySQL with Galera Cluster and also Amazon Web Services (AWS) Relational Database Service (RDS). Their evaluation criteria was to see how quickly there would be recovery after a crash, as well as performance while managing concurrent reads and writes.

In their tests, they found that time for …

As soon as you start running a database server and your usage grows, you are exposed to many types of technical problems, performance degradation, and database malfunctions.  Each of these could lead to much bigger problems, such as catastrophic failure or data loss. It’s like a chain reaction, where one thing can lead to another, causing more and more issues. Proactive countermeasures must be performed in order for you to have a stable environment as long as possible.

In this blog post, we are going to look at a bunch of cool features offered by ClusterControl that can greatly help us troubleshoot and fix our MySQL database issues when they happen.

Database Alarms and Notifications

For all undesired events, ClusterControl will log everything under …

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"); …