News

MariaDB Connection ID

Shinguz - Mon, 2021-11-29 15:09

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2372 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2373 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 2322 | +---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT * FROM information_schema.processlist WHERE id != CONNECTION_ID(); +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | 2398 | app | localhost:35512 | NULL | Sleep | 4 | | NULL | 4771.722 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14030 | NULL | 6946 | | 2392 | root | localhost | NULL | Sleep | 36 | | NULL | 36510.867 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14021 | NULL | 3850 | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-08-19 15:09:19 7fbb6c328700 Transaction: TRANSACTION 543875059, ACTIVE 0 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 124441421, OS thread handle 0x7fbb6c328700, query id 7822461590 192.168.1.42 fronmdual update INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130) Foreign key constraint fails for table `test`.`contact`: , CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `contact_id` tuple: DATA TUPLE: 2 fields; ... But in parent table `test`.`user`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 41; compact format; info bits 0 ... ------------ TRANSACTIONS ------------ Trx id counter 2499 Purge done for trx's n:o < 2486 undo n:o < 0 state: running History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data select * from test.test Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

PERFORMANCE_SCHEMA threads view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id FROM performance_schema.threads WHERE processlist_id = CONNECTION_ID(); +-----------+----------------+ | thread_id | processlist_id | +-----------+----------------+ | 2369 | 2321 | +-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

sys Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'. 2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses: 2021-11-25 16:22:34 1796 [Note] - 192.168.1.142 2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication) 2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)
MariaDB General Query Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02 4568 Connect fpmmm_agent@localhost on using TCP/IP 4568 Query SET NAMES utf8 4568 Query SHOW GRANTS 4568 Query SELECT "focmm" 4568 Query SHOW GLOBAL VARIABLES 4568 Query SHOW /*!50000 GLOBAL */ STATUS 4568 Quit
MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729 9:22:57 # User@Host: root[root] @ localhost [] # Thread_id: 34 Schema: test QC_hit: No # Query_time: 0.003995 Lock_time: 0.000114 Rows_sent: 2048 Rows_examined: 2048 # Rows_affected: 0 Bytes_sent: 79445 SET timestamp=1627543377; select * from test;
MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log 2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0 20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0 20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0 20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064 20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test, 20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0 20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0 20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0
INFORMATION_SCHEMA

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related topics to MariaDB Connection ID Thread Cache

When using the MariaDB Thread Cache it looks like the thread_id (and also the Connection ID) is changed each time a new connection is created. This is not what I expected, at least for the Thread ID. If I take a thread from the pool I would expect the same or at least another old thread_id again.

Connection Pooling

If you are using application side Connection Pooling different application connection handles will share the same DB connection. So you have to expect traffic from different application parts under the same Connection ID on the database side.

Pseudo thread ID

Since MySQL 8.0.14 there is a variable called pseudo_thread_id. It is for internal server use. Changing the variable on session level also changes the value of the function CONNECTION_ID(). I have no idea what this variable is used for.

Other related information

Taxonomy upgrade extras: connection max_used_connections general query log

MariaDB Connection ID

Shinguz - Mon, 2021-11-29 15:09

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2372 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2373 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 2322 | +---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT * FROM information_schema.processlist WHERE id != CONNECTION_ID(); +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | 2398 | app | localhost:35512 | NULL | Sleep | 4 | | NULL | 4771.722 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14030 | NULL | 6946 | | 2392 | root | localhost | NULL | Sleep | 36 | | NULL | 36510.867 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14021 | NULL | 3850 | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-08-19 15:09:19 7fbb6c328700 Transaction: TRANSACTION 543875059, ACTIVE 0 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 124441421, OS thread handle 0x7fbb6c328700, query id 7822461590 192.168.1.42 fronmdual update INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130) Foreign key constraint fails for table `test`.`contact`: , CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `contact_id` tuple: DATA TUPLE: 2 fields; ... But in parent table `test`.`user`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 41; compact format; info bits 0 ... ------------ TRANSACTIONS ------------ Trx id counter 2499 Purge done for trx's n:o < 2486 undo n:o < 0 state: running History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data select * from test.test Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

