Feed Aggregator

Many customers come to us and ask us whether to use MariaDB or MySQL. The answer is not so simple. FromDual is a neutral and vendor independent MariaDB/MySQL consulting company. So we should not have (in the meaning of neutral) a clear preference. For us internally we have chosen our strategy according to some clearly defined criteria. But what we have chosen for us is not necessarily the right choice for you.

So what we want to show you here is a tool which helps you to choose the right strategy for your own company or situation. In this case a tool to use is the decision matrix
[ 1
]. We tried to build such a decision matrix for your choice between MariaDB and MySQL. You can fill in your ratings into the table and decide yourself:

We just came today across the mysqlfrm utility which helps to extract the DDL statement from the .frm file. This is exactly what is needed to automatize and thus simplify the partial restore from MariaDB full backup.

See also feature request here: MDEV-18827.

MariaDB has some time ago introduced or reused the sql_mode = 'oracle'. What they basically try to do is to implement a subset of the Oracle PL/SQL language. Because we receive more and more request from customers about MariaDB’s Oracle PL/SQL it is worth investigating a bit more in this feature and summarize the state of the art of this topic in this article.

See also our former articles about the MariaDB sql_mode = 'oracle':

• Select Hello World FromDual with MariaDB PL/SQL
• MariaDB PL/SQL Examples

Items found in the MariaDB Jira database

If you look at the items in the MariaDB Jira database you can get some valuable information and see some trends.

It is a bit tricky to search the database because of the various different labels (Compatibility, Oracle, PL/SQL) and keywords. You will not find all items in one search. Please let us know if you find some more items we do not track yet!

In MySQL 8 the MySQL Data Dictionary was placed inside the InnoDB Storage Engine. This has some impact on multi-tenant applications:

Parameters used:

Sometimes customers want to have a very simple Galera Cluster set-up. They do not want to invest into machines and build up the know-how for load balancers in front of the Galera Cluster.

For this type of customers there is a possibility to just run a VIP controlled by Corosync/Pacemaker in front of the Galera Cluster moving an IP address from one node to the other. But this is just an active/passive/passive set-up and reads and writes are only possible to one node at the time.
So you loose the scaling read/write and load-balancing functionality and just have the high availability feature left.

Corosync/Pacemaker

A few words upfront about Corosync/Pacemaker:

Pacemaker is a Cluster Resource Manager (CRM) (similar to InitV or SystemD). It “is the thing that starts and stops services (like your database or mail server) and contains logic for ensuring both that they are running, and that they are only running in one location (to avoid data corruption).”
[ 1
]

Corosync on the other hand is the thing …

Thank you very much for your reply. I have not found an error in my conversion process. My DB is in latin1 and I am going to convert to utf8 or utf8mb4. I was unsure if the conversion would in its self cause errors in the text and binary data already present in the DB. You have, I believed, answered that. It should not. Your examples are interesting. I will need to study them to understand them better.

Thank you again for replying!

Cheers, Hal

Hello Hal

I do not really get your question. Do you have a good and reproducible example for your question?

The MySQL documentation states in Changing the Character Set:

The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8).

So the conversion SHOULD happen without an error. Everything else I consider a bug and should be filed as such.

But it theoretically CAN result in errors... Do you have an example for such an error?

I would say, that latin1 is technically not an exact sub-set of utf8mb4 because of the following example:

SQL> INSERT INTO test VALUES (NULL, 'äöü', NULL);

SQL> SELECT HEX(data) FROM test;
+--------------+
| hex(data)    |
+--------------+
| C3A4C3B6C3BC |
+--------------+

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4;

SQL> SELECT HEX(data) FROM test; …

Can altering the table character set to utf8mb4 cause error or special characters to appear in existing text or binary (blob) data. That is when ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4 is executed will existing latin1 text be converted properly. Perhaps this is a mute point as I believe latin1 is part of UTF8MB4, but I am not sure.

Thanks for any insight!!

Hal Richman

We see quite often customers complaining that their Galera Cluster is not stable and “crashes” from time to time. As always one has to investigate before rating.

What comes out quite often is that the customer (or better their developers) are running huge transactions.

In general transactional database do NOT like huge transactions because of various reasons (MVCC, ROLLBACK, UNDO, Locking etc.). They can do it. But they are not quite good in doing it and they do not like it. Instead you should better do many smaller transactions which you can run in parallel to keep the throughput. But: This causes more work for the one who should doing this transactions and needs more intelligence in the code…

Galera Cluster itself has some hard limits:

SQL> SHOW GLOBAL VARIABLES LIKE 'wsrep%ws%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| wsrep_max_ws_rows | 0          |
| wsrep_max_ws_size | 2147483647 |
+-------------------+------------+ …

The receipt above seems also to work with Percona Xtrabackup:

First you have to disabled the PXC strict mode: pxc_strict_mode = disabled. Then you have to copy the *.{exp|cfg|ibd} files to the right location and import the tablespaces again.

We further found, that an --export on an already prepared backup seems to be possible with xtrabackup.

Source: Github: codership / documentation

Codership Blog for Galera Cluster with release announcements.

Galera Plugin version 4 Series

Galera plug-in 26.4.21 Release Notes, Release Date: 12 December 2024

Galera plug-in 26.4.20 Release Notes, Release Date: 2 August 2024

Galera plug-in 26.4.19 Release Notes, Release Date: 1 July 2024

Galera plug-in 26.4.18 Release Notes, Release Date: 26 March 2024

Galera plug-in 26.4.17 Release Notes, Release Date: 8 January 2024

Galera plug-in 26.4.16 Release Notes, Release Date: 18 August 2023

Galera plug-in 26.4.15 Release Notes, Release Date: 16 June 2023

