You are here

MySQL Tech-Feed (en)

MariaDB/MySQL Environment MyEnv 2.0.3 has been released

Shinguz - Fri, 2021-07-02 15:32

FromDual has the pleasure to announce the release of the new version 2.0.3 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

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

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.x to 2.0.3 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.3.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.3 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.3 MyEnv
  • MariaDB 10.6 my.cnf hash added and bug in stop instance for fqdn fixed.
  • Function print replaced by output() function.
  • Function checkDataseOld function removed.
  • Functions checkDatabase, startDatabase and stopDatabase replaced by their Instance analogon.
  • Feature: CDPATH added to variables.conf.template pointing to instancedir.
  • Function print replaced by output in function checkMyEnvRequirements.
  • log_warnings commented out in my.cnf.template to make it work with MySQL 8.0.
  • Debug information improved.
  • my.cnf updated with new 8.0 findings.
  • Ubuntu tag fixed.
  • Variable gcache.recover added to template.
  • Error handling changed from procedural style to OO style.
  • Naming of return values fixed.
  • Function my_exec made variable naming more clear.
  • InnoDB monitor enabled by default.
  • Constants made more human readable.
  • Variable sync_binlog in my.cnf template adjusted to reasonable value.
  • my.cnf template updated to newest state.
  • New MySQL 5.7 Ubuntu repo hash for my.cnf added.
  • Package php-posix replaced by php-process on Redhat/CentOS.
  • Branch Homebrew added to extract Branch for MacOS.
  • Function formatTime added.
  • Comments changed from php5 to php7.
  • New MySQL 8.0 tmp schema #innodb_temp added to hideschema parameter.
  • Socket is right now when port is not 3306.
  • New interfaces for start/stopping instances and code cleaned up.

MyEnv Installer
  • Skipping init script is possible now in installMyEnv.
  • Instance proposal is now checked if it already exists in add instance in installMyEnv.
  • On Debian systems now systemd unit file advice is shown as well instead of initV files advice in installMyEnv.

MyEnv Utilities
  • Using a password on the command line hack replace by better solution for some utilities.
  • Utility pcs_standby_node.sh added.
  • Utilities: connect_times.php and galera_monitor.sh added.
  • Utilities: mixed_test.php, binlog_push.php and binlog_apply.php added.
  • VIP v2 start stop script added (vip2.sh).
  • Utility netstat.php added.
  • Utility insert_test.bat added and is working now like on Linux.
  • insert_test.sh made more flexible to run modify column command.
  • insert_test.php made ready for new 5.7/8.0 timestamp behaviour.
  • Utility connect_test.php added.
  • MySQL Group Replication monitor mad configurable and output made nicer.

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleasemysqld_multi

Query performance comparison between MariaDB Column Store and other Storage Engines

