- discuss - lists.mariadb.org

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

mysql_native_password authentication plugin
by cyusedfzfb 06 Oct '24

06 Oct '24

Hi, Just a quick question: we understand that mysql_native_password authentication plugin has become deprecated, and yes: we are in the process of moving to caching_sha2_password. We read everywhere that mysql_native_password is "subject to removal in a future version of MySQL" Can anyone say how soon that removal is going to be? (as for now it seems just a change of the default authentication mechanism, and I cannot find a date/year/version for *actual* removal) (and yes I did google) :-) Thanks!

3 2

Cannot backup database
by Marc Chamberlin 06 Oct '24

06 Oct '24

Hello - I am wanting to backup my databases and until now I haven't experienced any difficulties. I am working on an OpenSuSE 15.4 system and wanting to upgrade it which is why I want to backup the databases first. The version of mariadb is : > >> quasar:/srv/mysql #mariadb -V >> mariadb Ver 15.1 Distrib 10.6.15-MariaDB, for Linux (x86_64) using >> EditLine wrapper >> > But now I am hitting a new snag, when I try to run the backup script I > get the following response - > >> quasar:/srv/mysql #./mysqlbackup.sh >> mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ >> `EVENT_CATALOG`, `EVENT_SCHEMA`, `EVENT_NAME`, `DEFINER`, >> `TIME_ZONE`, `EVENT_BODY`, `EVENT_DEFINIT >> ION`, `EVENT_TYPE`, `EXECUTE_AT`, `INTERVAL_VALUE`, `INTERVAL_FIELD`, >> `SQL_MODE`, `STARTS`, `ENDS`, `STATUS`, `ON_COMPLETION`, `CREATED`, >> `LAST_ALTERED`, `LAST_ >> EXECUTED`, `EVENT_COMMENT`, `ORIGINATOR`, `CHARACTER_SET_CLIENT`, >> `COLLATION_CONNECTION`, `DATABASE_COLLATION` FROM `EVENTS`': Cannot >> proceed, because event sch >> eduler is disabled (1577) >> > The mysql.log file is showing these 2 error messages also - > >> 2024-10-05 14:43:30 0 [ERROR] Incorrect definition of table >> mysql.event: expected column 'definer' at position 3 to have type >> varchar(, found type char(141). >> 2024-10-05 14:43:30 0 [ERROR] mysqld: Event Scheduler: An error >> occurred when initializing system tables. Disabling the Event Scheduler. > My Duck Duck Go searches on the internet suggested I put the following > line in the file my.cnf > >> event_scheduler=ON > but no joy. Also, most of my internet searches hit on solutions that > were designed for running mariadb in a docker container. I am NOT > using docker, don't even know how to, and don't want solutions that > require docker. Too much to try an grok at this time for me. > > One interesting item of note is that on OpenSuSE (at least) the > systemd file mariadb.service runs both the install and the upgrade > services before starting the actual mysql daemon. Here is an excerpt > from mariadb.service that shows this - > >> [Service] >> ExecStartPre=/usr/lib/mysql/mysql-systemd-helper install >> ExecStartPre=/usr/lib/mysql/mysql-systemd-helper upgrade >> ExecStart=/usr/lib/mysql/mysql-systemd-helper start > Anyone got any bright and wonderful ideas on how to make mariadb a > happy camper that will allow me to do a database backup? > > Many thanks in advance for those who can guide me back to the mariadb > light! Marc C... > >

2 1

Re: Migrate MySQL5.7 to MariaDB 10.4, then to MariaDB 10.11 Failed
by Edwin Chan 04 Oct '24

04 Oct '24

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows

1 0

Re: Migrate MySQL5.7 to MariaDB 10.4, then to MariaDB 10.11 Failed
by Edwin Chan 04 Oct '24

04 Oct '24

Hi Marko, Attached the mysqld.dmp. Thanks. Regards, Edwin Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows

1 0

unexpected swap usage for mysqld (but mariadb) process
by cyusedfzfb 29 Sep '24

29 Sep '24

Hi all! New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space. Look at this example: RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654: > [user@db ~]$ free -g > total used free shared buff/cache > available > Mem: 187 9 11 0 165 175 > Swap: 3 3 0 and: > top - 08:52:46 up 39 days, 15:50, 2 users, load average: 1.70, 2.05, > 1.99 > Tasks: 616 total, 2 running, 614 sleeping, 0 stopped, 0 zombie > %Cpu(s): 6.0 us, 0.8 sy, 0.0 ni, 88.2 id, 4.7 wa, 0.1 hi, 0.1 > si, 0.0 st > MiB Mem : 191529.7 total, 11761.7 free, 9764.9 used, 170003.1 > buff/cache > MiB Swap: 4096.0 total, 1006.1 free, 3089.9 used. 179429.5 avail > Mem > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ > COMMAND *SWAP * > 1952811 mysql 20 0 14.4g 5.0g 15172 S 65.7 2.7 9663:14 > mysqld* 1.7g * > 2934963 root 20 0 5208 2056 1408 S 8.9 0.0 10:39.29 > gzip 0 > 2934962 root 20 0 33612 8268 6960 S 7.9 0.0 2:35.31 > mysqldump 0 while: > [user@db ~]$ cat /proc/sys/vm/swappiness > 1 We could (we should..) allocate more ram to mariadb, but the point is: there is plenty of RAM available, and there always has been, since boot. And yet, mariadb is using all swap. I guess this has no performance impact, probably the swapped pages are not actually used, but zabbix does not like 100% swap usage. And frankly: neither do I. Why? Can anyone here explain? Swappiness set to 1...is mariadb ignoring swappiness..? Is there something else we can configure..? I read some older posts on ram/swap, and here is some data, requested in that older thread: > [user@db ~]$ sudo ps -o pid,vsz,comm `pgrep mysqld` > PID VSZ COMMAND > 1952811 15117612 mysqld and > [user@db ~]$ for file in /proc/*/status ; do awk > '/VmSwap|Name/{printf $2 " " $3}END{ > > print ""}' $file; done | sort -k 2 -n -r | grep mysql > mysqld 1982672 kB We are seeing similar behaviour on another machines, running more recent mariadb version: 10.5.22-MariaDB MariaDB Server: > [user@db_another ~]$ free -g > total used free shared buff/cache > available > Mem: 62 40 0 1 21 20 > Swap: 4 3 1 Again: enough RAM available, and swap is used 3/4, mostly by MariaDB. Hope the provided information is enough. Let me know if there is anything else I can provide. Looking forward for any insight or help on the matter. :-) Thanks! MJ

5 7

InnoDB database corrupt after unplaned server restart
by Rainer Dorsch 22 Sep '24

22 Sep '24

Hello, I am using mariadb as backend for nextcloud. After an unplanned server restart by my provider I see in the log this error message repeating many times: Sep 21 23:14:06 contabo.bokomoko.de mariadbd[267649]: 2024-09-21 23:14:06 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/ kb/en/library/innodb-recovery-modes/ for information about forcing recovery. Sep 21 23:14:06 contabo.bokomoko.de mariadbd[267649]: 2024-09-21 23:14:06 0 [ERROR] InnoDB: Page [page id: space=0, page number=300] log sequence number 572290405174 is in the future! Current system log sequence number 572290220157. I have a backup of /var/lib/mysql from before the issue. The files which are currently in /var/lib/mysql seem to be stuck with a timestamp from immediately after the reboot. Can anybody advise on a good recovery strategy? Any hint or advice is welcome. Many thanks Rainer -- Rainer Dorsch http://bokomoko.de/

1 0

Database schema error while upgrading Koha 24.05 in Ubuntu 24.04 Noble LTS
by Md Monirul Islam Ansari 20 Sep '24

20 Sep '24

Dear folks, Greetings from Monirul this side, During an upgrade of Koha to version 24.05.03-1, the process failed due to a database access error. The upgrade script was unable to connect to the MySQL/MariaDB database, suggesting a potential issue with database permissions or configuration. Steps to Reproduce: Attempt to upgrade Koha-common package to version 24.05.03-1 Run sudo apt-get install mariadb-server Confirm the installation/upgrade process Observed Behavior: The upgrade process fails during the database schema upgrade step An error message indicates that access is denied for the Koha database user Error Message: "DBIx::Class::Storage::DBI::catch {...} (): DBI Connection failed: DBI connect('database=koha_monirul;host=localhost;port=3306','koha_monirul',...) failed: Access denied for user 'koha_monirul'@'localhost' (using password: YES) at /usr/share/koha/lib/Koha/Database.pm line 91. at /usr/share/koha/lib/Koha/Database.pm line 139" Environment: Koha version being upgraded to: 24.05.03-1 Database: MariaDB (version 1:10.11.8-0ubuntu0.24.04.1) Operating System: Ubuntu (likely 24.04 based on package versions)

2 1

Joining two tables on a list of values
by Gingko 14 Sep '24

14 Sep '24

Hello, I would like to know if is there some (efficient!) way to join two tables on a list of values contained in (likely a single column) of the joined table, whatever form could have this list of values ? For example, I have a main table containing: main_table: +------------+------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+------------+----------------+ | index | int(11) | NO | PRI | 0 | | | to_join | int(11) | NO | | 0 | | | <other fields, irrelevant> | | || | +------------+------------------+------+-----+------------+----------------+ … and a secondary table containing: joined_table: +------------+------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+------------+----------------+ | index | int(11) | NO | PRI | | auto_increment | | int_list | <list_of_ints> | YES | | (empty) | | | <other fields, irrelevant> | | | | | +------------+------------------+------+-----+------------+----------------+ We could suppose, here, as a simple test case, that main_table has five rows where to_join contains respectively 1, 2, 3, 4, and 5, and the joined_table has a single row with the single column int_list combining 2 and 4 in any form (but of course, this list could actually have any length). I'd like to join these tables using a statement like: SELECT <anything> FROM main_table AS M LEFT JOIN joined_table AS J ON M.to_join <some_operator to> J.int_list; … in such a way that main_table would be joined to joined_table using any value contained in int_list, and the statement is expected to give two rows as a result. I tried to set int_list as a TINYTEXT containing "2,4" and use: SELECT <anything> FROM main_table AS M LEFT JOIN joined_table AS J ON M.to_join IN (J.int_list); … but this doesn't work, of course, my comma separated list is seen as a whole single value. I also tried, still using TINYTEXT, the list as "[2][4]" and use: SELECT <anything> FROM main_table AS M LEFT JOIN joined_table AS J ON J.int_listLIKE CONCAT('%[', M.to_join , ']%'); This works (more or less), but it is excruciatingly slow, especially knowing that main_table can actually contain millions of rows, and joined_table several thousands. Is there a better solution for doing that? As the final goal implies, somewhere later, associating to_join in main_table, via int_list, to the index auto increment primary key of the joined_table, using multiple rows there is not an option. Regards, Gingko

5 5