- discuss - lists.mariadb.org

frm files were deleted, how to recover?
by lkhaajav＠limes.kr 27 Nov '24

27 Nov '24

Hi everybody? Today is the first snow day of the year in Seoul, every thing is white outside, it is a beautiful day. Some of *.frm and *.ibd files were deleted forever accidently. Those cannot be recovered. I used some file-recovery tools but failed. Also data was partitioned. Is there any way to recover *.ibd partitioned files without *.frm ? MariaDB version is 10.7.3. Storage Engine is InnoDB. I tried innodb_force_recovery=6, also created same structure frm files and used discard, import, but partitioned tables cannot be supported for discard and import. Please help me!

1 0

Slow Log Rotation Hang Bug
by Gordan Bobic 19 Nov '24

19 Nov '24

I've been doing some testing with various versions of MySQL and MariaDB regarding slow log rotation. And it looks like MariaDB (tested 10.4, 10.5, 10.6 so far) suffers from a bug. Setup: sloq_query_log=1 long_query_time=0 # sysbench oltp_read_write --threads=$(nproc) Action: mv slow.log slow.log.1 MariaDB [(none)]> flush slow logs; ^^ This never returns (never as in I haven't tried waiting for > 10 minutes). On MySQL 5.5 this is instantaneous. On 5.6 - 8.0 it takes a few seconds, worst case I measured over the weekend is 77s, which is pretty terrible, but with MariaDB 10.3 - 10.6 it never seems to complete at all. If I kill sysbench, it completes immediately. On 10.3 it works, in line with MySQL 5.6 - 8.0. This seems like a rather significant bug.

2 4

MariaDB 10.5 - add replication to existing installation
by Dan O'Reilly 15 Nov '24

15 Nov '24

