- discuss - lists.mariadb.org

Slow log and processlist.memory_used
by Gordan Bobic 13 Feb '24

13 Feb '24

In processlist, there is a column called memory_used. Is there a way to make the peak value for a query get logged into slow log? Failing that, does the performance_schema provide any such info for previously run statements to go with statement digests? Or is the only place where this can be found in the information_schema.processlist table while the query is running?

1 0

Upgrading MariaDB 10.6.15 to 10.6.17 on Ubuntu 18.04 (unsupported)
by stephan.vos＠metacom.net 11 Feb '24

11 Feb '24

We are currently unable to upgrade our server os from Ubuntu 18.04 to 22.04. So we are stuck on MariaDB 10.6.15 and cannot apply any subsequent minor/major releases. Is it possible to somehow use installation binary package for Focal/Jammy or worse case recompiling from source for 18.04?

4 10

Fwd: REGEXP_REPLACE behaviour
by Richard Ellerbrock 07 Feb '24

07 Feb '24

Hi All, I am trying to understand the behaviour of REGEXP_REPLACE when there is a backslash in the subject string. It appears that the subject needs to have \'s escaped which is not desirable (see example 1 below). I can understand having to escape \'s in the regex, but not the subject string. Any suggestions? Thanks. Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 397751 Server version: 10.11.5-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12\4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12\4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet124 | +---------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12\\4', '^Et', 'Ethernet'); +----------------------------------------------+ | REGEXP_REPLACE('Et12\\4', '^Et', 'Ethernet') | +----------------------------------------------+ | Ethernet12\4 | +----------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12/4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12/4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet12/4 | +---------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12 4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12 4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet12 4 | +---------------------------------------------+ 1 row in set (0.000 sec) -- Richard Ellerbrock

2 3

Question regarding AMD EPYC servers and MariaDB
by highclass99 06 Feb '24

06 Feb '24

Hello, In the past when 2nd gen EPYC's were launching and beating Intel servers on most benchmarks, I visited a MariaDB conference and when I asked a senior MariaDB engineer, and asked whether he recommended AMD servers. At that time, he recommended Intel for MariaDB, because MariaDB had much more Intel testing equipment and was optimized for Intel. I am curious whether that is still the case, and whether MariaDB should be more optimized for Intel? For example, would AMD EPYC 7763 vs Intel Platinum 8380, would one be recommended over the other for MariaDB? (of course cores, and workload differ, but on a general perspective) Thank you.

3 3

Replication corrupts index on InnoDB
by Oliver Welter 05 Feb '24

05 Feb '24

Hi All, I have a very strange problem which I am not able to debug any further, so I kindly ask here for any advise to find the root cause of this issue. I run a master-slave replication setup, both servers run on Debian Bookworm with the packages from the distro (10.11.4-MariaDB-1~deb12u1-log). There are two tables in two databases with the same setup (same shop software) and both suffer from recurring index corruption on the slave side. While the master server works without any issues, the slave machine stops replicating from time to time with the error: Last_Errno: 1712 Last_Error: Error 'Index s_order_details is corrupted' on query. Default database: 'foobar_shop'. Query: 'DELETE FROM s_order_details WHERE orderID='40007'' After rebuilding the index by running "alter table foobar_shop.s_order_details engine=innodb" I can restart the slave and it works again. What I did so far: Rebuild the whole slave from fresh SQL Dumps (twice!) Changed replication to "Using_Gtid: Slave_Pos" Reduced slave parallel threads Settings as different from debian defaults binlog_format = mixed expire_logs_days = 1 max_binlog_size = 256M slave_parallel_max_queued=524288 slave_parallel_threads=5 sync_binlog=0 sync_relay_log=0 innodb_flush_log_at_trx_commit=0 best regards Oliver -- Protect your environment - close windows and adopt a penguin!

2 4

terminology: Oracle and non-Oracle?
by Federico Razzoli 04 Feb '24

04 Feb '24

Peter Gulutzan has a very good point that the terms "Oracle" and "non-Oracle" for procedural dialects are not ideal: http://ocelot.ca/blog/blog/2024/01/31/packages-in-mariadb-default-mode/ I'd suggest you choose a name for your native procedural language, and consistently use it in the docs. A lot of DBMSs did that. Postgres uses PL/pgSQL, so you could go for PL/mdbSQL? Cheers, Federico -- Federico Razzoli Vettabase Ltd director & founder, Database Consultant https://vettabase.com federico.razzoli(a)vettabase.com +44 7739 427279 Telegram: federico_razzoli Skype: fede.razzoli The information in this e-mail and any attachments may be confidential. If you received it by mistake, please inform the sender. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.

1 0

Repeatable read anomalies
by Kyle Kingsbury 16 Jan '24

16 Jan '24