PERFORMANCE_SCHEMA threads view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id FROM performance_schema.threads WHERE processlist_id = CONNECTION_ID(); +-----------+----------------+ | thread_id | processlist_id | +-----------+----------------+ | 2369 | 2321 | +-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

sys Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'. 2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses: 2021-11-25 16:22:34 1796 [Note] - 192.168.1.142 2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication) 2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)
MariaDB General Error Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02 4568 Connect fpmmm_agent@localhost on using TCP/IP 4568 Query SET NAMES utf8 4568 Query SHOW GRANTS 4568 Query SELECT "focmm" 4568 Query SHOW GLOBAL VARIABLES 4568 Query SHOW /*!50000 GLOBAL */ STATUS 4568 Quit
MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729 9:22:57 # User@Host: root[root] @ localhost [] # Thread_id: 34 Schema: test QC_hit: No # Query_time: 0.003995 Lock_time: 0.000114 Rows_sent: 2048 Rows_examined: 2048 # Rows_affected: 0 Bytes_sent: 79445 SET timestamp=1627543377; select * from test;
MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log 2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0 20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0 20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0 20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064 20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test, 20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0 20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0 20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0
INFORMATION_SCHEMA

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related topics to MariaDB Connection ID Thread Cache

When using the MariaDB Thread Cache it looks like the thread_id (and also the Connection ID) is changed each time a new connection is created. This is not what I expected, at least for the Thread ID. If I take a thread from the pool I would expect the same or at least another old thread_id again.

Connection Pooling

If you are using application side Connection Pooling different application connection handles will share the same DB connection. So you have to expect traffic from different application parts under the same Connection ID on the database side.

Pseudo thread ID

Since MySQL 8.0.14 there is a variable called pseudo_thread_id. It is for internal server use. Changing the variable on session level also changes the value of the function CONNECTION_ID(). I have no idea what this variable is used for.

Other related information

Taxonomy upgrade extras: connection max_used_connections

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB / MySQL Advanced training end of October 2021

Shinguz - Mon, 2021-10-04 15:12

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB / MySQL Advanced training end of October 2021

Shinguz - Mon, 2021-10-04 15:12

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Automated MariaDB restore tests

Shinguz - Fri, 2021-08-27 19:38

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup...

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

Check that your backup was running fine. For example by checking the return code of your backup.
Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
Check the size of your backup. If the size of your backup significantly changed, it is worth to have a closer look at your backup.
And finally make your monitoring system aware if the backup was NOT running at all and if you are sure your backup is really triggered...

Backup test with FromDual Enterprise Tools

All this functionality is integrated in the newest releases of FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) and the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) in combination with the great monitoring solution Zabbix.

You have to run Backup Manager with the options --fpmmm-hostname and --fpmmm-cache-file:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily \ --fpmmm-hostname=mariadb-106 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-106.cache

then Backup Manager knows that it has to collect the metrics: return code, backup time and backup size and deposits them for the next Performance Monitor run. The next fpmmm run then will automatically gather these data and send it to the Zabbix Server.

In the Zabbix Monitor then you can see how your backup behaves:

and you get alerts if backups failed or did not even happen:

Restore Tests with FromDual Enterprise Tools

If your backup was running fine, returned with a zero return code, had the usual size and was running in a normal amount of time it does not necessarily mean that you also can do a restore with this backup!

For the sissies among us we also should do a restore test. To justify restore tests we have worked out 2 different concepts:

Staging systems restore test

Many of us have different stages of systems: Production, Quality Assurance, Integration, Testing and Development. All those systems need to be provisioned from time to time with new and fresh data from production. These systems would be ideal candidates for your daily restore tests. Also here the FromDual Enterprise Tools can help you: The backup, done with Backup Manager is shipped automatically to the other system where the Recovery Manager is restoring the database. Booth tools report their results to the Performance Monitor which sends the results to Zabbix. And Zabbix sends complains to the administrators if the backup or the restore did not happen or failed:

Spider system restore test

If you have many systems to do restore test or if you do not want to expose your precious data to other stages than production we recommend you the restore spider concept: Every production database does its daily backup on its local backup store. And additionally you have a centralized restore system which has access to the backup of each database. So this central spider restore system grabs now the backup of each database one after the other and does a restore on its restore system. The result will be reported to the Performance Monitor again and you get notice by Zabbix is the Restore did not happen, not work or was talking longer than a predefined time:

Taxonomy upgrade extras: Backup Restore brman fpmmm monitoring

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

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

Shinguz - Mon, 2021-08-23 17:14

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

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

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

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

Upgrade from 2.x to 2.2.4 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.4.tar.gz shell> rm -f brman shell> ln -s brman-2.2.4 brman
Changes in FromDual Backup and Recovery Manager 2.2.4

This release is a new minor release. It contains mainly 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

Redhat 8 RPM package added to FromDual repository.
Stripped unnecessary files from tarball.
From now on we fully support MySQL 5.7 to 8.0.
From now on we fully support PHP 8.
From now on we fully support MariaDB 10.3 to 10.6.
All old PHP 5 stuff was removed.
Library from myEnv updated.
Distribution distinguishing code was cleaned-up and Ubuntu, Rocky Linux, AlmaLinux and CloudLinux should pass checks correctly now.

FromDual Backup Manager

Schema backup was not ignoring SYS schema when doing test for non-transactional (Aria) tables. This test is done correct now.

FromDual Recovery Manager

Restore return code and restore time hook added for integration into fpmmm monitoring. Automated restore tests are better supported now.

FromDual brman Catalog

none

FromDual brman Data masking / data obfuscating

none

Testing

All tests passed for MySQL 8.0.
All tests passed with PHP 8.
Various tests added.
All tests passed for MariaDB 10.6.

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

Taxonomy upgrade extras: Backup Restore Recovery pitr brman release bman rman FromDual Backup and Recovery Manager

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Monitoring your MariaDB database with SNMP

Shinguz - Mon, 2021-08-16 14:24

Table of Contents

What is SNMP?
SNMP Agent (snmptrap)
SNMP Manager (snmptrapd)
Test the SNMP Agent
Creating your own MIB
Sending MariaDB SNMP traps from PHP
Literature

What is SNMP?

A customer recently had the question if an how his MariaDB database can be easily monitored with SNMP?

SNMP means Simple Network Management Protocol. It is a widely used and standardized protocol for monitoring the health of network and other devices (including services). In principle you can monitor nearly everything with SNMP.

On Linux a common implementation of SNMP is Net-SNMP, a suite of applications used to implement SNMP v1, SNMP v2c and SNMP v3 using both IPv4 and IPv6.

SNMP is a typical client-server architecture: The client which is collecting and sending the monitoring data is called agent and the server collecting all the monitoring data is called manager.

Source: Wikipedia: SNMP

An agent can be polled by the manager to collect the monitoring data (Request/Responses) or it can send monitoring data on its own (Trap). The latter one is called a SNMP Trap.

Source: Cisco: Understanding Simple Network Management Protocol (SNMP) Traps

Each measuring event type get its own Object Identifier (OID) which looks for example as follows: 1.3.6.1.4.1.57800.1.1.1. This is a representation of a tree hierarchy called MIB (Management Information Base):

Source: DPS Telecom SNMP OID: Introduction for Industry Professionals

An OID can also be represented in a human readable textual form which looks for example as follows: FromDual-fpmmm-MIB::fpmmmStart

SNMP Agent (snmptrap)

In this project we concentrate on the SNMP trap agent (snmptrap). Which sends an asynchronous notification to the manager (snmptrapd). To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmp snmp-mibs-downloader

These 2 packages contain:

snmpSNMP (Simple Network Management Protocol) applicationssnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is made so complicated because of some legal reasons:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf
SNMP Manager (snmptrapd)

There are 2 different types of SNMP managers. The SNMP daemon (snmpd) and the SNMP trap daemon (snmptrapd). We concentrate on the later one in this project. To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmptrapd snmp-mibs-downloader

These 2 packages contain:

snmptrapdNet-SNMP notification receiversnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is so complicated because of some legal problems:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf $ sed -i 's/export MIBS=/# export MIBS=/g' /etc/default/snmpd

For our tests we use the following configuration file:

# cat /etc/snmp/snmptrapd.conf disableAuthorization yes authCommunity log,execute,net public createUser myuser MD5 mypassword DES myotherpassword [snmp] logOption s 2 [snmp] logOption f /var/log/snmptrapd-direct.log format2 %V\n% Agent Address: %A \n Agent Hostname: %B \n Date: %H - %J - %K - %L - %M - %Y \n Enterprise OID: %N \n Trap Type: %W \n Trap Sub-Type: %q \n Community/Infosec Context: %P \n Uptime: %T \n Description: %W \n PDU Attribute/Value Pair Array:\n%v \n -------------- \n _EOF

Then the SNMP trap daemon has to be (re-)started:

$ systemctl start snmptrapd.service

The log messages then can be found in /var/log/snmptrapd-direct.log or otherwise like this: grep snmptrap /var/log/syslog.

If you write your own MIBs they can be located here: /usr/share/snmp/mibs.

Test the SNMP Agent

An SNMP trap is send as follows:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID='1.3.6.1.4.1.57800.1.1.2' $ OID='1.3.6.1.4.1.57800.1.1.1' $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

And then you will see in the SNMP trap daemon error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72136167) 8 days, 8:22:41.67 SNMPv2-MIB::snmpTrapOID.0 = OID: 1.3.6.1.4.1.57800.1.1.2 1.3.6.1.4.1.57800.1.1.1 = Counter32: 1628864995

Or more MariaDB specific:

$ VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"
Creating your own MIB

How you write your own MIBs you can find here: Writing your own MIBs.

MIBs can/should be located under $HOME/.snmp/mibs or /usr/local/share/snmp/mibs. The MIB search path can be found with this command:

$ snmptranslate -Dinit_mib .1.3 2>&1 | grep MIBDIRS $ ll /root/.snmp/mibs /usr/share/snmp/mibs /usr/share/snmp/mibs/iana /usr/share/snmp/mibs/ietf /usr/share/mibs/site /usr/share/snmp/mibs /usr/share/mibs/iana /usr/share/mibs/ietf /usr/share/mibs/netsnmp

A tool for checking your MIB is smilint:

$ apt-get install smitools $ smilint snmp/FromDual-fpmmm-MIB.mib --level=6 snmp/FromDual-fpmmm-MIB.mib:90: warning: node `fpmmmLastrun' must be contained in at least one conformance group

If you want to extend the MIB search path you can modify the MIBDIRS environment variable:

$ export MIBDIRS=/home/oli/fromdual_devel/fpmmm/snmp:/home/oli/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

To check if your MIB is correctly translated into an OID and vice versa you can use the tool snmptranslate:

$ snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun $ snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

And if the translation works you can send an SNMP trap with the MIB instead of the OID:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID="FromDual-fpmmm-MIB::fpmmmStart" $ OID="FromDual-fpmmm-MIB::fpmmmLastrun" $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

and this should also be translated correctly in the snmptrapd error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72176488) 8 days, 8:29:24.88 SNMPv2-MIB::snmpTrapOID.0 = OID: FromDual-fpmmm-MIB::fpmmmStart FromDual-fpmmm-MIB::fpmmmLastrun = Counter32: 1628864995
Sending MariaDB SNMP traps from PHP

It looks like the PHP native SNMP functions do not provide anything for sending SNMP traps. But luckily there is the FreeSDx/SNMP PHP library by ChadSikorra on GitHub which can do the job.

After installing PHP composer installing of the FreeDSx/SNMP library was no problem:

$ php composer.phar require freedsx/snmp Using version ^0.4.0 for freedsx/snmp ./composer.json has been updated Running composer update freedsx/snmp Loading composer repositories with package information Updating dependencies Lock file operations: 3 installs, 0 updates, 0 removals - Locking freedsx/asn1 (0.4.4) - Locking freedsx/snmp (0.4.0) - Locking freedsx/socket (0.3.1) Writing lock file Installing dependencies from lock file (including require-dev) Package operations: 3 installs, 0 updates, 0 removals - Downloading freedsx/socket (0.3.1) - Downloading freedsx/asn1 (0.4.4) - Downloading freedsx/snmp (0.4.0) - Installing freedsx/socket (0.3.1): Extracting archive - Installing freedsx/asn1 (0.4.4): Extracting archive - Installing freedsx/snmp (0.4.0): Extracting archive 2 package suggestions were added by new dependencies, use `composer suggest` to see details. Generating autoload files

This we need for adding SNMP support to our FromDual Performance Monitor for MariaDB and MySQL (fpmmm). A simple PHP SNMP trap example you can find as follows:

$aAutoload = require_once('vendor/autoload.php'); use FreeDSx\Snmp\SnmpClient; use FreeDSxph\Snmp\Exception\SnmpRequestException; use FreeDSx\Snmp\Oid; $snmp = new SnmpClient([ 'host' => '192.168.56.102' , 'community' => 'public' , 'version' => 2 , 'port' => 162 , ]); try { $date = time(); $trapOid = '1.3.6.1.4.1.57800.1.1.2'; // FromDual-fpmmm-MIB::fpmmmStart $Oid = '1.3.6.1.4.1.57800.1.1.1'; // FromDual-fpmmm-MIB::fpmmmLastrun # The parameters are: # 1. The system uptime (in seconds) # 2. The trap OID # 3. The OIDs and their values $snmp->sendTrap(60, $trapOid, Oid::fromCounter($Oid, $date)); } catch ( SnmpRequestException $e ) { printf('Unable to send trap: %s', $e->getMessage()); }
Literature

Taxonomy upgrade extras: SNMP monitoring

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB/MySQL Environment MyEnv 2.0.3 has been released

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

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

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

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

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

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

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

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

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

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

Changes in MyEnv 2.0.3 MyEnv

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

MyEnv Installer

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

MyEnv Utilities

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

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

Taxonomy upgrade extras: MyEnv multi-instance virtualization consolidation SaaS Operations release mysqld_multi

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Query performance comparison between MariaDB Column Store and other Storage Engines

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

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

This is how the table sales_fact looks like:

CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL ) ENGINE=ColumnStore DEFAULT CHARSET=utf8; CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL, KEY `i_sales_customer_id` (`customer_id`), KEY `i_sales_product_id` (`product_id`), KEY `i_sales_promotion_id` (`promotion_id`), KEY `i_sales_store_id` (`store_id`), KEY `i_sales_time_id` (`time_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

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

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

