- discuss - lists.mariadb.org

[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