Ubuntu server 22.04 LTS 64 bit AMD

michal25, What was the previous version you where using? What was the original version where the datadir was created? Are you running mysql_upgrade/mariadb_upgrade?

Do you have any special authentication configurations? The crash is loading from the authentication table.

Can you perform the following to get more information:

add-apt-repository 'deb [arch=amd64,arm64,ppc64el,s390x]  https://ftp.osuosl.org/pub/mariadb/repo/10.7/ubuntu jammy main/debug'

apt-get install -y mariadb-server-core-10.7-dbgsym gdb

sudo -u mysql gdb --args mariadbd

# on gdb prompt:

r

 bt  -frame-arguments all full

Previous version 10.3.34 (Ubuntu 20.04 server, LTS 64 bit AMD). Worked fine.
Not using mysql_upgrade/mariadb_upgrade
No special authentication configurations

Original version - somewhere on Ubuntu 16.04

Do you have a log from the very first crash? Or better yet, the last 10.3 session, shutdown, and 10.7 startup with the crash.
What the attached log contains is already an aftermath, it's crash recovery.

Last function log with the 10.3.34 version and last functionaly mysql config file.

I'm going to proceed the
https://jira.mariadb.org/browse/MDEV-29921?focusedCommentId=239811&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-239811
steps. error.log mysql.conf.old

I had to use this script and repository to correctly install Mariadb 10.7
https://computingforgeeks.com/how-to-install-mariadb-database-on-ubuntu-linux/

curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
sudo bash mariadb_repo_setup --mariadb-server-version=10.7

Here is the output:

1. sudo -u mysql gdb --args mariadbd
   GNU gdb (Ubuntu 12.1-0ubuntu1~22.04) 12.1
   Copyright (C) 2022 Free Software Foundation, Inc.
   License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
   This is free software: you are free to change and redistribute it.
   There is NO WARRANTY, to the extent permitted by law.
   Type "show copying" and "show warranty" for details.
   This GDB was configured as "x86_64-linux-gnu".
   Type "show configuration" for configuration details.
   For bug reporting instructions, please see:
   <https://www.gnu.org/software/gdb/bugs/>.
   Find the GDB manual and other documentation resources online at:
   <http://www.gnu.org/software/gdb/documentation/>.

For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from mariadbd...
Reading symbols from /usr/lib/debug/.build-id/85/403077df183eb2753bb75059874eb7ddbf546d.debug...
(gdb) r
Starting program: /usr/sbin/mariadbd
During startup program exited with code 1.
(gdb) bt -frame-arguments all full
No stack.
(gdb)

Last function log with the 10.3.34 version

Thanks. What about the very first crash on 10.7?

As we can see, 10.3 log error.log ends with a normal shutdown

2022-10-31 14:41:25 0 [Note] InnoDB: Shutdown completed; log sequence number 12334837215; transaction id 71917290

2022-10-31 14:41:25 0 [Note] /usr/sbin/mysqld: Shutdown complete

but 10.7 log mariadb-err.txt exhibits crash recovery

...

říj 31 22:07:10 snehurka mariadbd[100865]: 2022-10-31 22:07:10 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=13571,13571

říj 31 22:07:10 snehurka mariadbd[100865]: 2022-10-31 22:07:10 0 [Note] InnoDB: Starting final batch to recover  3 pages from redo log.

If there is a crash recovery, there must have been a crash at some point between 14:41 and 22:07. Probably there were a number of crashes, but the first one should have occurred without crash recovery, upon or after a normal startup. It could have happened right during the upgrade process, under the hood, but there should still be a log of it.

Can I ask for some ideas how to repair the crash?

I have backup for all databases, but the clean install if mariadb and database restore will take more time. And this crash can occure again.

I only made the ragular OS upgrade (from console) from ubuntu server 20.04 LTS 64 bit AMD to ubuntu 22.04 64 bit AMD and this mariadb crash is the result.

I did not find more logs. It looks that the upgrade process stopped the rsyslogd daemon. I had to restart it after upgrade.

Can I ask for some ideas how to repair the crash?

