fix InnoDB: corrupted TRX_NO after upgrading to 10.3.31

Question

After upgrading mariadb from 10.3.25 to 10.3.31 it fails to start like described in this bug report

I temporary fixed it by downgrading mariadb to 10.3.25 again like I described here.

To fix this completely, it seems I have to

1. mysqldump -p --all-databases>/tmp/all.sql
2. delete all tables
3. upgrade mariadb
4. reimport the dump

But that seems like a lot of work and dangerous. Also I get an error while exporting with mysqldump:

mysqldump: Couldn't execute 'SELECT `Vorname`, `Name`, `Stra?e` FROM `adresses`':
  Unknown column 'Stra?e' in 'field list' (1054)

There seemed to be a UTF-8 encoding problem. It should be "Straße" with a german sharp "s" I deleted that very old table and the error is gone.

I could wait a few months and see if a newer version will still produce that error. Is there any hope, they will fix this in a future version? Or is it something corrupt in my current database? Maybe the UTF-8 problem was the cause already?

I will try upgrading again, when I have the time and see if the corrupted TRX_NO error is gone too.

I'll keep you updated...

My question:
Is there another way to fix the corrupted TRX_NO error than dumping the whole database?

Answer 1

I ran into a similar issue. I used this link in order to add a new mariadb repository. Indeed, this repository should contain the mariadb fix and the right mariadb version (for mariadb 10.3.X).

Here is how I solved this problem (you may need a mysql dump; be sure to have one):

    # checking mariadb version before uninstall
    dpkg -l |grep mariadb-server-10|grep ii|xargs|cut -d" " -f3
    cp -rp /etc/mysql /etc/mysql.bck
    cp -rp /var/lib/mysql /var/lib/mysql.bck
    sudo apt-get install software-properties-common
    release=`lsb_release -c --short`
    distro=`lsb_release -i --short |tr [:upper:] [:lower:]`
    sudo add-apt-repository "deb [arch=amd64,arm64,ppc64el] http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.3/$distro $release main"
    sudo apt remove -y mysql-\* mariadb-\* galera-\* libmysqlclient libmariadb3
    sudo apt clean && sudo apt autoremove
    sudo apt update 2>/dev/null |awk '/NO_PUBKEY/ {print $NF}' |xargs -I{} sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0x{}
    sudo apt update && sudo apt install -y mariadb-server
    # checking the new mariadb version
    dpkg -l |grep mariadb-server-10|grep ii|xargs|cut -d" " -f3
    systemctl restart mariadb
    # seems to be ok. Upgrading, restoring, upgrading...
    mysql_upgrade -uroot -p --force
    # if it is ok, no need to restore a dump.

    # otherwise
    mysql -uroot -p < /path/to/your/backup
    mysql_upgrade -uroot -p --force
    systemctl restart mariadb
    ps aux |grep -E "mysql|maria"

I hope it could help...

Thanks,

Rémy