- discuss - lists.mariadb.org

10.11.8 writing all binary logs in row format (even though format is MIXED)
by mariadb＠Biblestuph.com 09 May '25

09 May '25

Doing a little fishing here ... Does someone know of a reason why a downstream replica would--seemingly spontaneously--start writing ALL of its binary log entries in ROW format, even though binlog_format is MIXED? This replica has been running for quite some time, though admittedly I had to restore it from a backup about 3 weeks ago as the result of a server crash. But it had been writing data fine over those three weeks (and prior); starting on March 2nd it started writing all of its entries in ROW format. I didn't notice until it filled the disk this morning. I can't think of anything that may have changed at that time. Normally a few entries will get written as ROW but most are definitely MIXED in normal operation. I stopped/restarted it this morning and it's writing in MIXED again, as expected. I also checked the upstream primary, it is _not_ writing its records as ROW. I.E., my replica is (apparently) not getting them that way from upstream. Both are 10.11.8. Any ideas? TIA, Dan

2 4

query optimization
by Jaco Kroon 06 May '25

06 May '25

Hi All, So I'm stuck with an application that does something like (can't trivially modify the code): Prepare SELECT * FROM view_name WHERE id = ? Execute SELECT * FROM view_name WHERE id = 'p1-p2' The problem is that id is a compound between two individual indexes from the underlying tables, eg: CREATE VIEW AS SELECT CONCAT(k1, '-', k2) id, other, fields FROM table1, table2; Yes, the full join is on purpose, table2 will only ever have a handful (three currently) rows in it. table1 however can get "big" (only a few hundred rows, and will likely mostly be in the buffer pool on all hosts, so most likely not the worst possible situation). Is there perhaps a mechanism to get mariadb to "break down" the searched for id value to enable it hitting the underlying indexes more properly? If not this is going to be a "let's see how far it stretches" kind of scenario for the time being, or possibly see if the mariadb query cache can help. In which case, is it possible to selectively enable the query cache on a per query basis, because as a rule we found that the query cache actually degraded performance (I believe due to lock contention). The only other option I can imagine is to create a separate table that regenerates whenever data in the source tables changes (by way of triggers). Kind regards, Jaco

3 5

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

06 May '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

6 17

Using mariabackup for partial backups
by Gingko 05 May '25

05 May '25

Hello, I have a problem when I want to use MariaBackup for getting a partial tables backup of a given database (actually willing to import them in another database right after in the same system after the copy, but this part doesn't seem to be relevant in this problem). For doing this, I proceed the following way: Suppose I have a database named “mydatabase” with some tables named “mytable1”, “mytable2”, “mytable3”, “mytable4”, etc., that I want to backup in a Linux folder like “/tmp/mydatabase/backup/”. But I only want to backup tables named “mytable3”, “mytable4” and “mytable5”. So I create a file named, for example, “/tmp/mydatabase-table-list.txt”, containing: mydatabase.mytable3 mydatabase.mytable4 mydatabase.mytable5 … and I issue the following command in a Linux bash shell, as told in the MariaBackup's documentation: mariabackup -u root --backup --target-dir=/tmp/mydatabase/backup/ --databases=mydatabase --tables-file=/tmp/mydatabase-table-list.txt At first, this seems to work, but actually, if I read the backup directory's contents after the backup, I see that _the whole database has been exported_, apparently _completely ignoring_ the “/tmp/mydatabase-table-list.txt” file's contents. But I can verify that this file is nevertheless read: If I put only this in the “/tmp/mydatabase-table-list.txt” file: mytable3 mytable4 mytable5 … I get an error message saying that the table names in the list must be qualified with database names (expected to be the same as in the --databases parameter, I suppose). Of course this doesn't work, but at least, this proves that the table list file is actually read. I also tried some other ways to specify the options, like removing the --databases=mydatabase parameter, but in this case I get no database contents, only the surrounding common files … … or writing the command like this, as the documentation seemed to allow it: mariabackup -u root --backup --target-dir=/tmp/mydatabase/backup/ --databases="mydatabase.mytable3 mydatabase.mytable4 mydatabase.mytable5" … with same result as above: no database contents. (by the way, my actual table names are not consistent between them like above, so I can't use any regex or wildcard parameter) My actual database is huge (more than 70 GB of files footprints), so it is really annoying to have to backup it as a whole. The database's size doesn't seem to be relevant: I also tested this with a smaller one with same results. What am I doing wrong in my “mariabackup” parameters? For information, the version that I use is “mariabackup based on MariaDB server 10.11.11-MariaDB debian-linux-gnu (x86_64)” (this is the result of the “mariabackup -v” command), and this is the one currently distributed as Debian packages with the “Debian GNU/Linux 12 (bookworm)” that I maintain in several servers and Linux boxes, matching the corresponding client and server versions installed on these systems. Gingko