When I try to add replication to an existing installation, mariadb refuses to start (I'm a novice at MariaDB). This is on RHEL 9. This is my server.cnf: # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld/mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid # next stuff copied from the original zabbix 5 server.cnf default-storage-engine=innodb innodb_autoinc_lock_mode=2 innodb_buffer_pool_size=1GB innodb_doublewrite=1 innodb_file_per_table=1 innodb_flush_method = O_DIRECT innodb_log_file_size=128M long_query_time=1 max-allowed-packet=16M max_connections=800 max_heap_table_size=256M performance_schema=on query_cache_size=0 query_cache_type=0 slow_query_log=1 slow_query_log_file=/var/lib/mysql/slowlog/mysql-slow.log thread-cache-size=50 tmp_table_size=512M # # * Galera-related settings # [galera] # Mandatory settings #wsrep_on=ON #wsrep_provider= #wsrep_cluster_address= #binlog_format=row #default_storage_engine=InnoDB #innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=0.0.0.0 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.5 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.5] # server-id=1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index =/var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index The error I get is: # systemctl status mariadb × mariadb.service - MariaDB 10.5 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; preset: disabled) Active: failed (Result: exit-code) since Mon 2024-11-11 16:31:02 MST; 12min ago Duration: 2h 35min 21.402s Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 426050 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS) Process: 426072 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=1/FAILURE) CPU: 281ms Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426111]: the maria-discuss email list at https://launchpad.net/~maria-discuss Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426111]: Please check all of the above before submitting a bug report Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426111]: at https://mariadb.org/jira Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426072]: Initialization of MariaDB database failed. Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426072]: Perhaps /etc/my.cnf is misconfigured or there is some problem with permissions of /var/lib/mysql. Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426072]: Initialization of MariaDB database was not finished successfully. Nov 11 16:31:02 tmv1zbdb01 mariadb-prepare-db-dir[426072]: Files created so far will be removed. Nov 11 16:31:02 tmv1zbdb01 systemd[1]: mariadb.service: Control process exited, code=exited, status=1/FAILURE Nov 11 16:31:02 tmv1zbdb01 systemd[1]: mariadb.service: Failed with result 'exit-code'. Nov 11 16:31:02 tmv1zbdb01 systemd[1]: Failed to start MariaDB 10.5 database server.

3 3

how to backup MyRocks Database without InnoDB Using mariadb-backup
by i18n.site 12 Nov '24

12 Nov '24

I am facing an issue while attempting to back up my MariaDB database, which is entirely based on the MyRocks storage engine. My MariaDB was compiled without InnoDB support, and I am using `mariadb-backup` to perform the backup. However, I encounter the following error: ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave [00] 2024-11-13 06:37:55 Connecting to MariaDB server host: localhost, user: mysql, password: not set, port: 2883, socket: /run/mysqld/mysqld.sock [00] 2024-11-13 06:37:56 Using server version 11.4.2-MariaDB-log /usr/local/mysql/bin/mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) [00] 2024-11-13 06:37:56 uses posix_fadvise(). [00] 2024-11-13 06:37:56 cd to /mnt/data/i18n/mariadb/data/ [00] 2024-11-13 06:37:56 open files limit requested 0, set to 108224 [00] 2024-11-13 06:37:56 mariabackup: using the following InnoDB configuration: [00] 2024-11-13 06:37:56 innodb_data_home_dir = . [00] 2024-11-13 06:37:56 innodb_data_file_path = ibdata1:10M:autoextend [00] 2024-11-13 06:37:56 innodb_log_group_home_dir = . 2024-11-13 6:37:56 0 [Note] InnoDB: Number of transaction pools: 1 2024-11-13 6:37:56 0 [ERROR] InnoDB: File ./ib_logfile0 was not found [00] 2024-11-13 06:37:56 Error: cannot read redo log header ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave --skip-innodb-backup /usr/local/mysql/bin/mariadb-backup: unknown option '--skip-innodb-backup' ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave --rocksdb-backup [00] 2024-11-13 06:38:53 Connecting to MariaDB server host: localhost, user: mysql, password: not set, port: 2883, socket: /run/mysqld/mysqld.sock [00] 2024-11-13 06:38:53 Using server version 11.4.2-MariaDB-log /usr/local/mysql/bin/mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) [00] 2024-11-13 06:38:53 uses posix_fadvise(). [00] 2024-11-13 06:38:53 cd to /mnt/data/i18n/mariadb/data/ [00] 2024-11-13 06:38:53 open files limit requested 0, set to 108224 [00] 2024-11-13 06:38:53 mariabackup: using the following InnoDB configuration: [00] 2024-11-13 06:38:53 innodb_data_home_dir = . [00] 2024-11-13 06:38:53 innodb_data_file_path = ibdata1:10M:autoextend [00] 2024-11-13 06:38:53 innodb_log_group_home_dir = . 2024-11-13 6:38:53 0 [Note] InnoDB: Number of transaction pools: 1 2024-11-13 6:38:53 0 [ERROR] InnoDB: File ./ib_logfile0 was not found [00] 2024-11-13 06:38:53 Error: cannot read redo log header ❯ mariadb-backup -v mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) ────── i18N.Site : MarkDown Translate / Website Solution ( 翻译&建站工具 )

1 0

(no subject)
by i18n.site 12 Nov '24

12 Nov '24

I am facing an issue while attempting to back up my MariaDB database, which is entirely based on the MyRocks storage engine. My MariaDB was compiled without InnoDB support, and I am using `mariadb-backup` to perform the backup. However, I encounter the following error: ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave [00] 2024-11-13 06:37:55 Connecting to MariaDB server host: localhost, user: mysql, password: not set, port: 2883, socket: /run/mysqld/mysqld.sock [00] 2024-11-13 06:37:56 Using server version 11.4.2-MariaDB-log /usr/local/mysql/bin/mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) [00] 2024-11-13 06:37:56 uses posix_fadvise(). [00] 2024-11-13 06:37:56 cd to /mnt/data/i18n/mariadb/data/ [00] 2024-11-13 06:37:56 open files limit requested 0, set to 108224 [00] 2024-11-13 06:37:56 mariabackup: using the following InnoDB configuration: [00] 2024-11-13 06:37:56 innodb_data_home_dir = . [00] 2024-11-13 06:37:56 innodb_data_file_path = ibdata1:10M:autoextend [00] 2024-11-13 06:37:56 innodb_log_group_home_dir = . 2024-11-13 6:37:56 0 [Note] InnoDB: Number of transaction pools: 1 2024-11-13 6:37:56 0 [ERROR] InnoDB: File ./ib_logfile0 was not found [00] 2024-11-13 06:37:56 Error: cannot read redo log header ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave --skip-innodb-backup /usr/local/mysql/bin/mariadb-backup: unknown option '--skip-innodb-backup' ❯ sudo -u mysql /usr/local/mysql/bin/mariadb-backup --backup --target-dir=/tmp/rebuild_slave --rocksdb-backup [00] 2024-11-13 06:38:53 Connecting to MariaDB server host: localhost, user: mysql, password: not set, port: 2883, socket: /run/mysqld/mysqld.sock [00] 2024-11-13 06:38:53 Using server version 11.4.2-MariaDB-log /usr/local/mysql/bin/mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) [00] 2024-11-13 06:38:53 uses posix_fadvise(). [00] 2024-11-13 06:38:53 cd to /mnt/data/i18n/mariadb/data/ [00] 2024-11-13 06:38:53 open files limit requested 0, set to 108224 [00] 2024-11-13 06:38:53 mariabackup: using the following InnoDB configuration: [00] 2024-11-13 06:38:53 innodb_data_home_dir = . [00] 2024-11-13 06:38:53 innodb_data_file_path = ibdata1:10M:autoextend [00] 2024-11-13 06:38:53 innodb_log_group_home_dir = . 2024-11-13 6:38:53 0 [Note] InnoDB: Number of transaction pools: 1 2024-11-13 6:38:53 0 [ERROR] InnoDB: File ./ib_logfile0 was not found [00] 2024-11-13 06:38:53 Error: cannot read redo log header ❯ mariadb-backup -v mariadb-backup based on MariaDB server 11.4.2-MariaDB Linux (x86_64) ────── i18N.Site : MarkDown Translate / Website Solution ( 翻译&建站工具 )

