- discuss - lists.mariadb.org

Re: Detect INSERT vs REPLACE in BEFORE INSERT trigger
by Jaco Kroon 03 May '24

03 May '24

Hi Alex, >> May I assume that it's also not quite as trivial as doing something >> like this: >> >> CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$') > Correct. It does its own conflict detection as UNIQUE keys can't > represent it. I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ... timing could then possibly again be an issue depending on whether the function will still see the old data or not (https://mariadb.com/kb/en/constraint/#check-constraints has some info, but testing will be required). "One can use most deterministic functions in a constraint, including UDFs <https://mariadb.com/kb/en/user-defined-functions/>." Kind regards, Jaco

2 3

SOUNDEX function
by lucyfrost＠tutanota.com 01 May '24

01 May '24

I'm developing a project which uses soundex matching (amongside other options more sophisticated than that). This is custom software with a mariad back end. MariaDB soundex built-in produces codes of apparently arbitary length; the usual is 4 characters. I've come to the conclusion that the best way forward is to mimic Maria's soundex in the code. I cannt find a desciption of the algorith Maria uses though: the only source code I can find is in mf_soundex.c, and that is explicitly limited wo 4 characters. But I have installed triggers which generate codes such as A4152636125262 Where is the soundex function found? Anyone know?

2 1

testing req/sec
by Marc 26 Apr '24

26 Apr '24

I would like to test how many requests/sec mariadb can process to authenticate users. So I need to be able to test how many cycles I can do of connecting to mariadb, executing the password sql statement and closing the connection. Anyone know what tool I can use for this?

2 3

mysqlcheck/mariadbcheck: consistency check before mysqldump
by Felix Natter 21 Apr '24

21 Apr '24

Dear mariadb experts, I would like to do this to verify my dbs (to detect hardware errors so that I can replay a backup timely): mysqlcheck -u root -pX --check --all-databases --extended Is it definitely read-only? Can I rely on the return code (0=success)? Or do I not need this because "mysqldump" does consistency checks? Many Thanks and Best Regards! Felix -- Felix Natter

2 2

Transaction Log Flushing got worse again in 10.5.23 -> 10.5.24
by Gordan Bobic 16 Apr '24

16 Apr '24