Shinguz - Thu, 2021-06-03 15:53

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 Column Store 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 NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL ) ENGINE=ColumnStore DEFAULT CHARSET=utf8; 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 NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL, KEY `i_sales_customer_id` (`customer_id`), KEY `i_sales_product_id` (`product_id`), KEY `i_sales_promotion_id` (`promotion_id`), KEY `i_sales_store_id` (`store_id`), KEY `i_sales_time_id` (`time_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As you may have noted the Column Store table does NOT contain indexes!

And this shows how it is stored in memory and on disk (left Row Store, right Column Store; in reality it is a bit more complicated):

     

Considering this and depending on your queries a Column Store can have some significant response time advantages compared to a Row Store. And the other way around as well!

Comparison with MyISAM

So let us compare some queries with the same set of data. We are still working with one single-node MariaDB Column Store as described here.

We have chosen MyISAM for comparison with MariaDB Column Store because it is the fastest storage engine we have so far for low concurrency SELECT statements. As data set we have used the good old foodmart schema prepared for Column Store. We concentrated on the sales_fact table because this is the table with the biggest amount of rows. The MyISAM key_buffer_size was set to 128 Mibyte which is big enough for all the indices.

Simple SELECT queries SELECT COUNT(*) FROM table

As experienced MyISAM users know already for this query MyISAM is unbeatable fast:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact; 1 row in set (0.040 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact; 1 row in set (0.000 sec)

This query is mainly to make sure that both tables have the same amount of data...

SELECT * FROM table or CHECKSUM TABLE table

In these queries we do a full table scans. This is probably the worst pattern you can do to Column Store. And MyISAM is significantly faster in this:

SQL> SELECT * FROM foodmart_cs.sales_fact; 1078880 rows in set (1.833 sec) SQL> SELECT * FROM foodmart_myisam.sales_fact; 1078880 rows in set (0.607 sec)

But this pattern is NOT what a Column Store is made for...

The Column Store architecture consists of 2 different types of modules: The User Module (UM) consisting of the MariaDB Server (mysqld), the Execution Manager (ExeMgr) and the Distribution Managers (DMLProc, DDLProc and cpimport). These processes are responsible for parsing SQL and distributing and executing the SQL statements.

The other type of module is the Performance Module (PM) consisting of the Managing and Monitoring Process (ProcMgr and ProcMon), the Primary Process (PrimProc) which handles the query execution and the Performance Module process which handles loads and writes (WriteEngineServer and cpimport). The Performance Module basically performs the work and does the I/O operations.

So we have the communication from the MariaDB server to the User Module to the Performance Module and this in normal situation over a network. So it is obvious that this costs a lot of time for huge data sets.

This can be shown when we execute the CHECKSUM TABLE command which does similar things like SELECT * FROM table but does NOT return the full result set:

SQL> CHECKSUM TABLE foodmart_cs.sales_fact; +------------------------+------------+ | Table | Checksum | +------------------------+------------+ | foodmart_cs.sales_fact | 2218293488 | +------------------------+------------+ 1 row in set (1.370 sec) SQL> CHECKSUM TABLE foodmart_myisam.sales_fact; +----------------------------+------------+ | Table | Checksum | +----------------------------+------------+ | foodmart_myisam.sales_fact | 2218293488 | +----------------------------+------------+ 1 row in set (0.853 sec)

It is interesting that Column Store is less slower here compared to the full table scan. It looks like it can already parallelize some of the work in this step already?

SELECT min(column), max(column) FROM table

Now let us come to more data warehouse (DWH) like queries:

SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_cs.sales_fact; 1 row in set (0.104 sec) SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; 1 row in set (0.001 sec)

Also here MyISAM is horribly fast because it can short cut:

SQL> EXPLAIN SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

It would be interesting to compare those queries with InnoDB which cannot do these short cuts.

SELECT column, COUNT(*) FROM table GROUP BY column

This is a query where MyISAM cannot shortcut and we see already an advantage for MariaDB Columns Store.

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id; 673 rows in set (0.099 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id; 673 rows in set (0.138 sec)

It would be interesting to see those number with really huge amount of rows (109 to 1010 rows) which do not fit into caches any more...

SELECT column, COUNT(*) FROM table GROUP BY column ORDER BY column

The last result was pretty much chaotic. So let us test an ordered result:

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.100 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.136 sec)

The ORDER BY is probably executed in the MariaDB Server. So for this small data set the network communication can be ignored. No difference observed.

SELECT COUNT(*) FROM table where column >= value

This SELECT queries data from a very big range (99%). We know MyISAM uses the index by doing an index-only-scan. MariaDB Column Store does NOT have indexes:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.054 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.159 sec) SQL> EXPLAIN SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | sales_fact | range | i_sales_time_id | i_sales_time_id | 4 | NULL | 1055594 | Using where; Using index | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+

Column Store outperforms MyISAM by factor of 3 already with a small data set.

The same query with a very small range of data (1%):

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 1090; 1 row in set (0.042 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 1090; 1 row in set (0.005 sec)

Column Store becomes only slightly faster with the smaller result set but here MyISAM key usage has a dramatic impact. Would be interesting to see the difference if the MyISAM data/key cannot be kept in memory any more.

SELECT SUM(column3) FROM table WHERE column1 = value AND column2 BETWEEN value AND value SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.072 sec) SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.002 sec)

As soon a MyISAM has an index with high cardinality and a strong filter it outperforms Column Store.

Let us go a step back to the query with the big range (99%). But forcing MyISAM to do a table access instead of an index-only-scan:

SQL> SELECT SUM(store_sales) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.117 sec) SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.133 sec)

Ohh! Here the MariaDB optimizer was clever enough to see that a full table scan is cheaper than accessing the index:

SQL> EXPLAIN SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | sales_fact | ALL | i_sales_time_id | NULL | NULL | NULL | 1078880 | Using where | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+

So MyISAM is only slightly slower than Column Store. But if we force MyISAM to use the index it becomes dramatically slow (about 9 times):

SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact FORCE INDEX (i_sales_time_id) WHERE time_id >= 400; 1 row in set (1.040 sec)

So here again: MariaDB Column store starts making fun if a huge amount of data is used...

SELECT SUM(column3) FROM table WHERE column1 = value OR column2 = value

We know that WHERE clauses with OR are always bad for the optimizer. So let us try this:

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.209 sec) MariaDB [(none)]> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM sales_fact 4995 (promotion_id,store_id,store_sales,unit_sales) 0 3163 0 0.172 132 TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1

Column Store has to touch 4 out of 8 column (50%) to get the result. How the Elapsed time is calculated I have to investigate some more...

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.873 sec) SQL> EXPLAIN SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | 1 | SIMPLE | sales_fact | index_merge | i_sales_promotion_id,i_sales_store_id | i_sales_promotion_id,i_sales_store_id | 4,4 | NULL | 706357 | Using union(i_sales_promotion_id,i_sales_store_id); Using where | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+

MyISAM tries to short cut by an INDEX MERGE operation which we know is not super fast but still better than a full table scan. But still MariaDB Column Store is about 4 times faster than MyISAM.

More complex SELECT queries from DWH benchmarks.

I borrowed these queries from Øystein's blog:

SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_cs.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; ERROR 1815 (HY000): Internal error: IDB-3012: Scalar filter and semi join are not from the same pair of tables. SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_myisam.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; 1 row in set (0.184 sec)

So some more complex queries seems not to work with Column Store yet. I have to figure out yet what to do in this case...

So let us try some more complex queries from Sergei:

SELECT SUM(store_sales) FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set, 1 warning (0.210 sec)

We get a warning when using JOINs:

SQL> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 9999 | Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-2651; BlocksTouched-2651; PartitionBlocksEliminated-0; MsgBytesIn-41KB; MsgBytesOut-74KB; Mode-Distributed | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Seems to be some statistics about how the query/join was executed...

SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set (0.412 sec)

With JOINs I was a bit more sceptic because of my former experience with NDB but it looks like also with JOINs Column Store outperforms MyISAM with already a small amount of data.

The table customer is a typical dimension table so this would probably be a candidate for hybrid approach (HTAP)?

Converting the customer table to MyISAM did NOT make it significantly slower:

1 row in set, 1 warning (0.215 sec) +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows CES UM - - - - - - 0.013 4093 BPS PM sf 4995 (customer_id,store_sales,unit_sales) 0 2636 0 0.182 132 HJS PM sf-c 4995 - - - - ----- - TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1
Converting the foodmart DWH schema to MariaDB Columns Store

Converting the existing foodmart schema to MariaDB Column Store was a bit cumbersome...

We used the normal mysqldump and replace the storage engine:

$ zcat foodmart_dump.sql.gz | sed 's/ENGINE=InnoDB/ENGINE=ColumnStore/' | mysql --user=root foodmart_cs ERROR 1178 (42000) at line 25: The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

The command ALTER TABLE ... ENGINE = ColumnStore seems to be a bit more flexible but it is horribly slow...

The foodmart dump optimized for MariaDB Column Store can be found here.

Problems between MariaDB server and Column Store dictionary SQL> RENAME TABLE customer TO customer_a; Query OK, 0 rows affected (0.137 sec) SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.018 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync) SQL> DROP TABLE customer; Query OK, 0 rows affected (0.014 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1146 (42S02): Table 'foodmart_cs.customer' doesn't exist SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.017 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync)

To fix this the following DDL command will help:

SQL> CREATE TABLE customer (id INT) ENGINE = ColumnStore COMMENT='SCHEMA SYNC ONLY'; Query OK, 0 rows affected (0.006 sec)

See also: Recovering from error "Cannot create table: table already exists (your schema is probably out-of-sync)"

Conclusion

MariaDB Column Store can outperform MyISAM already with a single-node set-up and a small dataset if you are choosing the right queries. It would be interesting to see the performance gains with a multi-node set-up and a huge data set. I was told that MariaDB Column Store makes sense from 100 Gibyte upwards... Our data set was about 100 Mibyte!

Taxonomy upgrade extras: mariadbdwhColumnStorequeryperformancemyisam

Create a single-node MariaDB ColumnStore test installation

Shinguz - Wed, 2021-05-19 19:37
Table of Contents

For a long time I wanted to investigate a bit more into the MariaDB ColumnStore architecture. Now I took the time to start with.

MariaDB ColumnStore is an interesting technology if you want to do reports (OLAP, DWH, BI) over only a few columns on a huge amount of rows and if you have only or mostly batch writes. These data are ideally spread (sharded) over several machines and the results where calculated on those different machines an aggregated. Because the data are stored in columns the locality of your data should be better and thus a more efficient caching (in-memory) should result in faster response times for you reporting queries.

MariaDB Column Store can be deployed as a single-node or a multi-node set-up. The first one is considered for testing, the later one for production purposes.

For setting up a testing system I have chosen a single-node set-up on a VirtualBox VM first.

MariaDB ColumnStore hardware requirements

There are different informations available about the minimal recommended hardware resources. The range lasts from 8 to 32 physical cores with 16 to 64 Gibyte of RAM, HDD disks are fine because the system is optimized for block streaming (sequential read and write). At least an 1 Gbit network is recommended. [ 1 ]

For our first test we rely on 1 virtual Machine with 4 Gibyte of RAM (possibly with 2 Gibyte of RAM it still would work?), 10 Gibyte of HDD disk space and 1 vCPU.

O/S (Linux) settings

Check Linux kernel settings:

$ sysctl -a | grep -e '^vm.swap' -e '^vm.vfs' -e '^net.core..mem_max' -e '^net.ipv4.tcp_.mem' -e '^net.ipv4.tcp_no_metrics' -e '^net.core.netdev_max' net.core.netdev_max_backlog = 1000 net.core.rmem_max = 212992 net.core.wmem_max = 212992 net.ipv4.tcp_no_metrics_save = 0 net.ipv4.tcp_rmem = 4096 131072 6291456 net.ipv4.tcp_wmem = 4096 16384 4194304 vm.swappiness = 60 vm.vfs_cache_pressure = 100

Check network speed:

$ ethtool enp0s9 | grep Speed Speed: 1000Mb/s

Persist the Linux kernel settings:

$ cat >/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf <<_EOF # # /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf # # minimize swapping vm.swappiness = 10 # optimize Linux to cache directories and inodes vm.vfs_cache_pressure = 10 # Increase the TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase the TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 # for 1 GigE, increase this to 2500 # for 10 GigE, increase this to 30000 net.core.netdev_max_backlog = 2500 _EOF

and make them active:

$ sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf

Disable AppArmor (for installation only?):

$ systemctl status apparmor $ systemctl stop apparmor $ systemctl disable apparmor $ aa-status

Configure Character Enconding (why exactly?):

$ localedef -i en_US -f UTF-8 en_US.UTF-8
Installation of MariaDB ColumnStore

The first problem I had was finding a good an reliable information about where to download the software. Finally I ended up here: MariaDB ColumnStore download. But MariaDB ColumnStore is the wrong tab. You have to choose the MariaDB Community Server tab.

If you prefer, the software can also be downloaded from the command line:

$ wget https://dlm.mariadb.com/1623874/MariaDB/mariadb-10.5.10/repo/ubuntu/mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar

and there are a lot of packages contained in this tar-ball:

$ tar xf mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar -rw-rw-r-- 1 oli oli 9059256 May 7 04:34 galera-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 5955264 May 7 04:34 galera-arbitrator-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 1698 May 7 04:35 InRelease -rw-rw-r-- 1 oli oli 149208 May 7 04:34 libmariadb3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3276 May 7 04:34 libmariadb3-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3248 May 7 04:34 libmariadbclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5130428 May 7 04:34 libmariadbd19_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6226020 May 7 04:34 libmariadbd-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 159556 May 7 04:34 libmariadb-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3452 May 7 04:34 libmariadb-dev-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 libmysqlclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6483096 May 7 04:34 mariadb-backup_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4672 May 7 04:35 MariaDB-C74CD1D8-public.asc -rw-rw-r-- 1 oli oli 1604684 May 7 04:34 mariadb-client-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3088 May 7 04:34 mariadb-client_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 784824 May 7 04:34 mariadb-client-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4428 May 7 04:34 mariadb-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 5811572 May 7 04:34 mariadb-plugin-columnstore_10.5.10-5.5.2+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 558288 May 7 04:34 mariadb-plugin-connect_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5216 May 7 04:34 mariadb-plugin-cracklib-password-check_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6172 May 7 04:34 mariadb-plugin-gssapi-client_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 7592 May 7 04:34 mariadb-plugin-gssapi-server_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 1187228 May 7 04:34 mariadb-plugin-mroonga_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 63820 May 7 04:34 mariadb-plugin-oqgraph_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3122524 May 7 04:34 mariadb-plugin-rocksdb_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 845428 May 7 04:34 mariadb-plugin-s3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 354276 May 7 04:34 mariadb-plugin-spider_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4562128 May 7 04:34 mariadb-server-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3216 May 7 04:34 mariadb-server_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 6932836 May 7 04:34 mariadb-server-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 mysql-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 42897 May 7 04:34 Packages -rw-rw-r-- 1 oli oli 2278 May 7 04:35 README -rw-rw-r-- 1 oli oli 816 May 7 04:34 Release -rw-rw-r-- 1 oli oli 833 May 7 04:35 Release.gpg -rwxrwxr-x 1 oli oli 1010 May 7 04:35 setup_repository*

Installation of MariaDB ColumnStore was straight forward:

$ apt-get install gnupg2 $ ./setup_repository $ apt-get update $ apt-get install mariadb-server mariadb-plugin-columnstore libjemalloc1 mariadb-backup mariadb-client
Configuration of MariaDB ColumnStore

The minimum recommended MariaDB configuration for ColumnStore looks like this:

$ cat >/etc/mysql/mariadb.conf.d/zz-fromdual.cnf <<_EOF # # /etc/mysql/mariadb.conf.d/zz-fromdual.cnf # [mariadb] log_error = mariadbd-error.log character_set_server = utf8 collation_server = utf8_general_ci columnstore_use_import_for_batchinsert = ALWAYS _EOF

In the MariaDB ColumnStore documentation you can find all other Server System Variables and Options (columnstore*).

Cross Engine Join User

For Cross Engine Joins a special user is required. These credentials are stored in the file /etc/columnstore/Columnstore.xml and can be queried as follows:

$ mcsGetConfig -v -a | grep Cross CrossEngineSupport.Host = 127.0.0.1 CrossEngineSupport.Port = 3306 CrossEngineSupport.User = root CrossEngineSupport.Password = CrossEngineSupport.TLSCA = CrossEngineSupport.TLSClientCert = CrossEngineSupport.TLSClientKey =

and changed as follows:

$ mcsSetConfig CrossEngineSupport Host 127.0.0.1 $ mcsSetConfig CrossEngineSupport Port 3306 $ mcsSetConfig CrossEngineSupport User cross_engine $ mcsSetConfig CrossEngineSupport Password secret

Caution: Querying the Cross Engine Join User password can be done by an non-privileged O/S user. Thus I consider this as a security relevant bug. This is known already since 2018 and should be fixed in version 5.6.1 :-( (MCOL-1175, MCOL-4714).

Start the ColumnStore Processes

Starting MariaDB ColumnStore after the configuration changes are quite intuitive:

$ systemctl restart mariadb $ systemctl restart mariadb-columnstore

After these commands the mariadbd and some ColumnStore processes are started:

$ ps aux | grep -e mysql -e VSZ USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND mysql 1380 0.0 2.4 1274896 99020 ? Ssl 11:20 0:00 /usr/sbin/mariadbd mysql 1499 0.0 0.4 267392 16904 ? Sl 11:21 0:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 0.0 0.2 530448 11648 ? Sl 11:21 0:00 /usr/bin/controllernode mysql 1522 0.0 0.2 2391288 10560 ? Sl 11:21 0:00 /usr/bin/PrimProc mysql 1545 0.0 0.3 300192 14116 ? Sl 11:21 0:00 /usr/bin/WriteEngineServer mysql 1560 0.0 0.2 212452 10188 ? Sl 11:21 0:00 /usr/bin/ExeMgr mysql 1571 0.0 0.4 342364 18704 ? Sl 11:21 0:00 /usr/bin/DMLProc mysql 1592 0.0 0.3 193640 12444 ? Sl 11:21 0:00 /usr/bin/DDLProc

It looks like each of those processes works already multi-threaded:

$ ps -eLf | grep -e mysql -e PID UID PID PPID LWP C NLWP STIME TTY TIME CMD mysql 1380 1 1380 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1381 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1382 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1383 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1384 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1385 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1389 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1391 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1392 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1407 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1499 1 1499 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1501 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1502 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1503 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1504 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 1 1507 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1567 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1573 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1580 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1581 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1596 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1522 1 1522 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1523 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1524 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1525 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1526 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1527 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1528 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1529 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1530 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1531 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1532 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1533 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1534 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1535 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1561 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1563 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1586 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1593 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1545 1 1545 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1545 1 1546 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1560 1 1560 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1562 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1564 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1565 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1572 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1577 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1571 1 1571 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1587 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1588 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1592 1 1592 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1594 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1595 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc

In the database you can run this SELECT query to check which MariaDB ColumnStore version is running:

SQL> SELECT plugin_name, plugin_status, plugin_type_version, plugin_library_version, plugin_license, plugin_maturity, plugin_auth_version FROM information_schema.plugins WHERE plugin_library LIKE 'ha_column%'; +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | plugin_name | plugin_status | plugin_type_version | plugin_library_version | plugin_license | plugin_maturity | plugin_auth_version | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | Columnstore | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_COLUMNS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_TABLES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_FILES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_EXTENTS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ 5 rows in set (0.002 sec)

or if the MariaDB ColumnStore is enabled at all with:

SQL> SHOW ENGINES; +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Columnstore | YES | ColumnStore storage engine | YES | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
Create the Cross Engine Join User in the database

The Cross Engine Join User specified above must also be created inside the database:

SQL> CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY 'secret'; SQL> CREATE USER 'cross_engine'@'localhost' IDENTIFIED BY 'secret'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'localhost';

This is basically all you have to do for a running single-node MariaDB ColumnStore installation.

MariaDB ColumnStore schemas

MariaDB ColunStore creates 3 new schemas in the database:

SQL> SHOW SCHEMAS; +---------------------+ | Database | +---------------------+ | calpontsys | | columnstore_info | | infinidb_querystats | | information_schema | | mysql | | performance_schema | +---------------------+

We will investigate later what they are used for.

Server System Variables and Server Status Variables

A detailed description of the Server System Variables (columnstore_*) can be found in the MariaDB documentation (old source: infinidb_*).

SQL> SHOW GLOBAL STATUS LIKE 'columnstore%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Columnstore_commit_hash | source | | Columnstore_version | 5.5.2 | +-------------------------+--------+ 2 rows in set (0.001 sec)
SQL> SHOW GLOBAL VARIABLES LIKE 'columnstore%'; +-------------------------------------------------+--------+ | Variable_name | Value | +-------------------------------------------------+--------+ | columnstore_cache_flush_threshold | 500000 | | columnstore_cache_inserts | OFF | | columnstore_compression_type | SNAPPY | | columnstore_decimal_scale | 8 | | columnstore_derived_handler | ON | | columnstore_diskjoin_bucketsize | 100 | | columnstore_diskjoin_largesidelimit | 0 | | columnstore_diskjoin_smallsidelimit | 0 | | columnstore_double_for_decimal_math | OFF | | columnstore_group_by_handler | ON | | columnstore_import_for_batchinsert_delimiter | 7 | | columnstore_import_for_batchinsert_enclosed_by | 17 | | columnstore_local_query | 0 | | columnstore_orderby_threads | 16 | | columnstore_ordered_only | OFF | | columnstore_replication_slave | OFF | | columnstore_select_handler | ON | | columnstore_select_handler_in_stored_procedures | ON | | columnstore_string_scan_threshold | 10 | | columnstore_stringtable_threshold | 20 | | columnstore_um_mem_limit | 0 | | columnstore_use_decimal_scale | OFF | | columnstore_use_import_for_batchinsert | ALWAYS | | columnstore_varbin_always_hex | OFF | +-------------------------------------------------+--------+ 24 rows in set (0.003 sec)

MariaDB ColumnStore also writes to the MariaDB error log:

$ cat mariadbd-error.log 210511 11:20:47 Columnstore: Started; Version: 5.5.2-2

The MariaDB ColumnStore configuration is stored in /etc/columnstore. Those files can be read by everybody on the system by default. So it possibly makes sense to prohibit access:

$ chown mysql: /etc/columnstore/* $ chmod o-rw /etc/columnstore/*

We have not seen yet an negative impact after this "hardening":

$ ll total 100 drwxr-xr-t 2 mysql mysql 4096 May 11 11:17 ./ drwxr-xr-x 78 root root 4096 May 11 10:12 ../ -rw-r----- 1 mysql mysql 19169 May 11 11:17 Columnstore.xml -rw-r----- 1 mysql mysql 19145 May 11 10:12 Columnstore.xml-20210511 -rw-rw---- 1 mysql mysql 19153 May 11 11:17 Columnstore.xml.columnstoreSave -rw-r----- 1 mysql mysql 15372 May 5 23:00 ErrorMessage.txt -rw-r----- 1 mysql mysql 5619 May 5 23:00 MessageFile.txt -rw-r----- 1 mysql mysql 7456 May 5 23:03 storagemanager.cnf
MariaDB Column Store on the file system

It is also interesting to know where on the filesystem the MariaDB ColumnStore files are located. By default they are located under /var/lib/columnstore (Note: Can they be located somewhere else and how?).

$ tree * data1 ├── 000.dir │   └── 000.dir │   ├── 003.dir │   │   ├── 233.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 004.dir │   │   ├── 000.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 018.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 007.dir │   │   ├── 209.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf │   │   └── 212.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 008.dir │   │   ├── 013.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 028.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 011.dir │   ├── 012.dir │   ├── 013.dir │   ├── 014.dir │   ├── 015.dir │   ├── 016.dir │   ├── 017.dir │   ├── 018.dir │   │   ├── 164.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   └── 019.dir │   ├── 000.dir │   │   └── 000.dir │   │   └── FILE000.cdf ... ... │   └── 221.dir │   └── 000.dir │   └── FILE000.cdf ├── bulkRollback ├── dbroot1-lock ├── systemFiles │   └── dbrm │   ├── BRM_savesA_em │   ├── BRM_savesA_vbbm │   ├── BRM_savesA_vss │   ├── BRM_savesB_em │   ├── BRM_savesB_vbbm │   ├── BRM_savesB_vss │   ├── BRM_saves_current │   ├── BRM_saves_em │   ├── BRM_saves_journal │   ├── BRM_saves_vbbm │   ├── BRM_saves_vss │   ├── oidbitmap │   ├── SMTxnID │   └── tablelocks └── versionbuffer.cdf local └── module storagemanager └── storagemanager-lock 688 directories, 353 files
Creating our first MariaDB ColumnStore table

For the very first tests we use our well known test table. But for MariaDB ColumnStore tables you should NOT use the test schema: ColumnStore tables should not be created in the mysql, information_schema, calpontsys or test databases. (Why not test?).
In other documents about MariaDB ColumnStore we can clearly see, that the one who was writing the article was using the test schema [ 2 ]. An other restriction is: ColumnStore stores all object names in lower case. So probably CamelCase table names will not work as expected or they will be converted to lower case.

SQL> SET SESSION default_storage_engine = ColumnStore; SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

Oppps! What is this? After some searching I found that MariaDB ColumnStore does not know indexes: There is no need for indexing. See also MCOL-1080. As such indexes typically used to optimize query access for row based systems do not make sense since selectivity is low for such queries.

After removing the Primary Key the next try:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Also AUTO_INCREMENT seems not to be supported:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

Hmmm. What is wrong now? Checking the MariaDB ColumnStore datatypes does not show any obvious problem. Also the the page ColumnStore Create Table was not helpful. Relying on intuition I tried this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); ERROR 1815 (HY000): Internal error: The default value is out of range for the specified data type.

Becoming stressed a bit now this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL ); ERROR 1815 (HY000): Internal error: Config::Config: error accessing config file /etc/columnstore/Columnstore.xml

OK. This makes sense. We were a bit to restrictive in the first run above when we were hardening the MariaDB ColumnStore configuration file.

After fixing this we tried again and it finally works...

Query OK, 0 rows affected (1.755 sec)

For comparison only: This CREATE TABLE DDL statement with InnoDB takes about 20 ms.

Migrating MariaDB (InnoDB, MyISAM, Aria) tables is not so straight forward as it could be. So the MariaDB ColumnStore seems to be still a bit picky! Interesting is, that if we look at the CREATE TABLE statement it is shown in a non usable form:

SQL> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=Columnstore DEFAULT CHARSET=utf8 1 row in set (0.001 sec)

See also: MCOL-4716, MCOL-1039 and MCOL-4039. This problem seems to be known since a while yet...

This also means that if you want to use mysqldump for migrating the table structure it will not work for restore without editing:

$ mysqldump --user=root --no-data --skip-lock-tables test > /tmp/test_structure_dump.sql
Querying and changing data in MariaDB ColumnStore

Note: This is NOT the preferred way how you should add a massive amount of data into MariaDB ColumnStore!

SQL> INSERT INTO test VALUES (1, 'some data', NULL); Query OK, 1 row affected (0.224 sec) SQL> INSERT INTO test VALUES (2, 'some data', NULL); Query OK, 1 row affected (0.137 sec) SQL> INSERT INTO test VALUES (3, 'some data', NULL); Query OK, 1 row affected (0.145 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 2 | some data | 2021-05-11 14:01:35 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 3 rows in set (0.104 sec) SQL> DELETE FROM test WHERE id = 2; Query OK, 1 row affected (0.194 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 2 rows in set (0.022 sec) SQL> UPDATE test SET data = 'new data' WHERE id = 3; Query OK, 1 row affected (0.222 sec) Rows matched: 1 Changed: 1 Warnings: 0 SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | new data | 2021-05-11 14:03:11 | +----+-----------+---------------------+ 2 rows in set (0.018 sec)

So basic functionality (INSERT, UPDATE, DELETE and SELECT) works. But as we can see, response times for these tiny data sets and single row DML commands are fare above as we are used with other MariaDB Storage Engines (InnoDB, Aria, MyISAM), which is expected... But at least it works. So this is not the best use case for MariaDB ColumnStore. And SELECT * FROM <table> is probably one of the worst things you can do to a ColumnStore in general.

MariaDB ColumnStore using sequences instead of AUTO_INCREMENT

Because AUTO_INCREMENT is not supported in MariaDB ColumnStore we try to use a SEQUENCE instead:

SQL> CREATE SEQUENCE test_s START WITH 5 INCREMENT BY 1; SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP(); Query OK, 1 row affected (1.403 sec) Records: 1 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4 rows affected (1.210 sec) Records: 4 Duplicates: 0 Warnings: 0

Querying the data shows that sequences work but not as expected. They are evaluated once per query and not once per row:

SQL> SELECT * FROM test; +----+---------------+---------------------+ | id | data | ts | +----+---------------+---------------------+ | 1 | Some data | 2021-05-19 16:58:09 | | 2 | Some data | 2021-05-19 16:58:14 | | 3 | Some data | 2021-05-19 16:58:18 | | 5 | Some new data | 2021-05-19 16:59:00 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | +----+---------------+---------------------+ 8 rows in set (0.028 sec)

And if we do this for many rows, we can suddenly see that this becomes much faster than with other MariaDB Storage Engines:

SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 131072 rows affected (1.221 sec) Records: 131072 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 262144 rows affected (2.236 sec) Records: 262144 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 524288 rows affected (2.266 sec) Records: 524288 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 1048576 rows affected (4.316 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 2097152 rows affected (8.367 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4194304 rows affected (15.472 sec) Records: 4194304 Duplicates: 0 Warnings: 0

To compare with InnoDB: About above 1 Mio rows seems to be the break even for MariaDB ColumnStore:

SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 1048576 rows affected (3.499 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 2097152 rows affected (10.100 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 4194304 rows affected (21.766 sec) Records: 4194304 Duplicates: 0 Warnings: 0
MariaDB Column Store Query Execution Plan

If we want to see how the queries are executed the usual EXPLAIN command does not help much:

SQL> EXPLAIN SELECT * FROM test; +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+

Instead we should do it in MariaDB ColumnStore like this:

SQL> SELECT calSetTrace(1); +----------------+ | calSetTrace(1) | +----------------+ | 0 | +----------------+ SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.090 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 69 44 0 0.066 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

If we run the query a second time we can see that the physical I/O (PIO) has been reduced, so everything comes out of memory:

SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.021 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 0 44 0 0.000 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

See also: Query execution and Viewing the ColumnStore query plan.

Taxonomy upgrade extras: dwhColumnStoreData Martmariadbreporting

FromDual Ops Center for MariaDB, MySQL and compatible databases 1.1.0 has been released

Shinguz - Mon, 2021-05-17 17:09

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB, MySQL and compatible databases farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MariaDB, MySQL and compatible databases operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB, MySQL and compatible databases (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB, MySQL and compatible databases please report it to the FromDual bug tracker or just send us an email.

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

Installation of Ops Center 1.1.0

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 1.1.0

Upgrade from 0.9.x to 1.1.0 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 1.1.0 Machine
  • Machine check error messages improved.

Instance
  • Function check_instance made more robust against system outages.
  • Safe account error message improved.
  • Instance restart prepared.
  • read_only in repo check added.
  • Old function removed.
  • Replication check moved from cluster check to instance check.
  • Instance checks for IO and SQL thread added.
  • Function testNode renamed to testInstance.
  • Code clean-up and fix in instance check.
  • Bug in account creation fixed for MariaDB 10.1 and MariaDB 10.0.
  • Instance check improved.
  • Target info was added in edit instance.
  • Check master_log_slave_updates fixed.
  • Function: chanageVariabelsInConfiguatrion added.
  • Functions makeInstanceReadOnly and makeInstanceReadWrite redesigned and cleaned-up.
  • Instance.inc renamed to TargetOperations.inc
  • Database.inc renamed to Target.inc
  • log_slave_updates check added to instance.

Cluster
  • Failover bugs fixed, error messages improved.
  • m/s cluster failover.
  • Change master to can now copy masters file and pos to change master to command.
  • Functionality switchover added.
  • failover_slave_id renamed to failover_instance_id, back button in cluster made correct, check on stopped instance fixed.
  • Cluster operations simplified and A_I inc and A_I offset added.
  • Adding and deleting cluster fixed.
  • read_only is also deployed during failover.
  • Final error failover fixed and log_slave_status check added to cluster.
  • Show cluster overview fixed.
  • Function getMasterInfo fixed for MySQL and MariaDB.
  • Missing read_only check on old master was added.
  • Failover took wrong user.
  • Cluster failover for mm cluster fixed.
  • Cluster operations made more colorful to show important operations better.
  • Function checkVipUser made ready for 3 node cluster.
  • New master is set correctly now.
  • Added more information for failover.
  • M/s and m/m failover should work now.
  • Error messages made more proper.
  • Bug in readonly in repo check fixed.
  • active_master_id added.
  • Failover vip in cluster only if vip is present.
  • Reattach slaves implemented.
  • Failover slave feature implemented.
  • Cluster was only shown if VIP was there, fixed
  • One test only true for m/m was also done on m/s, fixed.
  • Check instance should not interfere with cluster failover any more.
  • read_only and activity added to cluster operations view.
  • Cluster failover is moved from VIP to cluster.
  • Cluster operations simplified.
  • Cluster failover implemented.
  • Failover rules moved from vip to cluster.
  • Cluster test finished.
  • m/m a/a and a/p introduced.
  • Cluster vip check added.
  • Unique server_id check implemented for cluster.
  • Slave read_only in repo check implemented for repl cluster.
  • Slave activity on cluster implemented.
  • Cluster tests for m/s and m/m properly improved.
  • Replication check moved from cluster check to instance check.
  • Slave is shown in cluster overview.
  • Cluster checks improved and checks display made unique.
  • VIP is shown in replication cluster.
  • Bug in cluster handling and usability improved.
  • HTML code clean-up.
  • Some clean-up and M/M cluster fixed.

Load-Balancer
  • No changes.

Virtual IP (VIP)/Floating IP
  • Some VIP cosmetics fixed.
  • Amount of error messages reduced during vip start.
  • Bug in check for new VIP fixed.
  • VIP add check improved.
  • Bug in VIP check fixed, various smaller code clean-up.
  • VIP failover improved.
  • Arping command not found behaviour improved.
  • VIP code cleaned-up.
  • VIP interface check added.
  • VIP nonsense interface already used check removed.
  • Column of primary and failover machine added to VIP overview.
  • Edit VIP machines are sorted in drop down.
  • Key shortcuts added to VIP buttons and html made nicer according to our standards.

Tools
  • Error logging in file transfer improved.
  • Crontab title changed from hr to hour.
  • Crontab error 3800 relaxed to warning.
  • Temporay crontab is now cleaned-up to not leave any remains.
  • Bug in tools function fixed.
  • File transfer bugs fixed.
  • Run Crontab Entry now was implemented.
  • Adding Crontab entries with keyboard shortcuts is possible now.
  • Bug in Crontab with htmlspecialcharacters fixed.
  • Jobs: Error messages made more verbose in regular jobs.
  • Jobs: Jobs are sorted by start_ts DESC now.
  • Jobs: Made job deletion dramatically faster.
  • Jobs: Clean-up job remaining.
  • Jobs: Button in Job Tool provisioned with keys.
  • Jobs: Job checks improved.
  • Jobs: Os_user added to job. This means job can be started under every user now!
  • Jobs: Special characters from crontab inherited to jobs fixed.
  • Jobs: More verbose error messages in job state preparing.
  • Jobs: Job gets a start timestamp as soon as it moves to state Preparing.
  • Jobs: Code cleaned-up and some bugs fixed.
  • Jobs: Function getJobsWithFilter replaces by readJobs.
  • Jobs: Function addJob replaced by createJob.
  • Crontab: User in crontab can now be chosen, but not in jobs yet!
  • Crontab: Special characters from crontab inherited to jobs fixed.
  • Crontab: Only log output when error returned, not OK.

Configuration
  • Configuration: If focmm crontab is not activated complaints are shown on prominent place.
  • Configuration: Delete focmm crontab added.
Database-as-a-Service (DBaaS)
  • No changes.

Building and Packaging
  • Files directory added to package build instructions to make file copy work.
  • rpmlint warning removed.
  • Some lintian warnings implemented.
  • rpmlint warning removed.

Themes / UI
  • Nasty CSS error removed.

General
  • myEnv lib updated.
  • myEnv library updated.
  • Documentation improved and code cosmetics fixed.
  • Various minor bugs fixed.
  • CHANGELOG updated, license key changed.
  • myEnv library updated.
  • Library jquery updated from 3.5.0 to 3.6.0.
  • CHANGELOG updated.
  • Return codes made unique.
  • Some minor bugs fixed.
  • Status in menu on the left fixed.
  • PoC added.
  • Compare tables PoC added.
  • Smaller code fixes.
  • Copyright updated from 2020 to 2021.
  • Better error message, GUI test improved.
  • Minor bug fixed and typos fixed.
  • Function run remote command improved.
  • Wording improved.
  • Accesskey added to SubmitButton and ContinueButton.
  • Constant LOG_ERROR added.
  • ssh function run on remote server optimized.
  • Typos fixed and page titles made more precise.

Repository
  • Node.inc renamed to Catalog.inc.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaasfocmm

Limiting MySQL tmpdir size

Shinguz - Thu, 2021-04-29 17:37

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the file system as volume and mount it in a way you can use it as an separate mount point for your tmpdir directory. So in case your application runs amok it just fills up your tmpdir volume/file and not the whole / (root) filesystem.

# fallocate -l 4G /mysql-tmpdir # mkfs.ext4 /mysql-tmpdir # mke2fs 1.44.1 (24-Mar-2018) Discarding device blocks: done Creating filesystem with 1048576 4k blocks and 262144 inodes Filesystem UUID: 74c51e5c-bed8-4a7d-8f1b-e89669726e1d Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736 Allocating group tables: done Writing inode tables: done Creating journal (16384 blocks): done Writing superblocks and filesystem accounting information: done # grep loop /proc/mounts # mount | grep loop # mkdir /var/lib/mysql-tmpdir # mount -o loop=/dev/loop0 /mysql-tmpdir /var/lib/mysql-tmpdir # df -h | grep -e mysql-tmpdir -e Size Filesystem Size Used Avail Use% Mounted on /dev/loop0 3.9G 16M 3.7G 1% /var/lib/mysql-tmpdir # umount /var/lib/mysql-tmpdir # chown -R mysql: /var/lib/mysql-tmpdir

Add the entry to your /etc/fstab:

/mysql-tmpdir /var/lib/mysql-tmpdir ext4 loop 0 0

Test the fstab entry:

# mount /mysql-tmpdir # sudo -u mysql touch /var/lib/mysql-tmpdir/test

Configure your MySQL database accordingly (my.cnf):

tmpdir = /var/lib/mysql-tmpdir

and after database restart:

SQL> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | tmpdir | /var/lib/mysql-tmpdir | +---------------+-----------------------+

Then create some implicit temporary tables on disk:

# ls -la drwx------ 2 mysql mysql 16K Apr 29 16:41 lost+found -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAD' -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAI'

and check if tmpdir is really capped at 4 Gibyte:

# dd if=/dev/zero of=/var/lib/mysql-tmpdir/tmp bs=1M count=5000 dd: error writing '/var/lib/mysql-tmpdir/tmp': No space left on device 3731+0 records in 3730+0 records out 3912126464 bytes (3.9 GB, 3.6 GiB) copied, 13.708 s, 285 MB/s
Literature
Taxonomy upgrade extras: myisamtmpdirtemporary table

Do not trust other peoples benchmarks!

Shinguz - Tue, 2021-04-06 13:26

Because they do NOT reflect your problems.

One of our customers upgraded last month from MariaDB 10.2 to MariaDB 10.5. In the same change he also converted all his DWH/BI tables from MyISAM to Aria. An all this, naturally, without testing. And it miserably failed! And then we were under heavy time pressure to make things working again...

What has changed:

  • MariaDB version: MariaDB optimizer got a lot of changes between these 4 major release series (10.2, 10.3, 10.4 and 10.5)!
  • Storage Engine change from MyISAM to Aria.
  • MariaDB Server System Variable aria_pagecache_buffer_size was not tested and sized properly. In combination with a MariaDB documentation bug.
  • A newly introduced MariaDB bug (MDEV-25308)? caused also some confusion.

Literature research

Instead of testing and benchmarking on his own our customer relied on benchmarks done by some other people:

  • Benchmarking Aria: These benchmarks, which are older than 2016, claim, that MariaDB is partly faster than MyISAM for internal temporary tables. And partly slower. And they got better results with non default Aria block size (aria_block_size). And they did not benchmark joins (because of internal temporary tables but joins happen quite often in real world).
  • Further a MariaDB marketing/sales article by Roger Eisentrager also claims that Aria is partially faster than MyISAM (it was done by a Sales Engineer).
  • Then we found another benchmark from 2016 by Oļegs Čapligins and Andrejs Ermuiža showing that Aria is faster as well. Unfortunately we could not see exactly which workload/queries was tested.
  • Then we found and article by Denis Szalkowski who came to different results in 2018: Performances comparées des moteurs MyISAM, Aria, InnoDB

Do your own benchmarks

And finally we did our own tests. The following query is just one example out of several others SELECT queries:

EXPLAIN SELECT SQL_NO_CACHE a.`c6` , SUM(ap.`c59`) AS `c59` , SUM(ap.`c11`) AS `c11` FROM auftragsposition ap LEFT JOIN auftrag a ON (a.`c3` = ap.`c3`) WHERE a.`c6` BETWEEN '2020-01-01' AND '2020-01-31' AND a.`c33` BETWEEN 20 AND 80 AND a.`c33` != 22 AND a.`c16` != 21 GROUP BY a.`c6` WITH ROLLUP; +------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+ | 1 | SIMPLE | a | range | PRIMARY,A-Datum | A-Datum | 3 | NULL | 48418 | Using index condition; Using where | | 1 | SIMPLE | ap | ref | PRIMARY | PRIMARY | 12 | test.a.Order | 5 | | +------+-------------+-------+-------+-----------------+---------+---------+--------------+-------+------------------------------------+

And here the latency results for our test query:

MariaDBMyISAM1Aria2Aria3InnoDB410.20.59 s2.19 s0.86 s0.79 s10.30.59 s2.31 s0.86 s- 510.40.60 s2.28 s0.86 s0.77 s10.50.60 s1.49 s0.84 s0.64 s
  1. 1 MyISAM key_buffer_size = 128M
  2. 2 aria_pagecache_buffer_size = 1G
  3. 3 aria_pagecache_buffer_size = 2G
  4. 4 innodb_buffer_pool_size = 2G
  5. 5 Table was corrupted because of full disk during conversion from Aria to InnoDB and thus we lost a significant amount of data...

Why are peer reviews good

In our first test series we completely underestimated the footprint of Aria page caching. So our results where worse that they could be (aria_pagecache_buffer_size = 1G). After some discussions we got a hint from a peer and then we got better and thus more realistic results in our second test series (aria_pagecache_buffer_size = 2G).

Conclusion

What can we say so far: It depends! :-) Test your upgrades carefully and do your own benchmarks!

  • Aria with wrong configuration is dramatically worse than MyISAM. The impact is much worse than it would be with wrong MyISAM setting.
  • Aria for our test join query is about 40% slower than MyISAM.
  • Aria for our test join query is still about 20% slower than InnoDB.
  • MariaDB 10.5 seems to do some things better than MariaDB 10.4 and earlier. At least for this query. And mostly for InnoDB and Aria with disk reads.
  • We still have to fight in some cases with the newer MariaDB 10.5 optimizer which calculates sometimes different query execution plans than MariaDB 10.2. We have to invest more time into the new Engine-Independent Table Statistics (EITS).
  • Converting tables from MyISAM to Aria and back to MyISAM caused us a dramatic slow down on MyISAM tables after conversion. This is still under ongoing investigation (MDEV-25308).

Taxonomy upgrade extras: benchmarkperformancePerformance Tuningquery tuningdwhariamyisam

MariaDB configuration analysis

Shinguz - Tue, 2021-03-30 10:38

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 configuration is coming from. This makes it easier to find and fix wrong configurations.

MariaDB server system variables which are NOT default

A general assumption is that the defaults set by MariaDB are in most cases OK and if you change the defaults you need a good justification for the changes. "I do not know." is NOT a good justification!

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE != DEFAULT_VALUE AND GLOBAL_VALUE NOT LIKE '%home%' AND VARIABLE_NAME LIKE 'INNODB%' ORDER BY VARIABLE_NAME; +--------------------------------+--------------+----------------------+ | VARIABLE_NAME | GLOBAL_VALUE | DEFAULT_VALUE | +--------------------------------+--------------+----------------------+ | INNODB_BUFFER_POOL_INSTANCES | 1 | 0 | | INNODB_FLUSH_LOG_AT_TRX_COMMIT | 2 | 1 | | INNODB_IO_CAPACITY_MAX | 2000 | 18446744073709551615 | | INNODB_LOG_BUFFER_SIZE | 8388608 | 16777216 | | INNODB_LOG_FILE_SIZE | 268435456 | 100663296 | | INNODB_LOG_GROUP_HOME_DIR | ./ | | | INNODB_OPEN_FILES | 2000 | 0 | | INNODB_PAGE_CLEANERS | 1 | 0 | | INNODB_PRINT_ALL_DEADLOCKS | ON | OFF | | INNODB_UNDO_DIRECTORY | ./ | | +--------------------------------+--------------+----------------------+

If we look at the results, we see, that they are not 100% accurate yet. But it is already a big help.
We did NOT configure innodb_buffer_pool_instances for example! And also not innodb_io_capacity_max or innodb_page_cleaners as can be shown here:

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE != DEFAULT_VALUE AND GLOBAL_VALUE NOT LIKE '%home%' AND VARIABLE_NAME LIKE 'INNODB%' AND GLOBAL_VALUE_PATH IS NULL ORDER BY VARIABLE_NAME; +------------------------------+--------------+----------------------+-------------------+ | VARIABLE_NAME | GLOBAL_VALUE | DEFAULT_VALUE | GLOBAL_VALUE_PATH | +------------------------------+--------------+----------------------+-------------------+ | INNODB_BUFFER_POOL_INSTANCES | 1 | 0 | NULL | | INNODB_IO_CAPACITY_MAX | 2000 | 18446744073709551615 | NULL | | INNODB_LOG_GROUP_HOME_DIR | ./ | | NULL | | INNODB_OPEN_FILES | 2000 | 0 | NULL | | INNODB_PAGE_CLEANERS | 1 | 0 | NULL | | INNODB_UNDO_DIRECTORY | ./ | | NULL | +------------------------------+--------------+----------------------+-------------------+

According to MariaDB documentation the default of innodb_io_capactiy_max is 2000. But this is a detail.

MariaDB server system variables taken from which configuration file

Sometimes we do not know and also customer does not know from which MariaDB configuration file a variable is coming from. So the following query helps finding this out. Caution: This only works since MariaDB 10.5!

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE_PATH is NOT NULL ORDER BY VARIABLE_NAME LIMIT 5; +------------------------+--------------+--------------------------------------------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_PATH | +------------------------+--------------+--------------------------------------------------+ | BINLOG_CACHE_SIZE | 1048576 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | BINLOG_FORMAT | ROW | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | BINLOG_STMT_CACHE_SIZE | 1048576 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | EXPIRE_LOGS_DAYS | 5 | /home/mysql/database_slow/mariadb-105/etc/my.cnf | | GENERAL_LOG | OFF | /home/mysql/database_slow/mariadb-105/etc/my.cnf | +------------------------+--------------+--------------------------------------------------+
MariaDB server system variables which where set dymamically

Some customers change MariaDB server system variables dynamically because they want to test something. Typically short before they call us. And sometimes they forget about those changes or did not restart the database instance or did not persist those changes into their my.cnf configuration file. To find those changes the following query will help:

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, GLOBAL_VALUE_ORIGIN, DEFAULT_VALUE, GLOBAL_VALUE_PATH FROM information_schema.SYSTEM_VARIABLES WHERE GLOBAL_VALUE_ORIGIN = 'SQL' ORDER BY VARIABLE_NAME; +-----------------+--------------+---------------------+---------------+-------------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | DEFAULT_VALUE | GLOBAL_VALUE_PATH | +-----------------+--------------+---------------------+---------------+-------------------+ | KEY_BUFFER_SIZE | 16777216 | SQL | 134217728 | NULL | +-----------------+--------------+---------------------+---------------+-------------------+
MariaDB server system variables which cannot be set dynamically

Sometimes it is good to know which MariaDB server system variables can be set dynamically and which MariaDB server system variables require a database instance restart:

SQL> SELECT VARIABLE_NAME, READ_ONLY, GLOBAL_VALUE, GLOBAL_VALUE_ORIGIN, DEFAULT_VALUE, VARIABLE_SCOPE, NUMERIC_MIN_VALUE, NUMERIC_MAX_VALUE FROM information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME IN ('innodb_log_file_size', 'innodb_buffer_pool_size') ORDER BY VARIABLE_NAME; +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+ | VARIABLE_NAME | READ_ONLY | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | DEFAULT_VALUE | VARIABLE_SCOPE | NUMERIC_MIN_VALUE | NUMERIC_MAX_VALUE | +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+ | INNODB_BUFFER_POOL_SIZE | NO | 134217728 | CONFIG | 134217728 | GLOBAL | 5242880 | 9223372036854775807 | | INNODB_LOG_FILE_SIZE | YES | 268435456 | CONFIG | 100663296 | GLOBAL | 1048576 | 18446744073709551615 | +-------------------------+-----------+--------------+---------------------+---------------+----------------+-------------------+----------------------+

Thanks to Elena Stepanova from MariaDB for pointing me to the right place (MDEV-25034). I was blind!

Taxonomy upgrade extras: mariadbconfigurationvariablesserver

MariaDB or MySQL, that is the question

Shinguz - Fri, 2021-03-26 16:23

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:

CriteriaK.O.*MySQL**MariaDB**Query Cache***☐......Ease of use☐......Security☐......Major Release series stability☐......Feature 1 implementation☐......Feature 2 implementation☐......Feature 3 implementation☐......Distribution support☐......Supplier repository☐......Included in O/S Support☐......Enterprise Subscription type****☐......Enterprise Subscription pricing☐......Enterprise Subscription quality☐......Quality assurance of software vendor☐......Application software vendor support☑......Uniform software stack☐......Different supplier strategy☐......Software development☐......License (GPL, proprietary)☐......Mainstream☐......Cluster integration☐......Trust in software vendor☐......One vendor support☐......Migration to this solution☐......Integration into site license☐......Long term trust in to vendor☐......Total----=sum(c2:c27)=sum(d2:d27)

*  A K.O. criteria means that the solution or product is out of the game if it does not meet this criteria at all. For example if you need hard real time behaviour for the application and one of the chosen solutions / products does not meet this requirement this solution / product is out of the game. Side note: Neither MariaDB nor MySQL satisfy hard real time requirements!

**  In this column you can rate the criteria for example from 0..4. 0 means does absolutely not meet criteria, 1 to 4 means a little to perfect.

***  Does NOT exist any more in MySQL 8.

****  MariaDB per instance (mysqld), MySQL per physical machine (not VM!)

If you find/have some more criteria which should be listed in the matrix please let us know!

Literature
Taxonomy upgrade extras: mariadbmysql

MariaDB sql_mode = 'oracle'

Shinguz - Thu, 2021-03-25 20:15

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':


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!

Jira IDTitleAffected
VersionsStatusResolutionFix
Version/sReporterVotesWatchersCreatedUpdated/
ResolvedMCOL-1751Oracle Compatibility: SELECT IF (...) INTO variable FROM DUAL result in syntax error1.2.0OpenUnresolvedIceboxDaniel Lee012018-09-272021-01-14MCOL-1752Oracle Compatibility: A specific stored procedure caused mysqld to crash1.2.0OpenUnresolvedIceboxDaniel Lee012018-09-272021-01-14MCOL-2116TRIM() can not be used with ColumnStore 1.2.x in sql_mode=oracle1.2.1, 1.2.2OpenUnresolvedIceboxValerii Kravchuk032019-01-282021-01-15MCOL-2124substr functions fails with an aggregate function or with GROUP BY cluase with set oracle sql_mode1.2.2OpenUnresolvedIceboxZdravelina Sokolovska012019-01-292021-01-14MCOL-2127query returns empty set if it's executed after failing query in oracle sql-mode1.2.2OpenUnresolvedIceboxZdravelina Sokolovska012019-01-302021-01-14MCOL-2128cut off string from union with sql-mode oracle1.2.2OpenUnresolvedIceboxZdravelina Sokolovska 012019-01-302021-01-14MCOL-2191run the full tpc-ds query set to mcs with set infinidb_vtable_mode 2NoneClosedWon't DoN/AZdravelina Sokolovska022019-02-222021-02-25MCOL-2194run the full tpc-ds query set to mcs with set sql-mode orcle and infinidb_vtable_mode 2NoneClosedWon't DoN/AZdravelina Sokolovska022019-02-242021-02-25MCOL-4044Built In SQL Functions not working with sql_mode=ORACLENoneIn ProgressUnresolved5.6.1Todd Stoffel152020-06-050201-03-08MCOL-4587sql_mode=ORACLE specific functions do not work with ColumnStore tablesNoneClosedDuplicateN/AAlexander Barkov012021-03-052021-03-05MDEV-10137Providing compatibility to other databasesOpenUnresolvedNoneMichael Widenius032016-05-272017-07-19MDEV-10142sql_mode=ORACLE: Explicit cursor FOR LOOPNoneClosedFixed10.3.0Alexander Barkov042016-08-172017-03-10MDEV-10343sql_mode=ORACLE: Providing compatibility for basic SQL data typesNoneClosedFixed10.3.0Dmitry Tolpeko032016-07-072020-08-27MDEV-10411sql_mode=ORACLE: Providing compatibility for basic PL/SQL constructsNoneClosedFixed10.3.0Dmitry Tolpeko042016-07-212020-08-27MDEV-10481Inconsistency between CREATE FUNCTION SYSDATE and DROP FUNCTION SYSDATE5.5, 10.0, 10.1, 10.2, 10.3, 10.4OpenUnresolved10.4Alexander Barkov052016-08-022018-08-27MDEV-10485"Unreserve" MariaDB reserved keywords that are not reserved in the other databasesNoneOpenUnresolvedNoneAlexander Barkov342016-08-032018-05-23MDEV-10572Automatic transaction start for sql_mode=ORACLENoneOpenUnresolvedNoneAlexander Barkov062016-08-172020-05-11MDEV-10573Oracle style multi-table UPDATE syntaxNoneOpenUnresolvedNoneAlexander Barkov052016-08-172019-02-14MDEV-10574sql_mode=ORACLE: IS NULL and empty stringsNoneOpenUnresolved10.3, 10.4Alexander Barkov272016-08-172019-03-29MDEV-10575sql_mode=ORACLE: Date and timestamp formatsNoneOpenUnresolvedNoneAdam Erickson052016-08-172019-02-14MDEV-10576sql_mode=ORACLE: Functions with no parameters can be called without parenthesesNoneOpnUnresolvedNoneAlexander Barkov032016-08-172018-02-14MDEV-10577sql_mode=ORACLE: %TYPE in variable declarationsNoneClosedFixed10.3.0Alexander Barkov032016-08-172018-08-31MDEV-10578sql_mode=ORACLE: SP control functions SQLCODE, SQLERRMNoneClosedFixed10.3.0Alexander Barkov022016-08-172020-08-27MDEV-10579sql_mode=ORACLE: Triggers: Understand :NEW.c1 and :OLD.c1 instead of NEW.c1 and OLD.c1NoneClosedFixed10.3.0Alexander Barkov022016-08-172020-08-27MDEV-10580sql_mode=ORACLE: FOR loop statementNoneClosedFixed10.3.0Alexander Barkov022016-08-172020-08-27MDEV-10581sql_mode=ORACLE: Explicit cursor FOR LOOPNoneClosedFixed10.3.0Alexander Barkov042016-08-172020-08-27MDEV-10582sql_mode=ORACLE: Explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUNDNoneClosedFixed10.3.0Alexander Barkov022016-08-172020-08-27MDEV-10583sql_mode=ORACLE: SQL%ROWCOUNTNoneClosedFixed10.3.0Alexander Barkov032016-08-172020-08-27MDEV-10585EXECUTE IMMEDIATE statementNoneClosedFixed10.2.3, 10.3.0Alexander Barkov042016-08-172018-08-31MDEV-10586sql_mode=ORACLE: Predefined exceptions (part 2)NoneOpenUnresolvedNoneAlexander Barkov342016-08-182018-02-14MDEV-10587sql_mode=ORACLE: User defined exceptionsNoneClosedFixed10.3.0Alexander Barkov022016-08-182020-08-27MDEV-10588sql_mode=ORACLE: TRUNCATE TABLE t1 [ {DROP|REUSE} STORAGE ]NoneClosedFixed10.3.0Alexander Barkov032016-08-182020-08-27MDEV-10589sql_mode=ORACLE: LOG ERRORS clause in INSERT, DELETE, UPDATENoneStalledUnresolvedNoneAlexander Barkov032016-08-182019-02-14MDEV-10590sql_mode=ORACLE: Built-in package DBMS_OUTPUTNoneOpenUnresolvedNoneAlexander Barkov362016-08-182019-02-14MDEV-10591Oracle-style packagesNoneClosedFixed10.3.5Alexander Barkov282016-08-182019-04-24MDEV-10592sql_mode=ORACLE: TYPE .. TABLE OF for scalar data typesNoneOpenUnresolvedNoneAlexander Barkov592016-08-182019-04-03MDEV-10593sql_mode=ORACLE: TYPE .. AS OBJECT: basic functionalityNoneOpenUnresolvedNoneAlexander Barkov482016-08-182019-04-03MDEV-10596sql_mode=ORACLE: Allow VARCHAR and VARCHAR2 without length as a data type of routine parameters and in RETURN clauseNoneClosedFixed10.3.0Alexander Barkov022016-08-192020-08-27MDEV-10597sql_mode=ORACLE: Cursors with parametersNoneClosedFixed10.3.0Alexander Barkov022016-08-192020-08-27MDEV-10598sql_mode=ORACLE: Variable declarations can go after cursor declarationsNoneClosedFixed10.3.0Alexander Barkov022016-08-192018-08-31MDEV-10654IN, OUT, INOUT parameters in CREATE FUNCTIONNoneOpenUnresolvedNoneAlexander Barkov362016-08-242018-11-19MDEV-10655sql_mode=ORACLE: Anonymous blocksNoneClosedFixed10.3.0Alexander Barkov042016-08-242020-05-11MDEV-10697sql_mode=ORACLE: GOTO statementNoneClosedFixed10.3.0Alexander Barkov122016-08-292016-08-31MDEV-10764PL/SQL parser - Phase 2OpenUnresolvedNoneAlvin Richards372016-09-072020-08-01MDEV-10801sql_mode=ORACLE: Dynamic SQL placeholders10.2ClosedFixed10.3.0Alexander Barkov012016-09-132020-08-27MDEV-10839sql_mode=ORACLE: Predefined exceptions: TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEXNoneClosedFixed10.3.0Alexander Barkov012016-09-202020-08-27MDEV-10840sql_mode=ORACLE: RAISE statement for predefined exceptionsNoneClosedFixed10.3.0Alexander Barkov012016-09-202020-08-27MDEV-10914ROW data type for stored routine variables10.3ClosedFixed10.3.0Alexander Barkov022016-09-282018-08-31MDEV-11022sql_mode=ORACLE: SQLERRM(errcode)10.3OpenUnresolvedNoneAlexander Barkov242016-10-112018-02-14MDEV-11070Providing compatibility to other databases - Phase 2OpenUnresolvedNoneAlvin Richards052016-10-172018-10-08MDEV-11160Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar10.3ClosedFixed10.3.1Alexander Barkov012016-10-272018-08-31MDEV-11275sql_mode=ORACLE: CAST(..AS VARCHAR(N))10.3ClosedFixed10.3.0Alexander Barkov012016-11-122020-08-27MDEV-11283CAST(..AS VARCHAR(N))OpenUnresolvedNoneAlexander Barkov262016-11-152020-11-19MDEV-11300sql_mode=ORACLE: CURRENT_DATE functionNoneOpenUnresolvedNoneDmitry Tolpeko012016-11-172019-02-14MDEV-11781sql_mode=ORACLE: IN, OUT, IN OUT modes for dynamic SQL bind argumentsNoneOpenUnresolvedNoneAlexander Barkov242017-01-122018-02-14MDEV-11812sql_mode=ORACLE: AUTHID clause10.3OpenUnresolvedNoneAlexander Barkov132017-01-162018-02-14MDEV-11848Automatic statement repreparation changes query semantics10.0. 10.1, 10.2, 10.3ClosedFixed10.2.4Alexander Barkov012017-01-202018-08-31MDEV-11880sql_mode=ORACLE: Make the concatenation operator ignore NULL arguments10.3ClosedFixed10.3.0Alexander Barkov022017-01-232018-08-31MDEV-11921sql_mode=ORACLE: Translate NUMBER, FLOAT and DOUBLE to subtypes of DECIMAL10.3.23OpenUnresolvedNoneAlexander Barkov152017-01-272020-11-09MDEV-12007Allow ROW variables as a cursor FETCH target10.3ClosedFixed10.3.0Alexander Barkov012017-02-072020-08-27MDEV-12011sql_mode=ORACLE: cursor%ROWTYPE in variable declarations10.3ClosedFixed10.3.0Alexander Barkov022017-02-072020-08-27MDEV-12032sql_mode=ORACLE: recursive stored functionsNoneOpenUnresolvedNoneAlexander Barkov252017-02-092018-02-14MDEV-12033sql_mode=ORACLE: transactions in stored functions10.3OpenUnresolvedNoneAlexander Barkov242017-02-092018-02-14MDEV-12034Dynamic SQL in stored functions10.3OpenUnresolvedNoneAlexander Barkov772017-02-092019-03-18MDEV-12076CONCAT behavior with NULL is different to Oracle37662ClosedDuplicate10.3.0Alexander Barkov142017-02-162017-04-09MDEV-12085sql_mode=ORACLE: allow derived tables not to have aliasesNoneOpenUnresolvedNoneAlexander Barkov132017-02-202019-02-14MDEV-12086sql_mode=ORACLE: Allow SELECT UNIQUE as a synonym for SELECT DISTINCTNoneClosedFixed10.3.0Alexander Barkov012017-02-202020-08-27MDEV-12087sql_mode=ORACLE: a new option to make dash-dash to start a commentNoneOpenUnresolved10.3, 10.4Alexander Barkov252017-02-202019-06-12MDEV-12088sql_mode=ORACLE: Do not require BEGIN..END in multi-statement exception handlers in THEN clauseNoneClosedFixed10.3.0Alexander Barkov012017-02-202020-08-27MDEV-12089sql_mode=ORACLE: Understand optional routine name after the END keywordNoneClosedFixed10.3.0Alexander Barkov012017-02-202020-08-27MDEV-12098sql_mode=ORACLE: Implicit cursor FOR loopNoneClosedFixed10.3.0Alexander Barkov012017-02-202018-09-25MDEV-12107sql_mode=ORACLE: Inside routines the CALL keywoard is optionalNoneClosedFixed10.3.0Alexander Barkov012017-02-222020-08-27MDEV-12133sql_mode=ORACLE: table%ROWTYPE in variable declarations10.3ClosedFixed10.3Alexander Barkov022017-02-272018-08-31MDEV-12140sql_mode=ORACLE: Package metadata views10.3OpenUnresolvedNoneAlexander Barkov012017-02-272019-02-14MDEV-12143sql_mode=ORACLE: Make the CONCAT function ignore NULL argumentsNoneClosedFixed10.3.0Alexander Barkov022017-02-272018-08-31MDEV-12209sql_mode=ORACLE: Syntax error in a OPEN cursor with parameters makes the server crash10.3ClosedFixed10.3.0Alexander Barkov012017-03-082020-08-27MDEV-12224sql_mode=ORACLE: identifier naming convention10.3OpenUnresolvedNoneAlexander Barkov022017-03-102019-02-14MDEV-12291Allow ROW variables as SELECT INTO targets10.3ClosedFixed10.3.0Alexander Barkov012017-03-172020-08-27MDEV-12307ROW data type for built-in function return values10.3OpenUnresolvedNoneAlexander Barkov132017-03-202019-02-14MDEV-12314sql_mode=ORACLE: Implicit cursor FOR LOOP for cursors with parameters10.3ClosedFixed10.3.0Alexander Barkov012017-03-212018-08-31MDEV-12333Allow %ROWTYPE variable fields as FETCH INTO targets10.3OpenUnresolvedNoneAlexander Barkov142017-03-222019-02-14MDEV-12334Allow %ROWTYPE variable fields as SELECT INTO targets10.3OpenUnresolvedNoneAlexander Barkov132017-03-222019-02-14MDEV-12441Variables declared after cursors with parameters lose valueNoneClosedFixed10.3.0Alexander Barkov012017-04-042018-08-31MDEV-12450PL/SQL stored procedure appears to be removed after a drop database, but then fails to re-create - database still exists10.2.5ClosedFixedN/AAlvin Richards022017-04-052017-08-15MDEV-12457Cursors with parametersClosedFixed10.3.0Alexander Barkov012017-04-062017-04-09MDEV-12461TYPE OF and ROW TYPE OF anchored data types for stored routine variablesClosedFixed10.3.0Alexander Barkov012017-04-062017-08-18MDEV-12478CONCAT function inside view casts values incorrectly with Oracle sql_mode10.2.5ClosedFixed10.3.0Alexander Barkov142017-04-102020-08-25MDEV-12518Unify sql_yacc.yy and sql_yacc_ora.yyClosedFixed10.5.1Alexander Barkov052017-04-182020-05-05MDEV-12533sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVALNoneClosedFixed10.3.1Alexander Barkov012017-04-202018-08-31MDEV-12783sql_mode=ORACLE: Functions LENGTH() and LENGTHB()10.3ClosedFixed10.3.1Alexander Barkov012017-05-112018-08-31MDEV-12842sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash10.3ClosedFixed10.3.3Alexander Barkov012017-05-192017-11-15MDEV-12846sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash10.3ClosedFixed10.3.3.Alexander Barkov012017-05-192017-12-12MDEV-12883CREATE SEQUENCE with huge MAXVALUE10.3.0ClosedWon't FixN/AAndrii Nikitin042017-05-232018-04-06MDEV-12962Testing MDEV-10142 (PL/SQL parser)OpenUnresolvedNoneAndrii Nikitin022017-05-312019-07-07MDEV-12964sql_mode=ORACLE: multi-columns Unique index behavior to expect with NULL valueOpenUnresolvedNoeDavid JEGOU032017-05-312018-02-14MDEV-12977sql_mode=oracle: errors "Undefined CURSOR" AND "check ... right syntax to use near '%"10.3ClosedNot a BugN/AAndrii Nikitin022017-06-022017-06-02MDEV-13078NOT NULL routine variables10.3OpenUnresolvedNoneAlexander Barkov022017-06-132018-02-14MDEV-13298Change sp_head::m_chistics from a pointer to a structure10.3ClosedFixed10.3.1Alexander Barkov012017-07-122018-08-31MDEV-13414Fix the SP code to avoid excessive use of strlen10.3ClosedFixed10.3.1Alexander Barkov012017-07-312018-08-31MDEV-13417UPDATE produces wrong values if an updated column is later used as an update source10.0, 10.1, 10.2ClosedFixed10.3.5Alexander Barkov042017-08-012019-04-27MDEV-13418Compatibility: The order of evaluation of SELECT..INTO assignmentsStalledUnresolvedNoneAlexander Barkov032017-08-012018-04-10MDEV-13419Cleanup for Sp_handler::show_create_sp10.3ClosedFixed10.3.1Alexander Barkov022017-08-012018-08-31MDEV-13474MySQL dialect must still work in sql_mode=oracle10.3.0OpenUnresolved10.3, 10.4Andrii Nikitin032017-08-082019-03-29MDEV-13500sql_mode=ORACLE: can't create a virtual column with function MODNoneClosedFixed10.3.1Alexander Barkov022017-08-112017-08-15MDEV-13501sql_mode=ORACLE does not include STRICT_TRANS_TABLES10.3OpenUnresolved10.4Alexander Barkov142017-08-112018-04-05MDEV-13527Crash when EXPLAIN SELECT .. INTO row_sp_variable.fieldClosedFixed10.3.1Alexander Barkov012017-08-152017-08-15MDEV-13581ROW TYPE OF t1 and t1%ROWTYPE for routine parametersClosedFixed10.3.1Alexander Barkov012017-08-182018-08-31MDEV-13617tokudb_parts tests failed in buildbot10.3ClosedFixed10.3.1Alexander Barkov032017-08-222017-08-22MDEV-13686EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself10.3.1ClosedFixed10.3.2Anders Karlsson042017-08-312017-09-14MDEV-13695INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle10.3.1ClosedFixed10.3.7Anders Karlsson032017-09-012018-04-25MDEV-13707Server in ORACLE mode crashes on ALTER with wrong DEFAULT clause10.2, 10.3ClosedFixed10.2.9Alexander Barkov022017-09-012017-09-13MDEV-13863sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent10.3ClosedFixed10.3.2Alexander Barkov032017-09-222020-08-25MDEV-13919sql_mode=ORACLE: Derive length of VARCHAR SP parameters with no length from actual parameters10.3ClosedFixed10.3.2Alexander Barkov022017-09-272018-08-31MDEV-14012sql_mode=Oracle: substr(): treat position 0 as position 110.3ClosedFixed10.3.3Alexander Barkov012017-10-052018-08-31MDEV-14013sql_mode=EMPTY_STRING_IS_NULL10.3ClosedFixed10.3.3Alexander Barkov052017-10-052020-07-22MDEV-14139Anchored data types for variablesNoneClosedFixed10.3.3Alexander Barkov032017-10-262018-08-31MDEV-14164Unknown column error when adding aggregate to function in oracle style procedure FOR loop10.0, 10.1, 10.2, 10.3ClosedFixed10.0.34, 10.1.29, 10.2.11, 10.3.3Hartmut Holzgraefe052017-10-272020-08-25MDEV-14228MariaDB crashes with function10.3ClosedFixed10.3.3Alexander Barkov052017-10-312020-08-25MDEV-14388Server crashes in handle_select / val_uint in ORACLE mode10.3ClosedFixed10.3.3Elena Stepanova022017-11-142020-08-25MDEV-14415Add Oracle-style FOR loop to sql_mode=DEFAULTClosedFixed10.3.3Alexander Barkov042017-11-162019-06-20MDEV-14603signal 11 with short stacktrace10.2.11ClosedFixed10.2.13, 10.3.5Richard Stacke152017-12-072020-08-25MDEV-15070Crash when doing a CREATE VIEW inside a package routine10.3ClosedFixedN/AAlexander Barkov052018-01-252018-08-31MDEV-15080ASAN heap-use-after-free in Query_tables_list::set_query_tables_list / Sp_handler::sp_cache_package_routine or crash in MDL_key::mdl_namespaceN/AClosedDuplicateN/AElena Stepanova012018-01-262018-01-27MDEV-15107Add virtual Field::sp_prepare_and_store_item(), make sp_rcontext symmetric for scalar and ROWNoneClosedFixed10.3.5Alexander Barkov012018-01-292018-08-31MDEV-15416Crash when reading I_S.PARAMETERS10.2.13ClosedFixed10.3.6Hartmut Holzgraefe152018-02-242020-08-25MDEV-15545crash 11 during evaluating an expression10.3ClosedDuplicate10.2.14, 10.3.6 Richard Stacke042018-03-122020-08-25MDEV-15664sql_mode=ORACLE: Make TRIM return NULL instead of empty string10.3ClosedFixed10.3.6Alexander Barkov012018-03-262018-03-30MDEV-15715sql_mode = Oracle with MariaDB 10.3.4 and Store procedure10.3ClosedDuplicateN/AAurélien LEQUOY042018-03-292018-04-09MDEV-15739sql_mode=ORACLE: Make LPAD and RPLAD return NULL instead of empty string10.3ClosedFixed10.3.6Alexander Barkov012018-03-302018-04-03MDEV-15830Assorted notes on sql_mode=ORACLE documentation10.3OpenUnresolved10.3, 10.4Elena Stepanova012018-04-092019-03-29MDEV-15941Explicit cursor FOR loop does not close the cursor10.3ClosedFixed10.3.8Alexander Barkov012018-04-202018-06-20MDEV-15975PL/SQL parser does not understand historical queries10.3ClosedFixed10.3.7Alexander Barkov012018-04-222018-05-18MDEV-16095Oracle-style placeholder insid10.3ClosedFixed10.3.7Alexander Barkov012018-05-062018-05-08MDEV-16156PIPES_AS_CONCAT does not work well5.5, 10.0, 10.1, 10.2, 10.3, 10.4OpenUnresolved10.4Alexander Barkov022018-05-172018-10-04MDEV-16186Concatenation operator || returns wrong results in sql_mode=ORACLE5.5, 10.1, 10.2, 10.3, 10.4, 10.0ClosedFixed39151Alexander Barkov022018-05-162018-10-04MDEV-16202Latest changes made erroneously some keywords reserved in sql_mode=ORACLE10.3ClosedFixed10.3.7Alexander Barkov012018-05-172018-05-17MDEV-16244sql_mode=ORACLE: Some keywords do not work in variable declarations10.3ClosedFixed10.3.8Alexander Barkov012018-05-222019-07-19MDEV-16258sql_mode=ORACLE: Keywords from keyword_verb_clause do not work in assignments10.3OpenUnresolved10.3, 10.4Alexander Barkov122018-05-232019-03-29MDEV-16259sql_mode=ORACLE: Keywords from keyword_sp_head do not work in assignments10.3OpenUnresolved10.3, 10.4Alexander Barkov012018-05-232019-03-29MDEV-16360For every function, document exactly what type it returnsOpenUnresolvedNoneVladislav Vaintroub162018-05-312018-06-06MDEV-16427dual table is implemented a bit flakyOpenUnresolvedNoneOli Sennhauser052018-06-072018-06-21MDEV-16464Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc"10.3.7ClosedFixed10.3.8Mebo142018-06-112018-06-13MDEV-16471mysqldump throws "Variable 'sql_mode' can't be set to the value of 'NULL' (1231)"10.3.7ClosedFixed10.3.8DP062018-06-122018-06-21MDEV-16476PL/SQL CONSTANT declarationsOpenUnresolved10.3, 10.4Oli Sennhauser042018-06-122019-03-29MDEV-16479Oracle Comp.: Sql-Error when referencing database/schema in "select package-function from dual"10.3.7OpenUnresolved10.3, 10.4Mebo042018-06-132019-03-29MDEV-16482MariaDB Oracle mode misses SynonymsOpenUnresolvedOli Sennhauser282018-06-132021-02-11MDEV-16497Oracle Comp.: Sql-Error when referencing database/schema in "select package-function from dual"10.3OpenUnresolved10.3, 10.4Mebo042018-06-132019-03-29MDEV-16558Parenthesized expression does not work as a lower FOR loop bound10.3OpenUnresolved10.3, 10.4Alexander Barkov012018-06-252019-03-29MDEV-16891EVENTs created with SQL_MODE=ORACLE fail to execute10.3.8ClosedFixed10.3.9Hartmut Holzgraefe042018-08-032018-08-07MDEV-16991Rounding vs truncation for TIME, DATETIME, TIMESTAMPClosedFixed10.4.1Alexander Barkov142018-08-152020-11-28MDEV-17030Reset Sequence doesn't reset the value the second time.10.2.14ClosedWon't FixN/APramod Mahto042018-08-222020-08-25MDEV-17253Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly10.3.9ClosedFixed10.3.11Daniel Lee042018-09-202018-12-26MDEV-17359|| operator is not understand by "like" in Oracle10.3, 10.3.9ClosedFixed10.3.11Jérôme Brauge042018-10-032018-10-19MDEV-17375sql_mode=ORACLE: Incompatibility with CAST(number AS CHAR)10.3, 10.4OpenUnresolved10.4Alexander Barkov022018-10-052019-04-02MDEV-17387MariaDB Server giving wrong error while executing select query from procedure10.3.8ClosedFixed10.3.11Nilnandan Joshi 152018-10-082020-08-25MDEV-17389sql_mode=ORACLE: Incompatibility in datetime arithmetic5.5, 10.1, 10.2, 10.3, 10.4, 10.0OpenUnresolved10.4Alexander Barkov022018-10-082019-04-02MDEV-17652Add sql_mode specific tokens for some keywordsClosedDone10.3.11Alexander Barkov012018-11-092018-11-11MDEV-17660sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without10.3ClosedFixed10.3.11Alexander Barkov022018-11-102018-11-11MDEV-17661Add sql_mode specific tokens for the keyword DECODEClosedFixed10.3.11Alexander Barkov012018-11-102018-11-11MDEV-17664Add sql_mode specific tokens for ':' and '%'ClosedFixed10.3.11Alexander Barkov012018-11-112018-11-11MDEV-17666sql_mode=ORACLE: Keyword ELSEIF should not be reserved10.3ClosedFixed10.3.11Alexander Barkov012018-11-112018-11-11MDEV-17669Add sql_mode specific tokens for the keyword DECLAREClosedFixed10.3.11Alexander Barkov012018-11-122018-11-12MDEV-17687Add sql_mode specific tokens for keywords BLOB, CLOB, NUMBER, RAW, VARCHAR2ClosedFixed10.3.11Alexander Barkov012018-11-122018-11-14MDEV-17694Add method LEX::sp_proc_stmt_statement_finalize()ClosedFixed10.4.1Alexander Barkov012018-11-132018-11-14MDEV-17762PL/SQL FUNCTION arguments with IN/OUT declaration fails10.3.10ClosedDuplicateOli Sennhauser032018-11-182018-11-19MDEV-17959Assertion `opt_bootstrap || mysql_parse_status || thd->lex->select_stack_top == 0' failed in parse_sql upon DELETE HISTORY under ORACLE mode10.4ClosedFixed10.4.2Elena Stepanova022018-12-102018-12-27MDEV-18423Unable to unset a flag from sql_mode set to oracle10.3.11ClosedNot a BugN/AZdravelina Sokolovska042019-01-302019-04-17MDEV-18510sql_mode="oracle" does not support COMMENT statements in PL/SQLNoneOpenUnresolvedNoneManjot Singh062019-02-072020-12-11MDEV-18687SQL_MODE="ORACLE" fails to catch keywords10.3.12OpenUnresolved10.3, 10.4Adam Erickson142019-02-212019-03-25MDEV-18789Port "MDEV-7773 Aggregate stored functions" to sql_yacc_ora.yyClosedFixed10.4.4Alexander Barkov022019-03-012019-03-01MDEV-18813PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW10.3, 10.4ClosedFixed10.4.4Alexander Barkov022019-03-042019-03-07MDEV-18814The object name is quoted as a string.10.3.13ClosedNot a BugN/AAlena Subotina022019-03-042019-04-02MDEV-18825Document porting of user defined aggregate functions (UDAF) to sql_mode=ORACLEN/AClosedFixedN/AIan Gilfillan022019-03-052019-04-10MDEV-19144sql_mode="oracle" does not support interval data typeClosedDuplicateN/AManjot Singh022019-04-022019-05-15MDEV-19145sql_mode="oracle" does not support bfile data typeNoneOpenUnresolvedNoneManjot Singh032019-04-022019-09-11MDEV-19146sql_mode="oracle" does not support nclob data typeNoneOpenUnresolvedNoneManjot Singh022019-04-022019-08-01MDEV-19147sql_mode="oracle" does not support "long raw" data typeNoneOpenUnresolvedNoneManjot Singh022019-04-022019-08-01MDEV-19148sql_mode="oracle" does not support xmltype data typeNoneOpenUnresolvedNoneManjot Singh022019-04-022019-07-10MDEV-19149sql_mode="oracle" errors on DBMS_OUTPUT and subfunctionsNoneOpenUnresolvedNoneManjot Singh042019-04-022019-09-10MDEV-19150sql_mode="oracle" errors on create procedure AS10.3.14ClosedNot a BugN/AManjot Singh052019-04-022019-09-11MDEV-19162Some basic datatypes and functions in oracle compatibility mode do not workOpenUnresolvedNoneManjot Singh1102019-04-032021-01-05MDEV-19300Server crashes while executing ALTER TABLE in sql_mode=oracle10.2.4ClosedWon't Fix10.2.15Valerii Kravchuk022019-04-222019-07-15MDEV-19328sql_mode=ORACLE: Package function in VIEW 10.3, 10.4.4, 10.4 ConfirmedUnresolved10.3, 10.4Yuriy Kuleshov032019-04-252019-04-25MDEV-19476sql_mode="oracle" errors on create procedure AS... ORDER/NOORDERNoneOpenUnresolvedNoneAustin Rutherford 032019-05-152019-09-11MDEV-19477sql_mode="oracle" errors on create procedure AS ... MAXVALUE 10.3.8ClosedNot a BugN/AAustin Rutherford 042019-05-152019-09-11MDEV-19488sql_mode="oracle" - add Oracle function NVLNoneClosedFixed36595Austin Rutherford 032019-05-152019-03-21MDEV-19535sql_mode=ORACLE: 'SELECT INTO @var FOR UPDATE' does not lock the table10.4ClosedFixed10.4.6Alexander Barkov022019-05-212019-05-21MDEV-19589sql_mode="oracle" Rem does not work for commentsNoneOpenUnresolvedNoneManjot Singh022019-05-242019-07-15MDEV-19590sql_mode="oracle" @@? @@ and @ include commands do not workNoneOpenUnresolvedNoneManjot Singh022019-05-242019-07-15MDEV-19632Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode10.3, 10.3.16, 10.3.18, 10.4ClosedFixed10.3.24, 10.4.14, 10.5.5 Elena Stepanova1112019-05-292020-08-01MDEV-19635sql_mode="oracle" should provide dbms_sqlNoneOpenUnresolvedNoneManjot Singh032019-05-292019-07-16MDEV-19639sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr10.3, 10.4, 10.5ClosedFixed10.5.0Alexander Barkov012019-05-302019-06-04MDEV-19682sql_mode="oracle" does not support sysdateNoneIn ProgressUnresolved10.6Austin Rutherford 032019-06-042020-11-17MDEV-19683sql_mode="oracle" does not support Oracle function TO_DATENoneOpenUnresolvedNoneAustin Rutherford 022019-06-042019-09-10MDEV-19728Comments in SQL10.4.5ClosedDuplicateN/AWolfgang Draxler032019-06-112019-06-12MDEV-19782sql_mode=ORACLE: ROWNUMOpenUnresolvedNoneWolfgang Draxler162019-06-172020-11-02MDEV-19804sql_mode=ORACLE: call procedure in packages10.4.6OpenUnresolved10.4Wolfgang Draxler012019-06-192019-06-28MDEV-19915sql_mode=ORACLE: call procedure with "=>"10.4.6OpenUnresolved10.4Wolfgang Draxler042019-07-012019-07-04MDEV-19928sql_mode=ORACLE: Add/Subtract numbers from date10.4.6OpenUnresolved10.4Wolfgang Draxler042019-07-022019-07-04MDEV-19979Document OTHERS as reserved word in sql_mode=ORACLE in MariaDB 10.3+NoneClosedFixedN/AValerii Kravchuk012019-07-072020-08-25MDEV-20017Implement TO_CHAR() Oracle compatible functionNoneIn ProgressUnresolved10.6Faisal Saeed072019-07-102021-03-05MDEV-20018sql_mode="oracle" does not support FULL OUTER JOINNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20019sql_mode="oracle" does not support MERGE statementNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20020sql_mode="oracle" does not support "rownum" pseudo columnNoneOpenUnresolvedNoneFaisal Saeed032019-07-102020-11-02MDEV-20021sql_mode="oracle" does not support MINUS set operatorNoneOpenUnresolved10.6Faisal Saeed062019-07-102021-01-05MDEV-20022sql_mode="oracle" does not support TO_NUMBER() functionNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20023sql_mode="oracle" does not support TRUNC() functionNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20024sql_mode="oracle" does not support LISTAGG() functionNoneOpenUnresolvedNoneFaisal Saeed032019-07-102019-09-10MDEV-20025ADD_MONTHS() Oracle function10.6In ProgressUnresolved10.6Faisal Saeed042019-07-102021-02-08MDEV-20027LOAD DATA INFILE - REJECTED recordsNoneClosedDuplicateN/ADaniel Lee032019-07-102020-09-07MDEV-20028sql_mode="oracle" does not support automatic List / Interval PartitioningNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20029sql_mode="oracle" does not support DBMS_XML packageNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20030sql_mode="oracle" does not support FOR ALL ... BULK COLLECTNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20031sql_mode="oracle" should ignore the Oracle optimizer hints from the SQL automaticallyNoneOpenUnresolvedNoneFaisal Saeed032019-07-102019-09-10MDEV-20032sql_mode="oracle" does not support TO_TIMESTAMP() functionNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20033sql_mode="oracle" does not support INSERT INTO ... RETURNINGNoneClosedDuplicateN/AFaisal Saeed022019-07-102019-09-24MDEV-20034sql_mode="oracle" does not support stored code returning REFCURSOR or SYS_REFCURSORNoneOpenUnresolvedNoneFaisal Saeed032019-07-102020-12-11MDEV-20035sql_mode="oracle" REGEXP_SUBSTR gives error "incorrect parameter count"NoneOpenUnresolvedNoneFaisal Saeed032019-07-102019-09-10MDEV-20036sql_mode="oracle" A simple com10.3, 10.4, 10.5OpenUnresolvedNoneFaisal Saeed042019-07-102020-12-11MDEV-20037FUNCTION returning a TYPE failsNoneOpenUnresolvedNoneFaisal Saeed032019-07-102020-12-11MDEV-20039sql_mode="oracle" does not support Combined TRIGGERS eventsNoneOpenUnresolvedNoneFaisal Saeed022019-07-102019-09-10MDEV-20134sql_mode="oracle" does not support keyword "ENABLE"10.3.13OpenUnresolvedNoneManjot Singh042019-07-232020-12-11MDEV-20238sql_mode="oracle" does not support "DEFAULT" parameters for functions/proceduresNoneOpenUnresolvedNoneFaisal Saeed142019-08-022019-09-10MDEV-20263sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB10.3, 10.4, 10.5ClosedFixed10.3.18, 10.4.8Alexander Barkov032019-08-062019-08-06MDEV-20649sql_mode="oracle" does not support "RAISE_APPLICATION_ERROR()"NoneOpenUnresolvedNoneFaisal Saeed022019-09-232019-09-23MDEV-20650sql_mode="oracle" does not support ancient outer join syntax (+)NoneOpenUnresolvedNoneFaisal Saeed032019-09-232019-03-25MDEV-20651sql_mode="oracle" does not support "RAISE"NoneOpenUnresolvedNoneFaisal Saeed022019-09-232019-09-23MDEV-20652sql_mode="oracle" does not support "EXECUTE IMMEDIATE INTO" / "RETURNING"NoneOpenUnresolvedNoneFaisal Saeed022019-09-232019-09-23MDEV-20657sql_mode="oracle" does not support DATE/TIMESTAMP with TIMEZONENoneClosedDuplicateN/AFaisal Saeed012019-09-242019-09-25MDEV-20658sql_mode=oracle does not support XMLAGG(), XMLELEMENT() and EXTRACT() XML functionsNoneOpenUnresolvedNoneFaisal Saeed022019-09-242019-09-24MDEV-20659sql_mode=oracle does not support SYSTIMESTAMPNoneOpenUnresolvedNoneFaisal Saeed022019-09-242019-09-24MDEV-20660sql_mode=oracle does not support TO_TIMESTAMP_TZ() functionNoneOpenUnresolvedNoneFaisal Saeed022019-09-242019-09-24MDEV-20662sql_mode=oracle does not support custom EXCEPTIONsNoneOpenUnresolvedNoneFaisal Saeed022019-09-242019-09-24MDEV-20667Server crash on pop_cursor10.3, 10.4.7, 10.4.8, 10.4ClosedFixed10.3.22, 10.4.12Jérôme Brauge042019-09-252019-12-12MDEV-20817sql_mode="oracle" does not support PL/SQL Table TypeNoneOpenUnresolvedNoneFaisal Saeed022019-10-122019-10-12MDEV-20913sql_mode=ORACLE: INET6 does not work as a routine parameter type and return type10.5ClosedFixed10.5.0Alexander Barkov012019-10-292019-10-29MDEV-20924Unify grammar rules: field_type_string and sp_param_field_type_stringClosedFixed10.5.0Alexander Barkov012019-10-302020-01-23MDEV-21043Collect different bison %type declarations into a single chunkClosedFixed10.5.0Alexander Barkov012019-11-132020-01-23MDEV-21875Postfix for MDEV-20076: quotes in GRANT PROXY haven't been changed10.3, 10.4OpenUnresolved10.3, 10.4Elena Stepanova012020-03-042020-03-04MDEV-22022Various mangled SQL statements will crash 10.3 to 10.5 debug builds10.3, 10.4. 10.5ClosedFixed10.3.26, 10.4.16, 10.5.7 Roel Van de Paar042020-03-242020-10-06MDEV-22260Add a comment about potentially missing table options on SHOW CREATE in ORACLE modeClosedFixedN/AHartmut Holzgraefe162020-04-162020-10-15MDEV-22625SIGSEGV in intern_find_sys_var (optimized builds)10.5.2, 10.5.3, 10.5.4ClosedFixed10.5.4Roel Van de Paar042020-05-192020-05-29MDEV-22807make NULLS LAST default when sql_mode=ORACLEOpenUnresolvedNoneNilnandan Joshi 052020-06-052021-01-11MDEV-22808Oracle Mode should result into .1 rather than 0.1 for value 0.1 with SELECT statementOpenUnresolvedNonePramod Mahto062020-06-052021-03-04MDEV-22822sql_mode="oracle" cannot declare without variable errorsNoneClosedFixed10.5.4, 10.3.24, 10.4.14Manjot Singh042020-05-142020-06-07MDEV-22870Would like sort order for sql_mode=ORACLE to match Oracle sort orderOpenUnresolvedNoneAlexander Barkov062020-06-112021-03-05MDEV-22923Data Truncation when using UNION SELECT10.3.23, 10.4.13OpenUnresolved10.3, 10.4, 10.5Thomas Christlieb472020-06-172021-02-25MDEV-23005sql_mode mixture: a table with DECODE() in a virtual column refuses to work10.3, 10.4, 10.5OpenUnresolved10.3, 10.4, 10.5Alexander Barkov032020-06-242020-06-29MDEV-23023Put compatibility functions and data types into namespacesOpenUnresolved10.5Alexander Barkov022020-06-262020-08-16MDEV-23040sql_mode mixture: a table with TRIM() in DEFAULT refuses to INSERT10.3, 10.4, 10.5OpenUnresolved10.3Alexander Barkov032020-06-292020-06-29MDEV-23094Multiple calls to a Stored Procedure from another Stored Procedure crashes server10.4.13, 10.5.4ClosedFixed10.4.16, 10.5.7Björn Möller2102020-07-042020-10-27MDEV-23108Point in time recovery of binary log fails when sql_mode=ORACLE10.3, 10.4, 10.5ClosedFixed10.3.24, 10.4.14, 10.5.5 Sujatha Sivakumar 162020-07-072020-08-25MDEV-23288Add MariaDB_PARSER_PLUGINOpenUnresolved10.6Alexander Barkov032020-07-252020-10-28MDEV-23353Qualified data types in SPOpenUnresolved10.5Alexander Barkov022020-07-312020-09-24MDEV-23479Add a THD* argument to Item_func_or_sum::fix_length_and_dec()OpenUnresolved10.7Alexander Barkov032020-08-142021-03-15MDEV-24067True DECIMAL support for bitwise operators like &OpenUnresolvedHartmut Holzgraefe032020-10-302021-02-09MDEV-24089support oracle syntax: rownumIn ReviewUnresolved10.6woqutech062020-11-022021-01-04MDEV-24092support oracle syntax: sampleOpenUnresolvedwoqutech062020-11-022020-12-23MDEV-24525sql_mode="oracle" does not support "rowid" pseudo columnOpenUnresolvedNonewoqutech.com032021-01-052021-01-05MDEV-24611Unable to restore a Oracle package after creation10.3, 10.4, 10.5, 10.6, 10.3.25ConfirmedUnresolved10.3, 10.4, 10.5Kim Gert Nielsen032021-01-182020-02-01MDEV-24891Document mariadb_schema data type qualifierClosedFixedN/AAlexander Barkov022021-02-162021-03-10MDEV-25135Server crashes in Column_definition::prepare_stage1 (with different rest of stack) upon creation of SP in ORACLE mode10.3, 10.4, 10.5, 10.6OpenUnresolved10.3, 10.4, 10.5Elena Stepanova022021-03-142021-03-14MDEV-25158SIGSEGV in hp_rec_key_cmp10.5, 10.6OpenUnresolved10.5Ramesh Sivaraman012021-03-162021-03-23MXS-1264Migration plugin filterIceboxClosedWon't DoN/AAnders Karlsson022017-05-112019-09-04MXS-1275Recognize "set SQL_MODE=ORACLE" statementsNoneClosedDone2.2.0Johan Wikman012017-05-242018-02-14MXS-1278Turn on PL/SQL dynamically.NoneClosedDone2.2.0Johan Wikman022017-05-312017-10-13MXS-2080In SQL_MODE=ORACLE, sequence_name.nextval can get routed to slave2.2.13ClosedNot a BugN/AGeoff Montee022017-10-042020-08-25MXS-2166Default SQL mode should be service specific.IceboxClosedFixed2.5.0Johan Wikman012018-11-132019-09-13ODBC-225Excel+MariaDB driver not showing list of tables3.1.0, 3.0.8ClosedFixed2.0.19, 3.0.9, 3.1.1 IT Particip162019-02-212019-09-23ODBC-234SQLGetTypeInfo does not work with sql_mode='Oracle'2.0.18, 3.1.0, 3.0.8 ClosedFixed2.0.19, 3.0.9, 3.1.1 Lawrin Novitsky 012019-03-182019-03-18
What is new in MariaDB sql_mode = 'oracle'?

Preparation for Oracle style PL/SQL has been taking place in MariaDB 10.2 and the PL/SQL language subset was introduced in MariaDB 10.3. In later MariaDB releases 10.4 and 10.5 there were only bug fixes. Many new or missing Oracle PL/SQL features (constants, global synonyms, dual table) have not made it into the recent releases yet. No new features were found for 10.4 and 10.5 in the MariaDB release notes:


Only two bug fixes made it: MariaDB 10.4.14 Release Notes and MariaDB 10.5.5 Release Notes.

In MariaDB 10.6 we will see the introduction of some new Oracle PL/SQL compatibility functions: Changes and Improvements in MariaDB 10.6. But still no significant new features.

Why are no new MariaDB PL/SQL features implemented?

I have no insider know-how, so I am just guessing: MariaDB is a very customer driven company. If there is not huge demand for those features and also nobody is sponsoring the new features. The introduction will be postponed until somebody wants to do the work or pay for it...

So how can you contribute to MariaDB to make those features available:

  • Contribute a feature. If you need some help we assist you an make the contact to MariaDB Corp.
  • Participate in MariaDB Google Summer of Code 2021.
  • Pay a developer contributing the feature. Also here we can assist you.
  • Pay MariaDB for implementing some features. Here we help you to negotiate and get a quote.
  • Pay for a MariaDB Enterprise subscription so MariaDB Corp. has more resources available for implementing the features. We will be happy to send you a quote for MariaDB Enterprise Support Subscriptions. Then open bug reports or features requests for those features.

Taxonomy upgrade extras: Oraclemariadbpl/sqlsql_mode

MariaDB Galera Cluster with Corosync/Pacemaker VIP

Shinguz - Wed, 2021-03-17 20:26

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 that provides the messaging layer and talks to instances of itself on the other node(s). Corosync provides reliable communication between nodes, manages cluster membership and determines quorum. Think of Corosync as dbus but between nodes.

The following proof of concept is based on Pacemaker 2.0 and Corosync 3.0. Commands for older versions of Corosync/Pacemaker may vary slightly.

# crmadmin --version Pacemaker 2.0.1 # corosync -v Corosync Cluster Engine, version '3.0.1'
Prerequisites
  • DNS resolution must work.
  • Nodes must be reachable (firewall).
  • Nodes must allow traffic between them.

The following steps must be performed on all 3 nodes unless specified otherwise:

DNS resolution

Add the hosts to your /etc/host file (or however you do hostname resolution in your set-up):

# # /etc/hosts # 192.168.56.103 node1 192.168.56.133 node2 192.168.56.134 node3

Especially pay attention to choose the right IP address if you have different network interfaces: One for inter-cluster-communication (192.168.56.*) and one for application-traffic (192.168.1.*).

Check all the nodes on all the nodes:

# ping node1 # ping node2 # ping node3
Firewall

Check your firewall settings:

# iptables -L # systemctl status firewalld

A simple Corosync/Pacemaker Cluster needs the following firewall settings [ 3 ]:

  • TCP port 2224 for pcsd, Web UI and node-to-node communication.
  • TCP port 3121 if cluster has any Pacemaker Remote nodes.
  • TCP port 5403 for quorum device with corosync-qnetd.
  • UDP port 5404 for corosync if it is configured for multicast UDP.
  • UDP port 5405 for corosync.

Install Corosync/Pacemaker

Install the Corosync/Pacemaker packages:

# apt-get install pacemaker pcs

The user which is used for the Corosync/Pacemaker Cluster is the following:

# grep hacluster /etc/passwd hacluster:x:106:112::/var/lib/pacemaker:/usr/sbin/nologin

Set the password for the Corosync/Pacemaker Cluster user:

# passwd hacluster New password: Retype new password: passwd: password updated successfully
Configuring the Corosync/Pacemaker Cluster

Start the Pacemaker/Corosync Configuration System Daemon (pcsd):

# systemctl enable pcsd # systemctl start pcsd # systemctl status pcsd --no-pager # journalctl -xe -u pcsd --no-pager

Authenticate the nodes in the Cluster (on one node only):

# pcs host auth node1 node2 node3 Username: hacluster Password: node1: Authorized node3: Authorized node2: Authorized

If something fails the following command will do the undo operation:

# pcs pscd clear-auth [node]
Create the Corosync/Pacemaker Cluster

To create the Corosync/Pacemaker Cluster run the following command (on one node only):

# pcs cluster setup galera-cluster --start node1 node2 node3 --force No addresses specified for host 'node1', using 'node1' No addresses specified for host 'node2', using 'node2' No addresses specified for host 'node3', using 'node3' Warning: node1: Cluster configuration files found, the host seems to be in a cluster already Warning: node3: Cluster configuration files found, the host seems to be in a cluster already Warning: node2: Cluster configuration files found, the host seems to be in a cluster already Destroying cluster on hosts: 'node1', 'node2', 'node3'... node1: Successfully destroyed cluster node3: Successfully destroyed cluster node2: Successfully destroyed cluster Requesting remove 'pcsd settings' from 'node1', 'node2', 'node3' node3: successful removal of the file 'pcsd settings' node1: successful removal of the file 'pcsd settings' node2: successful removal of the file 'pcsd settings' Sending 'corosync authkey', 'pacemaker authkey' to 'node1', 'node2', 'node3' node1: successful distribution of the file 'corosync authkey' node1: successful distribution of the file 'pacemaker authkey' node3: successful distribution of the file 'corosync authkey' node3: successful distribution of the file 'pacemaker authkey' node2: successful distribution of the file 'corosync authkey' node2: successful distribution of the file 'pacemaker authkey' Synchronizing pcsd SSL certificates on nodes 'node1', 'node2', 'node3'... node2: Success node3: Success node1: Success Sending 'corosync.conf' to 'node1', 'node2', 'node3' node1: successful distribution of the file 'corosync.conf' node2: successful distribution of the file 'corosync.conf' node3: successful distribution of the file 'corosync.conf' Cluster has been successfully set up. Starting cluster on hosts: 'node1', 'node2', 'node3'...

This command creates the file: /etc/corosync/corosync.conf.

The command pcs cluster start will trigger Pacemaker and Corosync start in the background:

# systemctl status pacemaker --no-pager # systemctl status corosync --no-pager

Undo if something fails:

# pcs cluster destroy

Check your Corosync/Pacemaker Cluster:

# pcs status Cluster name: galera-cluster WARNINGS: No stonith devices and stonith-enabled is not false Stack: corosync Current DC: node3 (version 2.0.1-9e909a5bdd) - partition with quorum Last updated: Mon Mar 15 15:45:21 2021 Last change: Mon Mar 15 15:40:45 2021 by hacluster via crmd on node3 3 nodes configured 0 resources configured Online: [ node1 node2 node3 ] No resources Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/enabled

To start the pacemaker and corosync services at system restart enable them in SystemD (on all 3 nodes again):

# systemctl enable pacemaker # systemctl enable corosync
Add Corosync/Pacemaker Resources

A resource is a service which is managed by the Cluster. For example a Web-Server, a database instance or a Virtual IP address.

Add a Virtual IP (VIP) address resource (aka Floating IP, on one node only):

# pcs resource create VirtualIP ocf:heartbeat:IPaddr2 ip=192.168.1.199 cidr_netmask=32 op monitor interval=5s # pcs status resources VirtualIP (ocf::heartbeat:IPaddr2): Stopped # pcs status cluster Cluster Status: Stack: corosync Current DC: node3 (version 2.0.1-9e909a5bdd) - partition with quorum Last updated: Mon Mar 8 16:54:03 2021 Last change: Mon Mar 8 16:52:32 2021 by root via cibadmin on node1 3 nodes configured 1 resource configured PCSD Status: node2: Online node3: Online node1: Online # pcs status nodes Pacemaker Nodes: Online: node1 node2 node3 Standby: Maintenance: Offline: Pacemaker Remote Nodes: Online: Standby: Maintenance: Offline: # pcs resource enable VirtualIP # pcs status Cluster name: galera-cluster WARNINGS: No stonith devices and stonith-enabled is not false Stack: corosync Current DC: node3 (version 2.0.1-9e909a5bdd) - partition with quorum Last updated: Mon Mar 15 15:53:07 2021 Last change: Mon Mar 15 15:51:29 2021 by root via cibadmin on node2 3 nodes configured 1 resource configured Online: [ node1 node2 node3 ] Full list of resources: VirtualIP (ocf::heartbeat:IPaddr2): Stopped Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled

As we can see the resource VirtualIP is still stopped. To get more information, you can run the following command:

# crm_verify -L -V (unpack_resources) error: Resource start-up disabled since no STONITH resources have been defined (unpack_resources) error: Either configure some or disable STONITH with the stonith-enabled option (unpack_resources) error: NOTE: Clusters with shared data need STONITH to ensure data integrity Errors found during check: config not valid

Beacause we do NOT have shared data (Galera Cluster is a shared-nothing architecture) we do not need STONITH:

# pcs property set stonith-enabled=false

After stonith-enabled is set to false the VIP will be started:

# pcs resource status VirtualIP (ocf::heartbeat:IPaddr2): Started node1 # ip -f inet addr show enp0s8 3: enp0s8: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 inet 192.168.1.122/24 brd 192.168.1.255 scope global dynamic enp0s8 valid_lft 84918sec preferred_lft 84918sec inet 192.168.1.199/32 brd 192.168.1.255 scope global enp0s8 valid_lft forever preferred_lft forever

Because quorum and fencing is done also by Galera Cluster we do not want interferrence by Corosync/Pacemaker. Thus we set the no-quorum-policy to ignore:

# pcs property set no-quorum-policy=ignore
Graceful manual switchover

The rudest variant moving a resource away from a node is to take if offline:

# pcs cluster stop node2 node2: Stopping Cluster (pacemaker)... node2: Stopping Cluster (corosync)... # pcs cluster start node2 node2: Starting Cluster...

A softer possibility moving a resource away from a node is by putting the node into standby:

# pcs node standby node2

To get it back will move the resource to the node again:

# pcs node unstandby node2

Both methods have in common, that the resource is moved back when the node in online again. This is possibly not what you want. To nicest way moving a resource away is the move command:

# pcs resource status VirtualIP (ocf::heartbeat:IPaddr2): Started node2 # pcs resource move VirtualIP node3 # pcs resource status VirtualIP (ocf::heartbeat:IPaddr2): Started node3
Prevent Resources from Moving back after Recovery

To prevent a resource moving around we can define a stickiness for a resource:

# pcs resource defaults No defaults set # pcs resource defaults resource-stickiness=100 Warning: Defaults do not apply to resources which override them with their own defined values # pcs resource defaults resource-stickiness: 100

With later tests I have seen that a resource stickiness of INFINIY gave some better, but not perfect results.

Graphical Web User Interface

Pacemaker/Corosync also provides a Graphical Web User Interface. It can be reached via all IP addresses/interfaces of each node:

# netstat -tlpn | grep -e python -e PID Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:2224 0.0.0.0:* LISTEN 16550/python3 tcp6 0 0 :::2224 :::* LISTEN 16550/python3

It can simply be reached via the following Link: https://127.0.0.1:2224/login
The user and password are the same as you used above setting up the Cluster.

If you plan to NOT use the Web-GUI you can disable it on all nodes in the following files: /etc/default/pcsd (Debian, Ubuntu) or /etc/sysconfig/pcsd (CentOS) followed by a restart of the pcsd process.

Improvements

There is still some space for improvements: If a Galera node becomes not Synced (also including Donor/Desynced?) the VIP address should also move somewhere else. One possibility is to hook this into the wsrep_notify_command variable:

[mysqld] wsrep_notify_command = pcs_standby_node.sh

The script pcs_standby_node.sh should cover the following scenarios:

Sceariow/o scriptwith scriptMachine halts suddenly (power off)OKOKMachine reboots/restartsOKOKInstance restartsNOK*OKInstance goes non-syncedNOK*OKInstance dies (crash, Oom, kill -9)NOK*NOK**

* Your application will experience errors such as:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.199' (111) ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104 ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use

** For this last case we need some more tooling...

If you let Galera run the scrip now you will get some errors:

sudo /usr/sbin/pcs node unstandby node1 sudo: unable to change to root gid: Operation not permitted sudo: unable to initialize policy plugin ret=1

To make the script work we have to add the mysql user to the haclient group and add some ACLs [ 11 ]:

# grep haclient /etc/group haclient:x:112: # usermod -a -G haclient mysql # pcs acl enable # pcs acl role create standby_r description="Put node to standby" write xpath /cib # pcs acl user create mysql standby_r # pcs acl

Now the failover works quiet smooth and I have not seen any errors any more. Just sometimes the connections hang. I tried to reduce the hang with reducing tcp_retries2 to 3 as suggested here [ 10 ] but it did not help. If anybody has a hint please let me know!

General thoughts
  • A Corosync/Pacemaker Cluster is IMHO too complicated (!= KISS) for a simple VIP failover solution!
  • Probably keepalived is the simpler solution. See also: [ 4, 5 and 6 ]

Literature
Taxonomy upgrade extras: galeragalera clusterkeepalivedcorosyncpacemakervipHigh Availabilityfail-over

Keep your Galera Cluster up and running by all means

Shinguz - Fri, 2021-02-26 12:15

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 variable wsrep_max_ws_rows defines how many rows are allowed at maximum in one write set (~ transaction). Zero means NO limit. In older versions this value was set by default to 128k rows as far as I can remember. The variable wsrep_max_ws_size defines the maximum size of a write set in bytes. This is 2 Gibyte. In older versions this value was set by default to 1 Gibyte. Bigger is NOT possible except you are using Streaming Replication. For disadvantages of Streaming Replication see further down.

Do small transactions

So the technically good way to handle this situation is to make smaller transactions. We suggest a batch size of about 100 to 10000 rows per batch (= transaction). This causes much less stress to the database. And the chance to bring your Galera Cluster out of service is much smaller. If you run these smaller batches every let us say 10 seconds (to give the Galera Cluster a chance to breath) you can delete between 864k and 86.4 Mio rows per day. This should be sufficient in most cases.
Something like:

START TRANSACTION; DELETE FROM trash WHERE creation_date < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 YEAR) LIMIT 1000; COMMIT;