Galera plug-in 26.4.14 Release Notes, Release Date: 8 March 2023

Galera plug-in 26.4.13 Release Notes, Release Date: 24 November 2022

Galera plug-in 26.4.12 Release Notes, Release Date: 16 May 2022

Galera plug-in 26.4.11 Release Notes, Release Date: 14 February 2022

Galera plug-in 26.4.10 Release Notes, Release Date: 15 November 2021

Galera plug-in 26.4.9 Release Notes, Release Date: 26 July 2021

Galera plug-in 26.4.8 …

Some of my colleagues mentioned Window Functions. Maybe it works. But I fear that window functions are not fast because they do some materialization in between? I have to test...

For a fancy application we want to query a chunk of rows from a table and therefore we need the minimum and the maximum of the Primary Key of these rows.
Because InnoDB is an Index Organized Table or Index Clustered Table we know that this access will use the Primary Key. But to be sure and to be compliant with the standard (and compatible) we use and ORDER BY on the Primary Key.

MySQL 5.7

First we create some test data:

mysql> CREATE TABLE t_my (
  ID CHAR(32) NOT NULL PRIMARY KEY
) ENGINE = InnoDB;

mysql> INSERT INTO t_my
SELECT MD5(RAND())
FROM t_my;
... create more than 10 rows

mysql> SELECT id FROM t_my ORDER BY id LIMIT 11;
+----------------------------------+
| id                               |
+----------------------------------+
| 01a6e76643c83c91867636ce90a8def5 |
| 0ea1b1670343b4e70dd449207c720957 |
| 141ec92e809c1d6af83d27e8a3e74fe7 |
| 1605890e2c0244b019e6f66cc94790f2 |
| 19826d67b6013ed3bc1105b9708959c4 |
| 1a9ffd320187831df939d596c9a50aa1 |
| 24ae3a883803f5ae8416754593cd881c |
| …

We got some feedback from Paul Campbell about Handler_read_first:

Thought you might like to know that you can get do this without a full index scan (in 5.7 at least) with a query for SELECT MIN(pkey), only the first key is read in this instance. SELECT MAX(pkey) will do the same for Handler_read_last.

e.g.

mysql> EXPLAIN select min(a) from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away | …

FromDual has the pleasure to announce the release of the new version 2.2.3 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for brman).

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.3

shell> cd ${HOME}/product
shell> tar xf /download/brman-2.2.3.tar.gz
shell> rm -f brman
shell> ln -s brman-2.2.3 brman

Changes in FromDual Backup and Recovery Manager 2.2.3

This release is a new minor release. It contains only bug fixes. We have tried to …

Table of Contents

• How to make MariaDB Pull Replication as secure as possible
• MariaDB Push Replication
• Pushing data with the FederatedX Storage Engine and Triggers
• Traffic mirroring with MariaDB MaxScale or ProxySQL

How to make MariaDB Pull Replication as secure as possible

A normal MariaDB Replication is a Pull Replication. This means that a Slave connects to its Master and gathers or better requests Binary Log information from the Master and applies them in a streaming way.

In some set-ups the Slave is located in a less secure network zone and the Master is located in a more secure network zone. So from the security point of view a permanent connection from the less secure zone to the more secure zone is sometimes not acceptable. We had those discussions already 2 times in the last few months with Chief Security Officers (CSO) of our clients.

Arguing for the MariaDB Pull Replication

How can you secure the Master/Slave set-up in this case:

• On the Master:
  • There has to be a user with the REPLICATION SLAVE …

We recently had the situation that a customer complained about the Oom killer terminating the MariaDB database instance from time to time. The MariaDB database configuration was sized quit OK (about 50% of RAM was used for the database) but they did not have swap configured.

When we checked the memory for the specific mysqld process we found that VSZ was about 80 Gibyte (on a 64 Gibyte machine) and the RSS size was about 42 Gibyte. The very high VSZ value in combination with a lacking swap space and Oom killer let the alarm bells ring.

This customer was using a significant amount of (temporary) MEMORY tables (instead of TEMPORARY TABLE ... ENGINE = MEMORY) which are suspect to be the evildoer.

To verify if this could be the reason for the odd behaviour we have to know how MEMORY tables behave related to VSZ from the O/S point of view.

Creation of MEMORY table 1 (12 - 14):

SQL> SET GLOBAL max_heap_table_size = 1024*1024*1024;
SQL> SET SESSION max_heap_table_size = 1024*1024*1024;

SQL> CREATE TABLE …

An inside view from the MariaDB database instance can be received with:

SQL> SHOW GLOBAL STATUS LIKE 'memory_used';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Memory_used   | 3235456112 |
+---------------+------------+

Here the memory is listed as released when the TRUCATE TABLE is done.

I just got a note from Markus Makela with the following suggestion:

You could try and see if the mirror router from the experimental module package would work as a substitute for this.

Recently we had the case that a customer claimed that MariaDB 10.3 Binary Log is using 150% more space on disk than MySQL 5.7 Binary Log. Because I never observed something similar, but to be honest, I did not look to intensively for this situation, we had to do some clarifications.

First we checked the usual variables which could be candidates for such a behaviour:

binlog_format                = ROW
binlog_row_image             = FULL
binlog_rows_query_log_events = OFF   # MySQL only
binlog_annotate_row_events   = OFF   # MariaDB equivalent
log_bin_compress             = OFF   # MariaDB only

Those were all equal on MariaDB and MySQL. So is was not a trivial case to solve.

The customer did not like the suggestion to just increase the disk space. So we had to dig further…

In the MariaDB Enterprise support ticket we have noticed that the MariaDB support engineer tried to use MariaDB MaxScale to reproduce our problem (without success by the way). So time to try it out ourself because we have some other …