Agrégateur de flux

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

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

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

• 1 Decision matrix / Entscheidungsmatrix

Taxonomy upgrade extras: mariadbmysql

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

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

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

Items found in the MariaDB Jira database

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

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

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:

• Changes and Improvements in MariaDB 10.4
• Changes and Improvements in MariaDB 10.5

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

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

• 1Pacemaker, Heartbeat, Corosync, WTF?
• 2Corosync vs Pacemaker: wrong usage of "Corosync"
• 3Configuring the iptables Firewall to Allow Cluster Components
• 4Unbreakable MySQL Cluster with Galera and Linux Virtual Server (LVS)
• 5Making HAProxy High Available for MySQL Galera Cluster
• 6MariaDB master/master GTID based replication with keepalived VIP
• 7Clusters from Scratch
• 8Perform a Failover
• 9Prevent Resources from Moving after Recovery
• 10What value should I set for the tcp_retries2 parameter?
• 11Setting user permissions for a Pacemaker cluster

Taxonomy upgrade extras: galeragalera clusterkeepalivedcorosyncpacemakervipHigh Availabilityfail-over

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

• 1Streaming Replication
• 2Galera Cluster about wsrep-max-ws-rows
• 3MariaDB Galera Cluster about wsrep_max_ws_rows
• 4Percona XtraDB Cluster about wsrep_max_ws_rows

Taxonomy upgrade extras: galeragalera clustertransaction

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:

• Quickstart: Install SQL Server and create a database on Ubuntu
• Reset SA password on SQL Server on Linux

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

Das FromDual MariaDB und MySQL Schulungsprogramm 2021 steht jetzt zur Verfügung.

Die Schulungen werden remote oder auch vor Ort bei unseren drei Schulungspartnern in Essen, Köln und Berlin angeboten. Oder ein Seminar, ganz persönlich, remote oder vor Ort, bei Ihnen in der Firma.

Wer neu in der Thematik MariaDB oder MySQL ist, für den ist das Seminar MariaDB/MySQL für Einsteiger vorgesehen. Falls Sie sich mit MariaDB oder MySQL schon auskennen empfehlen wir Ihnen das Seminar MariaDB/MySQL für Fortgeschrittene. Wenn Sie Entwickler und nicht Administrator sind, dann ist das Seminar MariaDB/MySQL für Entwickler genau das richtige für Sie. Und sollten Sie mit dem Gedanken spielen, sich einen Galera Cluster anzulachen, dann zeigen wir Ihnen im Seminar Galera Cluster für MariaDB/MySQL wie man das richtig macht und wo Sie dabei aufpassen sollten.

Die geplanten Schulungstermine für das Jahr 2021 finden Sie, immer auf dem neusten Stand, auf unserer Website: MariaDB und MySQL Schulungstermine.

Taxonomy upgrade extras: schulungtrainingmysql schulungmariadb schulunggalera cluster schulung

Das FromDual MariaDB und MySQL Schulungsprogramm 2021 steht jetzt zur Verfügung.

Die Schulungen werden remote oder auch vor Ort bei unseren drei Schulungspartnern in Essen, Köln und Berlin angeboten. Oder ein Seminar, ganz persönlich, remote oder vor Ort, bei Ihnen in der Firma.

Wer neu in der Thematik MariaDB oder MySQL ist, für den ist das Seminar MariaDB/MySQL für Einsteiger vorgesehen. Falls Sie sich mit MariaDB oder MySQL schon auskennen empfehlen wir Ihnen das Seminar MariaDB/MySQL für Fortgeschrittene. Wenn Sie Entwickler und nicht Administrator sind, dann ist das Seminar MariaDB/MySQL für Entwickler genau das richtige für Sie. Und sollten Sie mit dem Gedanken spielen, sich einen Galera Cluster anzulachen, dann zeigen wir Ihnen im Seminar Galera Cluster für MariaDB/MySQL wie man das richtig macht und wo Sie dabei aufpassen sollten.

Die geplanten Schulungstermine für das Jahr 2021 finden Sie, immer auf dem neusten Stand, auf unserer Website: MariaDB und MySQL Schulungstermine.

Taxonomy upgrade extras: schulungtrainingmysql schulungmariadb schulunggalera cluster schulung

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

Table of Contents

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

How to make MariaDB Pull Replication as secure as possible

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

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

Arguing for the MariaDB Pull Replication How can you secure the Master/Slave set-up in this case:

• On the Master:
  
  • There has to be a user with the REPLICATION SLAVE 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

• About FederatedX
• FederatedX capabilities and limitations
• Trigger Overview

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