should do the job.

Unfortunately this has to be done and/or implemented most often by developers. And they are not aware of the issue and/or do not want to listen do DBA's, Admins or Consultants. What about the DevOps approach here? Can anybody explain me again, what DevOps means exactly???Responsibility for Operations by Developers?

Force small transactions

If you are responsible for a Galera Cluster and if it is your duty or your honour to keep it up an running by all means you first can try to educate the people in charge of transactions. But if all your effort does not help you possibly have to unwrap the big hammer.

This method is especially useful in situations where you have different applications running on the same consolidated Galera Cluster and one application always tears down the whole Galera Cluster and all other applications suffer.

You can use the wsrep_max_ws_rows variable to limit transaction size and keep your Galera Cluster through this limitation hopefully up an running.

SQL> SET GLOBAL wsrep_max_ws_rows = 10000;

If you want to watch some status variables during experimenting I suggest these:

shell> watch -d -n 1 "mysql -uroot --execute=\"SHOW GLOBAL STATUS WHERE variable_name LIKE 'wsrep_re%' OR variable_name LIKE 'innodb_rows%'\" | column -t" Variable_name Value Innodb_rows_deleted 41003 Innodb_rows_inserted 2097152 Innodb_rows_read 4224307 Innodb_rows_updated 0 wsrep_replicated 5 wsrep_replicated_bytes 1936 wsrep_repl_keys 9 wsrep_repl_keys_bytes 192 wsrep_repl_data_bytes 1419 wsrep_repl_other_bytes 0 wsrep_received 62 wsrep_received_bytes 99388160 wsrep_ready ON

