Mariadb

In MariaDB master/slave replication there is a feature called attribute promotion/demotion.

Simply put, it is about how the slave behaves or should behave if the master and slave have different column definitions or even a different number of columns or a different sequence of columns.

Use case of the customer

This week we discussed with a customer the case of how he could perform a rolling schema upgrade (RSU) in a Galera cluster.

With previous schema changes he has always had problems, which has led to a …

In our MariaDB advanced training courses, which we hold approximately every two months, we use machines that are directly exposed to the Internet with a public IP address. Warning: You should NEVER expose a database directly to the Internet without protection! Typically, it takes less than 72 hours (3 days) before we are exposed to the first external access attempts.

This looks something like this in the MariaDB error log:

[Warning] Aborted connection 22939 to db: 'unconnected' user: 'unauthenticated' host: …

Artificial intelligence (AI) and vector databases are on everyone’s lips these days. Since MariaDB will soon be coming onto the market with vector database functionality, as a database consultant I thought it was about time I got to grips with the subject so that I have at least a hint of what it’s all about…

Since I’m not so much of a theoretician but rather like to do something practical, I’ve built a small “AI” prototype that anyone can build very quickly and …

Due to an application error, our replication stopped for 5 days (over Easter). After the problem was solved, the replication was supposed to catch up, which turned out to be very slow. All the usual tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) had already been exhausted. So we tried our hand at parallel replication of the MariaDB server.

Parallel replication is deactivated by default:

SQL> SHOW GLOBAL VARIABLES LIKE '%parallel%';
+-------------------------------+------------+
| …

Recently I had to test a new MariaDB feature that was developed at our request (MDEV-33782). To test this feature I had to build the MariaDB server myself from source, which I have not done for a long time. So a new challenge, especially with CMake…

I followed the MariaDB documentation Get, Build and Test Latest MariaDB the Lazy Way to build the server.

On Ubuntu 22.04 it did not work for me, for reasons unknown to me. So I cloned an Ubuntu 23.04 (Lunar Lobster) LXC container and built the MariaDB …

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (M_a) and one passive Master (M_p). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. …

Storage Engines like InnoDB, Aria and MyISAM are Row Stores. They store rows one after the other in blocks or even directly in a single file (MyISAM). On the other hand a Column Store like MariaDB ColumnStore stores all the same attributes (columns) of the rows together in chunks.

This is how the table sales_fact looks like:

CREATE TABLE `sales_fact` (
  `product_id` int(11) NOT NULL,
  `time_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `promotion_id` int(11) NOT NULL,
  `store_id` int(11) NOT …

Table of Contents

• Hardware requirements
• O/S (Linux) settings
• Installation
• Configuration
• Cross Engine Join User
• Start ColumnStore
• Create the Cross Engine Join User in the database
• MariaDB ColumnStore schemas
• Server System Variables and Server Status Variables
• MariaDB ColumnStore on the file system
• Creating our first table
• Querying and changing data in MariaDB ColumnStore
• MariaDB ColumnStore using sequences instead of AUTO_INCREMENT
• MariaDB ColumnStore Query Execution Plan

For a long time I wanted to …

MariaDB Enterprise Platform

The MariaDB Enterprise Platform is available on-premises and on the MariaDB SkySQL DBaaS (Cloud database platform). MariaDB Enterprise Platform consists of:

MariaDB Enterprise Server (MariaDB database server)

MariaDB Xpand (distributed database, scaling writes, former ClustrixDB)

MariaDB Enterprise ColumnStore (former InfiniDB used for data warehouse (DWH), BI, Reporting)

MariaDB MaxScale (Load Balancer)

MariaDB Connectors

• MariaDB Connector/C++
• MariaDB Connector/R2DBC (Reactive …

If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size, …) are set appropriately but also if some MariaDB server system variables are configured completely wrong.

Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES view where you can find all the relevant information. But one!

Since MariaDB 10.5 we can also see from which file the MariaDB server system variable …

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 …

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 …

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 …

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 …

Because we conduct many customers in MariaDB Galera Cluster upgrades and because these customers sometimes have pretty old MariaDB Galera Cluster set-ups I think it is good to have a rough MariaDB Galera Cluster Upgrade Path.

For an Upgrade Path we have to consider a few things:

• We face different MariaDB Galera Cluster version (5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5 and soon 10.6).
• We face different Galera plug-in versions (v2, v3 and v4). Direct upgrade from v2 to v4 is not possible. Upgrade from latest …

Because upgrading from MariaDB 10.4 to MariaDB 10.5 (non-clustered) seems not to be a problem
[ 1
] we take the challenge and try to create a receipt based on the MariaDB 10.3 to MariaDB 10.4 Galera Cluster upgrade documentation
[ 3
]:

Before you start

Before you begin with the upgrade you should consider a few things:

• Downgrade is officially not supported!
  [ 4
  ] It might work, or not.
• So you should have taken a proper an clean backup before you start with the upgrade and you should be sure the restore …

Table of Contents

• Global Options
• Service Options
• Server Options
• Routing Modules
• Listener Options
• Monitor Options
• Filter Options

Global Options

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something “new” I did not know yet…

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were …

Table of Contents

• Before you start
• Infinite Loop Example

Before you start

SQL> SET SESSION sql_mode='ORACLE';

Infinite Loop Example

DELIMITER /

BEGIN

LOOP
  SELECT 'Hello world from MariaDB anonymous PL/SQL block!';
END LOOP; 

END;
/

DELIMITER ;

Up to version 5.5 MariaDB and MySQL can be considered as “the same” databases. The official wording at those times was “drop-in-replacement”. But now we are a few years later and times and features changed. Also the official wording has slightly changed to just “compatible”.
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and …

The MySQL sys Schema is a cool thing and was mostly developed by Mark Leith and Jesper Wisborg Krogh from Oracle/MySQL. The sys Schema is based on the somehow cryptic MySQL PERFORMANCE_SCHEMA which was developed by Oracle/MySQL as well.

The sys Schema is: “A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.”

Unfortunately the sys Schema was not ported from MySQL to MariaDB by the company MariaDB Corp. so far. So a good opportunity …

Table of Contents

• General findings
• Migration from MySQL 5.x to MariaDB 5.5
• Migration from MySQL 5.0 to MariaDB 10.3
• Migration from MariaDB 10.0 to Percona Server 5.6
• Migration from MySQL/Percona 5.6 to MariaDB 10.0
• Migration from MySQL 5.7 to MariaDB 10.2
• Migration from MySQL 5.7 to MariaDB 10.3
• Migration from MySQL 5.7 to MariaDB 10.4
• Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
• Migration from MySQL 5.7 to MariaDB 10.5
• Migration from MySQL 5.7 to MariaDB 10.6
• Migration from …

Since January 2019 FromDual tries to contribute actively a little bit against global warming too.

The best for the climate would be to NOT travel to the customer at all! For this cases we have our FromDual remote-DBA services for MariaDB and MySQL.

But sometimes customer wants or needs us on-site for our FromDual in-house trainings or our FromDual on-site consulting engagements. In these cases we try to travel by train. Travelling by train is after walking or travelling by bicycle the most climate friendly …

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values… And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data VARCHAR(32) DEFAULT NULL
, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
); …

MariaDB 10.3 was released GA a few weeks ago. One of the features which interests me most is the MariaDB Oracle PL/SQL compatibility mode.

So its time to try it out now…

Enabling Oracle PL/SQL in MariaDB

Oracle PL/SQL syntax is quite different from old MySQL/MariaDB SQL/PSM syntax. So the old MariaDB parser would through some errors without modification. The activation of the modification of the MariaDB PL/SQL parser is achieved by changing the sql_mode as follows:

mariadb> SET SESSION …

FromDual as a neutral and vendor independent database service company also provides various services for the MariaDB database.

With our training partners in Germany and Switzerland we provide regular trainings for MariaDB beginners, experienced MariaDB DBAs and MariaDB Galera Cluster administrators.

For MariaDB users without their own DBA FromDual provides remote-DBA services for MariaDB. We become your MariaDB DBAs!

If you prefer to see one of our consultants on-site FromDual visit and help you solving …

MySQL has introduced the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL 5.6 and added some enhancements in MySQL 5.7 and 8.0.

Unfortunately the PERFORMANCE_SCHEMA was not really intuitive for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. Fortunately the sys Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it for MariaDB as well. The version of the …

The Chemnitz Linux Days 2018 in Chemnitz (Germany) 10/11 March 2018 are over for more than a week now and IMHO it was a huge success.

I was following many very interesting talks, met a lot of interesting people and learned a lot!

For all those who could not follow our presentation about New Features in MariaDB 10.2 (PDF, 683 kib) the presentation slides are on-line available.

If you want to hear the presentation live you can join us at the SLAC 2018 in Berlin.

The Docker version of the distributions are often quite old. On Ubuntu 16.04 for example:

shell> docker --version 
Docker version 1.13.1, build 092cba3

But the current docker version is 17.09.0-ce (2017-09-26). It seems like they have switched from the old version schema x.y.z to the new year.month.version version schema in February/March 2017.

Install Docker CE Repository

Add the Docker’s official PGP key:

shell> curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
OK …

Important: FromDual does NOT recommend to use an automated VIP failover technology as described below for a MariaDB/MySQL Master/Master Replication. In such a topology we recommend a manual VIP failover with prepared scripts!

Some of our customers still want to have old-style MariaDB Master/Master Replication Clusters. Time goes by, new technologies appear but some old stuff still remains.

The main problem in a Master/Master Replication set-up is to make the service highly available for the application …

What is SELinux?

The Security-Enhanced Linux is an extension to the Linux Kernel, made by the NSA (National Security Agency). It implements Mandatory Access Controls (MAC), which allow an administrator to define, how applications and users can access resources on a system.

There is more detail in the SELinux Wki: https://selinuxproject.org/page/FAQ
… and the CentOS documentation: https://wiki.centos.org/HowTos/SELinux

Some distributions have it installed by default, but not active, some have it …

MySQL is well known and widely spread because of its philosophy of Keep it Simple (KISS).

We recently had the discussion that with newer releases also MySQL and MariaDB relational databases becomes more and more complicated.

One indication for this trend is the number of MySQL server system variables and status variables.

In the following tables and graphs we compare the different releases since MySQL version 4.0:

mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb%';
mysql> …

As you probably know MySQL is an Open Source product licensed under the GPL v2. The GPL grants you the right to not just read and understand the code of the product but also to use, modify AND redistribute the code as long as you follow the GPL rules.

This redistribution has happened in the past various times. But in the western hemisphere only 3 of these branches/forks of MySQL are of relevance for the majority of the MySQL users: Galera Cluster for MySQL, MariaDB (Server and Galera Cluster) and Percona …

If you have already Red Hat Enterprise Linux 7 (RHEL 7) or CentOS 7 in use you probably found out, that it is not that easy to install MySQL Enterprise Server because there are already pre-installed MariaDB 5.5 libraries:

shell> yum localinstall mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-server-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-common-5.7.16-1.1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining …

• max_user_connections Can be changed online in MySQL. Cannot be changed in MariaDB if value was set to 0.
• PERFORMANCE_SCHEMA is enabled in MySQL 5.6 and 5.7 by default. In MariaDB disabled by default.
• Replication incompatibilities from MariaDB (Master) to MySQL (Slave)
• MariaDB 10.2.0 still contains XtraDB 5.6. So MySQL 5.7 features cannot be used.
• MariaDB 10.2 Window Functions are missing in MySQL 5.7
• SHOW PROCESSLIST has additional column Progress in MariaDB.
• Progress indication in: mysqldump …

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions, Window Functions, Window function and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;
+-----+-------------+
| num | …

FromDual has the pleasure to announce the release of the new version 1.0.1 of the FromDual Nagios and Icinga plugins for MySQL, Galera Cluster and MariaDB.

Download

The new FromDual Nagios plugins can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Nagios plugins please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for Nagios plugins). …

You are already a database application developer or on the way there. Now you would like to learn more about, what MySQL and MariaDB features are available which you can use later to build even better applications, to develop more efficiently applications and shorten your time to market.

Requirements

You are already familiar with the SQL language and have already some experience with MySQL or any other relational SQL database system.

Audience

• Future MySQL application developer
• MySQL and MariaDB database …

We offer our MySQL and MariaDB trainings together with well-respected training organizations: The Linuxhotel in Essen (Germany), the Heinlein Academy in Berlin (Germany) and GFU Cyrus AG in Cologne (Germany). Our own MySQL and MariaDB training classes are hold in the building of the HSO in Zürich (Switzerland).

If you prefer a MySQL or MariaDB in-house training at your location and if you provide the infrastructure we are happy to train you on-site either in German or English as well.

Further dates or dates …

FromDual offers different MySQL, Galera Cluster and MariaDB training modules. For training dates see our MySQL and MariaDB training class schedule.

SQL for Beginners (3 days)

This training is made for all who want to learn the basics of the SQL language. Future DBAs, database developers and data analysts.

MySQL and MariaDB for Beginners (2 days)

This MySQL and MariaDB training is made for those who want to learn the basics of the MySQL database system. Future MySQL DBAs and MySQL database developers.

MySQL …

FromDual Performance Monitor for MySQL and MariaDB including MaaS

The FromDual Performance Monitor for MySQL, Galera Cluster, MariaDB and Percona Server is a Monitoring solution based on Zabbix. Its various alerts, advisors and graphs help you to monitor and operate your MySQL databases. This solution is also available as Monitoring as a Service solution if you want to avoid the hassle of installing your own monitoring solution.

Get the Latest release.

Database migration tools

A generic database migration tool to MySQL and MariaDB is the MySQL Workbench.

An alternative is the old MySQL Migration Tool Kit.

Recommended by MariaDB Foundation: Sqlines.

Adabas D to MariaDB migration

Project migrating an Adabas D database to MariaDB is in progress. More information are expected before end of 2015.

Oracle to MySQL or MariaDB migration

Data Migration Tools

• MyOraDump is a tool to export data from an Oracle database to MySQL or MariaDB. …

FromDual is pleased to announce the release of a new version 1.0.0 of the Nagios and Icinga plugins for MySQL, MariaDB, Percona Server and Galera Cluster.

Any information about the changes and the new functions of the Nagios and Icinga plugins you can find here.

If you have any problems with the new version you can reach us by e-mail at: contact@fromdual.com or by phone on +41 44 500 58 20.

Your FromDual Team

Table of Contents

• MariaDB Lifetime Support Matrix for MariaDB Releases
• MariaDB Corp Enterprise Support Subscriptions
• MySQL Lifetime Support Matrix for MySQL Releases
• Oracle Corp Enterprise Support Subscriptions for MySQL

Lifetime Support Matrix for MariaDB Releases

This week, we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

We stopped the MariaDB 10.0 server, replaced the binaries by the Percona Server 5.6 binaries, and started the …

Will you be at a loss having a MySQL or MariaDB database problem? Will you need urgently help from experienced FromDual staff when your production MySQL or MariaDB database stops working? Or do you need a third party opinion for one of your MySQL or MariaDB solutions?

FromDual offers you vendor independent database support for MySQL (community and enterprise edition), MariaDB (community and enterprise edition) and Percona Server for all versions starting with 4.0.

How we provide support?

Our FromDual …

Overview

• MySQL Security Products
• Useful Security Links
• Security Vulnerabilities Fixed in MariaDB
• Oracle Critical Patch Update (CPU) Advisory for MySQL
• RedHat/CentOS vulnerabilities by CVE name
• Ubuntu Security Notice (USN) for MariaDB
• Ubuntu Security Notice (USN) for MySQL
• Debian Security Advisories
• Debian Security Advisories for MySQL
• MySQL Exploits
• Debian Security Advisories for MariaDB
• MariaDB Exploits
• MariaDB Security Risk Matrix
• Cyber Emergency Response Team (CERT) in Australia and APAC
• MariaDB …

FromDual cooperates with the following companies:

Topic	Company	Description
MariaDB/MySQL Training	Linuxhotel GmbH	The Linuxhotel is one of the most important Open Source Software training and meeting places in German-speaking countries.
MariaDB Support	MariaDB AB	FromDual helps you to get into MariaDB Enterprise Subscriptions and Services.
MariaDB/MySQL Training	GFU Cyrus AG	GFU …

Henrik Ingo from the Open Database Alliance (ODBA) did an interview with Oli Sennhauser of FromDual about the European MySQL database landscape and its future. If you want to know more about our opinion in this matter please read here.

For more technical insight see also our former presentation: MySQL, where are you going?.

The following presentations are available:

FromDual delivers neutral and vendor independent on-site consulting services on MariaDB, MySQL, Galera Cluster and Percona Server. Our experienced consultants recommend you the best solutions fitting to your needs.

We help you in delicate matters as:

• Getting the maximum out of your current hardware through Performance Tuning and SQL Query Tuning by adding indexes where necessary.
• Evaluating more advanced Database Architectures like Master/Slave Replication or synchronous multi-Master Replication with …