Hi y'all! I've just completed an analysis of isolation levels in MySQL. The report focuses on MySQL, but the test suite runs against MariaDB as well. MariaDB exhibits all the same behaviors--"Repeatable Read" allows a whole bunch of anomalies that should be prohibited under Repeatable Read, including G2-item, G-single (even without predicates), non-repeatable reads, violations of Monotonic Atomic View, etc. Figured this might be of interest to the list. https://jepsen.io/analyses/mysql-8.0.34 I'm also curious whether this is widely-understood behavior in the MariaDB community, or if it's news to most people. The extant literature doesn't really seem to discuss these behaviors much, but maybe there's more tacit knowledge among experts? Sincerely, --Kyle

7 19

Mariadb with hibernate-6
by Rajesh Jose 14 Jan '24

14 Jan '24

Hello everyone, In our WildFly based application, we are using mariadb-10.5.8 with mysql-connector as the driver. From the compatibility matrix, it appears that mariadb-10.2 and later are compatible with mysql-5.7 ORM we use is hibernate-6.2 and the documentation says MySQL-5.7 or higher and MariaDB-10.3 or higher are supported with out-of-box dialects. . So the mariadb we use should be compliant to hibernate-6 if we go by mariadb version or corresponding mysql version. However, when we start our app, we get a WARNING message - "HHH000511: The 5.5.0 version for [org.hibernate.dialect.MySQLDialect] is no longer supported" When we look at the connection meta-data returned from DB, it returns "MySQL" as the product-name and "5.5.5-10.5.8-MariaDB" as the product version. That seems to be the reason, hibernate is complaining. But if we connect with the mariadb-driver, it return "MariaDB" as the product name and "10.5.8-MariaDB" as the version. - What does the 5.5.0 prefix signify in the version? Is it the compatible MySQL version? If so, why its not 5.7 as it appears from documentation that versions above mariadb-10.2 are compatible with mysql-5.7? - Is this WARNING a problem that warrant a DB migration or is there a community supported dialect as explained here - https://docs.jboss.org/hibernate/orm/6.2/migration-guide/migration-guide.ht… -Rajesh

2 2

Replication Speed / Lag 10.3.31 vs 10.3.39
by Michael Caplan 14 Jan '24

14 Jan '24

Hello, I'm trying to troubleshoot a performance a replication issue between two version of MariaDB. I know that we are using two EOL versions, but I want to rule out all issues before prioritizing migrating to a new version. I have built a new dedicated replication server (B) to replace the current dedicated replica (A). A and B are both running identical OSes and versions and near identical hardware in a RAID 10 configuration. The main difference is that B has newer disks that slightly outperform A. I benchmarked both A and B to validate this. Both A and B are configured identically (other then server-id). Replica A (and primary) is running MariaDB 10.3.31. Replica B is running 10.3.39. A few other things to note: * B is not feeding off of the primary, but A -- that is until we sunset A. * A is handing moderate read only traffic * B is just running as a replica with no other responsibilities I have external monitoring to alert when either hits 500+ Seconds_Behind_Master. Since standing B up, I am seeing it fall behind a few times a day. The interesting thing is that A is not falling behind from the primary. Upon further examination, a scheduled job that executes hundreds of similar delete operations off the same table (that has approx. 3 million records) is where B is largely getting hung up. Something like this: DELETE FROM table WHERE x = 800000 AND y = 40000 The table has an index on just x. Running ANALYZE FORMAT=JSON SELECT * FROM table WHERE x = 800000 AND y = 40000 from B show a striking difference in total time spent: REPLICA B: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 490.13, "table": { "table_name": "table", "access_type": "ref", "possible_keys": ["table_index"], "key": "table_index", "key_length": "4", "used_key_parts": ["x"], "ref": ["const"], "r_loops": 1, "rows": 795555, "r_rows": 31, "r_total_time_ms": 490.08, "filtered": 100, "r_filtered": 100, "index_condition": "table.y = '40000'" } } } vs: REPLICA A: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 106.37, "table": { "table_name": "table", "access_type": "ref", "possible_keys": ["table_index"], "key": "table_index", "key_length": "4", "used_key_parts": ["x"], "ref": ["const"], "r_loops": 1, "rows": 1146482, "r_rows": 31, "r_total_time_ms": 106.34, "filtered": 100, "r_filtered": 100, "index_condition": "table.y = '40000'" } } } That is a significant difference performance wise. From a hardware point of view, B should have a slight edge over A. But the contrary is true by a large margin. As a short term "fix", I added a secondary index: create index table_index2 on table(x, y); This significantly speeds up this operation, and, for now, seems to deal with much of the experienced replication lab in B: SERVER B: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.253, "table": { "table_name": "table", "access_type": "ref", "possible_keys": [ "table_index", "table_index2" ], "key": "table_index2, "key_length": "8", "used_key_parts": ["x", "y"], "ref": ["const", "const"], "r_loops": 1, "rows": 31, "r_rows": 31, "r_total_time_ms": 0.2037, "filtered": 100, "r_filtered": 100 } } } This seems reasonable as a general optimization, but provides little comfort in understanding the root cause of my performance issue with B. I'm hesitant to retire A in favor of B until I can get to the bottom of this. The question I have for the community is: what would you recommend that I do to get further perspective on the issue to determine a course of action? Thanks everyone, 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.

3 2

