- discuss - lists.mariadb.org

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

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