Side note: What we found during experimenting is that FLUSH STATUS seems to be replicated to the other nodes...

So for transactions less or equal 10'000 rows it works fine:

SQL> DELETE from TEST LIMIT 10000; Query OK, 10000 rows affected (0.066 sec)

If you go above this limit your application gets an error:

SQL> DELETE FROM test LIMIT 10001; ERROR 1180 (HY000): wsrep_max_ws_rows exceeded

and a ROLLBACK is done but the Galera Cluster is not severely affected.

I hope this hard action helps you keep your Galera Cluster up and running a bit more as before.

Disadvantages of Streaming Replication
  • Only works in Galera 4 and newer (MariaDB 10.4 and MySQL 8.0).
  • It is a "new" feature and not yet widely used. So you might hit some unknown bugs.
  • Streaming Replication increases the load on the node, which may adversely affect its performance. [ 1 ]
  • It is recommended that you only enable Streaming Replication at a session-level and then only for transactions that would not run correctly without it. [ 1 ]
  • The rollback operation consumes system resources on all nodes. When long-running write transactions frequently need to be rolled back, this can become a performance problem. [ 1 ]

Therefore, it is a good application design policy to use shorter transactions whenever possible. In the event that your application performs batch processing or scheduled housekeeping tasks, consider splitting these into smaller transactions in addition to using Streaming Replication. [ 1 ]