Comparison with MyISAM

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

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

Simple SELECT queries SELECT COUNT(*) FROM table

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

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

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

SELECT * FROM table or CHECKSUM TABLE table

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More complex SELECT queries from DWH benchmarks.

I borrowed these queries from Øystein's blog:

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

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

So let us try some more complex queries from Sergei:

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

We get a warning when using JOINs:

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

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

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

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

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

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

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

We used the normal mysqldump and replace the storage engine:

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

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

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

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

To fix this the following DDL command will help:

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

Conclusion

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

Taxonomy upgrade extras: mariadb dwh ColumnStore query performance myisam

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Create a single-node MariaDB ColumnStore test installation

Shinguz - Wed, 2021-05-19 19:37

Table of Contents

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

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

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

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

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

MariaDB ColumnStore hardware requirements

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

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

O/S (Linux) settings

Check Linux kernel settings:

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

Check network speed:

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

Persist the Linux kernel settings:

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

and make them active:

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

Disable AppArmor (for installation only?):

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

Configure Character Enconding (why exactly?):

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

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

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

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

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

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

Installation of MariaDB ColumnStore was straight forward:

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

The minimum recommended MariaDB configuration for ColumnStore looks like this:

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

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

Cross Engine Join User

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

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

and changed as follows:

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

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

Start the ColumnStore Processes

Starting MariaDB ColumnStore after the configuration changes are quite intuitive:

$ systemctl restart mariadb $ systemctl restart mariadb-columnstore

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

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

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

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

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

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

or if the MariaDB ColumnStore is enabled at all with:

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

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

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

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

MariaDB ColumnStore schemas

MariaDB ColunStore creates 3 new schemas in the database:

We will investigate later what they are used for.

Server System Variables and Server Status Variables

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

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

MariaDB ColumnStore also writes to the MariaDB error log:

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

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

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

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

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

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

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

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

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

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

After removing the Primary Key the next try:

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

Also AUTO_INCREMENT seems not to be supported:

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

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

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

Becoming stressed a bit now this:

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

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

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

