April 2025 - discuss - lists.mariadb.org

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 26 Apr '25

26 Apr '25

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

4 3

INSERT INTO InnoDB hang without deadlock on 10.11.11
by Robert L Mathews 24 Apr '25

24 Apr '25

We run several MariaDB 10.11.11 servers, using the stock Debian 12.10 package (version 1:10.11.11-0+deb12u1). Very occasionally (perhaps once per four months of uptime per server) we see a situation where an InnoDB thread "hangs" in the "Update" state of a simple "INSERT INTO". When this happens, the only way to kill the thread is to "kill -9" the overall process (the thread doesn't respond to a normal "KILL HARD query_id", other than changing the state to "Killed"). It doesn't seem to be a deadlock. This problem is not limited to a single database or hardware server; I've seen it on several different databases and physical servers. I've captured a SHOW FULL PROCESS LIST and SHOW ENGINE INNODB STATUS of it happening, pasted both below and at <https://pastebin.com/gX6Hdxvx>. Is this a known issue? Any suggestions? Thanks! ------------------------------------------------------------------------ Id User Host db Command Time State Info Progress 8597007 redacted localhost redacted Query 72 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:23', 1, '', '') 0.000 8597220 redacted localhost redacted Query 42 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:52', 1, '', '') 0.000 8597326 redacted localhost redacted Query 32 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:03', 1, '', '') 0.000 8597439 redacted localhost redacted Sleep 1 NULL 0.000 8597458 redacted localhost redacted Query 16 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('<redacted>', '2025-04-24 07:50:18', 1, '', '') 0.000 8597682 redacted localhost redacted Query 2 Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:32', 1, '', '') 0.000 8597699 redacted localhost redacted Sleep 0 NULL 0.000 8597706 root localhost NULL Query 0 starting SHOW FULL PROCESSLIST 0.000 ===================================== 2025-04-23 23:50:35 0x7fbb111f66c0 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 835 srv_active, 0 srv_shutdown, 798067 srv_idle srv_master_thread log flush and writes: 798902 ---------- SEMAPHORES ---------- ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-04-22 22:47:02 0x7fbb9c06c6c0 *** (1) TRANSACTION: TRANSACTION 15896049065, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 213 lock struct(s), heap size 24696, 18312 row lock(s) MariaDB thread id 7561112, OS thread handle 140443753301696, query id 1249654721 localhost redacted init for update UPDATE wp_actionscheduler_actions SET claim_id=3709294, last_attempt_gmt='2025-04-23 05:47:02', last_attempt_local='2025-04-23 06:47:02' WHERE claim_id = 0 AND scheduled_date_gmt <= '2025-04-23 05:47:02' AND status='pending' ORDER BY priority ASC, attempts ASC, scheduled_date_gmt ASC, action_id ASC LIMIT 25 *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049065 lock_mode X waiting Record lock, heap no 671 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b9bb78; asc x;; *** CONFLICTING WITH: RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049073 lock_mode X locks rec but not gap Record lock, heap no 671 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b9bb78; asc x;; 98 *** CONFLICTING WITH: RECORD LOCKS space id 1369764 page no 991 n bits 768 index status of table `redacted`.`wp_actionscheduler_actions` trx id 15896049065 lock_mode X Record lock, heap no 662 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b800cf; asc ;; Record lock, heap no 663 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b8044b; asc K;; Record lock, heap no 664 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b84042; asc @B;; Record lock, heap no 665 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b95118; asc Q ;; Record lock, heap no 666 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b9909f; asc ;; Record lock, heap no 667 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b99a64; asc d;; Record lock, heap no 668 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 70656e64696e67; asc pending;; 1: len 8; hex 0000000000b99a65; asc e;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 15900136305 Purge done for trx's n:o < 15900136296 undo n:o < 0 state: running History list length 1407 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7fbb9f404380), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x7fbb9f406f80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x7fbb9f3ff680), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x7fbb9f404e80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x7fbb9f405980), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 15900136247, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 8597682, OS thread handle 140443754120896, query id 1424570532 localhost redacted Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:32', 1, '', '') ---TRANSACTION (0x7fbb9f402d80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION (0x7fbb9f400c80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 15900135437, ACTIVE 17 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 8597458, OS thread handle 140444290127552, query id 1424555212 localhost redacted Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('<redacted>', '2025-04-24 07:50:18', 1, '', '') ---TRANSACTION (0x7fbb9f407a80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 15900134864, ACTIVE 32 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 8597326, OS thread handle 140443754325696, query id 1424540084 localhost redacted Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('02', '2025-04-24 07:50:03', 1, '', '') ---TRANSACTION 15900134271, ACTIVE 43 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 8597220, OS thread handle 140441414899392, query id 1424530951 localhost redacted Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:52', 1, '', '') ---TRANSACTION 15900132917, ACTIVE 72 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 8597007, OS thread handle 140441423496896, query id 1424510658 localhost redacted Update INSERT INTO `wp_rank_math_404_logs` (`uri`, `accessed`, `times_accessed`, `referer`, `user_agent`) VALUES ('15', '2025-04-24 07:49:23', 1, '', '') ---TRANSACTION (0x7fbb9f3feb80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync): 0 166351415 OS file reads, 6987933 OS file writes, 3639237 OS fsyncs 156.66 reads/s, 7878 avg bytes/read, 71.50 writes/s, 13.17 fsyncs/s ------------- INSERT BUFFER ------------- size 6, free list len 11863, seg size 11870, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 --- LOG --- Log sequence number 17970676587722 Log flushed up to 17970676583883 Pages flushed up to 17970643193368 Last checkpoint at 17970643193368 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2181038080 Dictionary memory allocated 92637472 Buffer pool size 129791 Free buffers 1396 Database pages 128387 Old database pages 47412 Modified db pages 80 Percent of dirty pages(LRU & free pages): 0.062 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 15 Pages made young 196468759, not young 3200160866 65.33 youngs/s, 3222.73 non-youngs/s Pages read 166314664, created 2924018, written 6791751 156.66 reads/s, 3.00 creates/s, 70.53 writes/s Buffer pool hit rate 994 / 1000, young-making rate 2 / 1000 not 108 / 1000 Pages read ahead 81.03/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 128387, unzip_LRU len: 0 I/O sum[10256]:cur[2292], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 1 read views open inside InnoDB state: sleeping ---------------------------- END OF INNODB MONITOR OUTPUT ============================ -- Robert L Mathews

1 0

Request to Adapt VIDEX (Virtual Index for what-if Analysis) for MariaDB
by 康荣 22 Apr '25

22 Apr '25

Dear MariaDB Community, I hope this message finds you well. We'd like to introduce VIDEX (Virtual Index for MySQL) and humbly seek your guidance and assistance in adapting it for MariaDB compatibility. We've developed a virtual index storage engine (plugin) called VIDEX. It can simulate the impact of indexes on query plan costs without requiring real data. VIDEX forwards the statistical information, ndv (records_per_key), and cardinality information (records_in_range) needed for making query plans via a RESTful approach, with responses provided by an "algorithm service." This disaggregated design is particularly well-suited for integrating various AI-boosted cardinality and ndv algorithms. VIDEX has been successfully deployed at ByteDance, serving thousands of MySQL instances daily and over millions of SQL queries for index optimization tasks. We implemented VIDEX based on MySQL 8.0 and now, thanks to Federico Razzoli for his suggestion (LinkedIn post 1 <https://www.linkedin.com/feed/update/urn:li:activity:7307955728613351424/>, LinkedIn post 2 <https://www.linkedin.com/posts/federicorazzoli_github-bytedancevidex-virtua…>, , Github Issue <https://github.com/bytedance/videx/issues/1>), we'd like to adapt it to MariaDB. However, we've discovered significant differences between MySQL 8.0 and the latest MariaDB codebase. Therefore, we're seeking help from the MariaDB community, and would greatly appreciate collaboration with experienced developers to modify the code. We're very willing to cooperate in this effort. In the future, we also hope to have the opportunity to add VIDEX to the official MariaDB plugin list. *About VIDEX* VIDEX GitHub repository: https://github.com/bytedance/videx/ Virtual index technology is already available in PostgreSQL, Oracle, and IBM DB2, and would provide MariaDB users with valuable capabilities for query optimization and database research. VIDEX offers key features that would benefit MariaDB users: - What-if analysis for index strategies without production impact - Support for integration of custom or AI-based estimation algorithms - Accurate simulation of query plans, verified with complex benchmarks like TPC-H and JOB If you're interested in collaborating or have guidance to offer on adapting our code to MariaDB, we'd greatly appreciate hearing from you. Thank you for your consideration. Best regards, Rong ByteBrain Team, Bytedance

4 4

Client version >= 11.4.1 can't connecto MariaDB server 11.4.5
by gerhard.pauls＠eviden.com 15 Apr '25

15 Apr '25

Hi, I installed MariaDB 11.4.5 on Windows Server 2019. The command line client installed together with server can't connect: C: \Program Files\MariaDB 11.4\bin>mysql -u user -p ERROR 2813 (HY000): Lost connection to server at 'sending authentication information', system error: 0 In the server err log: [Warning] Could not read packet: fd: 1424 state: 1 read length: 4 errno: 0 vio_errno: 1158 length: -1 Problem does not occur with HeidiSQL. Problem does not occur with MariaDB clients version 11.4.0 or lower Problem occurs with MariaDB clients version 11.4.1 or higher Any idea what the problem might be? Best regards, Gerhard

3 2

Re: Fixing typos
by Lakhin Vasilii 14 Apr '25

14 Apr '25

Forgot to attach the patch I mentioned.

5 9

Busy_time rate is a million times higher from 11.4.4 to 11.4.5
by mdbml.myself854＠passmail.net 14 Apr '25

14 Apr '25

Good day When I start a 11.4.4 image and I watch USER_STATISTICS.BUSY_TIME refreshing it every second or so, it increases with about 0.0002 at a time (for a user that's not really doing anything). But when I do the same in a 11.4.5 image it goes up by about 200 at a time (while the user is still not doing anything). I think that's wrong because it's supposed to be "number of seconds of activity". I couldn't find anything about it in the release notes https://mariadb.com/kb/en/mariadb-11-4-5-release-notes/ . Any ideas? I also see this in production and it quite messes up our metrics 😅 Thanks you

3 3

How to add a second Maxscale node
by Richard Reina 14 Apr '25

14 Apr '25

I have configured a second Maxscale node and was hoping to use it as a backup in case my other Maxscale node should fail. My question is how do I deploy it? I take it starting it up and having two Maxscale nodes working at the same time would be a problem because the both might try to effectuate failover changes such as promotions simultaneously that could cause conflicts? I have written the perl code below that can randomly select a node, but it would need two working nodes to choose between. How does one deploy a second node without it conflicting with the first Maxscale node? my @msn = qw(192.168.0.24 192.168.0.23); # Maxscale Nodes my $msn = $msn[rand @msn]; my $msn = splice(@msn, rand @msn, 1); print "I picked: $msn\n"; print "Remaining Maxscale Nodes are: @msn\n"; $dbh = DBI->connect("DBI:mysql:database=$self->{_db_name};host=$msn;port=4006",$user, $pwd);

2 1

increasing InnoDB history length and stalls on MariaDB 11.4
by kazuhiko＠nexedi.com 10 Apr '25

10 Apr '25

Hello, Our application has a sustained rate of DML queries in parallel with DQL. We can achieve good performance using innodb-locks-unsafe-for-binlog on MariaDB 10.4, with careful consideration of its 'unsafeness'. But innodb-locks-unsafe-for-binlog is removed in MariaDB 10.5 or later and our application runs much slower without it because of gap locking. https://mariadb.com/docs/server/ref/mdb/cli/mariadbd/innodb-locks-unsafe-fo… To achieve a good performance as before, we now use READ COMMITTED transaction isolation for DML and use REPEATABLE READ isolation for DQL on MariaDB 11.4. This way guarantees the consistency for our application. Our database contains totally 13.7G rows and uses 4.5TB storage. Here is the configuration : innodb_buffer_pool_size = 17179869184 (16GB) innodb_buffer_pool_instances = 16 (no meaning in 11.4) innodb_log_file_size = 134217728 (128MB) innodb_flush_method = O_DIRECT Please see the attached graphs drawing 'InnoDB history length (right axis)' and 'free redo log in % (left axis)' (i.e. 100 - Redo Log Occupancy) while running our reference workload. https://mariadb.com/kb/en/innodb-redo-log/#determining-the-redo-log-occupan… These tests are done with the same workload, but only MariaDB version and/or configuration is different. (Please disregard the 'warehouse history length' and 'activities history length' series in those plots, they are not relevant to the current issue.) On MariaDB 10.4, the performance and the behaviour between 'using innodb-locks-unsafe-for-binlog' and 'using READ COMMITTED and REPEATABLE READ' are similar. But on MariaDB 11.4, I found two serious issues. Issue 1: InnoDB history length keeps growing while running lots of DML and DQL queries in parallel. InnoDB history length is stable around 5k on 10.4, but it keeps growing on 11.4 up to 100k until the end. In our experience, increases in history list length are roughly correlated with poor query performance (causing queries to "step over tombstones" ?), and we do our best to avoid having long-running transactions in order to allow older snapshots to be freed ASAP. We are satisfied with the status on 10.4, where history list is constant. What is the reason behind such increases on 11.4 and is there any configuration to avoid such ? Issue 2: MariaDB stalls when free redo log is too small. When MariaDB stalls, even a quite simple query takes too long. (table definition) CREATE TABLE `catalog` ( `uid` bigint(20) unsigned NOT NULL, `path` varchar(255) NOT NULL DEFAULT '', ... PRIMARY KEY (`uid`), KEY `Path` (`path`), ... ) (from pt-query-digest output) # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 91 # Exec time 2 716s 1s 17s 8s 15s 5s 7s # Lock time 0 22ms 156us 491us 238us 366us 70us 204us # Rows sent 0 663 3 10 7.29 8.91 1.09 6.98 # Rows examine 0 663 3 10 7.29 8.91 1.09 6.98 # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 52.10k 330 749 586.24 685.39 68.46 563.87 # Query size 0 470.83k 3.56k 5.26k 5.17k 5.20k 225.56 5.20k SELECT uid, path FROM catalog WHERE path in ('...', /*... omitted 80 items ...*/)\G (here, these SELECT are issued in READ COMMITTED isolation level) Our understanding is that the stalls are caused by the undo log cleanup job, and/or the innodb buffer pool writeback job. Increasing the undo log size a lot seems to reduce the frequency of those stalls. For our reference workload here, we still have stalls with 1GB log file but we don't have stalls with 2GB or more log file. If we need 'big enough' log file size to avoid stalls, will the ideal size be proportional to the amount of data or proportional to the buffer pool size ? We have never had such stall on MariaDB 10.4 even with small 128MB log file. How this difference happen and is there any configuration to improve the behaviour ? Thanks in advance ! Best regards, Kazuhiko SHIOZAKI

4 17

Ansible role to deploy madiadb gaelra cluster using containers
by Engelmann Florian 08 Apr '25

08 Apr '25

Hi, is there any production ready ansible role to deploy a mariadb galera cluster using docker container (not packages)? All the best Florian EveryWare AG Florian Engelmann Cloud Platform Architect Zurlindenstrasse 52a CH-8003 Zürich T +41 44 466 60 00 F +41 44 466 60 10 florian.engelmann(a)everyware.ch www.everyware.ch

2 1

Moving forward the pull request
by pslawek83 08 Apr '25

08 Apr '25

Hello Everyone, made some changes to my pull request. How to move it forward? Want to see if design is ok to make some final code changes. github.com https://github.com/MariaDB/server/pull/3891 Thanks

1 0