IMHO Streaming Replication is a typical feature which was implement because the users want it and are to lazy to fix their stuff and not because it technically makes sense.

Literature
Taxonomy upgrade extras: galeragalera clustertransaction

Databases are standardized but in detail they behave different

Shinguz - Wed, 2021-02-10 11:47

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 | | 27e614f1b4490a6db1b26364e467d361 | | 285e3d84b81d97a40d66049d2f30071f | | 2db85e2f2639d637ee21888ca34334d7 | | 2f0e944ca977826730c352a1920cda1f | +----------------------------------+ 11 rows in set (0.00 sec)

Now comes the interesting part: We want the minimum and the maximum of the first chunk:

mysql> SELECT MIN(id), MAX(id) FROM t_my ORDER BY id LIMIT 10; +----------------------------------+----------------------------------+ | MIN(id) | MAX(id) | +----------------------------------+----------------------------------+ | 01a6e76643c83c91867636ce90a8def5 | f685b7269d76f47e7517cdd5fc4253bf | +----------------------------------+----------------------------------+

And this is completely not expected (aka wrong?)! Instead of the highest Primary Key of the chunk MySQL returns the highest Primary Key of the whole table:

mysql> SELECT MIN(id), MAX(id) FROM t_my; +----------------------------------+----------------------------------+ | MIN(id) | MAX(id) | +----------------------------------+----------------------------------+ | 01a6e76643c83c91867636ce90a8def5 | f685b7269d76f47e7517cdd5fc4253bf | +----------------------------------+----------------------------------+

