January 2024 - discuss - lists.mariadb.org

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

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

Re: "Access denied for user" on AWS RDS MariaDB Instance
by serg＠mariadb.org 04 Jan '24

04 Jan '24

Hi, Rhys.Campbell, First, please reply to (or cc:) the list, if you don't mind. On Jan 04, Rhys.Campbell(a)swisscom.com wrote: > Hello Sergei, > > SHOW PLUGINS; - List three authentication plugins... > > +----------------------------------+--------+--------------------+---------------+-------------+ > | Name | Status | Type | Library | License | > +----------------------------------+--------+--------------------+---------------+-------------+ > | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL | > +----------------------------------+--------+--------------------+---------------+-------------+ Okay. It shows what your user cannot don't use. But what they do use? Can you check whether they all - or at least those that get errors - only use IDENTIFIED WITH mysql_old_password USING 'xxxxx' ? I'm not asking to send SHOW CREATE USER to the list, you can just take a look yourself and reply "yes" /Sergei > -----Ursprüngliche Nachricht----- > Von: Sergei Golubchik <serg(a)mariadb.org> > Gesendet: Donnerstag, 4. Januar 2024 14:03 > An: Campbell Rhys, B2C-TSP-SRE <Rhys.Campbell(a)swisscom.com> > Cc: discuss(a)lists.mariadb.org > Betreff: Re: [MariaDB discuss] "Access denied for user" on AWS RDS MariaDB Instance > > Hi, Rhys.Campbell, > > How is authentication configured server-side? > Just the conventional mysql_native_password plugin? > > Regards, > Sergei > Chief Architect, MariaDB Server > and security(a)mariadb.org > > On Jan 04, Rhys.Campbell via discuss wrote: > > Hello All, > > > > I have a funny issue that is bothering me on a few MariaDB > > instances. We're running 10.6.14 on AWS RDS with very little changed > > in the default configuration. We have eight instances of a vendor > > application running on EC2 Instances that use these MariaDB > > Instances. This application is deployed and configured via > > TF/Ansible and we don't really manually login to these instance to > > change or configure anything. Very occasionally I get an issue with > > logging into the database. The vendor application is occasionally > > logging the following errors... > > > > Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds > > before retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before > > retry > > > > Of course I recognized these as the classic "wrong password" login > > error. There are naturally corresponding error log entries in the > > MariaDB Log... > > > > Access denied for user 'user'@'X.X.X.X' (using password: YES) > > > > The vendor is a bit dismissive of this, blaming the MariaDB > > Instance. I'm not certain that is the case. We see perhaps 1 > > instance of this per day although somedays it doesn't happen and a > > small number of days we see two instances. It does not appear to > > happen on a predictable schedule. The MariaDB Instances are working > > fine at all other times. CPU utilization is very low and we only > > serve a small number of connections. There is nothing else in the > > error log of interest. Credentials are set, via Ansible, in > > configuration files. Details are correct and 99.9% of the time the > > vendor application has no issues logging into the MariaDB Instance. > > I can't actually tie these errors to something not working... but > > we're running very low traffic at the moment, and I'm concerned > > about it becoming more of an issue later on. > > > > Two possibilities exist in my mind... > > > > > > 1. Bug in vendor application is occasionally munging the password > > and creating the error we see logged. > > 2. Something else as yet unknown, i.e. rogue cronjob configured to > > use false vendor app credentials that creates the above error. I > > don't believe this to be the case but I will not 100% exclude it as > > a possibility. > > > > Is anyone aware of any logging/auditing that I could activate on the > > MariaDB RDS Instances to get a little more information about this? > > I'm thinking about setting up an strace on the vendor process but > > want to see if there are any better options first. > > > > Cheers, > > Rhys

2 1

"Access denied for user" on AWS RDS MariaDB Instance
by Rhys.Campbell＠swisscom.com 04 Jan '24

04 Jan '24

Hello All, I have a funny issue that is bothering me on a few MariaDB instances. We're running 10.6.14 on AWS RDS with very little changed in the default configuration. We have eight instances of a vendor application running on EC2 Instances that use these MariaDB Instances. This application is deployed and configured via TF/Ansible and we don't really manually login to these instance to change or configure anything. Very occasionally I get an issue with logging into the database. The vendor application is occasionally logging the following errors... Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before retry Of course I recognized these as the classic "wrong password" login error. There are naturally corresponding error log entries in the MariaDB Log... Access denied for user 'user'@'X.X.X.X' (using password: YES) The vendor is a bit dismissive of this, blaming the MariaDB Instance. I'm not certain that is the case. We see perhaps 1 instance of this per day although somedays it doesn't happen and a small number of days we see two instances. It does not appear to happen on a predictable schedule. The MariaDB Instances are working fine at all other times. CPU utilization is very low and we only serve a small number of connections. There is nothing else in the error log of interest. Credentials are set, via Ansible, in configuration files. Details are correct and 99.9% of the time the vendor application has no issues logging into the MariaDB Instance. I can't actually tie these errors to something not working... but we're running very low traffic at the moment, and I'm concerned about it becoming more of an issue later on. Two possibilities exist in my mind... 1. Bug in vendor application is occasionally munging the password and creating the error we see logged. 2. Something else as yet unknown, i.e. rogue cronjob configured to use false vendor app credentials that creates the above error. I don't believe this to be the case but I will not 100% exclude it as a possibility. Is anyone aware of any logging/auditing that I could activate on the MariaDB RDS Instances to get a little more information about this? I'm thinking about setting up an strace on the vendor process but want to see if there are any better options first. Cheers, Rhys

2 1