- discuss - lists.mariadb.org

[Maria-discuss] mariabackup is slow (log scanned up to)
by John Pace 29 Apr '22

29 Apr '22

Hi, I am using mariabackup to backup a database. On day 1 a full backup with mariabackup --backup --user=Backup --password=my-password --target-dir=path-to-full and on day 2-7 a incremental backup with mariabackup --backup --user=Backup --password=my-password --target-dir=path-to-inc --incremental-basedir=path-to-full Then follows again a new backup for day 1 and so on. The database can be used while the backup is running. A web application for Apache Tomcat uses the database. This worked very well for a couple of weeks, the backup was finished within 20 minutes. But recently I noticed that the incremental backup took much longer than before. I checked the output of mariabackup and it was spammed several hours with lines like these [00] 2022-04-20 00:48:00 >> log scanned up to (1747989580536) [00] 2022-04-20 00:48:01 >> log scanned up to (1747989580536) [00] 2022-04-20 00:48:02 >> log scanned up to (1747989580536) 2022-04-20 0:48:03 0 [Note] InnoDB: Read redo log up to LSN=1747989580800 [00] 2022-04-20 00:48:03 >> log scanned up to (1747989580536) [00] 2022-04-20 00:48:04 >> log scanned up to (1747989580536) [00] 2022-04-20 00:48:05 >> log scanned up to (1747989580536) [00] 2022-04-20 00:48:06 >> log scanned up to (1747989580536) Yesterday the incremental backup took 7 hours so I wonder if there is something wrong. MariaDB shows no error in the log: 2022-03-15 8:35:48 0 [Note] InnoDB: Using Linux native AIO 2022-03-15 8:35:48 0 [Note] InnoDB: Uses event mutexes 2022-03-15 8:35:48 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2022-03-15 8:35:48 0 [Note] InnoDB: Number of pools: 1 2022-03-15 8:35:48 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2022-03-15 8:35:48 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2022-03-15 8:35:48 0 [Note] InnoDB: Completed initialization of buffer pool 2022-03-15 8:35:48 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2022-03-15 8:35:49 0 [Note] InnoDB: 128 rollback segments are active. 2022-03-15 8:35:49 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2022-03-15 8:35:49 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2022-03-15 8:35:49 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2022-03-15 8:35:49 0 [Note] InnoDB: 10.5.8 started; log sequence number 1741176510061; transaction id 38228169 2022-03-15 8:35:49 0 [Note] Plugin 'FEEDBACK' is disabled. 2022-03-15 8:35:49 0 [Note] InnoDB: Loading buffer pool(s) from /path-to-database/ib_buffer_pool 2022-03-15 8:35:49 0 [Note] Server socket created on IP: '::'. 2022-03-15 8:35:50 0 [Note] InnoDB: Buffer pool(s) load completed at 220315 8:35:50 2022-03-15 8:35:50 0 [Note] /path-to-mariadb/bin/mariadbd: ready for connections. Version: '10.5.8-MariaDB' socket: '/tmp/mysql.sock' port: 3306 MariaDB Server PS: Sorry for my test-post. ------------------------------------------------------------------------------------------------- FreeMail powered by mail.de - MEHR SICHERHEIT, SERIOSITÄT UND KOMFORT

1 0

[Maria-discuss] test
by John Pace 29 Apr '22

29 Apr '22

------------------------------------------------------------------------------------------------- FreeMail powered by mail.de - MEHR SICHERHEIT, SERIOSITÄT UND KOMFORT

1 0

[Maria-discuss] Understanding SQL queries
by João Paulo Almeida 14 Apr '22

14 Apr '22

Dear MariaDB Users, We are currently carrying out some research on the understanding and usability of different relational schemas (as part of a Ph.D. student's thesis work). We would like to invite you to contribute to this research by answering a small number of questions on SQL query interpretation. Participation is via an anonymous form and there are only 7 questions, so it should not take more than 10 minutes of your time: https://forms.gle/EA2jYGf4CTmLfEiaA We thank you in advance and would appreciate it if you could also share this research with colleagues who also have a background on SQL. thank you, João Paulo A. Almeida Federal University of Espírito Santo

2 1

[Maria-discuss] mysql 5.5 client vs mariadb 10 server
by Oliver 22 Mar '22

22 Mar '22

Hi, I'm sorry for the question if it is clear that it isn't possible. I'm trying connect from a mysql 5.5.56 client against a mariadb 10 server and I'm having the next error: ERROR 2003 (HY000): Can't connect to MySQL server on 'server' (10061) Subnet source is allowed in mariadb server and grant is given for 'root'@'source_server', it is allowed both IP and hostname. So not sure if it is config problem or compatibility problem. Someone knows please? Thanks beforehand. P.D.: I can connect using dbeaver from my computer client using mysql binaries (but not sure what mysql client version uses dbeaver). Cheers...

4 5

[Maria-discuss] History Length: Purge-able vs. Not Purge-able metrics
by mariadb＠Biblestuph.com 10 Mar '22

10 Mar '22

Greetings, I know I can get the current undo log history length via: > show status like '%history%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Innodb_history_list_length | 854 | +----------------------------+-------+ but my understanding is that at any given point in time that number represents both the records that can be purged because they are no longer needed by any current transactions (and the purge threads for whatever reason haven't gotten to them yet), as well as those that cannot be purged yet because there are current transactions running that may still need them. So the question is, is there any way to know the difference between those two? I.E., how many are waiting to be purged and how many cannot yet be purged? And a related question. Will the undo log size (the physical size of the file) grow even when there are many entries in the history that can be purged first? Or are those unneeded records purged first and that space reused before the undo log is made larger? Or perhaps I'm mis-understanding the process? TIA, Dan

1 0

[Maria-discuss] Unable to find specific format for mariaDB 10.6.*
by ragul rangarajan 25 Feb '22

25 Feb '22

Hi Team, I try to get MariaDB 10.6.* *bintar-linux-x86_64 tar* from http://archive.mariadb.org/ But I couldn't find any other formats except *bintar-linux-systemd-x86_64 *as in the older version. Can someone please share where to get* bintar-linux-x86-64 *for 10.6.* MariaDB version as like below for 10.5.* https://archive.mariadb.org/mariadb-10.5.15/bintar-linux-x86_64/mariadb-10.… Thanks and regards, Ragul

3 4

[Maria-discuss] demo: running MariaDB with rootless Podman and socket activation
by Erik Sjölund 17 Feb '22

17 Feb '22

Hi, Support for socket activation was added to MariaDB in release 10.6 (released April 2021). Podman also supports socket activation so I thought it would be interesting to combine both of them into a systemd user service. I wrote a small demo: https://github.com/eriksjolund/mariadb-podman-socket-activation (tweet: https://twitter.com/eriksjolundcomp/status/1491162016429768704) and got it to work. Do you have any suggestions on how to improve this? Thanks, Erik Sjölund

2 3

[Maria-discuss] Fwd: [MariaDB Announce] MariaDB 10.8.1 RC and MariaDB 10.7.2, 10.6.6, 10.5.14, 10.4.23, 10.3.33 and 10.2.42 now available
by Reindl Harald 10 Feb '22

10 Feb '22

https://downloads.mariadb.org/mariadb/10.3.33/ points to a form with 10.6 preselected because a redirect to https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.6.5&os=Linux&cpu=x86… and in that useless form 10.3.32 is the newest you can select when trying to download sources is there a reason why such nonsense on download pages is developed other than "some people need to be busy"? when you change to 10.3 and have selcted "source" before the form goes back to Linux with systemd as selection head - table - match -------- Weitergeleitete Nachricht -------- Betreff: [MariaDB Announce] MariaDB 10.8.1 RC and MariaDB 10.7.2, 10.6.6, 10.5.14, 10.4.23, 10.3.33 and 10.2.42 now available MariaDB 10.3.33 - Release Notes: https://mariadb.com/kb/en/mdb-10333-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10333-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.3.33/

2 1

[Maria-discuss] Weird behavior in Fedora 34 server
by Miguel Lavalle 05 Jan '22

05 Jan '22

Hi, I am installing MariaDB in Fedora 34 server with the following sequence of commands: sudo dnf upgrade -y sudo dnf install -y mariadb-server mariadb-devel sudo dnf -y install git vim With this sequence of commands, I get the following installed: $ mysql --version mysql Ver 15.1 Distrib 10.5.13-MariaDB, for Linux (x86_64) using EditLine wrapper and I can successfully perform the initial setup of the root password: sudo mysqladmin -u root password password and continue with the rest of my installation. However, if I change the sequence of commands to: sudo dnf upgrade -y sudo dnf -y install git vim sudo dnf install -y mariadb-server mariadb-devel What I get installed instead is: $ mysql --version mysql Ver 8.0.27 for Linux on x86_64 (Source distribution) and the 'sudo mysqladmin -u root password password' command fails with: mysqladmin: unable to change password; error: '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 '() IDENTIFIED BY 'secretdatabase'' at line 1' Could anyone shed some light on this? Thanks!