• Traffic mirroring with MariaDB MaxScale
• ProxySQL Mirroring

Taxonomy upgrade extras: mariadbreplicationsecurity

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

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

• 1 MXS-1267: Refactor tee filter
• 2 MXS-2029: Tee directly to backend server
• 3 MXS-16: Tee filter: statement router loses statements when other router gets enough ahead
• 4 MXS-3353: tee filter looses statements without any warning
• 5 ProxySQL Mirroring
• 6 Tee Filter
• 7 MXS-3349: maxscale does not consider log location

Taxonomy upgrade extras: mariadbmaxscalebinary logload balancer

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

• 1 Upgrading Galera Cluster
• 2 Upgrading from MariaDB Galera Cluster 5.5 to MariaDB Galera Cluster 10.0
• 3 Upgrading from MariaDB Galera Cluster 10.0 to MariaDB 10.1 with Galera Cluster
• 4 Upgrading from MariaDB 10.1 to MariaDB 10.2 with Galera Cluster
• 5 Upgrading from MariaDB 10.2 to MariaDB 10.3 with Galera Cluster
• 6 Upgrading from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster
• 7 Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster
• 8 Downgrading between Major Versions of MariaDB
• 9 Upgrading Between Major MariaDB Versions

Taxonomy upgrade extras: mariadbgaleraclusterupgrade

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

• OracleFAQs - Checkpoint
• Database Checkpoints (SQL Server)
• SQLShack - Database Checkpoints enhancements SQL Server 2016
• Checkpoints in DBMS
• PostgreSQL Checkpoint
• InnoDB Redo Log - Checkpoints
• More on Checkpoints in InnoDB MySQL 8
• MySQL checkpoint
• MySQL fuzzy checkpointing
• InnoDB Checkpoints
• How InnoDB Performs a Checkpoint
• The relationship between Innodb Log checkpointing and dirty Buffer pool pages
• InnoDB fuzzy checkpoints

Taxonomy upgrade extras: innodbbuffer poolflushingmebMySQL Enterprise BackupBackup

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

• 1 Upgrading from MariaDB 10.4 to MariaDB 10.5
• 2 Upgrading Galera Cluster
• 3 Upgrading from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster
• 4 Downgrading between Major Versions of MariaDB
• 5 Release Notes - MariaDB 10.4 Series

Taxonomy upgrade extras: mariadbupgradeclustergalera clusterrolling upgrade10.410.5

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

Ausschreibungszeitraum: Q4 2020 bis Q2 2021. Später bitte nicht mehr melden.

Einer unserer Kunden sucht eine/n erfahrene/n MariaDB/MySQL Datenbank-Administrator/in. Arbeitspensum: 80 bis 100% in Festanstellung. Arbeitsort: Hauptstadt Bern (Schweiz).
Erfahrung im Betrieb von MariaDB/MySQL Datenbanken im Enterprise-Umfeld sind erforderlich sowie gute MariaDB/MySQL sowie Galera Cluster Kenntnisse notwendig. Einsatzfeld hochkritische, produktive MariaDB Galera Cluster.

Auszug aus der Original-Stellenausschreibung:

Aufgaben:

• Aufsetzen, Testen, Betreiben, Warten und Dokumentieren von Datenbanken und Datenbankservern im Entwicklungs-, Abnahme- und Produktivumfeld inkl. Log und Backup
• Überwachen und Optimieren der Datenbanken und Datenbankservern hinsichtlich Sicherheit und Performance
• Unterhalten der Dokumentationen im DB-Umfeld
• Unterstützung des 3rd-Level-Supports für die von uns betriebenen Webanwendungen, Services und Datenbanken
• Unterstützen der Softwareentwicklerinnen/ Softwareentwickler bei datenbankbezogenen Fragen und Problemen

Kompetenzen:

• Hochschulausbildung im Bereich IT oder grosse Praxiserfahrung in der Datenbankadministration
• Fundierte Erfahrungen im Konzipieren, Optimieren, Administrieren und Betreiben (überwachen, absichern, testen) von SQL und NoSQL-Datenbanken und Datenbankclustern im Webumfeld
• Freude an neuen Technologien und Bereitschaft, sich in neue Themen einzuarbeiten und nach agilen Vorgehensweisen zu arbeiten
• Sehr gute Ausdrucksfähigkeit in Wort und Schrift sowie gute Englischkenntnisse

Wer Interesse hat, soll sich bei mir (Oli Sennhauser) melden, damit ich ihn/sie mit unserem Kunden kurzschliessen kann. FromDual hat kein direktes finanzielles Interesse an dieser Stellenausschreibung!