1 0

Monitoring mariaDB through SNMP
by Noah 04 Nov '24

04 Nov '24

Ahoy List Members, I hope you are well. Is there a good guide that explains how to set up monitoring mariaDB via SNMP? Cheers, Noah

3 2

Migrate MySQL5.7 to MariaDB 10.4, then to MariaDB 10.11 Failed
by edwin.chan＠checkit.net 30 Oct '24

30 Oct '24

Hi, I am going to migrate our existing MySQL5.7 to MariaDB 10.11.9. I setup a new Windows server that installed MySQL 5.7, MariaDB 10.4.34 and MariaDB 10.11.9 without create a new instance. I copied the data directory of MySQL5.7 from existing Windows server to the new server for migration test. First, I am able to start the MySQL5.7 on the new server. Then, I upgrade the MySQL5.7 to MariaDB 10.4.34 successfully using mysql_upgrade_service.exe which under MariaDB 10.4.34 bin directory. But failed at the last step that upgrade MariaDB 10.4.34 to MariaDB 10.11.9 using mariadb-upgrade-service.exe which under MariaDB 10.11.9 bin directory. From the error log, got below message 2024-10-03 16:17:39 0 [Note] InnoDB: Buffer pool(s) load completed at 241003 16:17:39 241003 16:17:40 [ERROR] mysqld got exception 0xc0000005 ; Sorry, we probably made a mistake, and this is a bug. Your assistance in bug reporting will enable us to fix this for the next release. To report this bug, see https://mariadb.com/kb/en/reporting-bugs From the upgrade log, got below message Phase 3/8: Running 'mysql_fix_privilege_tables' ERROR 2017 (HY000): Can't open named pipe to host: . pipe: mysql_upgrade_service_3564 (2) FATAL ERROR: Upgrade failed Checked named pipe enabled and local client able to connect using named pipe at each stage. Any idea why upgrade failed? When upgrade, MariaDB start the instance with socket which is not same as the socket option in my.ini. In this case, it start with socket mysql_upgrade_service_3564; I tried few times the last 4 numeric digits are not fixed and change every time. Thanks.

5 6

Logging options to debug SSL problem
by Andrew Wood 14 Oct '24

14 Oct '24

Hi Is there a way to get the server to log verbosely everything to do with connection attempts in order to try and debug why a client keeps getting error 2026 SSL connection error: socket layer receive error? There is currently nothing being logged on the server other than: [Warning] Aborted connection 332 to db: 'unconnected' user: 'unauthenticated' host: '192.168.253.231' (This connection closed normally without authentication) SHOW GLOBAL VARIABLES LIKE 'tls_version'; gives TLSv1.1,TLSv1.2,TLSv1.3 and the client is based on a relatively recent version of libmysqlclient so Im struggling to understand what is going wrong without some more detailed logging. Thanks Andrew

3 3

