- discuss - lists.mariadb.org

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

MariaDB ServerFest in Berlin on September 17
by Kristian Nielsen 26 Aug '24

26 Aug '24

The next MariaDB ServerFest will take place in Berlin, Germany. The event is on September 17, 2024, and is generously hosted by MariaDB Foundation sponsor Ionos: https://mariadb.org/fest-2024-berlin/ The event is open to all. I was at the MariaDB ServerFest in Helsinki last Autumn, and I really liked the close interaction with the many interesting participants: big and advanced users, developers, university students, service providers, etc. It is an excellent opportunity to meet many of the core developers, and hopefully a lot of other great people! I will be presenting at the event myself; my talk is titled: "MariaDB Replication – Fantastically Flexible and Fragile". I very much welcome questions during my talks, so feel free to prepare questions for me about anything replication. - Kristian.

2 1

MariaDB server crash status=11/SEGV on version 10.6.19
by zxc072211＠gmail.com 21 Aug '24

21 Aug '24

Hi, First, I apologize if my English isn't perfect. After upgrading my MariaDB server from version 10.6.18 to 10.6.19. I encountered an issue. When I execute a "SELECT ... FROM ( SELECT ... ) x WHERE x.colume1 .." statement, the server returns the error: "ERROR 2013 (HY000): Lost connection to server during query." I checked the `/var/log/messages` and found this message: "mariadb.service: Main process exited, code=killed, status=11/SEGV.". I used core dumping to investigate further and found that the stack frame count reaches #12171. The error at #0 is "... Item::build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item.h:1928". The previous stack frames keep repeating two frames: #1 Item_func_case_simple::do_build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item_cmpfunc.h:2448 #2 ... Item::build_clone ... at /usr/src/debug/MariaDB-/src_0/sql/item.h:1928 My OS is Rocky Linux 8.10. The MariaDB server versions where I encountered this issue are 10.6.19 and 10.11.9. The MariaDB server version without issues is 10.6.18. Has anyone else encountered this problem? Thanks in advance for any help.

2 2