If we look at how this is executed we see that the MySQL Optimizer took some kind of short cut:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

And from this result it does a LIMIT 10 on just one tuple which ends up in the result above. If this is a bug or not I cannot say. But it is at least not what I want.

Now we want to know how other RDBMS are dealing with this problem:

PostgreSQL 11 shell> su - postgres shell> psql postgres=# CREATE TABLE t_pg ( id char(32) PRIMARY KEY ); postgres=# INSERT INTO t_pg SELECT MD5(RANDOM()::TEXT) FROM t_pg; ... create more that 10 rows postgres=# SELECT id FROM t_pg LIMIT 11; id ---------------------------------- 9433b59ec7e14b1232743b3bdcc745a0 56c59c2ce35e79b8f4141160b6dbcb69 dbfe35456b12b741c7e20a973a65fcac f5bbd52a92c7c0f63b5bdf14e0b1b020 e9b11d9243c701155f43506f7da95076 aba50026e35562d867398ddb5e1ffc37 586b98cfb8d7b19bf09f32e611298be5 3fd768fda852972d096a015be675233c 8c33a72edf0479298093b83a2d53ad59 98dfbe2979df25d8169747ee15bced07 5f7594d8b9de2694b4438d62579d658d (11 rows)

Now comes the interesting part:

postgres=# SELECT MIN(id), MAX(id) FROM t_pg ORDER BY id LIMIT 10; ERROR: column "t_pg.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT MIN(id), MAX(id) FROM t_pg ORDER BY id LIMIT 10; ^