Taxonomy upgrade extras: mariadbmysqljob descriptionjob

Ausschreibungszeitraum: Q4 2020 bis Q2 2021. Später bitte nicht mehr melden.

Einer unserer Kunden sucht eine/n erfahrene/n MariaDB/MySQL Datenbank-Administrator/in. Arbeitspensum: 80 bis 100% in Festanstellung. Arbeitsort: Hauptstadt Bern (Schweiz).
Erfahrung im Betrieb von MariaDB/MySQL Datenbanken im Enterprise-Umfeld sind erforderlich sowie gute MariaDB/MySQL sowie Galera Cluster Kenntnisse notwendig. Einsatzfeld hochkritische, produktive MariaDB Galera Cluster.

Auszug aus der Original-Stellenausschreibung:

Aufgaben:

• Aufsetzen, Testen, Betreiben, Warten und Dokumentieren von Datenbanken und Datenbankservern im Entwicklungs-, Abnahme- und Produktivumfeld inkl. Log und Backup
• Überwachen und Optimieren der Datenbanken und Datenbankservern hinsichtlich Sicherheit und Performance
• Unterhalten der Dokumentationen im DB-Umfeld
• Unterstützung des 3rd-Level-Supports für die von uns betriebenen Webanwendungen, Services und Datenbanken
• Unterstützen der Softwareentwicklerinnen/ Softwareentwickler bei datenbankbezogenen Fragen und Problemen

Kompetenzen:

• Hochschulausbildung im Bereich IT oder grosse Praxiserfahrung in der Datenbankadministration
• Fundierte Erfahrungen im Konzipieren, Optimieren, Administrieren und Betreiben (überwachen, absichern, testen) von SQL und NoSQL-Datenbanken und Datenbankclustern im Webumfeld
• Freude an neuen Technologien und Bereitschaft, sich in neue Themen einzuarbeiten und nach agilen Vorgehensweisen zu arbeiten
• Sehr gute Ausdrucksfähigkeit in Wort und Schrift sowie gute Englischkenntnisse

Wer Interesse hat, soll sich bei mir (Oli Sennhauser) melden, damit ich ihn/sie mit unserem Kunden kurzschliessen kann. FromDual hat kein direktes finanzielles Interesse an dieser Stellenausschreibung!

Taxonomy upgrade extras: mariadbmysqljob descriptionjob

Ausschreibungszeitraum: Q4 2020 bis Q2 2021. Später bitte nicht mehr melden.

Einer unserer Kunden sucht eine/n erfahrene/n MariaDB/MySQL Datenbank-Administrator/in. Arbeitspensum: 80 bis 100% in Festanstellung. Arbeitsort: Hauptstadt Bern (Schweiz).
Erfahrung im Betrieb von MariaDB/MySQL Datenbanken im Enterprise-Umfeld sind erforderlich sowie gute MariaDB/MySQL sowie Galera Cluster Kenntnisse notwendig. Einsatzfeld hochkritische, produktive MariaDB Galera Cluster.

Auszug aus der Original-Stellenausschreibung:

Aufgaben:

• Aufsetzen, Testen, Betreiben, Warten und Dokumentieren von Datenbanken und Datenbankservern im Entwicklungs-, Abnahme- und Produktivumfeld inkl. Log und Backup
• Überwachen und Optimieren der Datenbanken und Datenbankservern hinsichtlich Sicherheit und Performance
• Unterhalten der Dokumentationen im DB-Umfeld
• Unterstützung des 3rd-Level-Supports für die von uns betriebenen Webanwendungen, Services und Datenbanken
• Unterstützen der Softwareentwicklerinnen/ Softwareentwickler bei datenbankbezogenen Fragen und Problemen

Kompetenzen:

• Hochschulausbildung im Bereich IT oder grosse Praxiserfahrung in der Datenbankadministration
• Fundierte Erfahrungen im Konzipieren, Optimieren, Administrieren und Betreiben (überwachen, absichern, testen) von SQL und NoSQL-Datenbanken und Datenbankclustern im Webumfeld
• Freude an neuen Technologien und Bereitschaft, sich in neue Themen einzuarbeiten und nach agilen Vorgehensweisen zu arbeiten
• Sehr gute Ausdrucksfähigkeit in Wort und Schrift sowie gute Englischkenntnisse

Wer Interesse hat, soll sich bei mir (Oli Sennhauser) melden, damit ich ihn/sie mit unserem Kunden kurzschliessen kann. FromDual hat kein direktes finanzielles Interesse an dieser Stellenausschreibung!

Taxonomy upgrade extras: mariadbmysqljob descriptionjob