1 0

MariaDB Bremen Meetup Tomorrow (Hybrid Event - Zoom Available)
by Anna Widenius 05 May '25

05 May '25

Dear MariaDB Community, Just a quick reminder that our next MariaDB Bremen Meetup is happening tomorrow, May 6! 📍 Location: Campus Ring 1, 28759 Bremen, Germany <https://maps.app.goo.gl/yTsBipcY5dyvLR3F6> (in person) 💻 Also available via Zoom (hybrid format) - https://constructor-university.zoom-x.de/j/68398052328 🕒 Time: 12:00 - 17:30 CEST 🔗 Event details and RSVP: https://www.meetup.com/mariadb-bremen-meetup-group/events/307250937/?eventO… Schedule: https://mariadb.org/events/code-vectors-and-vision/#schedule Whether you're joining us in person or online, we look forward to great discussios and networking around MariaDB and open source database technologies. Hope to see you there! best regards, Anna Widenius, CEO MariaDB Foundation |@mariadb_org | +358-45-2617358

1 0

Re: Fixing typos
by Lakhin Vasilii 29 Apr '25

29 Apr '25

Forgot to attach the patch I mentioned.

5 11

Which Mroonga version is being used?
by mariadb.robin842＠passfwd.com 29 Apr '25

29 Apr '25

Is there any way to check which Mroonga version is installed/being used? The GitHub repo shows commits from a few weeks ago in storage/mroonga, but running SELECT * FROM information_schema.PLUGINS; lists 7.7 as PLUGIN_VERSION, 1.15 as PLUGIN_LIBRARY_VERSION and 7.07 AS PLUGIN_AUTH_VERSION. So, which is it? Surely its not Mroonga 7.07 from 2017...

2 1

MariaDB 11.8 and Debian status - information to fellow Linux distro packagers/maintainers
by Otto Kekäläinen 28 Apr '25

28 Apr '25

Hi! Do we have any Linux distro packagers and maintainers on this mailing list? If you are working on importing MariaDB 11.8 into a Linux distro, you may want to take a peek at what was done in Debian, as it may save you some work in debugging build or test issues: * https://salsa.debian.org/mariadb-team/mariadb-server/-/commits/debian/latest * https://salsa.debian.org/mariadb-team/galera-4/-/commits/debian/latest You might also benefit from looking at work-in-progress and open Merge Requests at : * https://salsa.debian.org/mariadb-team/mariadb-server/-/merge_requests In particular the MR https://salsa.debian.org/mariadb-team/mariadb-server/-/merge_requests/115 about a NEWS item to Debian users is likely to have useful text/summary that others might want to re-use: * https://salsa.debian.org/otto/mariadb-server/-/blob/dev/NEWS-11.8.2/debian/… Overall Debian package statuses are best viewed at: * https://tracker.debian.org/pkg/mariadb * https://tracker.debian.org/pkg/galera-4 ..and respective in Ubuntu: * https://launchpad.net/ubuntu/+source/mariadb * https://launchpad.net/ubuntu/+source/galera-4 I've fixed a lot of bugs in past weeks. The Debian tracker MariaDB bugs are down from 60 to 44, and in Ubuntu down to 3. For Galera the numbers are now down to 1 and 0. Looking forward to collaborate with maintainers of MariaDB and Galera packages in other Linux distributions! - Otto

1 0

[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