June 2025 - discuss - lists.mariadb.org

reoccuring DB corruption we don't understand
by sacawulu 15 Jul '25

15 Jul '25

Hi, We are seeing recurrent corruption on one of our DB nodes, running RHEL9, under vmware, MariaDB 10.11.10. > 2025-05-13 6:32:08 0 [ERROR] InnoDB: tried to purge non-delete-marked record in index `cardChipNumber` of table `mydb`.`idm_usc_kaart_status`: tuple: TUPLE (info_bits=0, 2 fields): {[17]36147687118764292(0x3336313437363837313138373634323932),[4] " (0x9722A2FB)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[17]36147687118764292(0x3336313437363837313138373634323932),[4] " (0x9722A2FB)} > 2025-05-13 6:32:08 0 [ERROR] InnoDB: Flagged corruption of `cardChipNumber` in table `mydb`.`idm_usc_kaart_status` in purge > 2025-05-13 10:39:20 442752 [ERROR] Got error 180 when reading table './mydb/idm_usc_kaart_status' > 2025-05-13 12:44:51 0 [ERROR] InnoDB: tried to purge non-delete-marked record in index `inschrijving_id` of table `mydb`.`tinschrijving`: tuple: TUPLE (info_bits=0, 2 fields): {[4] ](0x800D0C5D),[4] \Y[(0x805C595B)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[4] ](0x800D0C5D),[4] \Y[(0x805C595B)} > 2025-05-13 12:44:51 0 [ERROR] InnoDB: Flagged corruption of `inschrijving_id` in table `mydb`.`tinschrijving` in purge > 2025-05-13 12:45:59 453516 [ERROR] Got error 180 when reading table './mydb/tinschrijving' > 2025-05-13 12:46:03 453524 [ERROR] Got error 180 when reading table './mydb/tinschrijving' > 2025-05-13 12:46:21 453540 [ERROR] Got error 180 when reading table './mydb/tinschrijving' > 2025-05-13 12:46:26 453541 [ERROR] Got error 180 when reading table './mydb/tinschrijving' and last thursday suddenly again: > 2025-05-29 6:32:22 290727 [ERROR] Got error 180 when reading table './mydb/idm_usc' It starts without an obvious (to us) reason. We have verified that we are running (with what seems to be) safe settings, like: innodb_doublewrite = ON and innodb_flush_method = O_DIRECT | The system has enough ram, swap configured but none in use, no full disks/partitions. At a certain moment (after may 13) it became so bad, that I took a full mysqldump of all DB's: > mysqldump --all-databases --single-transaction --routines --triggers --events > /mnt/sdc/full_admin_backup.sql emptied the datadir, and imported it all fresh again: > mysql < /mnt/sdc/full_admin_backup.sql And to my surprise, last week thursday, it happened again. Filesystem is xfs, mounted like: > /dev/sdb on /data type xfs (rw,relatime,seclabel,attr2,inode64,logbufs=8,logbsize=32k,noquota) No fs corruption detected checked with > xfs_scrub /data and > xfs_spaceman -c 'health -c ' /data/ | grep -v ok We are unsure what causes this. It should not be possible (I guess?) for userspace / php scripts (this is LAMP setup) to cause issues like this. We are unsure how to further troubleshoot. Anyone with valuable suggestions or insights? Or more information required..? Thanks in advance! MJ

3 3

Adding an index to a large table
by edi mari 12 Jul '25

12 Jul '25

Hi, I have a 200GB table with a foreign key constraint, and I need to drop an existing index and add a non-unique index to it. What is the best approach to do this with minimal impact on users? Thanks Edi

5 11

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 11 Jul '25

11 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

2 1

ODBC driver and SQLDescribeParam()
by Volker Wysk 05 Jul '25

05 Jul '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

3 8

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

30 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

8 24

Slave SQL thread exiting, replication stopped
by Mário Cova 27 Jun '25

27 Jun '25

