- discuss - lists.mariadb.org

MyISAM packed tables is uncompressed when index is added.
by Vassilis Virvilis 02 Jul '24

02 Jul '24

Hi, The question: Is uncompressing a myisam table when adding an index the expected behavior? Here is the story: I have a very big table so I thought to compress it. I used myisampack which worked great. The table.MYD shrank in size. myisamchk -ddv shows the table as compressed. I noticed the documentation that urges to run: myisamchk -rq after myisampack and I thought: Maybe I shouldn't create any indices on the uncompressed table since myisamchk will redo the work. But if I try to add an index to the compressed table suddenly it gets expanded to its original size and myisamchk -ddv does report the table a uncompressed. So the only solution is to: 1. add the indices in the uncompressed table 2. compress the table 3. run myisamchk -rq [--sort-index --analyze --parallel-recover --tmpdir=/somewhere/with/a/lot/of/space] 4. mysqladmin flush-tables; But this duplicates the index creations step. So is this the expected behavior? * If yes: why? Expanding the table behind the user's back is surprising... * If no: Should I report it as a bug? Here is how to replicate: #DROP TABLE IF EXISTS txxx; CREATE TABLE txxx (id INT, value TEXT); INSERT INTO txxx SELECT 1, REPEAT('A', 65536); *# Let's go to shell *ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql 1024 Jun 30 19:40 txxx.MYI -rw-rw---- 1 mysql mysql *65548* Jun 30 19:40 txxx.MYD <-- 64K cool *# cool! let's compress *myisampack -f txxx Compressing txxx.MYD: (1 records) - Calculating statistics - Compressing file 87.42% ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql *8255* Jun 30 19:40 txxx.MYD <-- 8K cool -rw-rw---- 1 mysql mysql 1024 Jun 30 19:41 txxx.MYI # great it is compressed *# because documentation says so: *myisamchk -qr txxx - check record delete-chain - recovering (with keycache) MyISAM-table 'txxx' Data records: 1 ls -ltr txxx.* -rw-rw---- 1 mysql mysql 466 Jun 30 19:34 txxx.frm -rw-rw---- 1 mysql mysql *8255* Jun 30 19:40 txxx.MYD <-- Still compressed -rw-rw---- 1 mysql mysql 1024 Jun 30 19:43 txxx.MYI myisamchk -ddv txxx MyISAM file: txxx Record format: *Compressed* <-------------------------------------!!!! OK Character set: utf8mb3_general_ci (33) File-version: 1 Creation time: 2024-06-30 19:40:36 Recover time: 2024-06-30 19:43:52 Status: checked Checksum: 2613455662 Data records: 1 Deleted blocks: 0 Datafile parts: 1 Deleted data: 0 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 3 Datafile length: 8248 Keyfile length: 1024 Max datafile length: 281474976710654 Max keyfile length: 17179868159 Recordlength: 15 table description: Key Start Len Index Type Rec/key Root Blocksize ro *# Now Let's add the index* ALTER TABLE txxx ADD INDEX(id); *# Shell again *ls -ltr txxx.* -rw-rw---- 1 mysql mysql 958 Jun 30 19:47 txxx.frm -rw-rw---- 1 mysql mysql *65548* Jun 30 19:47 txxx.MYD <--- *Oups - now it is uncompressed* -rw-rw---- 1 mysql mysql 2048 Jun 30 19:47 txxx.MYI myisamchk -ddv txxx MyISAM file: txxx Record format: *Packed* <----------------------------- *Why*? Character set: utf8mb3_general_ci (33) File-version: 1 Creation time: 2024-06-30 19:47:30 Status: changed Data records: 1 Deleted blocks: 0 Datafile parts: 1 Deleted data: 0 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6 Datafile length: 65548 Keyfile length: 2048 Max datafile length: 281474976710654 Max keyfile length: 288230376151710719 Recordlength: 16 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 multip. long NULL 0 1024 1024

2 2

Can Mariabackup import from a Percona Xtrabackup backup?
by Bryan K. Walton 18 Jun '24

18 Jun '24

Hi, We have a Maria DB 5.5 replica set running on Centos 7. With this OS version about to become end of life, we are planning an upgrade of our replica set to RHEL 9 and Maria DB 10.5. My general plan is to add a new replica secondary to the mix, running Maria DB 10.5 and then promote that server to be primary and replace the other replica members with a new secondary that is also running Maria DB 10.5. My question is, since Mariabackup didn't exist in Maria DB 5.5, is it possible to backup our existing Maria databases with Percona Xtrabackup and then have Mariabackup (from Maria DB 10.5) import that backup on the new server? Thanks, Bryan

2 2

No repo access since two days
by mail＠alexander-palm.de 09 Jun '24

09 Jun '24

Hello there! Sorry, I have no idea where to post this, I hope it's the right place. Since two days I get 403 forbidden errors by apt update at all URLs from mariadb.com I try to get out the reason but I can't. What I have done: - check the trusted.d keys and reinstall it - check if the VHost provider is the problem. So I install a clean new VM at Strato with Ubuntu 22.04 and 24.04 and get the same error on both by using the 'mariadb_repo_setup' script. - install a clean VM at Hetzner whit ubuntu 24.04 and get the same problem by using the 'mariadb_repo_setup' script. I have no Idee what's happend. If I use the URL via curl there is no error. But I can see that the Request to https://dlm.mariadb.com/repo/mariadb-server/11.4/repo/ubuntu/dists/jammy/In… was redirectet to some cloudflare services with some tokens and timestamps and so on perhaps apt cannot handle that? 3 days before there was no issue with this configuration. Here is a output-example: bash mariadb_repo_setup # [info] Checking for script prerequisites. # [info] MariaDB Server version 11.4 is valid # [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list # [info] Adding trusted package signing keys... # [info] Running apt-get update... E: The repository 'http://downloads.mariadb.com/Tools/ubuntu jammy InRelease' is not signed. E: Failed to fetch http://downloads.mariadb.com/Tools/ubuntu/dists/jammy/InRelease 403 Forbidden [IP: 2606:4700::6812:8718 80] E: Failed to fetch https://dlm.mariadb.com/repo/mariadb-server/11.4/repo/ubuntu/dists/jammy/In… 403 Forbidden [IP: 2606:4700::6811:bf0e 443] E: The repository 'https://dlm.mariadb.com/repo/mariadb-server/11.4/repo/ubuntu jammy InRelease' is not signed. E: Failed to fetch https://dlm.mariadb.com/repo/maxscale/latest/apt/dists/jammy/InRelease 403 Forbidden [IP: 2606:4700::6811:bf0e 443] E: The repository 'https://dlm.mariadb.com/repo/maxscale/latest/apt jammy InRelease' is not signed. # [error] Failed to add trusted package signing keys Anyone an idea? Thanks, Alex

1 1

Deleted database still present on replica
by Dave Hall 06 Jun '24

06 Jun '24

Hello, If this is not the right place for this question, please feel free to redirect... I am running MariaDB version 10.5.23 in a master/replica configuration with 4 replicas. I recently experienced a failure that required me to restore the DBs on all of my DB systems from backup, including all of my replicas. Somehow in the course of this process I have some databases on my some of my replicas that have been deleted on the master. What is the best way to remove these without confusing or breaking the replication process? Thanks. -Dave -- Dave Hall Binghamton University kdhall(a)binghamton.edu

2 1

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 07 May '24

07 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