It's difficult (and somewhat dangerous) to give advice blindly, but maybe you could try to run myisamchk or aria_chk on your system tables, those in mysql schema (the choice of the tool depends on whether they are still MyISAM as they came from 10.3, or they got converted to Aria already, as Ubuntu installation could have run mysql_upgrade automatically without your knowing it). If the check reveals any problems, you could try to do repair.
Make sure to create a backup (a physical copy) of mysql schema before doing so. The tools are able to backup tables they are touching, but it's always better to do it explicitly. The mysql schema shouldn't be big.

Alternatively, you can try to start the server with -skip-grant-tables and, if it starts, to look around, run CHECK TABLE .. EXTENDED on system tables and REPAIR if needed.

It's possible that you may lose some contents of system tables upon repair, but if you have a backup, you can later take the lost data from it, only in system tables, it shouldn't take as long as restoring the whole instance.

I found the log in /var/log/daemon.log (stupid rsyslogd). I will post it.

Here is the log with crash of mariadb 10.6.7 after upgrade from Ubuntu server 20.04 to ubuntu server 22.04. Previous version of mariadb was 10.3 and worked fine. mariadb-daemon.log

Thanks.

So, it failed on 10.6.7 already, and the original crash was the same as subsequent ones, in

Oct 31 15:25:25 snehurka mariadbd[142393]: ??:0(my_print_stacktrace)[0x55dac80ce702]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(handle_fatal_signal)[0x55dac7c094d8]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(__sigaction)[0x7f3eda91d520]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(create_virtual_tmp_table(THD*, List<Spvar_definition>&))[0x55dac795edae]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(acl_reload(THD*))[0x55dac7972783]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(acl_init(bool))[0x55dac797393e]

Oct 31 15:25:26 snehurka mariadbd[142393]: ??:0(mysqld_main(int, char**))[0x55dac792b7d6]

(10.6.7-MariaDB-2ubuntu1.1 is MariaDB server built by Ubuntu and coming from Ubuntu repos, even though the suffix looks odd).

I've tried the whole routine – installing 10.3 on Ubuntu 20.04 and upgrading to Ubuntu 22.04 which pulls 10.6.7 with it, – but didn't get a crash, it upgraded seemingly all right. So, it's not just the upgrade as such, either something specific to the data or to the environment.

I assume you've checked your disks and system logs for signs of corruption and other hardware failures?

Server HW looks OK.

It looks to corrupted InnoDB files.

At this moment I trying
innodb-force-recovery

included in local configuration file
/etc/mysql/conf.d/mysql.cnf
[mysql]
#Doplnil Michal dne 1.11.2022
innodb-force-recovery=2

Value 1 in innodb-force-recovery had no effect.

Bad luck
innodb-force-recovery=1,2,3,4,5

Has no effect.

InnoDB may have corrupt tables (especially if a massive corruption occurred on whatever reason), but it is unlikely to be the cause of failing acl load – unless of course you previously converted system tables to InnoDB, which some people do.

Did you try to run check on system tables?

But how?
~# systemctl start mysql
Job for mariadb.service failed because a fatal signal was delivered causing the control process to dump core.
See "systemctl status mariadb.service" and "journalctl -xeu mariadb.service" for details.

~# mysqlcheck --all-databases
mysqlcheck: Got error: 2002: Can't connect to local server through socket '/run/mysqld/mysqld.sock' (111) when trying to connect

As said in the comment above – not mysqlcheck, but myisamchk or aria_chk (depending on whether the tables are MYD/MYI or MAD/MAI). These tools work with on-disk files, not with the running database instance.
Alternatively, by trying to run the server with --skip-grant-tables.

You re right
/etc/mysql/my.cnf
skip-grant-tables

And ....
~# systemctl status mariadb.service
● mariadb.service - MariaDB 10.7.6 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Tue 2022-11-01 18:00:47 CET; 7s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 364450 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 364451 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 364453 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemc>
Process: 364497 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 364499 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 364485 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 13 (limit: 4390)
Memory: 80.8M
CPU: 4.846s
CGroup: /system.slice/mariadb.service
└─364485 /usr/sbin/mariadbd