Hi all, After upgrading *MariaDB from 10.2.33 to 10.6.19*, the *SQL Slave Thread* stops roughly every 10 minutes. This only started occurring *after the upgrade*. If I perform a *switchover*, the issue starts happening on the new standby as well. We've mined the *binary log * and found no pattern in the statements where it stops. There are *no errors shown* in slave status. When we run START SLAVE;, everything resumes normally. Currently, you're using a *cron job* to restart the SQL thread regularly. - What could be causing this? - What deeper analysis can be done? 2025-06-25 8:59:25 3152832 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001765' at position 657303910, master: xxxx:3306 > 2025-06-25 9:10:23 3157451 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001765' at position 988146286, master: xxxx:3306 > 2025-06-25 9:20:29 3162364 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001766' at position 220961201, master: xxxx:3306 > 2025-06-25 9:30:44 3166935 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001766' at position 529729081, master: xxxx:3306 > 2025-06-25 9:41:26 3171556 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001766' at position 848593645, master: xxxx:3306 > 2025-06-25 10:02:46 3176393 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001767' at position 420380054, master: xxxx:3306 > 2025-06-25 10:13:56 3186023 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001767' at position 753631615, master: xxxx:3306 > 2025-06-25 10:24:32 3191029 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001767' at position 1072243011, master: xxxx:3306 > 2025-06-25 10:36:14 3195944 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001768' at position 345337292, master: xxxx:3306 > 2025-06-25 11:17:57 3201245 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001769' at position 481728557, master: xxxx:3306 > 2025-06-25 11:49:04 3219634 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001770' at position 277881467, master: xxxx:3306 > 2025-06-25 11:59:55 3233322 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001770' at position 562687566, master: xxxx:3306 > 2025-06-25 12:30:41 3237753 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001771' at position 278373954, master: xxxx:3306 > 2025-06-25 12:51:30 3250513 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001771' at position 796578679, master: xxxx:3306 > 2025-06-25 13:02:19 3259002 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001771' at position 1052723773, master: xxxx:3306 > 2025-06-25 13:55:51 3263370 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001773' at position 98331358, master: xxxx:3306 > 2025-06-25 14:39:13 3283845 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001773' at position 968704509, master: xxxx:3306 > 2025-06-25 14:49:38 3299620 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001774' at position 93970604, master: xxxx:3306 > 2025-06-25 15:01:35 3303181 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001774' at position 313622321, master: xxxx:3306 > 2025-06-25 15:33:48 3307316 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001774' at position 895518570, master: xxxx:3306 > 2025-06-25 16:26:06 3318376 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001775' at position 770087908, master: xxxx:3306 > 2025-06-25 17:08:48 3336364 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001776' at position 459626350, master: xxxx:3306 > 2025-06-25 17:29:58 3350747 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001776' at position 839271624, master: xxxx:3306 > 2025-06-25 18:02:42 3357922 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001777' at position 349932886, master: xxxx:3306 > 2025-06-25 18:23:19 3369124 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001777' at position 694190140, master: xxxx:3306 > 2025-06-25 18:54:53 3375957 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001778' at position 93981370, master: xxxx:3306 > 2025-06-25 19:15:51 3385736 [Note] Slave SQL thread exiting, replication > stopped in log 'binlog.001778' at position 366703217, master: xxxx:3306 Thanks in advance

3 4

Issue on upgrading Mariadb 11.4.5 to 11.4.7 on Ubuntu 24.04
by Derick Turner 17 Jun '25

17 Jun '25

