May 2024 - 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

is blocked because of many connection
by Marc 31 Mar '25

31 Mar '25

I am constantly getting abuse from azure, amazon, digital ocean cloud. With previous mysql versions I never got this message. I assume you have set this to a default. I don't want to have this limit on ip but on ip+database, how can I change this?

6 40

External MyISAM partitions
by Jan Křístek 07 Aug '24

07 Aug '24

Hi, We have an old instance of MariaDB 10.3.9/10.3.39, with historical data. Every month I copy an old MyISAM partition (binary files) from the same version of the engine (but a different instance) to a big volume and replace an empty partition in the database with symlinks to the newly copied two files (MYD, MYI - when the engine is stopped). I have been doing it for a year or two, checking each time the data are accessible afterwards. This month it happened, that the data are not accessible anymore, and the database throwing an exception: *Got error 140: "Wrong create options" from storage engine MyISAM.* I tried doing it in a new instance, each time I replace a partition with a link to file elsewhere, the error appeared again. Does anyone have any ideas, why it's showing the error and how to avoid it? I cannot recall changing anything in the database configuration, upgrading any database, or doing any other changes. Thanks for any help you can provide, Jan

4 7

Aborted connection 0 to db: 'unconnected' user: 'unauthenticated' host: 'connecting host' (Too many connections)
by Rhys.Campbell＠swisscom.com 22 May '24

22 May '24

Hello All, We have a vendor app that attempts a large number of connections and creating the following error in the MariaDB logs (10.11) Aborted connection 0 to db: 'unconnected' user: 'unauthenticated' host: 'connecting host' (Too many connections) Could this error be cause by the vendor app reusing connections that have been idled out on the server side? Rhys

2 1

Deadlock Errors
by Scott Canaan 10 May '24

10 May '24

We have a 3-node Galera cluster running MariaDB 10.5.23. A couple months ago the cluster had an issue and one of my coworkers handled it. We have several cron jobs that run against the databases to gather statistics and other information. One of those jobs runs every 15 minutes. Since the last issue, one SQL in that script has been throwing a deadlock error on two of the three nodes in the cluster: ERROR 1213 (40001) at line 14: Deadlock found when trying to get lock; try restarting transaction I've been monitoring and I don't see the job running more than once at the same time on the same server. The SQL that it is failing on is: insert into rit_mdb_track_logins select report_date, user, count(*) logins from rit_mdb_track_login_detail group by report_date, user; I've run this SQL manually and it works fine. I'm not sure where to go from here. Scott Canaan '88 Sr Database Administrator Information & Technology Services Finance & Administration Rochester Institute of Technology o: (585) 475-7886 | f: (585) 475-7520 srcdco(a)rit.edu<mailto:srcdco@rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

3 5

TCMalloc with Maria and THPs
by Simon Avery 08 May '24

08 May '24

Hello I'm looking to test tcmalloc for use on our ~80 Maria 10.11.[6-7] servers, running either Rocky 8/9 or Debian 12. A few years ago we moved to using jemalloc which helped resolve or reduce many of the memory leaks on these servers from using the distro's default malloc. We still have a couple of machines where Maria eventually uses all the memory until oomkiller steps in - although we've largely mitigated that by adopting weekly restarts for them. Thanks to Sergei Golubchik for his most useful post here on the 5th on the subject of memory leaks, which has got us looking at tcmalloc and also raises the question of Transparent Huge Pages. Sergei cites several reliable sources recommending turning them off for MariaDb and similar databases. Now - my question; tcmalloc's documentation on tuning at https://google.github.io/tcmalloc/tuning.html says "TCMalloc heavily relies on Transparent Huge Pages (THP)." And explains it's built and tested with THPs enabled and seems to strongly recommend leaving them on. That contradicts the above articles, leaving me confused about which path is best practice. It seems that THP's should be on as advised by tcmalloc, but also off when running MariaDb. The true answer possibly depends on which has the highest priority in terms of database-specific performance and the ability to manage the memory tightly. What are others' views, please? I'm searching in particular for best practice and real world experiences with tcmalloc. Also - if it's unlikely to improve upon jemalloc. Simon Avery Linux Sysadmin: ATASS Sports Oxygen House | Grenadier Road, Exeter Business Park | EX1 3LH t: 01392 440 400 e: simon.avery(a)atass-sports.co.uk<mailto:simon.avery@atass-sports.co.uk> www.atass-sports.co.uk<http://www.atass-sports.co.uk/> Follow us on X: @atassSports<https://twitter.com/atassSports> NOTICE This email and any attachments confidential and intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. Please notify the sender immediately. ATASS Ltd is incorporated in England and Wales with company number 04807405. See our website for further details and our privacy policy

2 3

Detect INSERT vs REPLACE in BEFORE INSERT trigger
by Alex Hermann 06 May '24

06 May '24

Hi, Is there a (built-in) way to detect from within a BEFORE INSERT trigger whether it was triggered by an INSERT or REPLACE statement? I could surround every REPLACE statement with setting and clearing a variable. Problem is that this could easily be forgotten as there are multiple applications accessing the table. I'd rather not wrap access in a stored procedure or worse. What will not work is to do a SELECT (tailored to every UNIQUE key) to determine if a record will be replaced. I do not need to know if a record _will_ be replaced, just that the intention is that it _can_ be replaced. Background: the trigger does a validation that is not enforcable via a UNIQUE key. This precludes an INSERT ... ON DUPLICATE KEY UPDATE ... construction. When the validation finds a conflicting row, the query must fail when the _intent_ is to add a new record (INSERT), but succeed when the _intent_ is to replace (REPLACE) that row. -- Regards, Alex Hermann

3 2

Excessive memory usage 10.11.6
by Clint Todish 05 May '24

05 May '24

5 6

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