3 8

[Maria-discuss] Incremental backup with mariabackup and run-mariabackup
by Dave Wreski 02 Jan '22

02 Jan '22

Hi, I'm using mariadb-10.5.13 on fedora34 with run-mariabackup from https://github.com/omegazeng/run-mariabackup. Has anyone used this backup script or have any recommendations for ones that help to automate the process of using mariabackup for backups? This script appears to create an infinite amount of incremental backups once the original full backup is created. I believe this makes it necessary to restore each of the incrementals on top of the base. If the backups are stored off-site, this creates magnitudes more data that's needed to restore, and increases the time to restore it. It also only appears possible to backup the entire system, not just individual databases. Do you have any recommendations for other scripts that ease the process of making and restoring backups? Thanks, Dave

1 0

[Maria-discuss] sys schema backup
by William Edwards 04 Dec '21

04 Dec '21

Hi, The 'sys' schema is documented to be "A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage." Is there anything in this schema that should be backed up, or can I skip it when creating backups? -- With kind regards, William Edwards

3 2

[Maria-discuss] [Warning] WSREP: certification interval for trx source [...] exceeds the limit of 16384
by Giovanni Agozzino 01 Dec '21

01 Dec '21

Hi people, I'm relatively new to Mariadb+Galera and I'm trying to understand how to manage when a node goes down the best way. I don’t know if this is the right place to ask. If not, I’d appreciate suggestions for where to ask. I am using 1:10.6.5+maria~hirsute galera-4 26.4.9-hirsute I would understand what is the meaning of the warning: [Warning] WSREP: certification interval for trx source [...] exceeds the limit of 16384 and what are the ways to avoid it. I'll explain when I encounter it: I shut down one of my three mariadb nodes during peak hours and I wait to restart it until a SST is triggered. The SST is succesfully done (both the donor and the joiner go without errors). But when applying the IST post SST to keep up with the latest transactions, I see these warning in both donor (before switching from donor/desynced to synced state) and joiner (after completing SST). What is the implication of this warning? I suppose is not the certification failing or I would have errors, but I don't know if is serious business or not. And there is a parameter to tune in the way to avoid this warning? Thank you very much. Best regards, Giovanni

1 0

[Maria-discuss] Question about MariaDB Xpand
by huan zhang 30 Nov '21

30 Nov '21

Hi all, I have some questions about MariaDB Xpand. Not sure whether here is the right place to ask questions or not. If not, appreciate for the suggestions of where to ask. I firstly put my questions here, let's assume the performance topology: 1. Every node of Xpand can read and write as described in white paper, but my question is does MariaDB Xpand support Multi-Master to finish the Global Transactions? There is only one global transaction manager(GTM) or there exists multiple GTM workers at the same time? 2. What's the usage of Paxos in Xpand? If there is only one GTM at a time, it's easy to understand: Paxos is used to determine which is the leader (GTM). 3. MPP feature of Xpand is misleading and the document is too simple to understand the internals of MPP implementation. Queries are splitted into sub query fragments and it seems that each fragment can only do every single work. So can it support Hash Join? e.g. a join fragment builds a hash table for a small table firstly and then probes the big table from the remote node? Thanks in advance. Hubert Zhang

1 0

[Maria-discuss] Issue with MariaDB backup using LVM snapshot
by Riccardo Brunetti > 29 Nov '21

29 Nov '21

Hello. We are using a backup procedure based on a combination of LVM snapshots and rsync. In particular, the procedure is the following: > ... > mysql -h -u -p -e "FLUSH TABLES WITH READ LOCK; \! lvcreate -L -s -n " > > mount > > ... > > rsync ....... At the end, we change the working directory, unmount and lvremove . Sometimes, randomly, the umount command works but the lvremove fails and we are left with the lvm snapshot unmounted but we are unable to remove it. We tried to force a removal without success with the following error: > Logical volume contains a filesystem in use We didn't find any process using that device using lsof and we can't find any handler keeping the device busy. Even a mariadb restart doesn't fix it and the only solution is to reboot the server. The only thing we noticed is a message upon the snapshot creation: > File descriptor 4 (socket:[201844800]) leaked on lvcreate invocation. Parent PID 1564033: mysql We know that this is the mysql unix socket. Have you ever had such an issue? Thanks a lot. Kind regards. Riccardo

1 0

[Maria-discuss] MyISAM: single table GROUP BY plan changes based on LIMIT. BUG?
by Vassilis Virvilis 26 Nov '21

26 Nov '21

2 4

[Maria-discuss] mariadb 10.4.13-lp152.1.1 to 10.5.12-3.9.1 upgrade failure
by Marc Chamberlin 25 Nov '21

25 Nov '21

Hello - I have ran into a problem that maybe someone else has hit and knows a solution for it. I recently upgraded my OpenSuSE 15.2 x64 version to OpenSuSE 15.3 x64 and installed all additional updates and patches that were suggested for 15.3. Most of the upgrade succeeded, all except Mariadb. Google has not been helpful either. My understanding of Mariadb is that after a new install of a new version it is suppose to automagically update and fix the databases from the older version, when the server is restarted, but I think this is failing. I found the upgrade log file for Mariadb and it appears to be reporting some kind of corruption has occurred. This is preventing the mariadb (mysql) server from even starting, so it is not possible to use queries to find and repair the database. - > bigbang:/var/tmp/mysql-protected.lBP2YQ # more log_upgrade_run.err > 2021-10-31 12:11:36 0 [Note] InnoDB: !!! innodb_force_recovery is set > to 1 !!! > 2021-10-31 12:11:36 0 [Note] InnoDB: Uses event mutexes > 2021-10-31 12:11:36 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 > 2021-10-31 12:11:36 0 [Note] InnoDB: Number of pools: 1 > 2021-10-31 12:11:36 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions > 2021-10-31 12:11:36 0 [Note] mysqld: O_TMPFILE is not supported on > /tmp (disabling future attempts) > 2021-10-31 12:11:36 0 [Note] InnoDB: Using Linux native AIO > 2021-10-31 12:11:36 0 [Note] InnoDB: Initializing buffer pool, total > size = 134217728, chunk size = 134217728 > 2021-10-31 12:11:36 0 [Note] InnoDB: Completed initialization of > buffer pool > 2021-10-31 12:11:36 0 [ERROR] InnoDB: corrupted TRX_NO 5f737461748dea16 > 2021-10-31 12:11:36 0 [Note] InnoDB: Retry with innodb_force_recovery=5 > 2021-10-31 12:11:36 0 [ERROR] InnoDB: Plugin initialization aborted > with error Data structure corruption > 2021-10-31 12:11:36 0 [Note] InnoDB: Starting shutdown... > 2021-10-31 12:11:36 0 [ERROR] Plugin 'InnoDB' init function returned > error. > 2021-10-31 12:11:36 0 [ERROR] Plugin 'InnoDB' registration as a > STORAGE ENGINE failed. > 2021-10-31 12:11:36 0 [Note] Plugin 'FEEDBACK' is disabled. > 2021-10-31 12:11:36 0 [ERROR] Unknown/unsupported storage engine: InnoDB > 2021-10-31 12:11:36 0 [ERROR] Aborting On a hunch, I decided to install the OpenSuSE OSS repository for 15.2 and this allowed me to drop back and install version 10.4.13-lp152.1.1 of the mariadb server, client, errormessages, and tools packages. (OpenSuSE 15.3 wants to install version 10.5.12-3.9.1 of these mariadb packages.) And lo and behold the previous 10.4.13-lp152.1.1 package works fine! So what gives? Any idea why the upgraded version of mariadb packages are failing to upgrade and support my databases? As I said I have tried Googling for an answer but no joy finding one. A lot of the proposed solutions, Google found, assume the mariadb server is up and running (hump!) so the example solutions given, use mysql queries and commands to fix corrupt databases and tables. I should also point out that AFAIK most, if not all of my database structures are using InnoDB and not the older MYISAM structures (although the mysql database itself appears to have both). I did make a mariadb backup of all the databases but without the ability to start the mariadb server I don't know how to make use of this backup file to reconstruct all the databases and their tables etc. Thanks in advance for any and all advice on how to upgrade my mariadb server. Marc Chamberlin -- *"The Truth is out there" - Spooky* -- *_ _ . . . . . . _ _ . _ _ _ _ . . . . _ . . . . _ _ . _ _ _ . . . . _ _ . _ . . _ . _ _ _ _ . _ . _ . _ . _ . * Computers: the final frontier. These are the voyages of the user Marc. His mission: to explore strange new hardware. To seek out new software and new applications. To boldly go where no Marc has gone before! (/This email is digitally signed. My public key for sending encrypted email to me can be found at - https://keys.openpgp.org/search?q=marc@domesweetdome.us.com or just ask me for it and I will send it to you as an attachment. If you don't understand, no worries, just ignore it and/or ask me to explain it further./)