Performance questions after Migrating from MariaDB 5.5.68 to 10.11.6 (and 9)
by cyusedfzfb 10 Oct '24

10 Oct '24

Dear MariaDB List Readers, I hope formatting will survive...i tried to make this as readable as possible. Due to our ongoing migration from RHEL7 to RHEL9, we are also upgrading some databases from MariaDB 5.5.68 (on RHEL7) to MariaDB 10.11.6 (on RHEL9). During this process, we've noticed a significant performance drop, particularly with UPDATE and DELETE operations. To investigate this further and rule out external factors, I created a single RHEL9 VM and downloaded both the x86_64/systemd versions of MariaDB 5.5.68 and 10.11.9 from MariaDB Downloads. (https://mariadb.org/download) I installed both versions on the same VirtualBox VM, using default configurations, and placed them on the same partition (/usr/local). I followed the installation instructions in the INSTALL-BINARY file. I then ran a benchmark on both versions using a variation of this PHP benchmark script: https://github.com/sergix44/php-benchmark-script. Here are the results: *MariaDB 5.5.68*: Server version: 5.5.68-MariaDB MariaDB Server mysql::ping,0.0002 mysql::select_version,0.1299 mysql::select_all,0.6270 mysql::select_cursor,0.8542 mysql::seq_insert,2.0182 mysql::bulk_insert,0.0204 mysql::update,0.0005 mysql::update_with_index,0.0005 mysql::transaction_insert,0.0014 mysql::indexes,0.0000 mysql::delete,0.0005 mysql::select_version::q/s,15403 mysql::select_all::q/s,3190 mysql::seq_insert::q/s,991 mysql::update::q/s,3895386 mysql::update_with_index::q/s,4105436 mysql::transaction_insert::t/s,1460821 mysql::delete::q/s,4436065 Total time: 13.2027 s Peak memory usage: 23.82 MiB *MariaDB 10.11.9*: Server version: 10.11.9-MariaDB MariaDB Server mysql::ping,0.0001 mysql::select_version,0.1268 mysql::select_all,0.6435 mysql::select_cursor,0.8352 mysql::seq_insert,2.2696 mysql::bulk_insert,0.6556 mysql::update,1.4104 mysql::update_with_index,0.3981 mysql::transaction_insert,2.5425 mysql::indexes,0.2353 mysql::delete,2.2246 mysql::select_version::q/s,15772 mysql::select_all::q/s,3108 mysql::seq_insert::q/s,881 mysql::update::q/s,1418 mysql::update_with_index::q/s,8963 mysql::transaction_insert::t/s,787 mysql::delete::q/s,899 Total time: 20.9426 s Peak memory usage: 23.82 MiB The key observations: - **UPDATE performance:** - 5.5.68: *3,895,386* q/s - 10.11.9: *1,418* q/s - **UPDATE with index:** - 5.5.68: *4,105,436* q/s - 10.11.9: *8,963* q/s - **DELETE performance:** - 5.5.68: *4,436,065* q/s - 10.11.9: *899* q/s I also tested MariaDB 10.11.6, and the results were similar to 10.11.9. Our web application feels noticeably slower, and the benchmark results seem to confirm this. Is this drastic performance drop expected with newer MariaDB versions? While we are aware of tuning options (like `innodb_buffer_pool_size`, file system mount options, `innodb_flush_log_at_trx_commit`, etc.), both versions were run with default settings and no tuning. How can it be that MariaDB 5.5.68 is performing several orders of magnitude faster than 10.11.x for these specific operations? Best regards, MJ To end, and to be complete: find below the actual script used to create the stats: <?php $initialRowCount = 1000; $mysqli = null; setup(function ($args) use (&$mysqli, $initialRowCount) { if (!extension_loaded('mysqli')) { print('The mysqli extension is not loaded' . PHP_EOL); return; } if ($args['mysql_host'] === null || $args['mysql_user'] === null || $args['mysql_password'] === null) { print('The --mysql_host=, --mysql_user=, and --mysql_password= arguments are required' . PHP_EOL); return; } $mysqli = new mysqli($args['mysql_host'], $args['mysql_user'], $args['mysql_password'], null, isset($args['mysql_port']) ? $args['mysql_port'] : 3306); if ($mysqli->connect_error) { printf("Mysql Connect Error (%s) %s\n", $mysqli->connect_errno, $mysqli->connect_error); return; } // drop database $mysqli->query("DROP DATABASE IF EXISTS `bench_test`"); // create database $mysqli->query("CREATE DATABASE IF NOT EXISTS `bench_test`"); $mysqli->select_db('bench_test'); $mysqli->query("CREATE TABLE IF NOT EXISTS `bench_test`.`test` (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255))"); for ($i = 0; $i < $initialRowCount; $i++) { $values[] = "('test$i')"; } $mysqli->query("INSERT INTO `bench_test`.`test` (name) VALUES " . implode(',', $values)); }); teardown(function () use (&$mysqli) { if ($mysqli === null) { return; } $mysqli->query("DROP DATABASE IF EXISTS `bench_test`"); $mysqli->close(); }); return [ 'ping' => function () use (&$mysqli) { if ($mysqli === null) { return INF; } $mysqli->ping(); return 1; }, 'select_version' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("SELECT VERSION()"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); return $i; }, 'select_all' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("SELECT * FROM `bench_test`.`test`"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); return $i; }, 'select_cursor' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; for ($i = 0; $i < $count; $i++) { $result = $mysqli->query("SELECT * FROM `bench_test`.`test`"); while ($row = $result->fetch_assoc()) { } $result->close(); } return $i; }, 'seq_insert' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("INSERT INTO `bench_test`.`test` (name) VALUES ('test')"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); return $i; }, 'bulk_insert' => function ($multiplier = 1, $count = 100000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $values = []; for ($i = 0; $i < $count; $i++) { $values[] = "('test$i')"; } $mysqli->query("INSERT INTO `bench_test`.`test` (name) VALUES " . implode(',', $values)); return $i; }, 'update' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("UPDATE `bench_test`.`test` SET name = 'test' WHERE id = '$i'"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); return $i; }, 'update_with_index' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $mysqli->query("CREATE INDEX idx ON `bench_test`.`test` (id)"); $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("UPDATE `bench_test`.`test` SET name = 'test' WHERE id = '$i'"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); $mysqli->query("DROP INDEX idx ON `bench_test`.`test`"); return $i; }, 'transaction_insert' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->begin_transaction(); $mysqli->query("INSERT INTO `bench_test`.`test` (name) VALUES ('test')"); $mysqli->commit(); } extraStat('t/s', round($count / (StopWatch::time() - $time))); return $i; }, 'indexes' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $mysqli->query("CREATE INDEX idx_name ON `bench_test`.`test` (name)"); $mysqli->query("DROP INDEX idx_name ON `bench_test`.`test`"); return 1; }, 'delete' => function ($multiplier = 1, $count = 1000) use (&$mysqli) { if ($mysqli === null) { return INF; } $count = $count * $multiplier; $time = StopWatch::time(); for ($i = 0; $i < $count; $i++) { $mysqli->query("DELETE FROM `bench_test`.`test` WHERE id = '$i'"); } extraStat('q/s', round($count / (StopWatch::time() - $time))); return $i; }, ];

