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

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

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

MariaDB Push Replication

Shinguz - Mon, 2021-01-11 17:29

Table of Contents

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

How to make MariaDB Pull Replication as secure as possible

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

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

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

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

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

MariaDB Push Replication

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

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

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

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

Current limitations are:

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

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

shell> watch -n 60 ./binlog_push.php

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

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

Pushing data with the FederatedX Storage Engine and Triggers

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

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

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

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

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

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

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

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

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

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

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

Literature

Traffic mirroring with MariaDB MaxScale or ProxySQL

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

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

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

Literature

Taxonomy upgrade extras: mariadb replication security

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

VSZ behaviour with MariaDB MEMORY tables

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

We recently had the situation that a customer complained about the Oom killer terminating the MariaDB database instance from time to time. The MariaDB database configuration was sized quit OK (about 50% of RAM was used for the database) but they did not have swap configured.

When we checked the memory for the specific mysqld process we found that VSZ was about 80 Gibyte (on a 64 Gibyte machine) and the RSS size was about 42 Gibyte. The very high VSZ value in combination with a lacking swap space and Oom killer let the alarm bells ring.

This customer was using a significant amount of (temporary) MEMORY tables (instead of TEMPORARY TABLE ... ENGINE = MEMORY) which are suspect to be the evildoer.

To verify if this could be the reason for the odd behaviour we have to know how MEMORY tables behave related to VSZ from the O/S point of view.

Creation of MEMORY table 1 (12 - 14):

SQL> SET GLOBAL max_heap_table_size = 1024*1024*1024; SQL> SET SESSION max_heap_table_size = 1024*1024*1024; SQL> CREATE TABLE test_m1 LIKE test; SQL> ALTER TABLE test_m1 ENGINE = MEMORY; SQL> INSERT INTO test_m1 SELECT * FROM test; SQL> INSERT INTO test_m1 SELECT NULL, data, NULL FROM test_m1; ... ERROR 1114 (HY000): The table 'test_m1' is full

Creation of MEMORY table 2 (32 - 38):

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

Creation of MEMORY table 3 (45 - 48):

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

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

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

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

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

Taxonomy upgrade extras: memory table memory oom swap

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

Traffic mirroring with MariaDB MaxScale

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

Recently we had the case that a customer claimed that MariaDB 10.3 Binary Log is using 150% more space on disk than MySQL 5.7 Binary Log. Because I never observed something similar, but to be honest, I did not look to intensively for this situation, we had to do some clarifications.

First we checked the usual variables which could be candidates for such a behaviour:

binlog_format = ROW binlog_row_image = FULL binlog_rows_query_log_events = OFF # MySQL only binlog_annotate_row_events = OFF # MariaDB equivalent log_bin_compress = OFF # MariaDB only

Those were all equal on MariaDB and MySQL. So is was not a trivial case to solve.

The customer did not like the suggestion to just increase the disk space. So we had to dig further...

In the MariaDB Enterprise support ticket we have noticed that the MariaDB support engineer tried to use MariaDB MaxScale to reproduce our problem (without success by the way). So time to try it out ourself because we have some other scenarios where this could be useful as well.

Installation

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

shell> maxscale --version MaxScale 2.5.6

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

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

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

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

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

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

Starting MariaDB MaxScale

First we did a check of the configuration file:

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

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

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

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

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

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

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

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

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

On the main instance (mariadb-105):

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

The General Query Log looks as follows:

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

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

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

The General Query Log looks as follows:

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

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

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

On the main instance:

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

On the tee'd instance:

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

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

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

What about the original Binary Log problem?

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

On the main instance:

| chef_mariadb-105_binlog.000103 | 3017223 |

On the tee'd instance:

| chef_mysql-57-binlog.000601 | 4633484 |

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

Literature

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

Taxonomy upgrade extras: mariadb maxscale binary log load balancer

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

MariaDB Galera Cluster Upgrade Path

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

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

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

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

MariaDB Galera Cluster Upgrade from 5.5 to 10.0

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

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

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

MariaDB Galera Cluster Upgrade from 10.0 to 10.1

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

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

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

MariaDB Galera Cluster Upgrade from 10.3 to 10.4

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

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

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

Skipping MariaDB Major Release Series

About skipping Major Releases MariaDB documentation states:

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

And now the visual MariaDB Galera Cluster Upgrade Path:

Literature

Taxonomy upgrade extras: mariadb galera cluster upgrade

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

How to force InnoDB Buffer Pool flushing

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

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

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

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

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

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

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

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

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

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

Literature

Taxonomy upgrade extras: innodb buffer pool flushing meb MySQL Enterprise Backup Backup

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

Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster

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

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

Before you start

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

Downgrade is officially not supported! [ 4 ] It might work, or not.
So you should have taken a proper an clean backup before you start with the upgrade and you should be sure the restore works as well!
It is recommended to upgrade to the newest MariaDB minor release first [ 5 ] before you upgrade to a new major release. This reduces the risk that you run into already known and fixed bugs.
Take a look at Upgrading from MariaDB 10.4 to MariaDB 10.5 [ 1 ] to see what has changed between the major versions.
New MariaDB Major Release may behave differently than older MariaDB Major Release. Thus you should test the new major release series first before putting it into production!
Ideally, you want to have a large enough Galera Cache to avoid a State Snapshot Transfer (SST) during the rolling upgrade. The Galera Cache size can be configured by setting the Galera variable gcache.size.
For example: wsrep_provider_options = "gcache.size=2G"

Performing a Rolling Upgrade

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

For each node, perform the following steps:

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

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

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