5 12

[Maria-discuss] Failed DDL on Galera
by William Edwards 23 Nov '21

23 Nov '21

Hi, A customer ran an 'ALTER TABLE' query, which failed. This caused Galera slaves to shut themselves down: 2021-11-23 9:35:25 1 [ERROR] Slave SQL: Column 2 of table 'inserve_fpfvxiqm.invoices' cannot be converted from type 'date' to type 'int(10) unsigned', Internal MariaDB error code: 1677 2021-11-23 9:35:25 1 [Warning] WSREP: RBR event 17 Update_rows_v1 apply warning: 3, 28891485 2021-11-23 9:35:25 1 [Warning] WSREP: Failed to apply app buffer: seqno: 28891485, status: 1 at /home/buildbot/buildbot/build/galera/src/trx_handle.cpp:apply():353 [ 3 more times ] 2021-11-23 9:35:25 1 [Warning] WSREP: RBR event 17 Update_rows_v1 apply warning: 3, 28891485 2021-11-23 9:35:26 1 [ERROR] WSREP: Failed to apply trx: source: 41c479ab-0527-11ec-9fcc-9f02b1141194 version: 4 local: 0 state: APPLYING flags: 1 conn_id: 13475392 trx_id: 63620823 seqnos (l: 312653, g: 28891485, s: 28891454, d: 28891449, ts: 10603037948851154) 2021-11-23 9:35:26 1 [ERROR] WSREP: Failed to apply trx 28891485 4 times 2021-11-23 9:35:26 1 [ERROR] WSREP: Node consistency compromised, aborting... The node on which the query was executed, crashed ("mysqld got signal 11"). At first sight, this seems to be the result of "WSREP: Send action {(nil), 528, TORDERED} returned -107 (Transport endpoint is not connected)" - as all the slaves were down at this point. As 'ALTER' is a DDL statement that does not support rollback, I understand that the only option for Galera may be to abort. But is there a way to handle something like this more gracefully? -- With kind regards, William Edwards

1 0

[Maria-discuss] How can I separate one test from mysql_test
by Lukas Javorsky 19 Nov '21

19 Nov '21

Hi guys, I've found few particular tests from the test suite that is failing due to the OpenSSL-3 version and I want to separate them and give them to the OpenSSL team in Red Hat. The idea behind this is to give them a set of commands/config options (reproducer) for each test and they will try to find out what's the exact problem there and help us develop the patch. The tests that I'm interested in are (there are more of them, I just need to see how to reproduce few of them and I'll do the same for the other ones as well): encryption.corrupted_during_recovery encryption.innodb-checksum-algorithm encryption.innodb-discard-import-change encryption.innodb_page_encryption_key_change binlog_encryption.rpl_loaddata_local binlog_encryption.rpl_parallel_free_deferred_event IMPORTANT: These tests are failing only with the 'cbc' cipher in use, the 'ctr' is passing. Could you please help me with how to make a reproducer for each test, so I can create them and give them to the OpenSSL team? Thank you for any help you can provide Lukas -- S pozdravom/ Best regards Lukáš Javorský Associate Software Engineer, Core service - Databases Red Hat <https://www.redhat.com> Purkyňova 115 (TPB-C) 612 00 Brno - Královo Pole ljavorsk(a)redhat.com <https://www.redhat.com>

3 14

[Maria-discuss] Mariadb 10.2 - 10.6 mysqld_multi problem
by Tom Dangler 18 Nov '21

18 Nov '21

We're working on upgrading a mariadb-10.1 server to mariadb-10.6. We run multiple instances of Mariadb on this server, and some run different versions of Mariadb for different reasons. When we attempted to upgrade to mariadb-10.2, we noticed that we can no longer specify mysqld=/path/to/mysqld without generating an error and not starting the instance. We get [ERROR] /opt/mariadb10_2/bin/mysqld: unknown variable 'mysqld=/opt/mariadb10_2/bin/mysqld'. We tested this with 10.2 and 10.6 binaries. When using 10.1 binaries for the [mysqld_multi] group, it accepts the mysqld= argument just fine, and starts each instance with the version defined in mysql= in each option group. The documentation still shows that mysqld= is still a valid configuration, but it does not seem to work from 10.2 to current. Is there a different configuration for this now? We would like to continue using mysqld_multi with different versions of Mariadb. Using 10.1 binaries for mysqld_multi it works [mysqld_multi] mysqld = /opt/mariadb10_1/bin/mysqld_safe mysqladmin = /opt/mariadb10_1/bin/mysqladmin user = multi_admin password = ourpassword Using 10.2 - 10.6 binaries for mysqld_multi we get errors about unknown variable mysqld [mysqld_multi] mysqld = /opt/mariadb10_6/bin/mysqld_safe mysqladmin = /opt/mariadb10_6/bin/mysqladmin user = multi_admin password = ourpassword [mysqld1] mysqld = /opt/mariadb10_1/bin/mysqld datadir = /opt/mysql_data/mariadb10_1 port = 3306 socket = /var/run/mysqld/mariadb_10_1.sock pid-file = /var/run/mysqld/mariadb_10_1.pid user = mysql [mysqld2] mysqld = /opt/mariadb10_2/bin/mysqld datadir = /opt/mysql_data/mariadb10_2 port = 3307 socket = /var/run/mysqld/mariadb_10_2.sock pid-file = /var/run/mysqld/mariadb_10_2.pid user = mysql [mysqld6] mysqld = /opt/mariadb10_6/bin/mysqld datadir = /opt/mysql_data/mariadb10_6 port = 3308 socket = /var/run/mysqld/mariadb_10_6.sock pid-file = /var/run/mysqld/mariadb_10_6.pid user = mysql -- Tom Dangler IT Architect and Systems Manager T 214.778.3158 | M 214.493.0386 | F 888.811.1566 tom.dangler(a)oakwoodsoftware.com Oakwood Software Insurance Solutions 6201 W Plano Parkway | Suite 225 | Plano, TX 75093 Facebook <https://www.facebook.com/OakwoodSoftware> | LinkedIn <https://www.linkedin.com/company/oakwoodsoftware> | Twitter <https://twitter.com/TheRealOakwood> | oakwoodsoftware.com <http://www.oakwoodsoftware.com/> An Amwins Company

1 0

[Maria-discuss] JOB | Permanent DBA in Quebec
by James Tobin 15 Nov '21

15 Nov '21

Contact me using "jamesbtobin (at) gmail (dot) com" to learn more

1 0

[Maria-discuss] Timestamp column not inserting UTC
by William Edwards 13 Nov '21

13 Nov '21

Hi, I have a `timestamp` column, because I would like to insert timestamps in UTC. The documentation says that the inserted value should be converted from my session's time zone to UTC, but I can't reproduce that behaviour. I'm on MariaDB 10.5.9. I have a column of type `timestamp`: `created_at` timestamp NULL DEFAULT NULL The documentation at https://mariadb.com/kb/en/timestamp/ says: "If a column uses the TIMESTAMP data type, then any inserted values are converted from the session's time zone to Coordinated Universal Time (UTC) when stored, and converted back to the session's time zone when retrieved." However, I cannot reproduce this intended behaviour. I set my time zone as follows. (The value was 'SYSTEM', which should also be '+01:00', but I'm setting it explicitly just in case.) -- MariaDB [db]> set time_zone = '+01:00'; Query OK, 0 rows affected (0.006 sec) MariaDB [db]> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-13 14:56:09 | +---------------------+ 1 row in set (0.001 sec) MariaDB [db]> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | +01:00 | +--------------------+---------------------+ 1 row in set (0.000 sec) -- Then, I update the values in the `timestamp` column to the timestamp of my session's time zone: -- MariaDB [db]> update objs set created_at=now(); Query OK, 2 rows affected (0.001 sec) Rows matched: 2 Changed: 2 Warnings: 0 -- ... I would expect the inserted value to be converted from my session's time zone ('+01:00') to UTC. However, the inserted value is actually a timestamp of my session's time zone: -- MariaDB [db]> select created_at from objs; +---------------------+ | created_at | +---------------------+ | 2021-11-13 14:56:42 | | 2021-11-13 14:56:42 | +---------------------+ 2 rows in set (0.000 sec) -- I would not be surprised if I'm looking over something very obvious, but I haven't figured it out yet. Hopefully someone sees what I'm missing. TIA! -- With kind regards, William Edwards

1 1

[Maria-discuss] Clarification on Memory utilization of Mariadb
by ragul rangarajan 10 Nov '21

10 Nov '21