Data truncated using C API
by Jogchum Reitsma 08 Jan '24

08 Jan '24

Hi, Using server version: 11.2.2, I try to extract data from a database with this C-code: #include <stdlib.h> #include <stdio.h> #include <string.h> #include <mysql.h> define MAXFLEN 66 char *bo_src = "SELECT FILENAMME, FILENUM FROM BOEK WHERE TOKENS IS NULL ORDER BY UDC_KOADE, FILENUM"; char filename[MAXFLEN], stavering[3]; long filenum; int fetch_result; MYSQL *bo_src_sql; MYSQL_ST MT *bo_src_stmt; MYSQL_RES *bo_src_res; MYSQL_BIND bo_src_bind[2], bo_src_bind_result[2]; int main (argc, argv) int argc; char *argv[]; { bo_src_sql = mysql_init(bo_src_sql) ; if ((bo_src_stmt = mysql_stmt_init(bo_src_sql))==NULL) printf ("Can't initialize %s:\n %s/n", bo_src, mysql_error(bo_src_sql)) ; if (!mysql_real_connect(bo_src_sql,"localhost","<user>","<passwd>","tdb",0,NULL,0)) fprintf(stderr, "Failed to connect to book table for select: Error: %s\n", mysql_error(bo_src_sql)) ; if (mysql_stmt_prepare(bo_src_stmt, bo_src, strlen(bo_src))) printf ("Can't prepare %s:\n %s\n", bo_src, mysql_error(bo_src_sql)) ; bo_src_bind_result[0].buffer_type = MYSQL_TYPE_STRING ; bo_src_bind_result[0].buffer = filename ; bo_src_bind_result[0].buffer_length = 100 ; bo_src_bind_result[1].buffer_type = MYSQL_TYPE_LONG ; bo_src_bind_result[1].buffer = (long *)&filenum ; bo_src_bind_result[1].buffer_length = sizeof (filenum) ; memset (bo_src_bind, 0, sizeof (bo_src_bind)) ; memset (bo_src_bind_result, 0, sizeof (bo_src_bind_result)) ; if (mysql_stmt_bind_param (bo_src_stmt, bo_src_bind)) fprintf(stderr, "Failed to bind parameters in %s: \n Error: %s\n", bo_src, mysql_error(bo_src_sql)) ; if (mysql_stmt_bind_result(bo_src_stmt, bo_src_bind_result)) fprintf(stderr, "Failed to bind result in %s: \n Error: %s\n", bo_src, mysql_error(bo_src_sql)) ; if (!mysql_stmt_execute (bo_src_stmt)) { fetch_result = mysql_stmt_fetch(bo_src_stmt) ; if (fetch_result == 0) printf ("%ld %s %s\n", filenum, filename, stavering) ; else if (fetch_result == MYSQL_NO_DATA) printf ("No data found\n") ; else if (fetch_result == MYSQL_DATA_TRUNCATED) printf ("Data truncated.\n%d/n", fetch_result) ; else printf ("Something else! %d \n%s/n", fetch_result, mysql_error(bo_src_sql)); } ; mysql_stmt_close(bo_src_stmt) ; mysql_close(bo_src_sql); } The definition of the table is describe BOEK; +-----------+-------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------------------------+------+-----+---------+----------------+ | FILENUM | smallint(4) | NO | PRI | NULL | auto_increment | | TITEL | varchar(50) | YES | | NULL | | | UDC_KOADE | varchar(10) | YES | | NULL | | | ISBN | bigint(13) | YES | | NULL | | | FILENAMME | varchar(20) | YES | | NULL | | | SKR_NUM | smallint(5) | YES | | NULL | | | UTJ_NUM | smallint(5) | YES | | NULL | | | UTJ_DATUM | date | YES | | NULL | | | OERSET | tinyint(1) | YES | | NULL | | | PRINTINGE | smallint(3) | YES | | NULL | | | YNL_DATUM | date | YES | | NULL | | | STAVERING | enum('ald','mid','nij','int') | YES | | NULL | | | TOKENS | smallint(6) | YES | | NULL | | | TYPES | smallint(5) | YES | | NULL | | | MAXTOKENS | smallint(6) | YES | | NULL | | | MAXTYPES | smallint(5) | YES | | NULL | | | TIID | time | YES | | NULL | | +-----------+-------------------------------+------+-----+---------+----------------+ So the size of FILENAMME, varchar20, is much smaller than the buffer length specified in the code: ; bo_src_bind_result[0].buffer_type = MYSQL_TYPE_STRING ; bo_src_bind_result[0].buffer = filename ; bo_src_bind_result[0].buffer_length = 100 Yet I get a MYSQL_DATA_TRUNCATED fetching the result of the query. Debugging, only the integer filenum gets its value. The statement (SELECT FILENAMME, FILENUM FROM BOEK WHERE TOKENS IS NULL ORDER BY UDC_KOADE, FILENUM;) gives the expected result when entered in a terminal session. I obviously do something wrong, but I can't find out what. Googling gets me no answer. Anyone who can spot the error? Many thanks in advance, best regards, Jogchum

1 1