Create a link to a remote server and access data using CONNECT SE

In this blog we are going to see how to create a link to a remote server and use it to access multiple tables at once.
In the previous blog we have seen how to establish a remote connection between Docker containers.
The way we did it was to specify the connection string to reference a single table only.
But what if we need more tables, what if need a whole database?

The solution is to link to a remote database with the CREATE SERVER statement.
A link obtained this way can be passed to the CREATE TABLE statement of a storage engine (SE) to make a connection where using the table discovery feature SE will find out about the table fields and create the table.

Tricky Problems? MariaDB debug container

MariaDB does have bugs. Users see them sometimes. Sometimes developers look for a long time at bug reports and code and still cannot see how the situation occurred. Developers during their analysis ask questions like:

  • I wonder if this was already fixed in {not released version}? But how can I ask a user to test that?
  • Can I get the user to get good stack trace that would help understand this better? But users sometimes find this hard.
  • What exact hardware and kernel configuration is this bug occurring on? And how would I reproduce this?

New Service – quay.io/mariadb-foundation/mariadb-devel

During the development of MariaDB, a lot of things are tested. However the most important workload to be tested is the one we don’t have access to, and that is your workload.

As many of you run your own CI, we’d like you to invite you to join the testing of MariaDB. quay.io/mariadb-foundation/mariadb-devel is a container repository using the latest from our main stable branches. By the time any code gets into these branches it has been reviewed and passed our tests. The developers of the change consider it finished, so this is the perfect time to take this code and test it on your workload.

Using CONNECT SE to access remote JSON data with demo examples in Docker

CONNECT is a storage engine (SE) plugin used to access external, local or remote data. In this blog we are going to show how to install the CONNECT storage engine in a Docker container and how to share JSON data between containers.

Enabling CONNECT SE plugin in Docker

CONNECT SE needs to be installed within the container in order to use it. To see how to do that please check Installing plugins in the MariaDB Docker Library Container.

Create JSON data on remote server

The most important feature of CONNECT SE to MariaDB is the flexibility to create tables from various data sources, like the same database and other DMBS’s tables or files with different formats.

Installing plugins in the MariaDB Docker Library Container

MariaDB plugins are software components that may be added to the core software without the need to rebuild the MariaDB Server. Plugins can be storage engines, additional security requirements, special log information about the server and others. MariaDB has a large number of built-in plugins which are permanently installed (listed in SHOW PLUGINS query). Plugins can be loaded at start-up, during initialization, or loaded dynamically when the server is running.

In this blog we are going to see how to list available plugins in the MariaDB container as well as the methods of installing plugins in a container.

10.7 feature preview: Miscellaneous features

The “Misc features” preview includes all the other features that did not make it into a separate dedicated preview binary.


MariaDB Server 10.7 includes the JSON_EQUALS function, which compares inputs as JSON objects, regardless of whitespace, key order, or number format.

(more…)

10.7 preview feature: Password Reuse Check plugin

By default, MariaDB does not check if a user reuses a password. Some security policies require users to choose a new password each time, and the Password Reuse Check plugin, available in a MariaDB 10.7.0 preview, enables this functionality.

Old passwords are stored in the mysql.password_reuse_check_history table, and the period they are retained for is determined by the password-reuse-check-interval system variable, which specifies a number of days. By default this is zero, meaning unlimited retention.

The password can be used in conjunction with other password validation plugins, such as the Simple Password Check plugin or the CrackLib Password Check plugin.

10.7 preview feature: CONVERT PARTITION

If you are using table partitioning, you have likely heard of the ALTER TABLE … EXCHANGE PARTITION … WITH TABLE … command. It existed in MariaDB since forever. But if you check the manual (any manual) or search the web, you will see that almost the only use case of it is converting a partition to a standalone non-partitoned table, or converting a standalone non-partitoned table into a partition.

And the usage was designed back then to be anything but obvious. To convert a partition to a table you need first to create an empty table with the same structure as a partition, then you exchange it with a partition, and then you drop the empty partition.