Hi Team, Need clarification on Memory utilization of Mariadb. I have a java application with MariaDB that runs on the same Rhel 7 server with physical memory(RAM) allocated as 12GB where I would see the normal utilization at the start as below. *sys> free -h* * total used free shared buff/cache available* *Mem: 11G 7.8G 1.6G 9.0M 2.2G 3.5G* *Swap: 0B 0B 0B* Over a period of time say >6month, I could see the RAM utilization is almost full, and available space is very less but still, the application continues to work fine without any issues. *sys> free -h* * total used free shared buff/cache available* *Mem: 11G 11G 134M 17M 411M 240M* *Swap: 0B 0B 0B* I don't see any OOM issue or high usage of RSS at the java application level but I could see MariaDB occupies huge memory.... apart from that overall application runs fine. To ensure this we observed memory utilization at the large-scale application servers where we deployed MariaDB and Java applications into different servers with 128GB each and could observe that the system with MariaDB uses full RAM size whereas the Java application consumption looks stable. *Server with Java application* sys> free total used free shared buff/cache available Mem: 128755 125171 606 118 2977 2694 Swap: 65535 9746 55789 *Server with MariaDB* sys> free total used free shared buff/cache available Mem: 128755 125171 606 118 2977 2694 Swap: 65535 9746 55789 *MariaDb conf:* innodb_buffer_pool_size=102G innodb_buffer_pool_instances=8 innodb_io_capacity=3000 innodb_log_buffer_size=8M innodb_log_file_size=2047MB innodb_log_files_in_group=2 Is this expected that MariaDB consumes overall memory and manages within RAM size.... regardless of buffer size configured? Do we need to optimize any configuration to control the MariaDB consumption apart from buffer pool size? Thanks and Regards, Ragul

3 3

[Maria-discuss] Troubleshooting Statement That Crashes MariaDB (mysqld got signal 11)
by Michael Caplan 04 Nov '21

04 Nov '21

Hello, I have a nothing fancy SQL statement that crashes MariaDB 10.3.31. The error log reports "mysqld got signal 11" with the below included mini core dump. The statement caused no issues with my prior install of 10.2.xx. With 10.3, the statement runs the majority of the time. The vast majority of the time. I have never dealt with such an issue with MariaDB, and am unsure where to start with troubleshooting this. Any tips? FYI, there is a corresponding bug recorded here: https://jira.mariadb.org/browse/MDEV-26835 Thanks, Mike 211014 16:13:53 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=587 max_threads=752 thread_count=81 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1784362 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f13e83b3768 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f1644e96dd8 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x32)[0x55d5a2f6f3a2] /usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x55d5a2a46e8d] /lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f27569fc3c0] /usr/sbin/mysqld(+0x978f4f)[0x55d5a2bdbf4f] /usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x55d5a2a4c6d0] /usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x55d5a2b7cb2e] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x18e)[0x55d5a28869be] /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x55d5a28b0dce] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x55d5a28b1137] /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x55d5a28b1282] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x55d5a28b1bcb] /usr/sbin/mysqld(+0x5de9f1)[0x55d5a28419f1] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x55d5a284f6e4] /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x55d5a2852253] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x55d5a285462d] /usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x55d5a28565dd] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x55d5a293f186] /usr/sbin/mysqld(handle_one_connection+0x3f)[0x55d5a293f35f] /lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f27569f0609] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f2756915293] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f13e84a8b70): SELECT * FROM (( # # get postings from last statement and postings not on statements # SELECT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id WHERE ( a.statement_date IS NULL OR a.statement_date = '2021-09-27' ) AND a.practice_id='19686' ORDER BY post_date ) UNION ( # # get postings that have remaining balances # SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id LEFT JOIN ddx_lab_801317.postings b ON a.ref_type = b.ref_type AND a.ref_id = b.ref_id AND a.id != b.id WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT') AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT SUM(a.amount)) + (SELECT SUM(b.amount)) > 0) AND a.practice_id='19686' GROUP BY a.id ORDER BY post_date ) UNION ( # # get partial payments and credits from older statements # SELECT a.*, COALESCE(ci.id, i.id) as invoice_id FROM ddx_lab_801317.postings a LEFT JOIN ddx_lab_801317.cases_invoices ci ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id LEFT JOIN ddx_lab_801317.invoices i ON a.ref_type = 'INVOICE' AND a.ref_id = i.id WHERE ( a.statement_date < '2021-09-27' ) AND (a.type='PAYMENT' OR a.type='CREDIT') AND a.practice_id='19686' )) AS t1 ORDER BY post_date ASC, invoice_id ASC Connection ID (thread ID): 21014956 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes unlimited unlimited processes Max open files 1048576 1048576 files Max locked memory unlimited unlimited bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 514974 514974 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core

2 2

[Maria-discuss] Incorrectly formatted XML files in mariadb-server sources
by Zuzana Miklankova 26 Oct '21

26 Oct '21

Hi, Fedora's rpminspect tool reports [1] some incorrectly formatted XMLs (most of them used for testsuite) within mariadb source files, namely they are: mysql-test/plugin/sphinx/sphinx/testdata.xml - undefined namespace mysql-test/std_data/loadxml.dat - different parser errors, seems to me like it is ill formed on purpose of the test mysql-test/plugin/connect/connect/std_data/nocs.xml - not proper utf-8 encoding mysql-test/std_data/loaddata/mdev9874.xml - not proper utf-8 encoding sql/share/mariadb/charsets/Index.xml - redefined attribute mysql-test/std_data/ldml/Index.xml - redefined attribute It does not seem (to me) that the ill-formatting breaks anything, and that in some cases it is desired. Specifically the loadxml.dat XML, where the test case counts with the form as it is, and Index.xml files, where modification to the XML format could change the meaning of the file. In other cases, it seems (to me) that the correction of the XMLs to a valid format could be possible. These are specifically the XMLs with encoding issues, where it should be sufficient to specify a used charset (ISO-8859-1 in both cases). This is also the case with the undefined namespace in testdata.xml, whose definition should be also possible. Would the correct approach to this issue be to suppress the rpminspect warnings, or should the specified XML files be modified to a valid XML form? Thanks for your opinions in advance. [1] https://osci-jenkins-1.ci.fedoraproject.org/job/fedora-ci/job/rpminspect-pi… Zuzana Miklánková Associate Software Engineer Red Hat <https://www.redhat.com> <https://www.redhat.com>

2 2

[Maria-discuss] MariaDB 10.4 shows a 2e308 cost on a optimization plan correctly preferred by 10.0
by David Strauss 25 Oct '21

25 Oct '21

I'm seeing a regression in query optimization after testing a database+query that was running well on MariaDB 10.0. The issue occurs on an installation of 10.4.18. Version 10.4 considers the plan that 10.0 used but dismisses it as having a cost of 2e308 [1]. The plan 10.4 chooses is highly non-optimal. Based on reviewing some source code, it looks like this sort of "max" cost should only result if summing/multiplying other costs would exceed that maximum. The usual steps of rebuilding tables and index statistics don't help, especially because the JSON output for the optimization plan already shows reasonable index use costs. (The 2e308 cost only starts appearing in a later section.) Because 10.0 lacks the same optimization tracing as 10.4, here is the EXPLAIN output for comparison [2]. Hopefully, there's just something simple amiss and posting here helps anyone else who encounters a 2e308 cost on query optimization plans. I tried to see if this may be fixed in 10.5+, but I haven't found much discussion online of this phenomenon. [1] https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-… [2] https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-…

1 0

[Maria-discuss] State of "alternative" engines and mariadb
by pslawek83 19 Oct '21

19 Oct '21

Hi Guys, what's the state of "alternative" engines (aria and rocks). AFAIR there were some plans to make aria much more advanced than myisam, transactional (transaction safe?) and more optimized for todays hardware. What's the status of RocksBD, basically on the mariadb website i can't find recent updates and most of sources on the internet are outdated. Is it 100% compatible with mariadb nowdays, is there a possibility to have "one-file-per-table" (one set of files) or at least per-database? If everything resides in shared space is there a possibility that one write error (eg. resulting from power loss) can make all rocks tables unreadable? Is it possible to efficiently drop eg. 100GB table partition if the table is using rocks? Last time i checked there were some issues with compaction for Rocks (some limit on DB size, which was rather small can't recall this currently). Is that resolved, and how much additional space is required to compact eg. 1tb database? How's the adoption and developement of both engines going? Are people using it instead of myisam/toku/inno? Is there a lot of effor put into making aria/rocks better? As i'll have to update many servers in the future what's generally the preferred way to go? Drop everything and just use innodb like Oracle did or move towards rocks/aria for everything which doesn't play with innodb well? Another interesting topic is that myisam is having performance issues because of spectre/meltdown mitigations and it seems to hit this engine the most (at least in 10.3, 10.4 as far as I recall). Are there some plans to optimize it for new CPUs (maybe it was already done) or all the effors are going into different storage engines and i should abandon myisam long term? Thanks