License

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

Literature

Taxonomy upgrade extras: mariadb upgrade cluster galera cluster rolling upgrade 10.4 10.5

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

Partial Restore of a Table into a MariaDB Galera Cluster

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

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

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

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

For the restore we use the following procedure:

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

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

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

Restore all the files from the backup:

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

Then re-import the tablespace again:

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

And finally clean-up:

# rm -f ${DATADIR}/${SCHEMA}/${TABLE}.cfg
Taxonomy upgrade extras: Backup Restore schema database physical backup mariabackup table restore schema restore partial restore database restore

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

MariaDB/MySQL Datenbank-Administrator/in gesucht

Shinguz - Fri, 2020-11-20 09:02

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

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

Auszug aus der Original-Stellenausschreibung:

Aufgaben:

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

Kompetenzen:

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

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

Taxonomy upgrade extras: mariadb mysql job description job

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

Partial Table or Schema restore from mariabackup full backup

Shinguz - Wed, 2020-11-11 21:59

For me it was for a long time not clear if a mariabackup full backup can be used to do partial table or schema restores. Now we faced this challenge with a customer. So time to try it out...

This test was made with MariaDB 10.5.5. So it may not work with some older MariaDB releases...

Backup

Because I do not know during the backup if I need a full or a partial restore I always want to do a full mariabackup backup!

The full backup can be done as normal but the prepare should not be done yet during the backup (or I am not sure if the prepare can be done twice, first without --export during the backup and next with --export during the restore). Further I am not sure yet if a backup treated with --export can later be used for a full restore once more. Further research has to be done in this area...

For a partial table or schema restore we need the CREATE TABLE statements as well. So it makes sense to also backup the table structures already during backups. This avoids troubles or cumbersome and time consuming extracting operations during restore.

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/qamariadb105/data" # # Clean-up # rm -rf ${BACKUPDIR}/* # mariabackup --backup --user=root \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR} # # Backup also table structure for partial table/schema restore # mysqldump --user=root --no-data --all-databases > ${BACKUPDIR}/full_structure_dump.sql
Prepare and Restore one Schema # BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/mariadb-105/data" # SCHEMA="world" # mariabackup --prepare --export \ --databases="${SCHEMA}" \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR}

Additionally you can use the --tables option to only restore some tables: --tables='bla*bla'. The --export option creates the *.cfg files but further does not touch the *.ibd or *.frm files but ibdata?, ib_logfile? and aria_log* files!!! So I guess a backup treated like this cannot be used for a full restore any more... As mentioned above further research has to be done in this area.

-rw-rw---- 1 mysql mysql 551 Nov 11 20:41 world/CountryLanguage.cfg -rw-rw---- 1 mysql mysql 1215 Nov 11 20:41 world/Country.cfg -rw-rw---- 1 mysql mysql 578 Nov 11 20:41 world/City.cfg

From the structure dump we have to extract the CREATE DATABASE and the CREATE TABLE statements.

SQL> DROP SCHEMA world; SQL> CREATE SCHEMA world; SQL> CREATE TABLE ...;

Or more easy:

# mysql --user=root < ${BACKUPDIR}/${SCHEMA}_structure_dump.sql

Then we have to discard all the tablespaces we want to restore:

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

Restore all the files from the backup:

# cp ${BACKUPDIR}/${SCHEMA}/*.ibd ${DATADIR}/${SCHEMA}/ # cp ${BACKUPDIR}/${SCHEMA}/*.cfg ${DATADIR}/${SCHEMA}/

And then re-import the tablespaces:

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

That is it! We have restored one single schema with a physical MariaDB backup...

Possibly the *.cfg files can be cleaned-up yet:

# rm -f ${DATADIR}/${SCHEMA}/*.cfg
Literature

Taxonomy upgrade extras: Backup Restore schema database physical backup mariabackup table restore schema restore partial restore database restore

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

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

Shinguz - Wed, 2020-10-14 14:22

FromDual has the pleasure to announce the release of the new version 2.2.2 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.2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.2.tar.gz shell> rm -f brman shell> ln -s brman-2.2.2 brman
Changes in FromDual Backup and Recovery Manager 2.2.2

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

Implode command made compatible to new PHP versions.
Function readMyCnf restructured to not write to STDOUT any more.
Max connection reached problem solved.
myEnv library and constants synced/updated.

FromDual Backup Manager

Missing return code added.
RELOAD privilege is checked in doSchemaBackup and BinlogBackup.
Reset return code to not spoil final return code in doSchemaBackup.
WARN reduced severity level to INFO to not spoil STDERR in doSchemaBackup.
Error caught in case when binary logs were deleted but not available in doCleanup.
doBinlogBackup separated into own file.
Do not print wrong recommendation in case of too many connections for target.
Bman error message improved.
Code clean-up in checkForGeneralTablespaces function.
Bman falsely warns on MySQL 8.0 for general tablespace (new InnoDB system tablespace). This warning is suppressed now.
Schema overview output was ugly with long schema names. Now extended to 24 characters.
Bug caught better for clean-up backups in case the backup was already deleted by somebody else.
Error handling improved for binlog backup on remote machine, code cleaned-up.
Separated schema backup into own file.
Fixed error messages and added remote binary log backup tags for future feature request.
Function checkForGeneralTablespaces cleaned-up.
Lock directory moved to /var/lock and alternative location for some Cloud set-ups.
Physical backup for MariaDB 10.5 fixed.

FromDual Recovery Manager