PostgreSQL does NOT even allow this query. And complains about the id in the ORDER BY clause. If we write the query "correctly" we get a completely different result. Which is not usable for us:

postgres=# SELECT MIN(id), MAX(id) FROM t_pg GROUP BY id ORDER BY id LIMIT 10; min | max ----------------------------------+---------------------------------- 03581a898bcedb0fb2bbb842be2fdaf5 | 03581a898bcedb0fb2bbb842be2fdaf5 277ea3f40d4431cb9f41ac37848605f0 | 277ea3f40d4431cb9f41ac37848605f0 3d007edf4cb9b9ffed10d74ef30f6a4b | 3d007edf4cb9b9ffed10d74ef30f6a4b 3fd768fda852972d096a015be675233c | 3fd768fda852972d096a015be675233c 56c59c2ce35e79b8f4141160b6dbcb69 | 56c59c2ce35e79b8f4141160b6dbcb69 586b98cfb8d7b19bf09f32e611298be5 | 586b98cfb8d7b19bf09f32e611298be5 591ab5d306827cc7a8a3f5d9ee780edc | 591ab5d306827cc7a8a3f5d9ee780edc 5c33d18b907638956469d54630307b9d | 5c33d18b907638956469d54630307b9d 5f7594d8b9de2694b4438d62579d658d | 5f7594d8b9de2694b4438d62579d658d 6245ee76fbbe48d99b359deda7e38c0a | 6245ee76fbbe48d99b359deda7e38c0a (10 rows)

If we do it this way, which is not what we want, we get, similar to MySQL the "wrong" result.

postgres=# SELECT MIN(id), MAX(id) FROM t_pg LIMIT 10; min | max ----------------------------------+---------------------------------- 03581a898bcedb0fb2bbb842be2fdaf5 | f5bbd52a92c7c0f63b5bdf14e0b1b020

This result is the same like the maximum Primary Key:

postgres=# SELECT MIN(id), MAX(id) FROM t_pg; min | max ----------------------------------+---------------------------------- 03581a898bcedb0fb2bbb842be2fdaf5 | f5bbd52a92c7c0f63b5bdf14e0b1b020

If PostgreSQL does it like this, it at least according to the standards, I hope. But NOT what I want.

SQLite 3

The third candidate we have chosen is SQLite3:

sqlite> CREATE TABLE t_sl ( id TEXT PRIMARY KEY );

Here was the challenging part to make the MD5() function work. Luckily we found a nearly perfect solution on StackOverflow.

sqlite> INSERT INTO t_sl SELECT HEX(MD5(RANDOM())) FROM t_sl; ... create more that 10 rows sqlite> SELECT id FROM t_sl LIMIT 11; 467FE9B4EC744D1B4C21C1405936E863 F7A2E0BF53EA5243A734A1FAACCD1D28 3DE4FC5680C9F2E3AB9E7EA4BE7F6D69 0878C0298916B1FBFE7808263CA1703D 56332C0BC2EBCB3D960167CF475B9581 9F8661DE560EF8040B205A58224A2251 5F8A7807F56E604DC8BB595FE0F579B4 A015A9539F3966930F17EE4B545271F6 714E544157E871CE826E5923F84AA096 5FD9F4F71739AB75BD60B94F303973AA 96113F12CDDFEC20E98BA621783E0A6C

Same results as MySQL and PostgreSQL and still not what I want:

sqlite> SELECT MIN(id), MAX(id) FROM t_sl ORDER BY id LIMIT 10; 0249461D8D3516D513F18DE3BC4CE677|FB75ECCB85BDDA88E5B8D48CF056B1CC sqlite> SELECT MIN(id), MAX(id) FROM t_sl; 0249461D8D3516D513F18DE3BC4CE677|FB75ECCB85BDDA88E5B8D48CF056B1CC
Oracle 18

Now let us have a look how Oracle does it:

sqlplus> CREATE TABLE t_ora ( id CHAR(32) NOT NULL PRIMARY KEY ); sqlplus> INSERT INTO t_ora SELECT STANDARD_HASH(dbms_random.random, 'MD5') FROM dual; ... create more that 10 rows sqlplus> SELECT DISTINCT id FROM t_ora; 264270A59D9EE04668C8F298DF3DF184 CCE8CA725CD633FC1AC2C73C32F0EAF4 9B4C28001530BA8FA8F682597576B88C 215D03A9E409D99C6EB9EAD11CD722CA 770F99E6D2A4929DEE4D54214D1C99E4 39A48517FB5B58403C85317F02AFC167 7E2D9C597602637634C7164811C3FA15 1826607210B081381254F7D1D061B25E 6984D425379806E16AA81424438003BA EBBCAE0E0B263A223D70E94D1020CCE9 7E854FFBAA1EAB1D7E18ADB085975C40 06215B1756DA5926D27BDB0BC47DDEA9 D3CC548842BD1F978326CAED25518533 E80B9F42585E42F1AAE3726EC49FEE7F 62FA08D4EF65DEBEC08F219C1DC4F583 7ADECA10EACA57F9D75AD1CDB4E7C965

Here we get completely confusing results with ROWNUM:

sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 10; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 1826607210B081381254F7D1D061B25E EBBCAE0E0B263A223D70E94D1020CCE9 sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 5; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 C5EA7113397CF161A951ECBB80E1DFEF sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 6; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 E6692DFBF57EB110CAE2169A4204C37B sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 7; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 E6692DFBF57EB110CAE2169A4204C37B sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 8; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 E6692DFBF57EB110CAE2169A4204C37B sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 9; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 EF34D8DBC0832BFD813939FB1840804D sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 2 order by id ; MIN(ID) MAX(ID) -------------------------------- -------------------------------- B54068FF7AC56D6D8200F4E44410DCC6 C5EA7113397CF161A951ECBB80E1DFEF sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 3 order by id ; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 5D7FB42D47FB5F6F220B3872B48AC8ED C5EA7113397CF161A951ECBB80E1DFEF sqlplus> SELECT MIN(id), MAX(id) FROM t_ora WHERE ROWNUM <= 4 order by id; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 1CF93D49926CC3D9F83FC639B423A7F7 C5EA7113397CF161A951ECBB80E1DFEF

This is explainable according to my contact because Oracle does not guarantee ROWNUM and it is a new set of data (ROWNUM Pseudocolumn).

But rewriting the query would at least give us the right result.

sqlplus> SELECT MIN(id), MAX(id) FROM (SELECT id FROM t_ora ORDER BY id) WHERE ROWNUM <= 5 ; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 32ABDC3657FAE00B8EEE6EB2C42C12F1 sqlplus> SELECT MIN(id), MAX(id) FROM (SELECT id FROM t_ora ORDER BY id) WHERE ROWNUM <= 6 ; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 38FC2D4348D7A44C834D008F5B7BBD5E

I am pretty sure that this Query Execution Plan on Oracle would also look pretty bad and will not perform on huge data sets as we plan to have for our fancy application.

And in this case Oracle behaves like the other databases:

sqlplus> SELECT MIN(id), MAX(id) FROM t_ora FETCH FIRST 10 ROWS ONLY ; MIN(ID) MAX(ID) -------------------------------- -------------------------------- 06FDD744640D35C1E527AB05B8379349 F5D8266D3646C57F6AB3E6001D305A2C

Thanks to Markus R. for assistance on Oracle!

SQL Server 2019

And finally the last candidate to test is Microsoft SQL Server 2019 on Ubuntu 18.04:

shell> sqlcmd -S localhost -U SA mssql> CREATE TABLE t_ms ( ID CHAR(32) NOT NULL PRIMARY KEY ) GO mssql> INSERT INTO t_ms SELECT CONVERT(VARCHAR(32), HashBytes('MD5', STR(RAND(), 25, 20)), 2) GO ... create more than 10 rows mssql> SELECT id FROM t_ms ORDER BY id OFFSET 0 ROWS FETCH NEXT 11 ROWS ONLY GO id -------------------------------- 3203A25102554923CA11BD80C99D2728 3A0D79AA2466AE0EA580295FD5C81145 3DFE9A0A1FDD2654C6BBB24680D13B15 6A5CF25DDFE0278674EE98E02B5C4B38 8CAC11376C31E22E4AEF8214F31AA36B 96C5362832286577A3FB72A840855DFA A0CA369CAAE540A2A3E92317DC5B939F A5543C6D1244357CA89DD8B16A85E9EF B98148167FC627C0EDF632C981E9296B C50B5BC636BC29D2EDB13C5C1749F7D9 E633FD894FBFD6CAA20C7C4182D8EEBC (11 rows affected)

Similar error like PostgreSQL:

mssql> SELECT MIN(id), MAX(id) FROM t_ms ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY GO Msg 8127, Level 16, State 1, Server ubuntu1804, Line 10 Column "t_ms.ID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

And unusable results as with the other RDBMS:

mssql> SELECT MIN(id), MAX(id) FROM t_ms GROUP BY id ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY GO -------------------------------- -------------------------------- 3203A25102554923CA11BD80C99D2728 3203A25102554923CA11BD80C99D2728 3A0D79AA2466AE0EA580295FD5C81145 3A0D79AA2466AE0EA580295FD5C81145 3DFE9A0A1FDD2654C6BBB24680D13B15 3DFE9A0A1FDD2654C6BBB24680D13B15 6A5CF25DDFE0278674EE98E02B5C4B38 6A5CF25DDFE0278674EE98E02B5C4B38 8CAC11376C31E22E4AEF8214F31AA36B 8CAC11376C31E22E4AEF8214F31AA36B 96C5362832286577A3FB72A840855DFA 96C5362832286577A3FB72A840855DFA A0CA369CAAE540A2A3E92317DC5B939F A0CA369CAAE540A2A3E92317DC5B939F A5543C6D1244357CA89DD8B16A85E9EF A5543C6D1244357CA89DD8B16A85E9EF B98148167FC627C0EDF632C981E9296B B98148167FC627C0EDF632C981E9296B C50B5BC636BC29D2EDB13C5C1749F7D9 C50B5BC636BC29D2EDB13C5C1749F7D9 (10 rows affected) mssql> SELECT MIN(id), MAX(id) FROM t_ms GO -------------------------------- -------------------------------- 3203A25102554923CA11BD80C99D2728 F8FFC36AB5AE80748CE6248EE9C4ACD8 (1 rows affected)

Microsoft SQL Server seems to behave similar to PostgreSQL. And IMHO Microsoft SQL Server is really unhandy on the CLI.

Some help to make MS SQL Server work:


I do not know why it did not accept the password in the first run.

Solution

The "correct" (aka wanted) result we get like this which is a similar solution as Markus proposed above:

mysql> SELECT MIN(id), MAX(id) FROM (SELECT id FROM t_my ORDER BY id LIMIT 10) AS x; +----------------------------------+----------------------------------+ | MIN(id) | MAX(id) | +----------------------------------+----------------------------------+ | 01a6e76643c83c91867636ce90a8def5 | 2db85e2f2639d637ee21888ca34334d7 | +----------------------------------+----------------------------------+

The MySQL Query Execution Plan does not really look too cool but is acceptable:

+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL | | 2 | DERIVED | t_my | NULL | index | NULL | PRIMARY | 32 | NULL | 10 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
Final thoughts

Linux (Debian and Ubuntu) is really cool because we were capable to test 4 of the these 5 different database products within a very short time (2 hours from scratch install). There is just one product which causes a bit more headache. But for this product one has friends...

And finally we found a completely different and much more generic approach for the problem to solve in our fancy application...

Taxonomy upgrade extras: postgresqlsqliteSQL ServerOraclemysql

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.3 has been released

Shinguz - Mon, 2021-01-18 14:35

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 describe 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.

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 maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General
  • myEnv library updated.

FromDual Backup Manager
  • Bman wrongly complains for fpmmm cache file if it does not exists.
  • Separate function doFullLogicalBackup into own file.
  • Serious bug: 3 return codes were not returned correctly as errors. Probably introduced in 2.2.2.

FromDual Recovery Manager
  • none

FromDual brman Catalog
  • none

FromDual brman Data masking / data obfuscating
  • none

Testing
  • Test added for fpmmm cache file function.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

MariaDB Push Replication

Shinguz - Mon, 2021-01-11 17:29
Table of Contents
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 privilege and no other privileges. This means that this user is only allowed to request Binary Logs and nothing else.
    • This user can additionally be restricted to the IP address of the Slave machine (e.g. 'replication'@'192.168.1.42'). And thus only from this Slave machine this user can access to its Master.
    • Additionally firewall rules (iptables) can further restrict access from source IP (Slave) to destination IP (Master) on Port (3306) and Protocol (TCP) between Master and Slave. So nobody can get anywhere else from the non secure zone.
    • Only allow SSL connections from the Slave to the Master (require_secure_transport or REQUIRE SSL).
    • The use of secure password goes without saying.
  • On the Slave:
    • Access only via SSL to the Master (this can be enforced on the Master). So nobody can listen to the Slave → Master → Slave communication. This can be enforced globally or per account. If this is not sufficient the whole set-up can be secured with VPN (stability?).
  • If you upgrade Master and Slave on a regular base every 3 months (MariaDB/Oracle CPU) the chances are very small to be hurt by potential security holes.
  • With an intrusion detection system and an data integrity tools you can further secure your Slave (and Master) system and detect potential manipulations or attacks.
  • Security features like SElinux (Rocky Linux, RedHat and SuSE) and AppArmor (Debian and Ubuntu) additionally can be activated (or better should not be disabled at all!).
  • If somebody manages to take over the machine of your Slave in the less secure zone (with O/S user root) you have:
    • Done something wrong.
    • Lost anyway.

If this arguments are not sufficient to convince your Chief Security Officer (CSO) we have some other ideas how to deal with the problem:

MariaDB Push Replication

The first idea is that we do a Push Replication from the more secure network zone to the less secure network zone instead of a Pull Replication. But MariaDB does not provide this feature natively. So we have to build it ourself. For a Proof of Concept (PoC) we wrote 2 little programs:

  • One for pushing the Binary Logs from the Master to Slave (binlog_push.php) and
  • another program for applying the Binary Logs (binlog_apply.php) on the Slave.

These 2 programs are started every minute via crontab. So we get a pulsating Push Replication with a maximum lag of about 3 minutes. With this rhythm after barely 2 years the 6-digit Binary Log numbers will overflow. But Monty stated that the Binary Log numbers then just become 7-digit long.

We were running this PoC with our new mixed test workload (mixed_test.php) and it looks like this way of Push Replication is working correctly (data on Master and Slave were the same). This mechanism behaves similarly like the normal MariaDB Master/Slave Pull Replication: The push program will throw errors if there is a problem and the apply program will also throw an error and stop if it cannot apply the Binary Log events.

Current limitations are:

  • Only one Slave is supported.
  • Only full Binary Log Push is supported. Partial Binary Log Push could be implemented.
  • GTID based Slave set-up is not considered (or better tested) yet.

The push program on the Master is simply started like this (as alternative to crontab):

shell> watch -n 60 ./binlog_push.php

The Slave is set-up as normal and the Binary Log applier program on the Slaves ist started like this:

shell> mariadb-dump --user=root --master-data=1 --single-transaction --all-databases | mariadb --user=root shell> ./binlog_apply.php --start-logfile=binlog.000001 --start-position=678901234 shell> watch -n 60 ./binlog_apply.php

Pushing data with the FederatedX Storage Engine and Triggers

An other possibility to transfer the data from the Master to the Slave is using the FederatedX Storage Engine and Triggers to move the data from the original tables to the FederatedX tables.

This method is a bit less convenient if you want to transfer the data of many or all tables to the Slave. If you want to transfer only a few tables this might work quite well.

Creating the FederatedX tables with federated Server as data source: SQL> SELECT plugin_name, plugin_version, plugin_maturity FROM information_schema.plugins WHERE plugin_type = 'STORAGE ENGINE' AND plugin_name = 'FEDERATED' ; +-------------+----------------+-----------------+ | plugin_name | plugin_version | plugin_maturity | +-------------+----------------+-----------------+ | FEDERATED | 2.1 | Stable | +-------------+----------------+-----------------+ SQL> INSTALL SONAME 'ha_federatedx';
SQL> CREATE SERVER 'mysql-57' FOREIGN DATA WRAPPER 'mysql' OPTIONS ( HOST '127.0.0.1' , PORT 3320 , SOCKET '' , USER 'app' , PASSWORD 'secret' , DATABASE 'test' ); SQL> SELECT * FROM mysql.servers; +-------------+-----------+------+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+------+----------+----------+------+--------+---------+-------+ | mysql-57 | 127.0.0.1 | test | app | secret | 3320 | | mysql | | +-------------+-----------+------+----------+----------+------+--------+---------+-------+ SQL> CREATE TABLE `test_fed` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql-57' ; ERROR 1434 (HY000): Can't create federated table. Foreign data src error: database: 'test' username: 'app' hostname: '127.0.0.1' SQL> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------+ | Error | 1434 | Can't create federated table. Foreign data src error: database: 'test' username: 'app' hostname: '127.0.0.1' | | Warning | 1030 | Got error 1 "Operation not permitted" from storage engine FEDERATED | +---------+------+-----------------------------------------------------------------------------------------------------------------+

There reason for this error was just, that the underlying table on the remote system did not exist! But when we found out what was the problem we realized that a SERVER is possibly not the way we want to do it because the tables on Master and Slave must be named the same. Probably it is better to use direct connections because then we can have different table names on Master an Slave.

It would be a nice feature to have some kind of rewrite for tables in the SERVER.

Creating the FederatedX tables with a direct connection: SQL> CREATE TABLE `test_fed` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://app:secret@127.0.0.1:3320/test/test' ; SQL> SELECT * FROM test_fed; +----+----------------------------------+---------------------+ | id | data | ts | +----+----------------------------------+---------------------+ | 1 | Test data insert | 2021-01-06 09:27:22 | | 2 | Test data insert | 2021-01-06 09:27:03 | | 3 | Test data insert | 2021-01-06 09:25:37 | +----+----------------------------------+---------------------+

The FederateX variable federated_pushdown does currently not work properly for me and is buggy (MDEV-2453):

SQL> SHOW VARIABLES LIKE '%federat%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | federated_pushdown | OFF | +--------------------+-------+ SQL> SET GLOBAL federated_pushdown = on; SQL> SELECT id, data FROM test_fed WHERE id = 1; ERROR 1030 (HY000): Got error 10000 "Unknown error 10000" from storage engine FEDERATED
Creating the Triggers to feed the FederatedX tables

Now we have the connection between Master and Slave but somehow the data must come from the main table (test) to the FerderatedX table (test_fed). This will be achieved by Triggers:

SQL> DELIMITER // CREATE TRIGGER test_insert AFTER INSERT ON test FOR EACH ROW INSERT INTO test_fed VALUES (NEW.id, NEW.data, NEW.ts) ; // CREATE TRIGGER test_update AFTER UPDATE ON test FOR EACH ROW UPDATE test_fed SET id = NEW.id, data = NEW.data, ts = NEW.ts WHERE id = OLD.id ; // CREATE TRIGGER test_delete AFTER DELETE ON test FOR EACH ROW DELETE FROM test_fed WHERE id = OLD.id ; // DELIMITER ; SQL> SELECT CONCAT(trigger_schema, '.', trigger_name) AS 'trigger', event_manipulation AS event, CONCAT(event_object_schema, '.', event_object_table) AS 'table', action_timing AS timing FROM information_schema.triggers; +------------------+--------+-----------+--------+ | trigger | event | table | timing | +------------------+--------+-----------+--------+ | test.test_insert | INSERT | test.test | AFTER | | test.test_update | UPDATE | test.test | AFTER | | test.test_delete | DELETE | test.test | AFTER | +------------------+--------+-----------+--------+

After running our mixed_test.php again the comparison of the data was fine. Number and content was the same.

Literature
Traffic mirroring with MariaDB MaxScale or ProxySQL

The last idea, which comes to my mind, is mirroring the traffic with a Proxy for example MariaDB MaxScale or ProxySQL as described in the article: Traffic mirroring with MariaDB MaxScale.

The disadvantage here is, at least under high pace, that we loose some information (statements) on the tee'd instance. At least for MariaDB MaxScale. It was further suggested to use the Mirror Router instead of the Tee Filter. If this also happens with ProxySQL we cannot say yet. So this method is possibly not ideal for reliably pushing data from a Master to a Slave right now.

There is an open Bug which is currently under investigation: Tee filter loses statements if branch target is slower.

Literature
Taxonomy upgrade extras: mariadbreplicationsecurity

VSZ behaviour with MariaDB MEMORY tables

Shinguz - Tue, 2021-01-05 17:08

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 test_m1 LIKE test; SQL> ALTER TABLE test_m1 ENGINE = MEMORY; SQL> INSERT INTO test_m1 SELECT * FROM test; SQL> INSERT INTO test_m1 SELECT NULL, data, NULL FROM test_m1; ... ERROR 1114 (HY000): The table 'test_m1' is full

Creation of MEMORY table 2 (32 - 38):

SQL> CREATE TABLE test_m2 LIKE test_m1; SQL> INSERT INTO test_m2 SELECT NULL, data, NULL FROM test_m1 LIMIT 100000; ... ERROR 1114 (HY000): The table 'test_m2' is full

Creation of MEMORY table 3 (45 - 48):

SQL> CREATE TABLE test_m3 like test_m1; SQL> INSERT INTO test_m3 SELECT NULL, data, NULL FROM test_m1 LIMIT 500000; ... ERROR 1114 (HY000): The table 'test_m3' is full

Truncation of all 3 MEMORY tables (57 - 58):

SQL> TRUNCATE TABLE test_m1; SQL> TRUNCATE TABLE test_m2; SQL> TRUNCATE TABLE test_m3; Drop of all 3 MEMORY tables had no effect (ca. 65): SQL> DROP TABLE test_m1; SQL> DROP TABLE test_m2; SQL> DROP TABLE test_m3;

Restart of the database process releases the memory (71 - 74):

shell> restart ... SUCCESS! Timeout is 60 seconds: . SUCCESS!

Taxonomy upgrade extras: memory tablememoryoomswap

Traffic mirroring with MariaDB MaxScale

Shinguz - Thu, 2020-12-24 16:27

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 scenarios where this could be useful as well.

Installation

For our test set-up we were using MariaDB MaxScale version 2.5.6:

shell> maxscale --version MaxScale 2.5.6

The MariaDB MaxScale version seems to be quite important because MariaDB changed a lot in MaxScale in the past and it is not always backwards compatible!

Because MariaDB does not provide binary tar-balls for MaxScale we extracted them ourself from the DEB packages. To make it work we have to set the LD_LIBRARY_PATH and the PATH environment variables:

shell> BASEDIR='/home/mysql/product/maxscale' shell> export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${BASEDIR}/lib/x86_64-linux-gnu/maxscale shell> export PATH=${PATH}:${BASEDIR}/bin shell> maxscale --help
Configuration

MariaDB MaxScale documentation is not really complete and some actual and good examples are missing a bit. So we had to do some experiments. After these experiments we came to a configuration which worked well for our case (please let me know if there are better ways to do it):

# # /home/mysql/etc/maxscale_load_split.cnf # [Load-Split-Listener] type=listener service=Split-Service protocol=MariaDBClient address=0.0.0.0 port=3392 [Split-Service] type=service router=readconnroute servers=mariadb-105 filters=TeeFilter user=maxscale password=secret [TeeFilter] type=filter module=tee target=mysql-57 match=/.*/ # exclude=/truncate*/ [Monitor] type=monitor module=mariadbmon servers=mariadb-105,mysql-57 user=maxscale password=secret monitor_interval=60000 [mariadb-105] type=server address=192.168.1.108 port=3357 protocol=MariaDBBackend [mysql-57] type=server address=192.168.1.108 port=3320 protocol=MariaDBBackend

Caution: This configuration probably only works for MaxScale 2.5 and newer. For details see: [ 2 ].

Starting MariaDB MaxScale

First we did a check of the configuration file:

shell> maxscale --nodaemon --config=/home/mysql/etc/maxscale_load_split.cnf \ --log=stdout --libdir=${BASEDIR}/lib/x86_64-linux-gnu/maxscale \ --persistdir=/home/mysql/tmp --datadir=/home/mysql/tmp --logdir=/home/mysql/log \ --piddir=/home/mysql/tmp --cachedir=/home/mysql/tmp/cache \ --config-check

Here we hit a bug. Or at least a bug in my opinion. But MariaDB support decided, that it is not a bug: [ 7 ]. Then we started MariaDB MaxScale:

shell> maxscale --nodaemon --config=/home/mysql/etc/maxscale_load_split.cnf \ --log=stdout --libdir=${BASEDIR}/lib/x86_64-linux-gnu/maxscale \ --persistdir=/home/mysql/tmp --datadir=/home/mysql/tmp --logdir=/home/mysql/log \ --piddir=/home/mysql/tmp --cachedir=/home/mysql/tmp/cache
Creating users

Then we found out (it was not very well documented) that we need a user for MaxScale with the following privileges:

SQL> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'secret'; SQL> GRANT SELECT ON mysql.user TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.db TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; SQL> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';

And we also need an application user for doing the tests:

SQL> CREATE USER 'app'@'%' IDENTIFIED BY 'secret'; SQL> GRANT ALL ON test.* TO 'app'@'%';
Testing and observations

We were running some simple manual tests, than our famous insert test and last our new mixed test. On the first look it looks like everything was working fine. The load was split on both servers (I checked with the General Query Log) and they executed all the queries simultaneously. I knew that the second server was configured in a way it was processing the queries slower than the first one and thus I was wondering what happens.

If one back-end is lagging, in our case it was lagging more than 450 seconds, we found that the disconnect was not done properly. Then we found, that some rows were missing. So it seems like we have some Statement Cache overflow.

On the main instance (mariadb-105):

SQL> SELECT COUNT(*) FROM test.test; +----------+ | count(*) | +----------+ | 221056 | +----------+

The General Query Log looks as follows:

... 3655 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 3655 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 3655 Quit

On the tee'd instance (mysql-57):

SQL> SELECT COUNT(*) FROM test.test; +----------+ | count(*) | +----------+ | 190466 | +----------+

The General Query Log looks as follows:

... 2020-12-24T08:19:24.497319Z 4322 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 2020-12-24T08:19:25.430806Z 4322 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP())

So we lost about 30k rows on the tee'd instance!!! And this without any error or warning. For this behaviour we filed a bug [ 4 ]. And thus this feature is not usable for production IMHO atm. If somebody has a solution for this, please let me know (documentation did not state anything).

Beside of loss of data we further found, that the data were not 100% equal. Because the statements are routed asynchronously it can be, that some statement are executed at different times:

On the main instance:

| 910 | Test data insert | 2020-12-24 09:23:36 |

On the tee'd instance:

| 910 | Test data insert | 2020-12-24 09:23:37 |

One second difference! We did not investigate further... Other functions like RAND(), NOW(), etc. will behave similarly.

When we throttled the pace from 10 µs sleep between statements to 10 ms between statements we have not seen losses any more (number of rows and checksum was correct). But we cannot know for sure (because no warnings).

What about the original Binary Log problem?

We have not observed the described behaviour with a mixed INSERT, UPDATE and DELETE workload:

On the main instance:

| chef_mariadb-105_binlog.000103 | 3017223 |

On the tee'd instance:

| chef_mysql-57-binlog.000601 | 4633484 |

In contrary: the MySQL Binary Logs were about 50% bigger than the MariaDB Binary Logs. So our customer must have hit a special query pattern where MariaDB behaves worse related to Binary Log size than MySQL.

Literature
Taxonomy upgrade extras: mariadbmaxscalebinary logload balancer

MariaDB Galera Cluster Upgrade Path

Shinguz - Fri, 2020-12-18 10:47

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 lower version to higher version is supported.
  • We should upgrade to the newest version of a Release Series first before upgrading to the next major Release Series.
  • MariaDB supports skipping of Major Release Series in general but we should consider the Galera plug-in as well.
  • New versions in a Release Series can have new bugs.
  • We should make sure that also application side MariaDB connectors are ready for the new version and not only the MariaDB database server. In MariaDB this is a bit less of an issue than with MySQL but it can happen as well (see 10.4.17).
  • Upgrading with dump/restore (without the mysql schema) should always work also skipping Major Release Series or Galera versions.
  • Following the Upgrade Path can take 3 to 7 steps. So it makes sense to not postpone upgrades too long into the future!
  • To reduce impact on service and having better testing possibilities use traditional Master (old) → Slave (new) Replication.
  • To not get a kick in the teeth you should test your MariaDB Galera Cluster Upgrade carefully (see for example 10.3.27, 10.4.16 and 10.4.17 (MDEV-24229, MDEV-23851 and MDEV-24406)).
  • Do NOT forget to always run mysql_upgrade/mariadb-upgrade after each and every upgrade!

MariaDB Galera Cluster Upgrade from 5.5 to 10.0

At these times we had the change from Galera 2 to Galera 3. You should upgrade to the newest MariaDB 5.5 version first and then do the upgrade to MariaDB 10.0:

If you are upgrading from the most recent MariaDB Galera Cluster 5.5 release to MariaDB Galera Cluster 10.0, then the versions will be compatible. The latest releases of MariaDB Galera Cluster 5.5 and MariaDB Galera Cluster 10.0 use Galera 3, so they should be compatible. [ 2 ]

If a direct upgrade from 5.5 to 10.3 (or any intermediate major version) is working you should test carefully!

MariaDB Galera Cluster Upgrade from 10.0 to 10.1

MariaDB 10.1 is Galera ready by default. This is not so much of a database issue but more of an installation issue:

Since MariaDB 10.1, the MySQL-wsrep patch has been merged into MariaDB Server. Therefore, in MariaDB 10.1 and above, the functionality of MariaDB Galera Cluster can be obtained by installing the standard MariaDB Server packages and the Galera wsrep provider library package. [ 3 ] MariaDB Galera Cluster Upgrade from 10.1 to 10.2 and 10.2 to 10.3

Here we have no huge changes so we do not expect any significant problems.

MariaDB Galera Cluster Upgrade from 10.3 to 10.4

In MariaDB 10.4 we have the new Galera version 4 plug-in with the new streaming replication functionality. An upgrade is possible from newest MariaDB 10.3 to MariaDB 10.4 as follows:

If you are upgrading from the most recent MariaDB 10.3 release to MariaDB 10.4, then the versions will be compatible. MariaDB 10.3 uses Galera 3, and MariaDB 10.4 uses Galera 4. This means that upgrading to MariaDB 10.4 also upgrades the system to Galera 4. However, Galera 3 and Galera 4 should be compatible for the purposes of a rolling upgrade, as long as you are at least MariaDB 10.4.4 or later. [ 6 ] MariaDB Galera Cluster Upgrade from 10.4 to 10.5

Here again we have no huge changes so we do not expect any significant problems.

Skipping MariaDB Major Release Series

About skipping Major Releases MariaDB documentation states:

You should be able to trivially upgrade from ANY earlier MariaDB version to the latest one (for example MariaDB 5.5.x to MariaDB 10.5.x), usually in a few seconds. [ 9 ] MariaDB Galera Cluster Upgrade Path

And now the visual MariaDB Galera Cluster Upgrade Path:

Literature
Taxonomy upgrade extras: mariadbgaleraclusterupgrade

How to force InnoDB Buffer Pool flushing

Shinguz - Thu, 2020-12-10 17:38

InnoDB tries to keep pages in Buffer Pool to be fast. If a page is changed by a DML statement (INSERT, UPDATE, DELETE) this change will be done in InnoDB Buffer Pool and not directly on disk. But those changed InnoDB pages residing in InnoDB Buffer Pool must be flushed sooner or later to disk to become persistent. This is done by the InnoDB background writer thread(s) (default 4).

InnoDB flushes the dirty pages with a pace of innodb_io_capactiy (default 200) pages/s. This variable should be set depending on the rate you are dirtying pages and on the capacity of your I/O system. 1 single server HDD has an I/O capacity of about 200 IOPS, a SSD between 1000 and 50000 IOPS.
The rate of dirtying pages depends on the number of DML statements and the locality of the changes in your database blocks (random vs. sequential, AUTO_INCREMENT vs. UUID).

Keeping many dirty pages in InnoDB Buffer Pool is good from performance point of view. But in certain cases you want to have the number of dirty pages small or even close to zero. This case is during Backups done with MEB. LVM snapshots should in theory not have this problem (otherwise MySQL/InnoDB would not be crash-safe) and MariaDB Backup (mariabackup) and Percona Xtrabackup (xtrabackup) can deal with the problem yet. See: Mariabackup - Concurrent DDL and Backup Issues.

With the new optimized (without redo logging) DDL operations in MySQL 5.7 we were running into the following problems with MEB:

ERROR: InnoDB: An optimized(without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet. MEB will not be able take a consistent backup. Retry the backup operation.

We found out, that systems with a smaller InnoDB Buffer Pool had this problem much less frequent than systems with a bigger InnoDB Buffer Pool. Thus we came to the idea instead of shrinking the InnoDB Buffer Pool to reduce the number of dirty pages in the Buffer Pool.

This is similar to a Checkpoint how it is called in other RDBMS. But I found that the term checkpoint does not always mean the exact same thing in different RBDMS. Further details you can find in Literature.

To force a Checkpoint you can lower the variable innodb_max_dirty_pages_pct variable to 0 before doing a MEB backup and then increasing it again to its original value after the MEB backup. This should reduce the probability of running into the error mentioned above.
Reducing innodb_max_dirty_pages_pct to 0 will possibly lead to an I/O burst on the disk. So lowering the value could be done in smaller decrements.

An other possibility, which is probably much less intrusive, would be to increase innodb_io_capacity to a higher value. But this only works if your I/O system is capable to deal with the higher amount of I/O.

Literature
Taxonomy upgrade extras: innodbbuffer poolflushingmebMySQL Enterprise BackupBackup

Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster

Shinguz - Sat, 2020-11-21 20:58

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 works as well!
  • It is recommended to upgrade to the newest MariaDB minor release first [ 5 ] before you upgrade to a new major release. This reduces the risk that you run into already known and fixed bugs.
  • Take a look at Upgrading from MariaDB 10.4 to MariaDB 10.5 [ 1 ] to see what has changed between the major versions.
  • New MariaDB Major Release may behave differently than older MariaDB Major Release. Thus you should test the new major release series first before putting it into production!
  • Ideally, you want to have a large enough Galera Cache to avoid a State Snapshot Transfer (SST) during the rolling upgrade. The Galera Cache size can be configured by setting the Galera variable gcache.size.
    For example: wsrep_provider_options = "gcache.size=2G"

Performing a Rolling Upgrade

The following steps can be used to perform a rolling upgrade from MariaDB 10.4 to MariaDB 10.5 when using Galera Cluster. In a rolling upgrade, each node is upgraded individually, so the cluster is always operational. There is no downtime from the application's perspective.

For each node, perform the following steps:

  • Modify the repository configuration, so the system's package manager installs MariaDB 10.5.
  • If you use a load balancer such as MaxScale, ProxySQL or HAProxy, make sure to drain the node from the pool so it does not receive any new connections.
  • Stop the MariaDB node.
  • Uninstall the old version of MariaDB and the Galera wsrep provider library.
  • Install the new version of MariaDB and the Galera wsrep provider library.
  • Make any desired changes to configuration options in option files, such as my.cnf. This includes removing any system variables or options that are no longer supported.
  • On Linux distributions that use systemd you may need to increase the service start-up timeout as the default timeout of 90 seconds may not be sufficient.
  • Start the MariaDB node.
  • Run mariadb-upgrade with the --skip-write-binlog option (I personally think this option is not necessary because it is the default).
    mariadb-upgrade does two things:
    • 1. Ensures that the system tables in the mysql database are fully compatible with the new version.
    • 2. Does a very quick check of all tables and marks them as compatible with the new version of MariaDB.

When this process is done for one node, move onto the next node.

Note: When upgrading the Galera wsrep provider library, sometimes the Galera protocol version can change. The Galera wsrep provider should not start using the new protocol version until all cluster nodes have been upgraded to the new version, so this is not generally an issue during a rolling upgrade.
However, this can cause issues if you restart a non-upgraded node in a cluster where the rest of the nodes have been upgraded already.

License

This page is licensed as follows: CC-BY-SA / GNU FDL/

Literature
Taxonomy upgrade extras: mariadbupgradeclustergalera clusterrolling upgrade10.410.5

Partial Restore of a Table into a MariaDB Galera Cluster

Shinguz - Fri, 2020-11-20 15:08

In my former Blog Post Partial Table or Schema restore from mariabackup full backup we worked out the basics of a partial restore of a table into a MariaDB database instance.

An now we use this know-how to try the same procedure on a Galera Cluster.

The backup is done in the exact same way as described in the mentioned article. We can even use the backup made there.

For the restore we use the following procedure:

Prepare and Restore a table # BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/magal-105-a/data" # SCHEMA="world" # TABLE="City" # mariabackup --prepare --export \ --databases="${SCHEMA}" \ --tables="${TABLE}" \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR}

But now comes the little difference to a simple MariaDB database instance. The following operations have to be done on ALL nodes of the Galera Cluster:

SQL> ALTER TABLE `world`.`City` DISCARD TABLESPACE;

Restore all the files from the backup:

# scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.ibd mysql@node[1-3]:${DATADIR}/${SCHEMA}/ # scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.cfg mysql@node[1-3]:${DATADIR}/${SCHEMA}/

Then re-import the tablespace again:

SQL> ALTER TABLE `world`.`City` IMPORT TABLESPACE;

And finally clean-up:

# rm -f ${DATADIR}/${SCHEMA}/${TABLE}.cfg
Taxonomy upgrade extras: BackupRestoreschemadatabasephysical backupmariabackuptable restoreschema restorepartial restoredatabase restore

Pages

Subscribe to FromDual aggregator - MySQL Tech-Feed (en)