2 1

[Maria-discuss] Loading a backup created from 10.6.4 into newly installed 10.6.4 causes mysql.proc to appear corrupt
by Jeff Dyke 30 Sep '21

30 Sep '21

Failing process: Install mariadb from ppa using apt (happens through salt, fwiw) Download last night's backup from a 10.6.4 server, which is a replica from a galera cluster. run the script attached restore_mariabackup.sh Once this is done the database itself works, for our application, but any client trying to access anything that requires mysql.proc gets an error similar to: *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: error : CorruptSep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: sys.x$schema_table_statistics* *Sep 30 19:29:07 devmaria /etc/mysql/debian-start[24656]: Error : Cannot load from mysql.proc. The table is probably corrupted * I just built two new servers, one that builds mariadb and then overwrites the data from a backup and one that just installs mariadb. The two different files base_install.proc.sql and backupedup.proc.sql. The error output from sudo mysql_upgrade on the server that received the backup is in mysql_upgrade.out There are more differences in the proc table if i dump the data as well, but i think much of it is driven by the utf8_mb4 rather than 3. Tried to keep this sucient and use attachments. This process has worked for me for until i upgraded to 10.6.4. Please ask anything you need. I think i've been using this similar process to build dev machines since about 10.4 Thanks Jeff

1 2

[Maria-discuss] A general question about performance of update command
by Lehrbass, Robert 27 Sep '21

27 Sep '21

Hi there, I'm new to MariaDB and I'm running tests on the update command. Server version 10.5.11. I'm finding that updates to a table with engine=columnstore are far slower than to a table with default engine=innodb. Obviously, there is no primary key in a table with engine=columnstore, whereas the innodb table does have a primary key. In my tests, for engine=columnstore, 10000 rows took 1995 seconds to be updated, but for engine=innodb, 10000 rows took only 7 seconds to transfer. The difference suggests columnstore takes about 285X longer than innodb. Is this to be expected? I don't need a lengthy answer, btw, just general guidance. Thanks very much, Rob

1 0

[Maria-discuss] DBSeeder Version 3.0.3
by Walter Weinmann 13 Sep '21

13 Sep '21

We are pleased to release version 3.0.3 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder) DBSeeder also supports the latest version of MariaDB Server available on Docker Hub (DBMS 10.6.4-focal) and Maven Repository (JDBC 2.7.4). [image: Perf_Snap_3.0.3_win10.png]

2 2

[Maria-discuss] AWS Key management plugin key rotation in replication
by Reinis Rozitis 30 Aug '21

30 Aug '21

Hello, I wanted to clarify - does the key rotation (with AWS KMS backend) work in replication scenario? As in do the slaves (somehow) get also notified and perform the re-encryption if the key is rotated (on master) in background or you have to use different key for each slave for rotation to work? In https://mariadb.com/kb/en/encryption-key-management/#key-rotation it wasn't mentioned. wbr rr

2 3

[Maria-discuss] Issues with Upgrading from MariaDB 10.2.22 to 10.5.12
by Michael Caplan 25 Aug '21

25 Aug '21

Hi there. I'm going through an upgrade process I have done before with earlier versions of mariaDB and Mysql, but running into an issue. My goal is to create a new slave including upgrade process from MariaDB 10.2.22 (serverOld) to 10.5.12 on a new server (serverNew) (as inspired by the brighter mind at https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replicat… ) 1. rsync /var/lib/mysql from serverOld to serverNew 2. On serverOld: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 3. rerun rsync from serverOld to serverNew 4. Release read lock from serverOld 5. Start mariadb on serverNew 6. run mysql_upgrade on serverNew 7. Celebrate and have a nap In actual practice step #5 failed numerous times before what I think is now an okay serverNew # Innodb settings between serverOld and serverNew #serverOld innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M #serverNew innodb-flush-method = O_DIRECT innodb-log-file-size = 48M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 128M The only difference is innodb-log-files-in-group setting is removed in serverNew, as it has been depricated and removed in 10.5 # Upgrade after crash? When attempting to start serverNew, it would fail with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error the log file on serverOld showed no evidence of crashing. None the less, I restarted serverOld, and reran the process above. Still the same issue step #5. # Remove ib_logfile* files I then tried forcing the recreation of the redo logs by removing them and then starting up serverNew. This resulted in the following scary errors: [ERROR] InnoDB: Page [page id: space=0, page number=1984] log sequence number 285722576186 is in the future! Current system log sequence number 259573448177. [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. # innodb_fast_shutdown = 0 The next attempt started with serverOld being restarted and innodb_fast_shutdown = 0 being set beforehand. I reran the above steps and still got stuck on step #5 with the following error: [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.2.22. [ERROR] InnoDB: Plugin initialization aborted with error Generic error This time, however, when I removed the ib_logfile* files, we seemed to have an okay startup: [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 [Note] InnoDB: Completed initialization of buffer pool [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 [Note] InnoDB: New log file created, LSN=288830457728 [Note] InnoDB: 128 rollback segments are active. [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" [Note] InnoDB: Creating shared tablespace for temporary tables [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... [Note] InnoDB: File './ibtmp1' size is now 12 MB. [Note] InnoDB: 10.5.12 started; log sequence number 0; transaction id 3037161218 [Note] Plugin 'FEEDBACK' is disabled. [Note] Recovering after a crash using /var/lib/mysql/mysql-bin [Note] Starting crash recovery... [Note] Crash recovery finished. [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool [Note] Server socket created on IP: '::'. [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. [Note] Reading of all Master_info entries succeeded [Note] Added new Master_info '' to hash table [Note] /usr/sbin/mariadbd: ready for connections. Version: '10.5.12-MariaDB-1:10.5.12+maria~focal-log' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution Running mysql_upgrade completed without issue. I'm not feeling confident that this upgrade is actually okay. removing the ib_logfile* seems questionable as a requirement to get the upgrade done. Anyone have similar troubles? Recommendations on what my process should be? Thanks, Mike

3 3

[Maria-discuss] Adding MySQL database backup to rsnapshot for Linux Servers
by Turritopsis Dohrnii Teo En Ming 22 Aug '21

22 Aug '21

Subject: Adding MySQL database backup to rsnapshot for Linux Servers Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 22 August 2021 Sunday Singapore Time Type of Pulication: Plain Text Document Version: 20210822.01 DETAILED INSTRUCTIONS ===================== Looking for mysqldump ===================== # which mysqldump /usr/bin/mysqldump Testing if mysqldump works ========================== # /usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql Check the filesize of MySQL dump ================================ # du -h mysqldump_all_databases.sql 23M mysqldump_all_databases.sql Backup rsnapshot configuration file first ========================================= # cd /etc/ # cp rsnapshot.conf rsnapshot.conf.backup20082021 Deploying MySQL backup script ============================= # cp /usr/share/doc/rsnapshot-1.4.3/utils/backup_mysql.sh /usr/local/bin/ # chown root.root /usr/local/bin/backup_mysql.sh # chmod o-w /usr/local/bin/backup_mysql.sh # chmod +x /usr/local/bin/backup_mysql.sh # cat /usr/local/bin/backup_mysql.sh ===BEGIN of backup_mysql.sh=== #!/bin/sh ############################################################################## # backup_mysql.sh # # by Nathan Rosenquist <nathan(a)rsnapshot.org> # http://www.rsnapshot.org/ # # This is a simple shell script to backup a MySQL database with rsnapshot. # # The assumption is that this will be invoked from rsnapshot. Also, since it # will run unattended, the user that runs rsnapshot (probably root) should have # a .my.cnf file in their home directory that contains the password for the # MySQL root user. For example: # # /root/.my.cnf (chmod 0600) # [client] # user = root # password = thepassword # host = localhost # # This script simply needs to dump a file into the current working directory. # rsnapshot handles everything else. ############################################################################## # $Id: backup_mysql.sh,v 1.6 2007/03/22 02:50:21 drhyde Exp $ umask 0077 # backup the database /usr/bin/mysqldump --all-databases > mysqldump_all_databases.sql # make the backup readable only by root /bin/chmod 600 mysqldump_all_databases.sql ===END of backup_mysql.sh=== Edit rsnapshot configuration file ================================== # nano /etc/rsnapshot.conf Under BACKUP POINTS / SCRIPTS add the following line ==================================================== backup_script /usr/local/bin/backup_mysql.sh secret.teo-en-ming-corp.com/mysqldump/ Test the rsnapshot configuration file for syntax errors ======================================================= # rsnapshot configtest require Lchown Lchown module loaded successfully Syntax OK Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 22 August 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

1 0

[Maria-discuss] MariaDB Server Fest CfP
by Ian Gilfillan 19 Aug '21