Password is not shown any more in the log for restore.
Physical restore for MySQL 5.7 with Xtrabackup should work now.
Exception for sync_frm for MySQL 8.0 added.

FromDual brman Catalog

Bugs around catalog entries fixed.

FromDual brman Data masking / data obfuscating

Data masking / data obfuscating POC work has been started to work exactly the same way as mariadb-dump / mysqldump.

Testing

PHP Lint test added.
Tests improved.
New MariaDB 10.5 version added and test for stopped instance added.
Tests for MySQL 8.0 fixed.

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

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

Creating synthetic data sets for tuning SQL queries

Shinguz - Fri, 2020-10-02 16:50

When it comes to SQL Query tuning with customers we often get the slow running SQL query and possibly, in good cases, also the table structure. But very often, for various reasons, we do not get the data.

SQL query tuning on an empty table or a table with only little data is not really fun because either the results of the optimizer have nothing to do with reality or the response times do not really show if your change has improved anything. For example if your query response time before the change was 2 ms and after 1 ms this can be either the consequence of your improvement but more probable a hiccup of your system.

So what to do to get valid results from your SQL query optimizer during SQL query tuning?

The best case is you get real data from the customer in size and content.
The second best case is if you get real data from the customer in content. So you can analyze this content and synthetically pump it up.
The worst case is if you get no data at all from your customer. In this case you have to create your own data set in size (this is easy) and in content. And this is a bit more tricky.

So let us have a look at how we get to this synthetic data.

Creating data volume by pumping up the table

We get from the customer a slow query for a data cleansing job on the call detail record (CDR) table. This table is used in telecom solutions like VoIP (Asterix, OpenSIPS), PBX and so on.

SELECT COUNT(*) FROM cdrs WHERE start >= end;

And fortunately we also get the CDR table:

CREATE TABLE `cdrs` ( `uniqueid` varchar(40) NOT NULL, `callid` varchar(40) NOT NULL, `asteriskid` varchar(20) NOT NULL DEFAULT '', `machine` int(11) NOT NULL DEFAULT 0, `status` varchar(15) NOT NULL DEFAULT '', `start` TIMESTAMP(6) NOT NULL, `end` TIMESTAMP(6) NOT NULL, `scustomer` int(11) NOT NULL DEFAULT 0, `stype` varchar(30) NOT NULL DEFAULT '', `snumber` varchar(255) NOT NULL DEFAULT '', `dcustomer` int(11) NOT NULL DEFAULT 0, `dtype` varchar(30) NOT NULL DEFAULT '', `dnumber` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`uniqueid`), KEY `server` (`callid`), KEY `start` (`start`), KEY `scustomer` (`scustomer`,`start`), KEY `dcustomer` (`dcustomer`,`start`), KEY `asteriskid` (`asteriskid`) );

So how can we pump up this table to get a decent volume? For pumping up the table we use the concept of the Rice/Wheat and chessboard problem:

We first insert one row and then pump it up by adding rows from itself. This gives us an exponential growth and after a few statement we have enough data (possibly time becomes an issue sooner or later):

INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' ; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' FROM cdrs ; -- Repeat this second query about 20 times to get 1 Mio rows. Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.001 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.000 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 8 rows affected (0.000 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 16 rows affected (0.001 sec) Records: 16 Duplicates: 0 Warnings: 0 Query OK, 32 rows affected (0.002 sec) Records: 32 Duplicates: 0 Warnings: 0 Query OK, 64 rows affected (0.002 sec) Records: 64 Duplicates: 0 Warnings: 0 Query OK, 128 rows affected (0.094 sec) Records: 128 Duplicates: 0 Warnings: 0 Query OK, 256 rows affected (1.406 sec) Records: 256 Duplicates: 0 Warnings: 0 Query OK, 512 rows affected (2.747 sec) Records: 512 Duplicates: 0 Warnings: 0 Query OK, 1024 rows affected (4.888 sec) Records: 1024 Duplicates: 0 Warnings: 0

What happened here???

Query OK, 2048 rows affected (0.178 sec) Records: 2048 Duplicates: 0 Warnings: 0 Query OK, 4096 rows affected (0.259 sec) Records: 4096 Duplicates: 0 Warnings: 0 Query OK, 8192 rows affected (1.879 sec) Records: 8192 Duplicates: 0 Warnings: 0 Query OK, 16384 rows affected (4.149 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 32768 rows affected (3.256 sec) Records: 32768 Duplicates: 0 Warnings: 0 Query OK, 65536 rows affected (7.209 sec) Records: 65536 Duplicates: 0 Warnings: 0 Query OK, 131072 rows affected (13.555 sec) Records: 131072 Duplicates: 0 Warnings: 0

Buffer Pool seems to be full! More RAM helps more...

Query OK, 262144 rows affected (6 min 17.659 sec) Records: 262144 Duplicates: 0 Warnings: 0

Increased Buffer Pool (online!) 6 times and waited for dirty page flushing.

Query OK, 524288 rows affected (1 min 14.629 sec) Records: 524288 Duplicates: 0 Warnings: 0

It definitely helped! More RAM helps more!!!

How to get more or less useful or realistic data DescriptionFunctionExampleUnique ID:SELECT UUID();09e16608-017f-11eb-9cc7-a8a15920b138Random Float from 0 TO 99:SELECT RAND() * 100;82.15320322863124Random Integer from 10 to 19:SELECT 10 + FLOOR(RAND() * 10);10Random Float for currencies:SELECT ROUND(RAND() * 1000, 2);628.07Random String:SELECT UUID(), MD5(RAND()), CRC32(RAND());232fccee-017f-11eb-9cc7-a8a15920b138 0e468db120211529f5fc2940994024a8 263783538Random Timestamp ≥ 1970-01-01 00:00:00 UTC:SELECT FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6));1992-06-30 11:04:04.784335Random Timestamp ≥ 2020-01-01 and < 2020-12-31:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2020-01-01') + (365 * 24 * 3600 * RAND()));2020-08-06 04:48:53.342219Some kind of email address:SELECT CONCAT(CRC32(RAND()), '@', MD5(RAND()), '.com');1619088853@6b20a5dad4522feee5efbfd3ebb17d71.comTime range of 21 days from now:SELECT FROM_UNIXTIME(@begin := UNIX_TIMESTAMP()), FROM_UNIXTIME(@begin + (86400 * 21));2020-10-02 11:14:48 2020-10-23 11:14:48Street name:SELECT CONCAT(CRC32(RAND()), 'street ', (1 + FLOOR(RAND() * 100)));3416042219street 14