Hi all, Patching one of our spare servers this morning and ran into a couple of issues. The first was a complaint in the service status: mariadb.service: Referenced but unset environment variable evaluates to an empty string: MYSQLD_OPTS, _WSREP_NEW_CLUSTER Which was fixed by running systemctl edit mariadb.service and adding Environment="MYSQLD_OPTS=" Environment="_WSREP_NEW_CLUSTER=" to the end of the file. The second was from the error log which stated that the service was unable to read /etc/mysql/rest/keyfile.passwd, which turned out to be a permissions error on /etc/mysql/rest which was 600 and not 700 (I checked the other servers and theirs were also 600 so I guess it's the fix for MDEV-36229 which caused that) Now the server starts but it core dumps almost immediately. Systemctl status shows: ● mariadb.service - MariaDB 11.4.7 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf, override.conf Active: activating (auto-restart) (Result: core-dump) since Sat 2025-06-07 12:45:30 UTC; 1s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 26495 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 26497 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 26500 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 26684 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=dumped, signal=SEGV) Main PID: 26684 (code=dumped, signal=SEGV) CPU: 1.136s and error log shows 2025-06-07 12:33:57 0 [Note] Starting MariaDB 11.4.7-MariaDB-ubu2404-log source revision 118cfcf82107188f2295631193658b2ef94f4f3f server_uid 7bAWQJ3wkO2Wd/rTUTlrW6jyD3E= as process 4209 2025-06-07 12:33:57 0 [Note] InnoDB: Compressed tables use zlib 1.3 2025-06-07 12:33:57 0 [Note] InnoDB: Number of transaction pools: 1 2025-06-07 12:33:57 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions 2025-06-07 12:33:57 0 [Note] InnoDB: Using liburing 2025-06-07 12:33:57 0 [Note] InnoDB: innodb_buffer_pool_size_max=22528m, innodb_buffer_pool_size=22528m 2025-06-07 12:33:58 0 [Note] InnoDB: Initialized memory pressure event listener 2025-06-07 12:33:58 0 [Note] InnoDB: Completed initialization of buffer pool 2025-06-07 12:33:58 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes) 2025-06-07 12:33:58 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer 2025-06-07 12:33:58 0 [Note] InnoDB: End of log at LSN=43439159458895 2025-06-07 12:33:58 0 [Note] InnoDB: Opened 3 undo tablespaces 2025-06-07 12:33:58 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active. 2025-06-07 12:33:58 0 [Warning] InnoDB: Cannot free the unused segments in system tablespace because a previous shutdown was not with innodb_fast_shutdown=0 or XA PREPARE transactions exist 2025-06-07 12:33:58 0 [Note] InnoDB: System tablespace defragmentation process starts 2025-06-07 12:33:58 0 [Note] InnoDB: Moving the data from extents 15552 through 2452672 250607 12:33:58 [ERROR] /usr/sbin/mariadbd got signal 11 ; Sorry, we probably made a mistake, and this is a bug. Your assistance in bug reporting will enable us to fix this for the next release. To report this bug, see https://mariadb.com/kb/en/reporting-bugs about how to report a bug on https://jira.mariadb.org/. Please include the information from the server start above, to the end of the information below. Server version: 11.4.7-MariaDB-ubu2404-log source revision: 118cfcf82107188f2295631193658b2ef94f4f3f The information page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains instructions to obtain a better version of the backtrace below. Following these instructions will help MariaDB developers provide a fix quicker. Attempting backtrace. Include this in the bug report. (note: Retrieving this information may fail) Thread pointer: 0x0 stack_bottom = 0x0 thread_stack 0x30000 /usr/sbin/mariadbd(my_print_stacktrace+0x30)[0x5a41189db560] /usr/sbin/mariadbd(handle_fatal_signal+0x2a1)[0x5a4118574ae1] /lib/x86_64-linux-gnu/libc.so.6(+0x45330)[0x7650bf845330] /usr/sbin/mariadbd(+0x636615)[0x5a41180bb615] /usr/sbin/mariadbd(+0x1043036)[0x5a4118ac8036] /usr/sbin/mariadbd(+0xec468d)[0x5a411894968d] /usr/sbin/mariadbd(+0x622c5a)[0x5a41180a7c5a] /usr/sbin/mariadbd(+0xd9b926)[0x5a4118820926] /usr/sbin/mariadbd(_Z24ha_initialize_handlertonPv+0x8c)[0x5a411857717c] /usr/sbin/mariadbd(+0x8165b3)[0x5a411829b5b3] /usr/sbin/mariadbd(+0x8167a7)[0x5a411829b7a7] /usr/sbin/mariadbd(_Z11plugin_initPiPPci+0xbfc)[0x5a41182a1e3c] /usr/sbin/mariadbd(+0x6bbd65)[0x5a4118140d65] /usr/sbin/mariadbd(_Z11mysqld_mainiPPc+0xf79)[0x5a4118144f79] /lib/x86_64-linux-gnu/libc.so.6(+0x2a1ca)[0x7650bf82a1ca] /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x8b)[0x7650bf82a28b] /usr/sbin/mariadbd(_start+0x25)[0x5a411811e865] Writing a core file... Working directory at /var/lib/mysql Resource Limits (excludes unlimited resources): Limit Soft Limit Hard Limit Units Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max processes 139959 139959 processes Max open files 1048576 1048576 files Max locked memory 524288 524288 bytes Max pending signals 139959 139959 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Core pattern: |/usr/share/apport/apport -p%p -s%s -c%c -d%d -P%P -u%u -g%g -F%F -- %E Kernel version: Linux version 6.8.0-60-generic (buildd@lcy02-amd64-054) (x86_64-linux-gnu-gcc-13 (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, GNU ld (GNU Binutils for Ubuntu) 2.42) #63-Ubuntu SMP PREEMPT_DYNAMIC Tue Apr 15 19:04:15 UTC 2025 Is there anything I'm missing before I raise this as a bug? Kind regards Derick -- Derick Turner - He/Him Managing Director e-Learn Design Ltd | Scotland's Moodle Partner T +44 (0)845 474 4512 | M +44 (0)7808 068 797 |www.e-learndesign.co.uk

4 6

MariaDB on StackExchange
by Otto Kekäläinen 06 Jun '25

06 Jun '25

Hi, This was news to me, so I thought I'd share that there are quite a few posts on tag 'mariadb' at https://dba.stackexchange.com/questions/tagged/mariadb People on this discussion list might want to participate in similar discussions at DBA StackExchange too. - Otto PS. If you like MariaDB, show your support and help it get to 10k stars at https://github.com/MariaDB/Server

2 1

Re: haproxy mysql-check user not working
by vbvbrj 06 Jun '25

06 Jun '25

On 06.06.2025 11:19, Markus Mäkelä wrote: > > Hi, > > If you're interested, you could try out MariaDB MaxScale that's > designed for proxying database connections. The 21.06 version is > available under GPLv2 if you want to try it with no strings attached: > https://mariadb.com/kb/en/mariadb-maxscale-21-06/ > > Markus > As I understand this is an alternative to ProxySql. The good thing this is from same developers and tables for users are from mariadb server itself. ProxySql hase separate authentication table on its one besides autentication table in mariadb. But really is a good balancing alternative.

1 0

haproxy mysql-check user not working
by vbvbrj 06 Jun '25

06 Jun '25

Hello. mariadb 11.8.1 I want to use haproxy with check option. Using in haproxy `option mysql-check` works but maridb logs: [Warning] Aborted connection 24971 to db: 'unconnected' user: 'unauthenticated' host: '10.0.1.165' (This connection closed normally without authentication) This is confusing as I don't know that this is regular check from haproxy, or someone tried to connect. So I want to use in haproxy `option mysql-check user haproxy-check post-41`. But this fails with error: [Warning] Access denied for user 'haproxy-check'@'10.0.1.164' (using password: NO) and haproxy shows the mariadb server as DOWN. Because in haproxy there is no way to specify a password for the user to connect to the instance of mariadb, I tried to allow passwordless access for the haproxy-check user from the specified network and minimum grants. But this does not help to allow access without password. Tried also to allow access for this user from any host and givin full access and also authentication fails. Is there an option in mariadb that does not allow paswordless authentication?

2 2