19 Aug '21

The Call for Papers for the MariaDB Server Fest is open until 16 August. If you have an idea to share, anything MariaDB-related, new, fun, interesting, scary or exciting, you are welcome to submit at https://mariadb.org/fest2021-cfp-submission/ The MariaDB Server Fest will be held on Tuesday and Wednesday 21-22 September - full details at https://mariadb.org/fest2021/

2 1

[Maria-discuss] ALTER vs MODIFY to set default on a column
by Shane Bishop 14 Aug '21

14 Aug '21

Hi folks, Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so I need to make this as performant as possible. I originally used a query like this: ALTER TABLE wp_ewwwio_images ALTER updated SET DEFAULT CURRENT_TIMESTAMP; This was speedy, and worked a treat, but now I'm finding it doesn't work on all MySQL servers. Notably, we've run into trouble with sites running MariaDB 10.1 and MySQL 5.7, where it says something like this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near CURRENT_TIMESTAMP Per https://dev.mysql.com/doc/refman/5.7/en/alter-table.html the syntax looks fine. At least, the syntax in the manual is identical to that for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html And ditto for MariaDB of course: https://mariadb.com/kb/en/alter-table/ The alternative is to use this: ALTER TABLE wp_ewwwio_images MODIFY updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; This works fine for the affected server versions, but is much slower; ALTER is near instant, MODIFY takes 3-4 seconds on a table with 260k records on SSDs. Who knows what it looks like if some poor sap is on spinning disks still... So I know how to work around said "issue", but what I really want to know is, what IS the actual problem here? Why don't older MySQL (and MariaDB) versions accept the ALTER syntax when the docs are identical? Notably, 10.5 works fine, and I think 10.4 was okay also, don't have any sites to test older versions myself. Thanks! Shane Bishop

3 5

[Maria-discuss] MariaDB master-slave chained replication and parallelism
by Jan Křístek 06 Aug '21

06 Aug '21

Hi, We have a MariaDB 10.3 replication setup with one master and a few chained slaves (each has log_slave_updates switched on). Master uses mainly MyISAM tables, slaves have about 10 or 40 threads for parallel replication. Interesting is, that the first slave in the chain counts replicated statements into Non-Transactional Groups and the following ones count them into Transactional Groups. Also, when checking process lists it seems that just one statement is being processed at the time (of the many threads) on the first slave, while there are multiple slave replication statements being executed on the 2nd and following slaves. Please, does anyone know the reason why the replicated statements are counted into different groups? Or, more importantly, how to increase the parallelism on the first slave in the chain? Thank you for any help. Jan

3 3

[Maria-discuss] sssd with authentication plugin pam
by Michael Barkdoll 05 Aug '21

05 Aug '21

Hi, I'm having issues getting the pam plugin to work with Rocky Linux 8 (RHEL 8) with AppStream MariaDB 10.5. I've installed mariadb appstream for 10.5 and mariadb-pam packages. Added the following to /etc/my.cnf.d: [mariadb] plugin_load_add = auth_pam My sssd is joined to Active Directory. I've created /etc/pam.d/mariadb trying both local pam_unix and pam_sss configurations: # /etc/pam.d/mariadb for local accounts auth required pam_unix.so audit account required pam_unix.so audit # /etc/pam.d/mariadb for sssd active directory accounts auth required pam_sss.so account required pam_sss.so Tried creating local accounts with: #CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam; #CREATE USER 'user2'@'%' IDENTIFIED VIA pam; #GRANT SELECT ON db.* TO 'user2'@'%' IDENTIFIED VIA pam; I've also tried creating AD accounts: #CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'aduser'@'%' IDENTIFIED VIA pam; #CREATE USER 'aduser(a)college.edu'@'%' IDENTIFIED VIA pam USING 'mariadb'; #GRANT SELECT ON db.* TO 'aduser(a)college.edu'@'%' IDENTIFIED VIA pam; I see Redhat has issues with MariaDB 10.3 working with pam plugin but it sounded like 10.5 should work? https://bugzilla.redhat.com/show_bug.cgi?id=1942330 I feel like I'm missing something in my /etc/sssd/sssd.conf file or some pam configuration steps. I'm using authselect with sssd: authselect select custom/user-profile with-mkhomedir with-sudo with-pamaccess All attempts to `mysql -u user -p` fail. MariaDB [(none)]> show plugins; | pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | I tried adding a [pam] section to sssd. [pam] pam_public_domains = all pam_verbosity = 3 Didn't seem to help. I used realmd to join AD. Any help is much appreciated. mysql -u user -p Enter password: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)

4 13

Re: [Maria-discuss] MariaDB master-slave chained replication and parallelism
by Jan Křístek 05 Aug '21

05 Aug '21

Hi Kristian, Thank you for your answer. On Thu, 5 Aug 2021 at 09:44, Kristian Nielsen <knielsen(a)knielsen-hq.org> wrote: > > Interesting. Where do you see these counts? My guess is that these are > counting the "transactional" status flag on each GTID event in the binlog. > I see these counts almost at the end of 'mysql -e "show slave status\G"' You can see these yourself in a mysqlbinlog output from a binlog on the > master respectively the slaves. > I will check that when I get the setup running again. Thank you for the tip. If these show non-transactional on the master but transactional on the first > slave, it sounds like you are replicating from MyISAM tables on the master > to InnoDB tables on the slave. Try SHOW CREATE TABLE t on a relevant table > on the master and the slave and see which storage engine they are using. > I am sure that at least the big tables use MyISAM on master and all the slaves. Could be that one or two small tables use InnoDB. But when the replication is running, just one counter is always increasing, the other is staying zero (or in case of switching the master while being run) staying fixed. Thus, the first slave sees MyISAM changes, and does not do parallel > operation, but writes InnoDB transactions. These InnoDB transactions are > then seen by following slaves which enables the parallel replication > algorithms. > This could be the reason, even though I am sure that all slaves use MyISAM for almost all tables. I suppose that (could be) the default engine might be switched to InnoDB. > The obvious answer is to change the tables to be InnoDB on the master. > Which > may or may not be possible in your setup. > Oh, this is not so easy. The size of a few MyISAM tables is reaching almost TBs. And thank you for the idea with the BlackHole engine :) Jan

2 4

[Maria-discuss] DBSeeder Version 3.0.1
by Walter Weinmann 03 Aug '21

03 Aug '21

We are pleased to release version 3.0.1 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder) The most important enhancement is the possibility to deactivate the constraints of the type FOREIGN, PRIMARY and UNIQUE KEY before the DML statements and to activate them again afterwards. The JDBC DatabaseMetaData class is used for this purpose. DBSeeder also supports the latest version of MariaDB Server available on Docker Hub (DBMS 10.6.3) and Maven Repository (JDBC 2.7.3).

1 0

[Maria-discuss] MySQL crash after 'partial' version upgrade
by William Edwards 26 Jul '21

26 Jul '21