and there are for sure many more possibilities...

Now back to the query: With no rows the Query Execution Plan with EXPLAIN looks as follows:

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.004 sec)

If we pump up the table the query execution plan with EXPLAIN looks like this:

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.230 sec)

So we have a good starting point for testing tuning measures on this SQL query...

But caution: There is still a logical error in the data above... Did you find it?

If and how this query can be speed up is an other story...

Skew data distribution

What we got so far is a completely random data distribution or at least random in a specific range. This is very often far from reality. So we have to influence or manipulate this random data distribution a bit more into the direction that it reflects our reality.

There are 2 extreme cases: We are only searching for one unique value or all values are equal. In reality we are somewhere in between.

If you where using a UUID or hash function to create the data they should be pretty unique. So this extreme case is covered:

UPDATE cdrs SET start = '2020-04-29 12:00:00.000000', end = '2020-04-29 12:13:13.999999' WHERE uniqueid = 'd00c7166-01a4-11eb-9cc7-a8a15920b138';

Or you can specifically UPDATE ONE row to your needs. This other extreme case also can be solved by a simple UPDATE statement:

UPDATE cdrs SET machine = 42;

If you want to set only every nth row to a specific value the modulo operation might help:

SET @nth = 7; UPDATE cdrs SET machine = 42 WHERE id % @nth; UPDATE cdrs SET machine = IF(HEX(SUBSTR(UNIQUEID, 8, 1)) % @nth, 42, machine);

And finaly if you need monotonic increasing numbers this is a possibility to do it:

SET @row_number = 0; UPDATE cdrs SET machine = (@row_number := @row_number + 1);
Taxonomy upgrade extras: performance tuning query Optimizer sql explain optimizing query tuning Performance Tuning

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

MyISAM locking and who is the evil?

Shinguz - Wed, 2020-09-23 09:58

Yes, I know, MyISAM is deprecated and unofficially discontinued by the vendors. But we still have from time to time customers using MyISAM and even evangelize for MyISAM...

And to be honest in some cases MyISAM has even advantages (beside some huge disadvantages) over other Storage Engines (simple file copy, footprint, single-query latency, ...). But most of our customers are not aware of these advantages and are using MyISAM just because they did it since ever...

One of the biggest problems we see at customers is the MyISAM table lock behaviour. They claim things like the database stalls, crashes or stocks beside other non-qualified expressions. Which is typically not the case but the database just runs out of connections because they reach the max_connections fuse. In fact what happens is that one long running writer connection blocks an important and frequently used (MyISAM) table and other writer and reader connections have to wait (Waiting for table level lock) until the writer finishes its work. If you are lucky the system relaxes again afterwards. If not, the database rejects new connections because in the meanwhile other connections have filled up the allowed number of connections up to max_connections.

Instead of finding and solving the problem, customers typically just increase max_connections until it becomes so cumbersome they cannot live any more with it. And then they show up at FromDual consulting services.

Now, how can we find that this scenario happened? The first thing you should do when this situation happens is to gather immediately the output of the command SHOW FULL PROCESSLIST and store it away for later analysis by your preferred spreadsheet tool or editor (please not a screenshot but just copy plain characters from your CLI!).

If you are a bit more prepared for the situation you can also use:

SELECT thread_id, processlist_id, processlist_user, processlist_host, processlist_db, processlist_time, processlist_state, processlist_info FROM performance_schema.threads WHERE PROCESSLIST_COMMAND != 'Sleep' AND TYPE = 'FOREGROUND' ORDER BY PROCESSLIST_TIME DESC ;

An this is how it looks like if you have a MyISAM table level locking situation:

INSERT INTO ... SELECT * FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 12 | Sending data | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 11 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

or

+-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 21 | Writing to binlog | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 20 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
UPDATE ... SET ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 47 | Updating | UPDATE test SET data = 'Blub' WHERE id > 1000 | | 117892 | 117279 | root | localhost | test | 47 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
DELETE FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 7 | Updating | DELETE FROM test WHERE id > 10 | | 153272 | 152659 | root | localhost | test | 7 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
SELECT ... FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 2 | Sending data | SELECT * FROM test | | 160014 | 159401 | root | localhost | test | 2 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

This means for searching the locker you have to find all connections which are not in processlist command Sleep and which are not in processlist state Waiting for table level lock sort the remaining by processlist time and the one with the longest processlist time is probably the evildoer.

In some cases where you have a Master/Master set-up (or if you have the problem on the slave) data changes can also be induced by the (other) Master. In this situation it looks as follows:

and then you can check the according PERFORMANCE_SCHEMA views:

SQL> SELECT @uptime := variable_value FROM information_schema.global_status WHERE variable_name = 'Uptime'; SQL> SELECT @start := DATE_SUB(NOW(), INTERVAL @uptime SECOND) AS start;

General statistics:

SQL> SELECT * FROM performance_schema.table_lock_waits_summary_by_table; +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_READ_NORMAL | SUM_TIMER_READ_NORMAL | MIN_TIMER_READ_NORMAL | AVG_TIMER_READ_NORMAL | MAX_TIMER_READ_NORMAL | COUNT_READ_WITH_SHARED_LOCKS | SUM_TIMER_READ_WITH_SHARED_LOCKS | MIN_TIMER_READ_WITH_SHARED_LOCKS | AVG_TIMER_READ_WITH_SHARED_LOCKS | MAX_TIMER_READ_WITH_SHARED_LOCKS | COUNT_READ_HIGH_PRIORITY | SUM_TIMER_READ_HIGH_PRIORITY | MIN_TIMER_READ_HIGH_PRIORITY | AVG_TIMER_READ_HIGH_PRIORITY | MAX_TIMER_READ_HIGH_PRIORITY | COUNT_READ_NO_INSERT | SUM_TIMER_READ_NO_INSERT | MIN_TIMER_READ_NO_INSERT | AVG_TIMER_READ_NO_INSERT | MAX_TIMER_READ_NO_INSERT | COUNT_READ_EXTERNAL | SUM_TIMER_READ_EXTERNAL | MIN_TIMER_READ_EXTERNAL | AVG_TIMER_READ_EXTERNAL | MAX_TIMER_READ_EXTERNAL | COUNT_WRITE_ALLOW_WRITE | SUM_TIMER_WRITE_ALLOW_WRITE | MIN_TIMER_WRITE_ALLOW_WRITE | AVG_TIMER_WRITE_ALLOW_WRITE | MAX_TIMER_WRITE_ALLOW_WRITE | COUNT_WRITE_CONCURRENT_INSERT | SUM_TIMER_WRITE_CONCURRENT_INSERT | MIN_TIMER_WRITE_CONCURRENT_INSERT | AVG_TIMER_WRITE_CONCURRENT_INSERT | MAX_TIMER_WRITE_CONCURRENT_INSERT | COUNT_WRITE_DELAYED | SUM_TIMER_WRITE_DELAYED | MIN_TIMER_WRITE_DELAYED | AVG_TIMER_WRITE_DELAYED | MAX_TIMER_WRITE_DELAYED | COUNT_WRITE_LOW_PRIORITY | SUM_TIMER_WRITE_LOW_PRIORITY | MIN_TIMER_WRITE_LOW_PRIORITY | AVG_TIMER_WRITE_LOW_PRIORITY | MAX_TIMER_WRITE_LOW_PRIORITY | COUNT_WRITE_NORMAL | SUM_TIMER_WRITE_NORMAL | MIN_TIMER_WRITE_NORMAL | AVG_TIMER_WRITE_NORMAL | MAX_TIMER_WRITE_NORMAL | COUNT_WRITE_EXTERNAL | SUM_TIMER_WRITE_EXTERNAL | MIN_TIMER_WRITE_EXTERNAL | AVG_TIMER_WRITE_EXTERNAL | MAX_TIMER_WRITE_EXTERNAL | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | TABLE | test | test | 994184 | 410880205008924 | 126840 | 413283678 | 101480121005292 | 6 | 7095792 | 300792 | 1182330 | 2797728 | 994178 | 410880197913132 | 126840 | 413285943 | 101480121005292 | 3 | 2583912 | 300792 | 861153 | 1359906 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 4511880 | 608832 | 1503960 | 2797728 | 0 | 0 | 0 | 0 | 0 | 497080 | 410589058214256 | 126840 | 826001559 | 101480121005292 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 2890140 | 162174 | 320724 | 953112 | 497089 | 291136808736 | 287202 | 585276 | 69665964 | | TABLE | test | vol | 110 | 113655888 | 94224 | 1032840 | 3911202 | 64 | 30244998 | 94224 | 472479 | 3911202 | 46 | 83410890 | 318912 | 1812906 | 3246198 | 32 | 11410164 | 94224 | 356511 | 558096 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32 | 18834834 | 96036 | 588447 | 3911202 | 0 | 0 | 0 | 0 | 0 | 23 | 22661778 | 318912 | 985275 | 1338162 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 23 | 60749112 | 1040088 | 2640990 | 3246198 | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+

Locks currently in use (column EXTERNAL_LOCK?):

Threads currently locking:

But I still have not found a direct view to prove who is the locker of a specific locked thread/connection. So some work is still to do...

Taxonomy upgrade extras: myisam lock locking

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

MariaDB and MySQL package holding or locking

Shinguz - Fri, 2020-08-07 16:02

Repositories from your favourite Linux distribution and from your favourite database software vendor get regular package updates. If you do periodic system upgrades (for example every 2 weeks as we do) you get the newest packages of a release series.
Unfortunately recently the software vendors started not only to fix bugs in new releases but also to introduce new features. And when you fix bugs or introduce new stuff new bugs might appear.
To avoid being hit unexpectedly by new bugs you do not want to upgrade untested software. To achieve this you want to keep/not upgrade some important packages. For example the MariaDB or MySQL server package.
This package pinning is called package version lock on CentOS and Redhat and package holding on Debian and Ubuntu.

How you do this I have summarized below:

Debian / Ubuntu

