- discuss - lists.mariadb.org

Fulltext index tokenization rules / DOC_ID
by wknauf＠hg-online.de 14 Sep '24

14 Sep '24

Hi all, first question: is there a document that describes the rules how MariaDB builds the fulltext index words? I set "innodb_ft_min_token_size=1" to include also one letter words in the index. But this does not seem to work for words like "D'Artagnan": the "d" letter is not found in "information_schema.INNODB_FT_INDEX_TABLE". It works for "D Artagnan". So I assume that single letters that are followed by a quote are ignored when building the index? You could also post the link to the source code file, but I don't know whether my C knowledge is sufficient ;-) Second question: the doc (https://mariadb.com/kb/en/information-schema-innodb_ft_index_table-table/) states that "information_schema.INNODB_FT_INDEX_TABLE.doc_id" matches to the id column of the original table: "DOC_ID: Document ID of the newly added row, either an appropriate ID column or an internal InnoDB value." But this did not happen for me - the doc_id values do not match my ID column. I tried the datatypes "int" and "bigint". Best regards Wolfgang

4 4

MariaDB ServerFest in Berlin on September 17
by Kristian Nielsen 26 Aug '24

26 Aug '24

The next MariaDB ServerFest will take place in Berlin, Germany. The event is on September 17, 2024, and is generously hosted by MariaDB Foundation sponsor Ionos: https://mariadb.org/fest-2024-berlin/ The event is open to all. I was at the MariaDB ServerFest in Helsinki last Autumn, and I really liked the close interaction with the many interesting participants: big and advanced users, developers, university students, service providers, etc. It is an excellent opportunity to meet many of the core developers, and hopefully a lot of other great people! I will be presenting at the event myself; my talk is titled: "MariaDB Replication – Fantastically Flexible and Fragile". I very much welcome questions during my talks, so feel free to prepare questions for me about anything replication. - Kristian.

2 1

MariaDB server crash status=11/SEGV on version 10.6.19
by zxc072211＠gmail.com 21 Aug '24

21 Aug '24

Hi, First, I apologize if my English isn't perfect. After upgrading my MariaDB server from version 10.6.18 to 10.6.19. I encountered an issue. When I execute a "SELECT ... FROM ( SELECT ... ) x WHERE x.colume1 .." statement, the server returns the error: "ERROR 2013 (HY000): Lost connection to server during query." I checked the `/var/log/messages` and found this message: "mariadb.service: Main process exited, code=killed, status=11/SEGV.". I used core dumping to investigate further and found that the stack frame count reaches #12171. The error at #0 is "... Item::build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item.h:1928". The previous stack frames keep repeating two frames: #1 Item_func_case_simple::do_build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item_cmpfunc.h:2448 #2 ... Item::build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item.h:1928 My OS is Rocky Linux 8.10. The MariaDB server versions where I encountered this issue are 10.6.19 and 10.11.9. The MariaDB server version without issues is 10.6.18. Has anyone else encountered this problem? Thanks in advance for any help.

2 2

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

Query Performance Drops 10.3 -> 10.5 - Query Optimizer Refusing Index
by Michael Caplan 02 Aug '24

02 Aug '24

HI there, Looking for some advice. A common query "structure" that performed well in 10.3 is showing a significant performance decrease in 10.5.24. The root cause seems to be 10.5's refusal to use a key. The table in question is has a "key => value" like structure: CREATE TABLE `cases_meta` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `case_id` int(10) unsigned NOT NULL, `key` varchar(50) NOT NULL, `value` mediumtext DEFAULT NULL, PRIMARY KEY (`id`), KEY `key` (`key`), KEY `fk_caes_meta_1` (`case_id`), CONSTRAINT `fk_caes_meta_1` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci The query in question simply is looking for a `key` with a specific value: SELECT * FROM cases_meta AS cm WHERE cm.`key` = 'PAN_NUMBER' AND cm.`value` = '153566' In this example, the table has 2.8 million records. In 10.5, it skips the `key` key, and grinds through 2.8 million records: ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 2743.92693, "table": { "table_name": "cm", "access_type": "ALL", "possible_keys": ["key"], "r_loops": 1, "rows": 1004130, "r_rows": 2820048, "r_table_time_ms": 2548.916341, "r_other_time_ms": 195.0068087, "filtered": 30.31918144, "r_filtered": 0, "attached_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER' and ddx_lab_801615.cm.`value` = '153566'" } } } In 10.3, it uses the `key` key, and reads through a radically smaller subset of tables to fetch the result: ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 357.84, "table": { "table_name": "cm", "access_type": "ref", "possible_keys": ["key"], "key": "key", "key_length": "152", "used_key_parts": ["key"], "ref": ["const"], "r_loops": 1, "rows": 304484, "r_rows": 155123, "r_total_time_ms": 340.94, "filtered": 100, "r_filtered": 0, "index_condition": "ddx_lab_801615.cm.`key` = 'PAN_NUMBER'", "attached_condition": "ddx_lab_801615.cm.`value` = '153566'" } } } I'm not sure why 10.5 is taking this substandard approach. Adding a USE INDEX (`key`) seems to have no effect in 10.5 either, it just skips the index. Any ideas? Thanks, Mike Confidentiality Notice: The information contained in this electronic message and any attachments to this message are intended only for the individual(s) addressed in the message and may contain proprietary and confidential information. If you are not the intended recipient, you should not disseminate, distribute, or copy this e-mail. Please notify the sender and destroy this message. WARNING: Computer viruses can be transmitted via email. The recipient should scan this email before opening it. The company accepts no liability for any damage caused by any virus transmitted by this email. Confidentiality Notice: The information contained in this electronic message and any attachments to this message are intended only for the individual(s) addressed in the message and may contain proprietary and confidential information. If you are not the intended recipient, you should not disseminate, distribute, or copy this e-mail. Please notify the sender and destroy this message. WARNING: Computer viruses can be transmitted via email. The recipient should scan this email before opening it. The company accepts no liability for any damage caused by any virus transmitted by this email.

2 3

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

30 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 10

insert multiple SET type values in prepared statement?
by J.D. 28 Jul '24

28 Jul '24

Trying to insert multiple values of a SET type column using (MariaDB) prepared statements, but keep running into "Data truncated for column <columnN>" no matter how the multi-value string is formatted. Processing is done using PHP-8.2. ... INSERT INTO <table> SET ....<columnN=?> ....; $BindValuesArray[<columnN>]="'value1,value2,value3'"; $BindVarsArray[]=&${$<columnN>}; $BindParamTypes.="s"; ... extract($BindValuesArray); So, trying to insert a (multi-valued) "list string" into a SET type column.

1 0

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

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