Query OK, 0 rows affected (1.755 sec)

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

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

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

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

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

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

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

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

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

MariaDB ColumnStore using sequences instead of AUTO_INCREMENT

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

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

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

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

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

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

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

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

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

Instead we should do it in MariaDB ColumnStore like this:

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

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

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

Taxonomy upgrade extras: dwh ColumnStore Data Mart mariadb reporting

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

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

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

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

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

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

Download

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

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

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

Installation of Ops Center 1.1.0

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

Upgrade from 0.9.x to 1.1.0

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

Changes in Ops Center 1.1.0 Machine

Machine check error messages improved.

Instance

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

Cluster

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

Load-Balancer

No changes.

Virtual IP (VIP)/Floating IP

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

Tools

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

Configuration

Configuration: If focmm crontab is not activated complaints are shown on prominent place.
Configuration: Delete focmm crontab added.

Database-as-a-Service (DBaaS)

No changes.

Building and Packaging

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

Themes / UI

Nasty CSS error removed.

General

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

Repository

Node.inc renamed to Catalog.inc.

Taxonomy upgrade extras: Operations release FromDual Ops Center ops center dbaas focmm

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Limiting MySQL tmpdir size

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

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

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

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

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

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

Add the entry to your /etc/fstab:

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

Test the fstab entry:

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

Configure your MySQL database accordingly (my.cnf):

tmpdir = /var/lib/mysql-tmpdir

and after database restart:

Then create some implicit temporary tables on disk:

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

and check if tmpdir is really capped at 4 Gibyte:

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

Use A File As A Linux Block Device

Taxonomy upgrade extras: myisam tmpdir temporary table

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Do not trust other peoples benchmarks!

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

Because they do NOT reflect your problems.

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

What has changed:

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

Literature research

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

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

Do your own benchmarks

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

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

And here the latency results for our test query:

MariaDBMyISAM1Aria2Aria3InnoDB410.20.59 s2.19 s0.86 s0.79 s10.30.59 s2.31 s0.86 s- 510.40.60 s2.28 s0.86 s0.77 s10.50.60 s1.49 s0.84 s0.64 s

1 MyISAM key_buffer_size = 128M
2 aria_pagecache_buffer_size = 1G
3 aria_pagecache_buffer_size = 2G
4 innodb_buffer_pool_size = 2G
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: benchmark performance Performance Tuning query tuning dwh aria myisam

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB configuration analysis

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

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

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

Since MariaDB 10.5 we can also see from which file the MariaDB server system variable configuration is coming from. This makes it easier to find and fix wrong configurations.

MariaDB server system variables which are NOT default

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

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

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

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

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

MariaDB server system variables taken from which configuration file

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

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

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

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

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

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

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

Taxonomy upgrade extras: mariadb configuration variables server

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB or MySQL, that is the question

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

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

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

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

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

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

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

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

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

Literature

1 Decision matrix / Entscheidungsmatrix

Taxonomy upgrade extras: mariadb mysql

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB sql_mode = 'oracle'

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

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

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

Items found in the MariaDB Jira database

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

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

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

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

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

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

Why are no new MariaDB PL/SQL features implemented?

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

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

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

Taxonomy upgrade extras: Oracle mariadb pl/sql sql_mode

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB Galera Cluster with Corosync/Pacemaker VIP

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

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

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

Corosync/Pacemaker

A few words upfront about Corosync/Pacemaker:

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

Corosync on the other hand is the thing that provides the messaging layer and talks to instances of itself on the other node(s). Corosync provides reliable communication between nodes, manages cluster membership and determines quorum. Think of Corosync as dbus but between nodes.

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

# crmadmin --version Pacemaker 2.0.1 # corosync -v Corosync Cluster Engine, version '3.0.1'
Prerequisites

DNS resolution must work.
Nodes must be reachable (firewall).
Nodes must allow traffic between them.

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

DNS resolution

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

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

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

Check all the nodes on all the nodes:

# ping node1 # ping node2 # ping node3
Firewall

Check your firewall settings:

# iptables -L # systemctl status firewalld

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

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

Install Corosync/Pacemaker

Install the Corosync/Pacemaker packages:

# apt-get install pacemaker pcs

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

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