shell> dpkg -l | grep mariadb ii libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C ii mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries ii mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries ii mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) ii mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries ii mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-mark hold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 galera-4 set on hold. libmariadb3 set on hold. mariadb-client-10.4 set on hold. mariadb-client-core-10.4 set on hold. mariadb-common set on hold. mariadb-server-10.4 set on hold. mariadb-server-core-10.4 set on hold. shell> apt-mark showhold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 shell> dpkg -l | grep -e mariadb -e galera hi galera-4 26.4.2-bionic amd64 Replication framework for transactional applications hi libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C hi mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries hi mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries hi mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) hi mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries hi mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-get upgrade Reading package lists... Done Building dependency tree Reading state information... Done Calculating upgrade... Done The following packages were automatically installed and are no longer required: ... Use 'apt autoremove' to remove them. The following packages have been kept back: galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 netplan.io The following packages will be upgraded: ... 67 to upgrade, 0 to newly install, 0 to remove and 11 not to upgrade. Need to get 107 MB of archives. After this operation, 3,876 kB of additional disk space will be used. Do you want to continue? [Y/n]

With the command apt-mark unhold you can release pinned packages again.

CentOS / Redhat

To restrict a package to a fixed version number with yum you have to first install the package yum-plugin-versionlock [ man yum-versionlock ]:

shell> yum install yum-plugin-versionlock

To add a version lock for the MariaDB packages:

shell> yum versionlock add MariaDB-server MariaDB-client MariaDB-common MariaDB-compat galera-4 Adding versionlock on: 0:MariaDB-server-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-client-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-common-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-compat-10.4.10-1.el7.centos Adding versionlock on: 0:galera-4-26.4.3-1.rhel7.el7.centos versionlock added: 5

To list any available updates that are currently blocked by version lock you can run:

shell> yum versionlock status 0:MariaDB-common-10.4.13-1.el7.centos.* 0:MariaDB-compat-10.4.13-1.el7.centos.* 0:MariaDB-client-10.4.13-1.el7.centos.* 0:MariaDB-server-10.4.13-1.el7.centos.* 0:galera-4-26.4.4-1.rhel7.el7.centos.* versionlock status done

And to list the current version lock entries:

shell> yum versionlock list 0:MariaDB-server-10.4.10-1.el7.centos.* 0:MariaDB-client-10.4.10-1.el7.centos.* 0:MariaDB-common-10.4.10-1.el7.centos.* 0:MariaDB-compat-10.4.10-1.el7.centos.* 0:galera-4-26.4.3-1.rhel7.el7.centos.* versionlock list done

With:

shell> yum versionlock delete MariaDB-server

you can remove any matching version lock entries again.

And to clear the whole list of version lock entries use:

yum versionlock clear

If you will do the next yum update you will not see the MariaDB packages in the list any more:

shell> yum update ... Dependencies Resolved ======================================================================================================================================================== Package Arch Version Repository Size ======================================================================================================================================================== Installing: kernel x86_64 3.10.0-1127.18.2.el7 updates 50 M Updating: bind-export-libs x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-libs-lite x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-license noarch 32:9.11.4-16.P2.el7_8.6 updates 90 k binutils x86_64 2.27-43.base.el7_8.1 updates 5.9 M ca-certificates noarch 2020.2.41-70.0.el7_8 updates 382 k dbus x86_64 1:1.10.24-14.el7_8 updates 245 k dbus-libs x86_64 1:1.10.24-14.el7_8 updates 169 k device-mapper x86_64 7:1.02.164-7.el7_8.2 updates 295 k device-mapper-libs x86_64 7:1.02.164-7.el7_8.2 updates 324 k grub2 x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-common noarch 1:2.02-0.86.el7.centos updates 729 k grub2-pc x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-pc-modules noarch 1:2.02-0.86.el7.centos updates 850 k grub2-tools x86_64 1:2.02-0.86.el7.centos updates 1.8 M grub2-tools-extra x86_64 1:2.02-0.86.el7.centos updates 1.0 M grub2-tools-minimal x86_64 1:2.02-0.86.el7.centos updates 174 k kernel-tools x86_64 3.10.0-1127.18.2.el7 updates 8.1 M kernel-tools-libs x86_64 3.10.0-1127.18.2.el7 updates 8.0 M libgudev1 x86_64 219-73.el7_8.8 updates 107 k microcode_ctl x86_64 2:2.1-61.10.el7_8 updates 2.7 M python-perf x86_64 3.10.0-1127.18.2.el7 updates 8.1 M rsyslog x86_64 8.24.0-52.el7_8.2 updates 621 k selinux-policy noarch 3.13.1-266.el7_8.1 updates 497 k selinux-policy-targeted noarch 3.13.1-266.el7_8.1 updates 7.0 M systemd x86_64 219-73.el7_8.8 updates 5.1 M systemd-libs x86_64 219-73.el7_8.8 updates 416 k systemd-sysv x86_64 219-73.el7_8.8 updates 94 k yum-plugin-fastestmirror noarch 1.1.31-54.el7_8 updates 34 k Removing: kernel x86_64 3.10.0-957.1.3.el7 @updates 63 M Transaction Summary ======================================================================================================================================================== Install 1 Package Upgrade 28 Packages Remove 1 Package Total download size: 105 M
Server restart required: reboot

After you did a full upgrade of all packages typically a server restart is required. Because this causes downtime and service loss we only want to do this if it is really necessary. To find out if a server restart is really required you can run the following commands:

Debian / Ubuntu shell> cat /var/run/reboot-required
CentOS / Redhat shell> yum install yum-utils shell> needs-restarting -r Core libraries or services have been updated: kernel -> 3.10.0-1127.18.2.el7 systemd -> 219-73.el7_8.8 dbus -> 1:1.10.24-14.el7_8 Reboot is required to ensure that your system benefits from these updates.

If only some services must be restarted you can see with:

shell> needs-restarting -s systemd-logind.service NetworkManager.service dbus.service avahi-daemon.service sshd.service polkit.service systemd-udevd.service tuned.service systemd-journald.service firewalld.service
Taxonomy upgrade extras: debian ubuntu centos redhat package upgrade lock locking

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

MariaDB SQL Error Log Plugin

Shinguz - Thu, 2020-07-30 12:26

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

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were sent from the MariaDB daemon to applications/clients and writes it to a log file.

This Client error log file can be analysed later to find and fix bugs in the application if the applications did not catch the errors themselves.

Installation

The Plugin can be easily installed with:

SQL> INSTALL PLUGIN sql_error_log SONAME 'sql_errlog';

and uninstalled again with:

SQL> UNINSTALL PLUGIN sql_error_log; Query OK, 0 rows affected, 1 warning (0.002 sec)

The installation and uninstallation informations are persistent and survive a database instance restart.

If you uninstall the plugin you get a warning:

telling you that the plugin is still running until the next database instance restart.

If the plugin currently is installed you can find with the following command:

SQL> SELECT * FROM information_schema.plugins WHERE PLUGIN_TYPE = 'AUDIT'\G *************************** 1. row *************************** PLUGIN_NAME: SQL_ERROR_LOG PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 3.2 PLUGIN_LIBRARY: sql_errlog.so PLUGIN_LIBRARY_VERSION: 1.14 PLUGIN_AUTHOR: Alexey Botchkov PLUGIN_DESCRIPTION: Log SQL level errors to a file with rotation PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0
Variables

With the command:

you can see the SQL Error Log configuration variables. These variables are NOT shown if the the plugin is not installed yet or uninstalled again.

The SQL Error Log is written by default to $datadir/sql_error.log and cannot be change during runtime:/

SQL> SET GLOBAL sql_error_log_filename = '/home/mysql/database_slow/mariadb-105/log/sql_errors.log'; ERROR 1238 (HY000): Variable 'sql_error_log_filename' is a read only variable
Log Rotation

The other four variables are related to log rotation. It is cool, that this is done by the database and has not to be done by O/S means: See also: MariaDB Log Rotation.

On the first look it did not work like expected. But when reading the documentation again: This is the 'write-only' variable. Assigning TRUE to this variable forces the log rotation. it becomes understandable. If you enter:

SQL> SET GLOBAL sql_error_log_rotate=1; Query OK, 0 rows affected (0.000 sec)

the SQL Error Log is rotated in the background and you can see this in $datadir:

shell> ls -ltr sql_errors.log* -rw-rw---- 1 mysql mysql 53994 Jul 30 10:28 sql_errors.log.4 -rw-rw---- 1 mysql mysql 0 Jul 30 10:28 sql_errors.log.3 -rw-rw---- 1 mysql mysql 1517 Jul 30 10:29 sql_errors.log.2 -rw-rw---- 1 mysql mysql 0 Jul 30 10:29 sql_errors.log.1 -rw-rw---- 1 mysql mysql 6337 Jul 30 10:34 sql_errors.log

You should think in the way of: FLUSH SQL_ERROR LOGS;

The variable itself always stays OFF:

There are currently no status variables available to show how many errors were sent to the application.

Options

There is a configuration option NOT shown in the variable output: sql_error_log. Valid values are OFF, ON, FORCE and FORCE_PLUS_PERMANENT. It looks like that this option can only me used when injected directly into the call of the mysqld (mysqld_safe, systemd unit file, etc.) and thus it is useless for most cases because you do not want to change those things in production (upgrade, etc.).

Enabling/Disabling of the SQL Error Log

You can disable the SQL Error Log with the following command:

SQL> SET GLOBAL sql_error_log_rate = 0;

You enable it again by setting the value greater than 0.

Interpretation of the SQL Error Log

The format of the SQL Error Log looks roughly as follows: timestamp user[user] @ host[host] ERROR nnnn: Error Message : SQL Query

Here some examples:

shell> grep 2020 sql_errors.log 2020-07-30 10:30:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS -- All users 2020-07-30 10:36:51 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 2020-07-30 10:37:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 2020-07-30 10:40:03 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null)

This helped us for example to identify easily what privileges were missing for our applications.

Aggregation of the SQL Error Log

A simple way to aggregate the SQL Error Log is the following command:

shell> cat sql_errors.log* | sed 's/users2020/users\n2020/' | grep ^2020 | cut -b21- | sort | uniq -c 21 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_current' : SELECT th.PROCESSLIST_USER AS user 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_summary_by_user_by_event_name' : SELECT user, SUM(sum_created_tmp_disk_tables) AS created_tmp_disk_tables 22 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT processlist_user AS user, COUNT(*) AS connections 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT th.processlist_user AS user, SUM(trx.trx_rows_locked) AS rows_locked 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT trx.trx_started, trx.trx_query 16 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT DISTINCT user AS user FROM mysql.user 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND create_user_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND grant_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "root" AND host NOT IN ("localhost", "127.0.0.1", "::1", "chef") 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND super_priv = "Y" 20 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 32 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null) 1 [] @ localhost [] ERROR 1160: Got an error writing communication packets : (null) 1 root[root] @ localhost [] ERROR 1049: Unknown database 'bla' : (null) 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SONAME 'sql_errlog'' at line 1 : UNINSTALL PLUGIN sql_error_log SONAME 'sql_errlog' 1 root[root] @ localhost [] ERROR 1193: Unknown system variable 'sql_error_log_rotat' : set global sql_error_log_rotat=on 1 root[root] @ localhost [] ERROR 1229: Variable 'sql_error_log_rotate' is a GLOBAL variable and should be set with SET GLOBAL : set session sql_error_log_rotate=on 1 root[root] @ localhost [] ERROR 1238: Variable 'sql_error_log_filename' is a read only variable : set global sql_error_log_filename='/home/mysql/database_slow/mariadb-105/log/sql_errors.log'