5 9

mariadb 10.6.17 + galera-26.4.19
by Jaco Kroon 07 Oct '24

07 Oct '24

Hi, We're observing the following crash using galera and mariadb versions as per subject. Usually only one of the three nodes will go down, but last night two went down within minutes of each other resulting in a fairly nasty outage. Given the frequency of these crashes (about one once a month) I highly suspect we're doing something wrong causing us to run into this, but not others. So any advise appreciated. Stack trace as per logs: 241007 1:01:25 [ERROR] mysqld got signal 11 ; Sorry, we probably made a mistake, and this is a bug. Your assistance in bug reporting will enable us to fix this for the next release. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.6.17-MariaDB-log source revision: 15c75ad083a55e198ae78324f22970694b72f22b key_buffer_size=536870912 read_buffer_size=1048576 max_used_connections=522 max_threads=10002 thread_count=83 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 10498887186 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f675c000c68 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f7f78056da8 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x32)[0x5632cb5f2212] /usr/sbin/mysqld(handle_fatal_signal+0x2b3)[0x5632cb133db3] /lib64/libc.so.6(+0x3c760)[0x7f7f7b653760] /usr/sbin/mysqld(+0x8ee692)[0x5632cb44b692] /usr/sbin/mysqld(+0x8ef5cd)[0x5632cb44c5cd] /usr/sbin/mysqld(+0x8acfa5)[0x5632cb409fa5] /usr/sbin/mysqld(+0x24e630)[0x5632cadab630] /usr/sbin/mysqld(+0x24e6b5)[0x5632cadab6b5] /usr/sbin/mysqld(+0x949418)[0x5632cb4a6418] /usr/sbin/mysqld(+0x960b34)[0x5632cb4bdb34] /usr/sbin/mysqld(+0x8b9e7c)[0x5632cb416e7c] /usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x232)[0x5632cb13b022] /usr/sbin/mysqld(_ZN14Rows_log_event8find_rowEP14rpl_group_info+0x3e4)[0x5632cb2624c4] /usr/sbin/mysqld(_ZN21Delete_rows_log_event11do_exec_rowEP14rpl_group_info+0x142)[0x5632cb2629b2] /usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEP14rpl_group_info+0x35f)[0x5632cb255c9f] /usr/sbin/mysqld(_Z18wsrep_apply_eventsP3THDP14Relay_log_infoPKvm+0x1fd)[0x5632cb3def8d] /usr/sbin/mysqld(+0x868ff0)[0x5632cb3c5ff0] /usr/sbin/mysqld(_ZN21Wsrep_applier_service15apply_write_setERKN5wsrep7ws_metaERKNS0_12const_bufferERNS0_14mutable_bufferE+0xb5)[0x5632cb3c6ba5] /usr/sbin/mysqld(+0xb0c921)[0x5632cb669921] /usr/sbin/mysqld(+0xb1dfd6)[0x5632cb67afd6] /usr/lib64/galera/libgalera_smm.so(+0x60724)[0x7f7f7b260724] /usr/lib64/galera/libgalera_smm.so(+0x6fa67)[0x7f7f7b26fa67] /usr/lib64/galera/libgalera_smm.so(+0x74d65)[0x7f7f7b274d65] /usr/lib64/galera/libgalera_smm.so(+0x9fdc3)[0x7f7f7b29fdc3] /usr/lib64/galera/libgalera_smm.so(+0xa098e)[0x7f7f7b2a098e] /usr/lib64/galera/libgalera_smm.so(+0x75200)[0x7f7f7b275200] /usr/lib64/galera/libgalera_smm.so(+0x4f35f)[0x7f7f7b24f35f] /usr/sbin/mysqld(_ZN5wsrep18wsrep_provider_v2611run_applierEPNS_21high_priority_serviceE+0x12)[0x5632cb67b652] /usr/sbin/mysqld(+0x8842d1)[0x5632cb3e12d1] /usr/sbin/mysqld(_Z15start_wsrep_THDPv+0x276)[0x5632cb3d0d16] /usr/sbin/mysqld(+0x80e941)[0x5632cb36b941] /lib64/libc.so.6(+0x8ad22)[0x7f7f7b6a1d22] /lib64/libc.so.6(+0x10698c)[0x7f7f7b71d98c] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f7efdea05db): delete from `jobs` where `id` = 808933436 Connection ID (thread ID): 2 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off,cset_narrowing=off The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 1031460 1031460 processes Max open files 91983 91983 files Max locked memory 8388608 8388608 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 1031460 1031460 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core Kernel version: Linux version 6.4.12-uls (root@sysrescue) (gcc (Gentoo 13.2.1_p20230826 p7) 13.2.1 20230826, GNU ld (Gentoo 2.41 p2) 2.41.0) #2 SMP PREEMPT_DYNAMIC Thu Jan 4 20:10:49 SAST 2024 I'm unable to locate the referenced core file or I'd already have pulled that into gdb. Any advise or pointers appreciated. I do notice there are galera updates, and a number of mariadb upgrades available. For galera this looks like improvements in logging, so probably not the cause of the crash, and for mariadb in 10.6.18 these items stand out for me: Server crashes in JOIN_CACHE::write_record_data upon EXPLAIN with subqueries and constant tables (MDEV-21102 <https://jira.mariadb.org/browse/MDEV-21102>) - but the stack trace doesn't match. Server crash in Rows_log_event::update_sequence upon replaying binary log (MDEV-31779 <https://jira.mariadb.org/browse/MDEV-31779>) - this looks feasible. If anyone can confirm my conjecture that would be great. We'd want to test 10.11 or even 11.4 in a non-production environment before making a big jump to one of those versions in production. Kind regards, Jaco

1 0