- discuss - lists.mariadb.org

Poor fulltext index performance in conjunction with order by.
by gava100＠gmail.com 26 Jul '24

26 Jul '24

Hello All, I'm looking for a way to improve a query using fulltext index and so far, couldn't find any good solution. I have an alert table (described below) with around 6 million records and a full-text index on these two columns (ALERT_COMMENT, ALERT_SOURCE). I'd like to query the latest 30 received alerts based on a string content. Currently, I am proceeding with the following query: SELECT * FROM Alert WHERE MATCH(ALERT_COMMENT, ALERT_SOURCE) AGAINST ('string content' IN BOOLEAN MODE) > 0.0 ORDER BY ALERT_ID DESC LIMIT 30; However, this query is taking minutes to retrieve the data, likely due to poor performance of the full-text index in conjunction with the ORDER BY statement. When I remove the 'ORDER BY' statement, the result is returned in seconds. Do you have any suggestions for improving the retrieval time for this query? Below is the table structure: CREATE TABLE `Alert` ( `ALERT_ID` int(11) NOT NULL AUTO_INCREMENT, `SITE_ID` varchar(50) NOT NULL, `ALERT_LEVEL` int(4) NOT NULL, `RECEIVED_TIME` datetime NOT NULL, `OCCURRED_TIME` datetime NOT NULL, `ALERT_SOURCE` varchar(50) NOT NULL, `ALERT_COMMENT` varchar(1024) NOT NULL, PRIMARY KEY (`ALERT_ID`), FULLTEXT KEY `IX_ALERT_COMMENT_SOURCE_CODE` (`ALERT_COMMENT`, `ALERT_SOURCE`) ); Thanks in Advance Gava

1 0

mariadb connector/c client not using SSL
by Chris Ross (cross2) 25 Jul '24

25 Jul '24

Hey there. I am trying to get a local application which uses the mariadb client library to connect to a server with SSL. On my client system, I have added “ssl-ca” and “ssl-verify-server-cert” to the [client] section of my my.cnf. I can see these in effect with both “mariadb --help” and with “my_print_defaults client”. And, using mariadb client itself with all of the right connection parameters (host/port/username/port/schema) it connects just fine. However, a program of mine which uses mysql_real_connect() fails, reporting “Access denied for user ‘foo’@’ip’ (using password: YES)”. This is, I assume, because the user on the database side requires ssl, and my client is not using ssl. I’ve added: (void)mysql_optionsv(conn, MYSQL_READ_DEFAULT_FILE, NULL); Into my sources before calling mysql_real_connect(). I thought that would make it pull the params from the config file, and all should be good. But, that’s clearly not enough. Can someone help me figure out what I’m missing? Thank you. * Chris

3 7

is blocked because of many connection
by Marc 24 Jul '24

24 Jul '24

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 35

Cannot Start MariaDB
by gw1500 22 Jul '24

22 Jul '24

I've just installed maria on ubuntu but cannot start mariadb: *× mariadb.service - MariaDB 10.6.18 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Sun 2024-07-21 16:12:47 UTC; 54s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 3483 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 3484 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 3486 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set> Process: 3515 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE) Main PID: 3515 (code=exited, status=1/FAILURE) Status: "MariaDB server is down" CPU: 114ms Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [Note] InnoDB: Starting shutdown... Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [Note] Plugin 'FEEDBACK' is disabled. Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [ERROR] Could not open mysql.plugin table: "Unknown storage engine 'Aria'". Some plugins may be not loaded Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [ERROR] Failed to initialize plugins. Jul 21 16:12:47 dap002 mariadbd[3515]: 2024-07-21 16:12:47 0 [ERROR] Aborting Jul 21 16:12:47 dap002 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Jul 21 16:12:47 dap002 systemd[1]: mariadb.service: Failed with result 'exit-code'. Jul 21 16:12:47 dap002 systemd[1]: Failed to start MariaDB 10.6.18 database server. *I searched for this error (Unknown storage engine Aria) but the solution is to remove the log from /var/log/mysql. However, that directory is empty. If ubuntu puts that log somewhere else I cannot find it. Can someone point me in the right direction for resolving this? TIA.

3 5

MaxScale BSL licensing
by stephan.vos＠metacom.net 12 Jul '24

12 Jul '24

Hi, I need some clarification with regards to the BSL license for MaxScale: Licensed Work: MariaDB MaxScale (TM) version 22.08 The Licensed Work is (c) 2022 MariaDB Corporation Ab Additional Use Grant: You may use the Licensed Work when your application uses the Licensed Work with a total of less than three server instances in production. The the server instances above refer to the servers on which MaxScale is installed or does it refer to the number of database servers that each MaxScale instance monitors. For example can I have MaxScale installed on 5 application servers which then monitors two or less database servers without a license?

3 4

Upgrade from MariaDB 10.6 to 11.4
by stephan.vos＠metacom.net 05 Jul '24

05 Jul '24

I only see details on the website on how to upgrade from 11.6 to 10.11. https://mariadb.com/kb/en/upgrading-from-mariadb-10-6-to-mariadb-10-11/ Does this mean in order to get to 11.4 safely I first have to upgrade to 10.11 and then to 11.4? Has anyone managed to upgrade directly from 10.6 to 11.4 without issues?

3 4

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