lis 01 18:00:47 snehurka mariadbd[364485]: 2022-11-01 18:00:47 0 [Note] Plugin 'FEEDBACK' is disabled.
lis 01 18:00:47 snehurka mariadbd[364485]: 2022-11-01 18:00:47 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
lis 01 18:00:47 snehurka mariadbd[364485]: 2022-11-01 18:00:47 0 [Note] Server socket created on IP: '127.0.0.1'.
lis 01 18:00:47 snehurka mariadbd[364485]: 2022-11-01 18:00:47 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysq>
lis 01 18:00:47 snehurka mariadbd[364485]: 2022-11-01 18:00:47 0 [Note] /usr/sbin/mariadbd: ready for connections.
lis 01 18:00:47 snehurka mariadbd[364485]: Version: '10.7.6-MariaDB-1:10.7.6+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distr>
lis 01 18:00:47 snehurka systemd[1]: Started MariaDB 10.7.6 database server.
lis 01 18:00:47 snehurka /etc/mysql/debian-start[364501]: Upgrading MySQL tables if necessary.
lis 01 18:00:47 snehurka /etc/mysql/debian-start[364504]: /usr/bin/mysql_upgrade: unknown option '--skip-grant-tables'
lis 01 18:00:47 snehurka /etc/mysql/debian-start[364505]: Checking for insecure root accounts.

I tried
skip-grant-tables

yesterday, but in another config section.

I'm going to inspect the database.

Some tables have broken .frm files

redmine.wiki_redirects
Error : Table 'redmine.wiki_redirects' doesn't exist in engine

The table exists on the filesystem.

Any idea to repair?

The problem is partially solved.
/etc/mysql/my.cnf
[mysqld]
skip-grant-tables

after this
mysql -u root -p
mysql> FLUSH PRIVILEGES;

back to bash

I had to (manualy) drop/erase each broken database, create the database again and fill it from the backup file.

But, here is a problem with the "mysql" database which is a system file. And the drop/create/restore method will not work.

Look at the attached file table-mysql.err

How to repar?
The StellarRepairforMySQL-U.deb utility is unusable here (dependencies on some libmysql library, which is missing on the mariadb instance).

The four broken system tables in the attachment are all InnoDB tables. Most likely they don't contain anything of the essence, unless you were running traditional replication based on GTID and/or had transaction-precision system-versioned tables. And statistics is probably outdated anyway. So, you can drop and re-create these tables.
It is unclear though how they would affect acl loading.

Upd:
Sorry our comments crossed. What do you mean by "delete/recreate does not work"? The tables cannot be dropped?

Apologize
The database mysql can't be dropped.

I'm going to try to drop and recreate the 5 mysql broken tables.

Grrrrr.

Can you help me, please?

How to recreate a InnoDB table in the mysql database from the console?

It works like this?

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

Thank you very much.

Yes, except that of course you should take the exact table definition for those system tables, to avoid further problems.

So, this way?

https://dba.stackexchange.com/questions/54608/innodb-error-table-mysql-innodb-table-stats-not-found-after-upgrade-to-mys#54624

You need definitions from MariaDB 10.x (whichever x you are using now, 10.7 or 10.6 or something else). E.g. for 10.7 you can find them here https://github.com/MariaDB/server/blob/2092881984117b9e10b5d2afc8d387ec90199287/scripts/mysql_system_tables.sql , same idea for 10.6.

The easier way could be after dropping the tables to re-run mysql_upgrade (with --force if necessary), it should re-create the missing tables.

Done, but with errors.
mysql-upgrade--force.err

They didn't get dropped properly then.

mysql> flush tables;

is not helping.

But I can start the mariadb environment without the

/etc/mysql/my.cnf
[mysqld]
skip-grant-tables

Thank you very much!

SOLVED!

systemctl stop mysql
manually deleted all the broken database tables in
/var/lib/mysql/mysql

systemctl start mysql

mysql_upgrade -u root -p --force

Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
.....
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

Broken tables in /var/lib/mysql/mysql
has been recreated and mariadb is working fine.