May 2025 - discuss - lists.mariadb.org

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

09 Jun '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

7 21

After upgrade from 10.11.11 to 10.11.13, Mariadb will not start with the existing SSL certs.
by Simon Avery 27 May '25

27 May '25

Hello, (Not reporting as a bug immediately; looking for confirmation and advice before doing so as it feels like this would be more widely reported if others are affected.) Several of our Mariadb servers failed to return after regular patching this morning after Mariadb upgraded from 10.11.11 to 10.11.13. Looks like, after being happy with the SSL keys for over six months, Mariadb suddenly doesn't like them. This looks like something has changed in Mariadb in this version that is stopping it reading our SSL keys, but I cannot see anything in the release notes relating to changes to SSL for .12 or .13 https://mariadb.com/kb/en/mariadb-10-11-13-changelog/ Workings: Patching immediately before updated from 10.11.11to 10.11.13. No other updates applied apart from zabbix-agent). VMs are running Rocky 9. Upgrading: MariaDB-client x86_64 10.11.13-1.el9 mariadb_10.11_r9 9.3 M MariaDB-common x86_64 10.11.13-1.el9 mariadb_10.11_r9 88 k MariaDB-server x86_64 10.11.13-1.el9 mariadb_10.11_r9 18 M MariaDB-shared x86_64 10.11.13-1.el9 mariadb_10.11_r9 131 k Before patching, mariadb was using one-sided SSL with self-signed SSL certs. Everything working as expected, been in place for over six months. After Maria upgraded from 10.11.11 to .13, Mariadb will not start. 250526 6:38:03 server_audit: logging started to the syslog. SSL error: Unable to get private key from '/etc/my.cnf.d/ssl/server-key.pem' 2025-05-26 6:38:03 0 [ERROR] Failed to setup SSL 2025-05-26 6:38:03 0 [ERROR] SSL error: Unable to get private key 2025-05-26 6:38:03 0 [ERROR] Aborting server-key.pem looks okay, starts "-----BEGIN RSA PRIVATE KEY-----" - privs are 700, owned by mysql user, turning off selinux doesn't fix it. File date six months old (it's a year long key with checks to renew at -30d), and openssl likes it; # openssl rsa -in server-key.pem -check RSA key ok writing RSA key -----BEGIN RSA PRIVATE KEY----- My temporary fix is removing this from the config and then mariadb will restart. Fortunately are clients don't require SSL. [mysqld] #ssl-ca=/etc/my.cnf.d/ssl/ca-cert.pem #ssl-cert=/etc/my.cnf.d/ssl/server-cert.pem #ssl-key=/etc/my.cnf.d/ssl/server-key.pem Thank you

2 3

ODBC driver and SQLDescribeParam()
by Volker Wysk 26 May '25

26 May '25

Hi! I'm working on a high-level database access library, using UnixODBC, MariaDB and the MariaDB-ODBC-driver. I's for the Mercury programming language. The MariaDB ODBC 3.1.4 Driver doesn't work when using SQLDescribeParam(). This ODBC function is used to query metadata for the parameters of a prepared SQL statement (one which includes question marks). Such as the type of the parameter. The driver returns SQL_VARCHAR for all parameters, regardless of the actual type of the parameter. I've installed and tried the newest MariaDB ODBC 3.2.5 Driver, and there's no difference. It also reports SQL_VARCHAR for all parameters. But I'm not 100% sure I've installed the newer driver correctly. What is the state of affairs about MariaDB and ODBC parameter metadata? Thanks, Volker

2 2

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

21 May '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.

5 4

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

14 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 7

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

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