I've mentioned this here a few times before, but it looks like the latest 10.5.x update to 10.5.24 actually made things worse than in 10.5.23. I have attached two screenshots of transaction log checkpoint age over a 5 minute oltp_write_only sysbench test with 10.5.23 and 10.5.24. 10.5.23 starts off with the flushing all or nothing behaviour but it dampens out after the first minute or so. 10.5.24 exhibits it permanently. I ran multiple separate tests and the behaviour is very consistent for each version. 10.5.23 almost (not quite but almost) seems to have had things fixed to a state where things were in 10.5.8. It's pretty bad that whatever causes this undesirable flushing behaviour seems to have been merged as of 10.5.9 (10.5.8 seems to be the newest version that doesn't exhibit it). It's much more concerning that nobody seems to care that the last good major version (10.4) is going EOL soon with apparently no newer long-term tested version that is not broken. I also tested the latest 10.6.17 and that exhibits the same broken behaviour. Is a fix for this behaviour expected any time soon? On high throughput production systems that can have prolonged bursts of large writes that can hit at any time - if they hit at the top of the checkpoint age that the database made no effort to flush, there is no space left in the transaction log to absorb that impact and it leads to stalls. If combined with disk I/O exhaustion, these stalls can be so bad that for a period of tens of seconds or even minutes even the database telemetry gathering starts to fails. I also attached a screenshot with a 10 minute period where 10.5.8 and 10.5.9 were sysbenched back-to-back for 5 minutes each, with an update/restart in the middle. How extensively is the commit that introduced this behaviour built upon in subsequent changes? How much of a rabbit hole am I heading down if I try to cherry-unpick it? The config I tested with is as follows: ``` [mysqld] innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_size = 1G innodb_fast_shutdown = 0 # in case a upgrade introduces redo log format change innodb_io_capacity = 2000 innodb_io_capacity_max = 10000 innodb_log_file_size = 256M innodb_log_write_ahead_size=16K innodb_monitor_enable = all innodb_use_native_aio = 0 # native aio is slow on ZFS innodb_use_atomic_writes = 0 # native aio is slow on ZFS ```

3 5

Windows gssapi plug-in with multiple SIDs/Groups
by darrel.fenstad＠gmail.com 15 Apr '24

15 Apr '24

I am using MariaDB 10.117 and gssapi plug-in to allow SID:BA as follows: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA';" This works fine. But i want to add an additional GROUP besides SID:BA. I have tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA' OR gssapi USING 'GROUP:My Group';" However, when I look at mysql - View - user it shows the authentication_string as only GROUP:My Group. I also tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA', 'GROUP:My Group'; But that showed authentication_string in mysql - View - user as only SID:BA. Is it possible to specify multiple SIDs and GROUPs and, if so, what is the syntax to do so? Regards, Darrel

2 1

Backup MariaDB Databases for Suprema BioStar 2 Door Access System
by Turritopsis Dohrnii Teo En Ming 15 Apr '24

15 Apr '24

Subject: Backup MariaDB Databases for Suprema BioStar 2 Door Access System Good day from Singapore, On 7 March 2024 Thursday, when I was installing new self-signed SSL certificate for the door access system for a law firm in Singapore, I notice that Suprema BioStar 2 also uses the open source MariaDB database server. If I did not remember wrongly, there are 3 databases. However, the entire platform is a Windows environment, not Linux. How can I backup all the databases in the MariaDB database server? Thank you. Regards, Mr. Turritopsis Dohrnii Teo En Ming Targeted Individual in Singapore Blogs: https://tdtemcerts.blogspot.com https://tdtemcerts.wordpress.com GIMP also stands for Government-Induced Medical Problems.

2 2

Why MariaDB 11.4.1 master-slave replication not sync old data
by i18n.site＠gmail.com 07 Apr '24

07 Apr '24

I have a question about an issue I'm facing with MariaDB 11.4.1 (MariaDB 11 should have GTID enabled by default). I am trying to set up a highly available MariaDB (1 master with 2 slaves) using orchestrator. After shutting down the master database, orchestrator automatically switches one of the slave databases (u3) to become the new master (all slaves have log_slave_updates enabled). At this point, I manually create a brand new, empty slave database u2 (with no data) to synchronize with this new master. The Slave_IO_Running and Slave_SQL_Running status on u2 are both Yes. I attempt to create a new database on the master, and it appears on u2 as well. However, the problem is that the old databases present on u3 are not showing up on u2. ( see this image https://p.3ti.site/1712458958.webp ) I have searched online for solutions and it seems like I would need to manually back up the old databases, import them, and then modify the binlog files and synchronization positions. This feels cumbersome and is not convenient for automated recovery. I am wondering if there might be a misconfiguration on my part that is causing this issue.

2 1

Why is configuring init-file causing the error COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary'
by i18n.site 07 Apr '24

07 Apr '24

Why is configuring init-file causing the error COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary' I use default character-set-server=binary ❯ rg binary conf.d/mariadb.cnf 7:character-set-server=binary 8:collation-server=binary ❯ /usr/local/mysql/bin/mariadbd --init-file=/etc/mysql/init.sql --defaults-file=/etc/mysql/mariadb.cnf 2024-04-07 12:07:40 0 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary' 2024-04-07 12:07:40 0 [ERROR] Aborting ❯ cat /etc/mysql/init.sql STOP SLAVE;CHANGE MASTER TO master_use_gtid=slave_pos, MASTER_USER='sync',MASTER_HOST='xx',MASTER_PORT=2002,MASTER_PASSWORD='xx',MASTER_CONNECT_RETRY=1; START SLAVE; /etc/mysql

1 0

LOCK TABLES FOR WRITE with mysqldump imports
by stephan.vos＠metacom.net 05 Apr '24

05 Apr '24

I noticed that my data archiving process has been adding LOCK TABLES (table) FOR WRITE to the mysqldump file. I am now worried that if I ever need to import this data into the 3 node Galera cluster it will cause deadlocking as described in the MariaDB known issues article. Does MariaDB with Galera still lock up when this statement is executed or would it just ignore the LOCK TABLES command?

1 0