Set the password for the Corosync/Pacemaker Cluster user:

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

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

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

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

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

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

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

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

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

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

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

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

Undo if something fails:

# pcs cluster destroy

Check your Corosync/Pacemaker Cluster:

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

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

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

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

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

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

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

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

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

# pcs property set stonith-enabled=false

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

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

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

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

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

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

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

# pcs node standby node2

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

# pcs node unstandby node2

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

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

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

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

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

Graphical Web User Interface

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

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

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

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

Improvements

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

[mysqld] wsrep_notify_command = pcs_standby_node.sh

The script pcs_standby_node.sh should cover the following scenarios:

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

* Your application will experience errors such as:

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

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

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

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

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

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

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

General thoughts

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

Literature

Taxonomy upgrade extras: galera galera cluster keepalived corosync pacemaker vip High Availability fail-over

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Keep your Galera Cluster up and running by all means

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

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

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

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

Galera Cluster itself has some hard limits:

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

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

Do small transactions

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

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

should do the job.

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

Force small transactions

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

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

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

SQL> SET GLOBAL wsrep_max_ws_rows = 10000;

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

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

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

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

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

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

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

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

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

Disadvantages of Streaming Replication

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

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

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

Literature

Taxonomy upgrade extras: galera galera cluster transaction

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Databases are standardized but in detail they behave different

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

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

MySQL 5.7

First we create some test data:

mysql> CREATE TABLE t_my ( ID CHAR(32) NOT NULL PRIMARY KEY ) ENGINE = InnoDB; mysql> INSERT INTO t_my SELECT MD5(RAND()) FROM t_my; ... create more than 10 rows mysql> SELECT id FROM t_my ORDER BY id LIMIT 11; +----------------------------------+ | id | +----------------------------------+ | 01a6e76643c83c91867636ce90a8def5 | | 0ea1b1670343b4e70dd449207c720957 | | 141ec92e809c1d6af83d27e8a3e74fe7 | | 1605890e2c0244b019e6f66cc94790f2 | | 19826d67b6013ed3bc1105b9708959c4 | | 1a9ffd320187831df939d596c9a50aa1 | | 24ae3a883803f5ae8416754593cd881c | | 27e614f1b4490a6db1b26364e467d361 | | 285e3d84b81d97a40d66049d2f30071f | | 2db85e2f2639d637ee21888ca34334d7 | | 2f0e944ca977826730c352a1920cda1f | +----------------------------------+ 11 rows in set (0.00 sec)

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

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

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

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

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

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)

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

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

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

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

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

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

Upgrade from 2.x to 2.2.3 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.3.tar.gz shell> rm -f brman shell> ln -s brman-2.2.3 brman
Changes in FromDual Backup and Recovery Manager 2.2.3

This release is a new minor release. It contains only bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

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

shell> fromdual_bman --version shell> bman --version shell> rman --version
General

myEnv library updated.

FromDual Backup Manager

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

FromDual Recovery Manager

none

FromDual brman Catalog

none

FromDual brman Data masking / data obfuscating

none

Testing

Test added for fpmmm cache file function.

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

Taxonomy upgrade extras: Backup Restore Recovery pitr brman release bman rman FromDual Backup and Recovery Manager

Kategorien: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Languages

Login

You are here

MariaDB Connection ID

MariaDB Connection ID

MariaDB / MySQL Advanced training end of October 2021

MariaDB / MySQL Advanced training end of October 2021

Automated MariaDB restore tests

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

Monitoring your MariaDB database with SNMP

MariaDB/MySQL Environment MyEnv 2.0.3 has been released

Query performance comparison between MariaDB Column Store and other Storage Engines

Create a single-node MariaDB ColumnStore test installation

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

Limiting MySQL tmpdir size

Do not trust other peoples benchmarks!

MariaDB configuration analysis

MariaDB or MySQL, that is the question

MariaDB sql_mode = 'oracle'

MariaDB Galera Cluster with Corosync/Pacemaker VIP

Keep your Galera Cluster up and running by all means

Databases are standardized but in detail they behave different

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

Pages

About us

Navigation

Trademarks

Languages

Login

Search form

You are here

News

Pages

About us

Navigation

Trademarks