September 2024 - discuss - lists.mariadb.org

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

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