The sed command was needed because it looks like not every new entry starts at the beginning of a new line (bug?).

How to get warnings

The default sql_mode in MariaDB 10.5 is set to:

SQL> SHOW GLOBAL VARIABLES LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+

by adding TRADITIONAL to the sql_mode:

SQL> SET SESSION sql_mode=concat(@@sql_mode, ',TRADITIONAL');

you can make the SQL Error Log plugin also aware of warnings:

SQL> insert into test.test values (null, 'Some data', '2020-02-31'); Query OK, 1 row affected, 1 warning (0.001 sec) SQL> set session sql_mode=traditional; SQL> insert into test.test values (null, 'Some data', '2020-02-31'); ERROR 1292 (22007): Incorrect datetime value: '2020-02-31' for column `test`.`test`.`ts` at row 1

Just for curiosity, this query was never logged to the SQL Error Log:

SQL> SELECT 5/0; +------+ | 5/0 | +------+ | NULL | +------+ 1 row in set, 1 warning (0.000 sec)

But this seems to be more of a parser issue?

Logging comments

Comments are also logged. But the MariaDB client by default strips comments away. When you run the MariaDB client with the --comment option they are present to the server:

shell> mariadb --user=root --comment test SQL> /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31'); -- blub ERROR 1365 (22012): Division by 0

And it will be shown in the SQL Error Log:

2020-07-30 11:23:31 root[root] @ localhost [] ERROR 1365: Division by 0 : insert into test.test values (5/0, 'Some data', '2020-02-31') 2020-07-30 11:23:08 root[root] @ localhost [] ERROR 1365: Division by 0 : /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31') -- blub
Summary

The SQL Error Log is a cool plugin which helps us to identify problems and bugs in the application. This is similar functionality like in MariaDB/MySQL PERFORMANCE_SCHEMA: Finding syntactically wrong/erroneous MySQL queries.

Taxonomy upgrade extras: mariadb sql error logging error log syntax

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

FromDual Ops Center File Transfer

Shinguz - Tue, 2020-07-28 09:59

With the FromDual Ops Center file transfer tool you can easily upload files from your personal computer to the focmm machine, download files from the focmm machine to your personal computer or transfer files from the focmm machine to any of your database machines or between them. This feature is made for importing, exporting or transferring data from, to or between your different database instances. For example to copy a production schema to a testing database instance.

For backup and restore of a database instance or schema see Operations.

You can reach the file transfer tool under the menu Tools on the left and then File transfer.

Upload

For uploading a file just click to the Browse... button, select a file from your local file system and then click to Upload File.

After a while, when the upload is completed, the file will appear in the Download section of your focmm machine.

Alternatively you can also upload files from your personal computer to the focmm machine via your favourite file transfer tools like scp or sftp.

Download

If you want to download a file from your focmm machine to your personal computer just click on one of the files shown in the download section. A window will pop-up and let you choose the location to store the file to.

Alternatively you can also download files from your focmm machine to your personal computer via your favourite file transfer tools like scp or sftp.

Transfer

To transfer files between your focmm machine and your database machines or between your different database machines you have to choose the machines first, then navigate through your file system tree on each server and then press the transfer link (arrow left to right or arrow right to left) to transfer the file.

Taxonomy upgrade extras: focmm FromDual Ops Center file file transfer

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

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

The first column shows if the crontab job is active or not.
The second column indicates the O/S user the crontab job should run as.
Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
Now follows the actual command which should be run with a comment describing the command.
The column Last indicates when the job was run last successfully.
And RC shows the last Return Code of the job (0 - success, otherwise error number).
With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmm FromDual Ops Center crontab centralization

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

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

The first column shows if the crontab job is active or not.
The second column indicates the O/S user the crontab job should run as.
Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
Now follows the actual command which should be run with a comment describing the command.
The column Last indicates when the job was run last.
And RC shows the last Return Code of the job (0 - success, otherwise error number).
With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmm FromDual Ops Center crontab centralization

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

Languages

Login

You are here

MySQL Tech-Feed (en)

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

MariaDB Push Replication

VSZ behaviour with MariaDB MEMORY tables

Traffic mirroring with MariaDB MaxScale

MariaDB Galera Cluster Upgrade Path

How to force InnoDB Buffer Pool flushing

Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster

Partial Restore of a Table into a MariaDB Galera Cluster

MariaDB/MySQL Datenbank-Administrator/in gesucht

Partial Table or Schema restore from mariabackup full backup

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

Creating synthetic data sets for tuning SQL queries

MyISAM locking and who is the evil?

MariaDB and MySQL package holding or locking

MariaDB SQL Error Log Plugin

FromDual Ops Center File Transfer

Centralized Crontab with FromDual Ops Center

Centralized Crontab with FromDual Ops Center

Pages

Navigation

Trademarks

Languages

Login

Search form

You are here

MySQL Tech-Feed (en)

Pages

Navigation

Trademarks