Hello, The weather probably has an effect on me, because I'm not seeing the cause for the issue below right away. Hopefully someone can use the cluebat. Also, I apologize if we're not supposed to paste snippets of this size in email, but I couldn't find a mailing list policy on this. I was using this repo: `deb [arch=amd64,i386,ppc64el] http://mariadb.mirror.pcextreme.nl/repo/10.3/debian stretch main` This repo was no longer being updated, so I replaced it with this repo: `deb [arch=amd64,arm64,ppc64el] http://ams2.mirrors.digitalocean.com/mariadb/repo/10.3/debian buster main` That made these updates available: ``` galera-3/unknown 25.3.33-buster amd64 [upgradable from: 25.3.32-stretch] libmariadb-dev-compat/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] libmariadb-dev/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] libmariadb3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-client-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-client-core-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-common/unknown,unknown,unknown 1:10.3.30+maria~buster all [upgradable from: 1:10.3.28+maria~stretch] mariadb-server-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mariadb-server-core-10.3/unknown 1:10.3.30+maria~buster amd64 [upgradable from: 1:10.3.28+maria~stretch] mysql-common/unknown,unknown,unknown 1:10.3.30+maria~buster all [upgradable from: 1:10.3.28+maria~stretch] ``` Ansible then wrongly attempted installing the meta package 'mariadb-server', which was not installed yet ('mariadb-server-10.3' was installed directly). This meta package depends on 'mariadb-server-10.3 (>= 1:10.3.30+maria~buster)' (we were running 10.3.28, and an update was now available), so apt did this: ``` Building dependency tree... Reading state information... The following packages were automatically installed and are no longer required: gir1.2-packagekitglib-1.0 libappstream4 libglib2.0-bin libgstreamer1.0-0 libpackagekit-glib2-18 libstemmer0d packagekit packagekit-tools python3-distro-info python3-software-properties Use 'apt autoremove' to remove them. The following additional packages will be installed: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 Suggested packages: mailx mariadb-test netcat-openbsd tinyca The following NEW packages will be installed: mariadb-server The following packages will be upgraded: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 5 upgraded, 1 newly installed, 0 to remove and 32 not upgraded. Need to get 12.0 MB of archives. [...] dpkg: error processing package mariadb-server-10.3 (--configure): installed mariadb-server-10.3 package post-installation script subprocess returned error exit status 1 dpkg: dependency problems prevent configuration of mariadb-server: mariadb-server depends on mariadb-server-10.3 (>= 1:10.3.30+maria~buster); however: Package mariadb-server-10.3 is not configured yet. dpkg: error processing package mariadb-server (--configure): dependency problems - leaving unconfigured Processing triggers for man-db (2.8.5-2) ... Processing triggers for systemd (241-7~deb10u8) ... Errors were encountered while processing: mariadb-server-10.3 mariadb-server ``` From 16:06:44 (apt upgrade fail time), SST kept failing due to https://jira.mariadb.org/browse/MDEV-26172 (most likely not related to this issue), till 16:09:32, which is when I stopped MariaDB. After stopping and starting MariaDB (probably loading new libraries), I started seeing this: ``` 210723 16:09:33 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.3.30-MariaDB-1:10.3.30+maria~buster-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=0 max_threads=102 thread_count=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 355304 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f0d8c000c08 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f0d9effcce8 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x561a7200ddee] /usr/sbin/mysqld(handle_fatal_signal+0x54d)[0x561a71b2087d] /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f0df182b730] /usr/sbin/mysqld(+0x9a6f22)[0x561a71d10f22] /usr/sbin/mysqld(+0xa80632)[0x561a71dea632] /usr/sbin/mysqld(+0xa03852)[0x561a71d6d852] /usr/sbin/mysqld(+0xa039f5)[0x561a71d6d9f5] /usr/sbin/mysqld(+0x9fe0f1)[0x561a71d680f1] /usr/sbin/mysqld(+0x9fe1e2)[0x561a71d681e2] /usr/sbin/mysqld(+0xa00aba)[0x561a71d6aaba] /usr/sbin/mysqld(+0xa01129)[0x561a71d6b129] /usr/sbin/mysqld(+0x9c2d88)[0x561a71d2cd88] /usr/sbin/mysqld(+0xa2777c)[0x561a71d9177c] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f0df1820fa3] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f0df174f4cf] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x0): (null) Connection ID (thread ID): 3 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash. We think the query pointer is invalid, but we will try to print it anyway. Query: Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 15657 15657 processes Max open files 32133 32133 files Max locked memory 67108864 67108864 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15657 15657 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core ``` Thanks to systemd, this went on for 2 minutes, until this, which is when I gave up trying to fix this and replaced the node in the Galera cluster: ``` 2021-07-23 16:11:07 0 [Note] Starting crash recovery... 2021-07-23 16:11:07 0 [Note] Crash recovery finished. 2021-07-23 16:11:07 6 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired 2021-07-23 16:11:07 6 [Warning] Checking table: './mysql/user' 2021-07-23 16:11:07 6 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f0f17430fa3] 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11326, page number=3] log sequence number 30633696699 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11330, page number=3] log sequence number 30634051832 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11332, page number=3] log sequence number 30634054073 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Page [page id: space=11333, page number=3] log sequence number 30633696016 is in the future! Current system log sequence number 30424127374. 2021-07-23 16:11:08 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f0f1735f4cf] ``` These are the packages that apt installed/upgraded: ``` The following additional packages will be installed: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 Suggested packages: mailx mariadb-test netcat-openbsd tinyca The following NEW packages will be installed: mariadb-server The following packages will be upgraded: mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server-10.3 mariadb-server-core-10.3 ``` That means that these packages, that were available for upgrade, were not upgraded: - mysql-common - libmariadb3 - libmariadb-dev - libmariadb-dev-compat - galera-3 However, none of the upgraded packages depend on newer versions of the packages above, so I would have expected this - even though installing the meta package was a mistake - to just work (assuming the problem was an e.g. outdated library). I have two other machines in the same situation, where I simply did 'apt upgrade' after replacing the repo, and all seems well. In conclusion: replacing a repo that provides an update from 10.3.28 to 10.3.30, then installing the 'mariadb-server' meta package that does not install all available updates (like 'libmariadb3'), caused MySQL to crash (badly)... While installing all available updates at the same time ('apt upgrade') does not cause this issue on another machine. I don't necessarily want to understand how to avoid this specific situation, because I should've done a full upgrade in any case, but I am wondering what I am missing that caused this issue. -- With kind regards, William Edwards

3 3

[Maria-discuss] MariaDB daily backups on EC2/EBS via EBS snapshots?
by Joachim Dorn 12 Jul '21

12 Jul '21

Hi, for the scenario of a daily backup on a MariaDB with InnoDB engine running on a EC2 with EBS - would a EBS snapshot be enough to have consistent backups that MariaDB could restore from? I know that I might lose in-flight transactions without any "flushing", but for a daily backup, I accept the backup being a few (milli) seconds bit behind, as long as I have a consistent persistent state. To my understanding Amazon EBS snapshots are consistent point-in-time snapshots (e.g via copy-on-write) of the volume, and MariaDB with a transactional storage engine guarantees transactional consistent writes to disk under all conditions. Thanks for any clarification! Joachim

1 0

[Maria-discuss] dist-upgrade for minor version upgrade
by William Edwards 10 Jul '21

10 Jul '21

Hi, https://mariadb.com/kb/en/upgrading-from-mariadb-104-to-mariadb-105/ (and all other minor upgrade guides) contain these steps: 2. Stop MariaDB. 3. Uninstall the old version of MariaDB: On Debian, Ubuntu, and other similar Linux distributions, execute the following: sudo apt-get remove mariadb-server 4. Install the new version of MariaDB. I'm thinking to myself: doesn't it make more sense to instruct readers to 'dist-upgrade' on Ubuntu and Debian (which works because of the 'Replaces' in newer packages)? That would merge steps 3 and 4 (and maybe step 2 as well, because of stop_server() in preinst) into one command that could be easier to use for novice users. Sorry if I missed a detail that dist-upgrade does not take care of. -- With kind regards, William Edwards

2 1

[Maria-discuss] Differences between MariaDB packages
by William Edwards 27 Jun '21

27 Jun '21

Hello, I recently discovered a bug in my MariaDB deployment process. I have two Ansible tasks that respectively add the downloads.mariadb.org Debian repo, and install mariadb-server-10.*. I forgot to add 'update_cache: yes' to the repo add task, so incidentally mariadb-server-10.* was actually installed from the Debian repo. I have seen the following differences between the mariadb-server-10.* packages: - Packages from Debian repo only come with the 'root@localhost' user. Packages from MariaDB repo come with the users 'root@localhost', 'root@::1' and 'root(a)127.0.0.1'. - Packages from Debian repo don't come with 'debian-sys-maint@localhost' user. Instead, the 'root' user is used in /etc/mysql/debian.cnf - Packages from Debian repo use DebianAlternatives for my.cnf. Packages from MariaDB repo also come with a more advanced my.cnf, e.g. 'bind-address = 127.0.0.1' is set - not being able to remotely connect is how I discovered this bug on my side. - Packages from MariaDB repo come with package configuration (specifically: setting root password). Q: I'm wondering if anyone has a more complete list of differences between these packages (the above is just what I've observed from fixing issues due to this bug). I don't know if the Debian maintainers for this package branch off a package by MariaDB, so I posted this here, but please let me know if I would be better off posting this on a Debian mailing list. TIA. With kind regards, William Edwards

3 2

[Maria-discuss] OpenSSL 3.0 support
by Lukas Javorsky 17 Jun '21

17 Jun '21

Hi, We've created two Jira tickets for the OpenSSL 3.0 support, however, we didn't really get a response or any detailed information from you about it. Connector-c: https://jira.mariadb.org/browse/CONC-503 Server: https://jira.mariadb.org/browse/MDEV-25785 It's our big concern to support this ASAP because we are aiming to support OpenSSL version 3.0 in the RHEL-9 which is coming soon. Our mission in RHEL-9 is to aim for the newest software version because we are going to support this OS for more than 10 years, and the major changes should be done in the beginning. We would like to know your opinion about this so we can maybe try to help you in some way. Please let us know what you think about it. Thank you and have a nice day -- S pozdravom/ Best regards Lukáš Javorský Associate Software Engineer, Core service - Databases Red Hat <https://www.redhat.com> Purkyňova 115 (TPB-C) 612 00 Brno - Královo Pole ljavorsk(a)redhat.com <https://www.redhat.com>

2 1

[Maria-discuss] Problem with mariabackup
by Riccardo Brunetti 17 Jun '21

17 Jun '21

Hello. We are using the mariabackup tool to stream to a remote server the backup of our databases. We are having some issue because in the middle of the operation the db server stops responding for many minutes until we ultimately kill the mariabackup process. During the lock, we can see the following: > MariaDB [(none)]> show processlist; > > +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+ > > | Id | User | Host | db | Command | Time | State | Info | Progress | > > +--------+----------------------------+-----------------+--------------------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+----------+ > > | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | > > | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | > > | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | > > | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000 | > > | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query | 17 | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET reason = CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 | > > | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47 | Waiting for global read lock | DELETE FROM `ps4d_marketplace_product_action` > > WHERE `id_product` = 7356 AND `id_lan | 0.000 | > > | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Query | 46 | Waiting for global read lock | INSERT INTO wp_post_views (id, type, period, count) > > VALUES (62075, 3, '2021', 1) > > ON DUPLIC | 0.000 | > > | 302395 | uqlugtoj_enedina.it | localhost | uqlugtoj_enedina | Query | 44 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '1623829414' WHERE `option_name` = '_transient_timeout_wc_r | 0.000 | > > | 302404 | udlah5r1_remigio | localhost:50782 | udlah5r1_vtiger71 | Query | 44 | Waiting for global read lock | insert into vtiger_pbxmanager(pbxmanagerid,direction,callstatus,starttime,endtime,totalduration,bill | 0.000 | > > | 302431 | utlmb6ko_6M0oD0 | localhost | utlmb6ko_6M0oD0 | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '1623743080' WHERE `option_name` = 'action_scheduler_lock_a | 0.000 | > > | 302432 | uml1gkxr_58IJ3 | localhost:50836 | uml1gkxr_prod | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 | > > | 302433 | uml1gkxr_58IJ3 | localhost:50838 | uml1gkxr_prod | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 | > > | 302440 | uqtx0c94_giorgio | localhost:50848 | uqtx0c94_due | Query | 38 | Waiting for global read lock | UPDATE `wp_options` SET `option_value` = '[]' WHERE `option_name` = '_transient_adsforwp_transient_a | 0.000 | > > | 302473 | uw6vnwff_helpdesk | localhost:50932 | uw6vnwff_helpassist | Query | 30 | Waiting for global read lock | INSERT INTO ost_syslog SET created=NOW(), updated=NOW() ,title='DB Error #1205',log_type='Error',log | 0.000 | At the same time, we see messages like these in the system log: > Executing FLUSH NO_WRITE_TO_BINLOG TABLES... > > Executing FLUSH TABLES WITH READ LOCK... This is the command we launch to backup data: > mariabackup --backup --stream=xbstream --user=root --extra-lsndir=${BCKDBDIR} -u${MYSQLUSER} -p${MYSQLPW} -H ${MYSQLHOST}" Is it somehow an expected behavior (some of the db are quite heavily used)? How can we avoid those locks? Thanks Riccardo

3 7

[Maria-discuss] Disable Some Operators in Fulltext Boolean mode
by Samuel Lorch 12 Jun '21

12 Jun '21

Hello, I am using MariaDB in a Project and to Allow users to Search the Data we use Fulltext Indexes, as you cannot make a Fulltext Index across multiple tables we have multiple match against statements and are Combining them with OR. The Problem is that the Natural Language Mode only Matches Full Words and not Partial ones. The Solution is Boolean Mode where you can append a * to a word to allow 0 or more characters. But Boolean Mode also Brings Other Operators. These other Operator can Behave Unexpectedly for The User when Combining Multiple Match Against Statements With OR. Using the "ft_boolean_syntax" variable one can change the characters used for these operators but this is limited to ASCII characters below 127. I suggest That we add a special character that disables a operator. What do you think about this? Or do you See a different Solution? Regards, Samuel

1 0

[Maria-discuss] DBSeeder Version 2.9.0
by Walter Weinmann 08 Jun '21

08 Jun '21

We are happy to announce that version 2.9.0 of DBSeeder ( https://github.com/KonnexionsGmbH/db_seeder) is now available for general use. DBSeeder also supports the latest version of MariaDB Server available on DockerHub (10.6.1).

2 2

[Maria-discuss] Can't build mariadb 10.5 without gssapi?
by Chris Ross (cross2) 27 May '21

27 May '21

G’day there. So, I was trying to turn off the auth_gssapi plugin which we aren’t using in our build of mariadb 10.5.8. However, adding -DPLUGIN_AUTH_GSSAPI=NO, which is how I've turned off multiple other plugins, seems to have no effect. It still searches for gssapi, and then later builds auth_gssapi_client. Is there an issue/defect open yet that this plugin can't be disabled, or have I done something incorrectly to turn it off? - Chris

2 1

[Maria-discuss] Performance of MariaDB ODBC connector
by Dušan Pavlica 05 May '21

05 May '21

Hi, I'm new with MariaDB. We try to switch from MySQL 5.0.96 to MariaDB 10.5.9. Move data to MariaDB was easy but now we have problems with speed of ODBC connector. I tried MariaDB ODBC connector 3.1.12 with default settings without SSL and our applications (mostly written in Embarcadero RAD Studio) showed very poor performance. When I tried MySQL ODBC connector 8.0.24 everything ran smoothly. Queries through MariaDB's connector was 3-5 times slower. In general query log I found that MariaDB's connector generates a lot of queries to information_scheme.columns table and all over again it reads variable TX_ISOLATION. Short extract - MariaDB connector: 3 Query SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION' 3 Prepare select * from stanice 3 Execute select * from stanice 3 Prepare SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Execute SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Close stmt 3 Reset stmt 3 Query SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION' 3 Prepare SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID 3 Execute SELECT * FROM stanice WHERE Sit_ID = 1 ORDER BY Sit_ID, Podsit_ID, ID 3 Prepare SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Execute SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 3 Close stmt 3 Reset stmt MySQL connector: 3 Query SELECT @@tx_isolation ... 3 Query select * from stanice 3 Prepare SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID 3 Execute SELECT * FROM stanice WHERE Sit_ID = '1' ORDER BY Sit_ID, Podsit_ID, ID 3 Close stmt I think those "unnecessary" queries are making difference in performance of those connectors. Does anybody have any ideas what could I do to speed up MariaDB's connector? We prefer not using MySQL connectors anymore. Thanks in advance Dušan Pavlica

2 1

[Maria-discuss] How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server
by Turritopsis Dohrnii Teo En Ming 04 May '21

04 May '21

Subject: How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 3rd May 2021 Monday Type of Publication: PDF Manual Document Version: 20210503.01 ***IMPORTANT NOTICE*** Please note that Turritopsis Dohrnii Teo En Ming’s guide is based on Xiao Guoan’s guide at linuxbabe.com. Reference Guide Used by Teo En Ming: How to Host Multiple Mail Domains in iRedMail with Nginx Link: https://www.linuxbabe.com/mail-server/set-up-iredmail-multiple-domains-nginx Original Author: Xiao Guoan The following is a list of open-source software that will be automatically installed and configured by iRedMail. • Postfix SMTP server • Dovecot IMAP server • Nginx web server to serve the admin panel and webmail • OpenLDAP, MySQL/MariaDB, or PostgreSQL for storing user information • Amavised-new for DKIM signing and verification • SpamAssassin for anti-spam • ClamAV for anti-virus • Roundcube webmail • SOGo groupware, providing webmail, calendar (CalDAV), contacts (CardDAV), tasks and ActiveSync services. • Fail2ban for protecting SSH • mlmmj mailing list manager • Netdata server monitoring • iRedAPD Postfix policy server for greylisting In addition, you need to add MX, A and TXT records to your ISC BIND DNS domain name server. Redundant download links for Turritopsis Dohrnii Teo En Ming's PDF manual: [1] https://www.mediafire.com/file/q6txmx0rc1cwfzw/Additional+Mail+Domains+1st+… [2] https://www.docdroid.net/Q9TRL5D/additional-mail-domains-1st-release-pdf [3] https://www.scribd.com/document/506193434/Additional-Mail-Domains-1st-Relea… [4] https://drive.google.com/file/d/10AYGt4-omBeC3vXJfzswQq0M7E09gXpG/view?usp=… [5] https://drive.google.com/file/d/113dy0AKmBYGugBueK1vS_dJwI35J6XmJ/view?usp=… [6] https://drive.google.com/file/d/1dhpYIZp31ug5hoiVNbjh6uh99s_PEx8q/view?usp=… Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 4th May 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -----BEGIN EMAIL SIGNATURE----- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html ******************************************************************************************** Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -----END EMAIL SIGNATURE-----

2 1

[Maria-discuss] Help for USE is missing
by Krzysztof Żelechowski 27 Apr '21

27 Apr '21

Mel: \h USE mysql: Nothing found Expected: <URL: https://mariadb.com/kb/en/use/ > Please fix, Chris

1 0