MariaDB --tc-heuristic-recover in Docker container

Question

I have a MariaDB database in a docker container for my home automation (homeassistant). For some reason it stopped working and I get this error in the docker system logs:

221001 12:27:03 mysqld_safe Logging to '/config/databases/4ff0eb6f2180.err'.
221001 12:27:03 mysqld_safe Starting mariadbd daemon with databases from /config/databases

The MariaDB-logs named in the error are:

221001 12:27:03 mysqld_safe Starting mariadbd daemon with databases from /config/databases
2022-10-01 12:27:03 0 [Note] /usr/bin/mariadbd (mysqld 10.5.17-MariaDB-log) starting as process 23782 ...
2022-10-01 12:27:03 0 [Note] InnoDB: Uses event mutexes
2022-10-01 12:27:03 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-10-01 12:27:03 0 [Note] InnoDB: Number of pools: 1
2022-10-01 12:27:03 0 [Note] InnoDB: Using ARMv8 crc32 + pmull instructions
2022-10-01 12:27:03 0 [Note] InnoDB: Using Linux native AIO
2022-10-01 12:27:03 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728
2022-10-01 12:27:03 0 [Note] InnoDB: Completed initialization of buffer pool
2022-10-01 12:27:04 0 [Note] InnoDB: Transaction 369240 was in the XA prepared state.
2022-10-01 12:27:04 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
2022-10-01 12:27:04 0 [Note] InnoDB: Trx id counter is 369241
2022-10-01 12:27:04 0 [Note] InnoDB: 128 rollback segments are active.
2022-10-01 12:27:04 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
2022-10-01 12:27:04 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2022-10-01 12:27:04 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-10-01 12:27:04 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-10-01 12:27:04 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-10-01 12:27:04 0 [Note] InnoDB: 10.5.17 started; log sequence number 490048287; transaction id 369242
2022-10-01 12:27:04 0 [Note] InnoDB: Loading buffer pool(s) from /config/databases/ib_buffer_pool
2022-10-01 12:27:04 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-10-01 12:27:04 0 [Note] InnoDB: Starting recovery for XA transactions...
2022-10-01 12:27:04 0 [Note] InnoDB: Transaction 369240 in prepared state after recovery
2022-10-01 12:27:04 0 [Note] InnoDB: Transaction contains changes to 31 rows
2022-10-01 12:27:04 0 [Note] InnoDB: 1 transactions in prepared state after recovery
2022-10-01 12:27:04 0 [Note] Found 1 prepared transaction(s) in InnoDB
2022-10-01 12:27:04 0 [ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.
2022-10-01 12:27:04 0 [ERROR] Aborting
221001 12:27:05 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I read the advice about running MariaDB with --tc-heuristic-recover but I don't know how to do that when I run MariaDB in a docker container? docker inspect gives this information:

{
    "AppArmorProfile": "docker-default",
    "Args": [],
    "Config": {
        "AttachStderr": true,
        "AttachStdin": false,
        "AttachStdout": true,
        "Cmd": null,
        "Domainname": "",
        "Entrypoint": [
            "/init"
        ],
        "Env": [
            "DATADIR=/config/databases",
            "HOME=/root",
            "MYSQL_DATABASE=homeassistant",
            "MYSQL_DIR=/config",
            "MYSQL_PASSWORD=secret",
            "MYSQL_ROOT_PASSWORD=secret",
            "MYSQL_USER=homeassistant",
            "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
            "PGID=1000",
            "PS1=$(whoami)@$(hostname):$(pwd)\\$",
            "PUID=1000",
            "S6_CMD_WAIT_FOR_SERVICES_MAXTIME=0",
            "TERM=xterm",
            "TZ=Europe/Berlin"
        ],
        "ExposedPorts": {
            "3306/tcp": {}
        },
        "Hostname": "4ff0eb6f2180",
        "Image": "lscr.io/linuxserver/mariadb:latest",
        "Labels": {
            "build_version": "Linuxserver.io version:- 10.5.17-r0-ls68 Build-date:- 2022-09-03T07:55:37+02:00",
            "com.docker.compose.config-hash": "b3afe9f3eaf0c52ad2925d1ccd1c5c43bf58d7c50e742dd26e278f562231f231",
            "com.docker.compose.container-number": "1",
            "com.docker.compose.depends_on": "",
            "com.docker.compose.image": "sha256:61d2da9c65c5d8d9803c6d818e20246f4048ad701636931b2c05e4ac68d77416",
            "com.docker.compose.oneoff": "False",
            "com.docker.compose.project": "homeassistant_v01",
            "com.docker.compose.project.config_files": "/data/compose/17/docker-compose.yml",
            "com.docker.compose.project.working_dir": "/data/compose/17",
            "com.docker.compose.service": "mariadb",
            "com.docker.compose.version": "2.5.1",
            "maintainer": "thelamer,nemchik",
            "org.opencontainers.image.authors": "linuxserver.io",
            "org.opencontainers.image.created": "2022-09-03T07:55:37+02:00",
            "org.opencontainers.image.description": "[Mariadb](https://mariadb.org/) is one of the most popular database servers. Made by the original developers of MySQL.",
            "org.opencontainers.image.documentation": "https://docs.linuxserver.io/images/docker-mariadb",
            "org.opencontainers.image.licenses": "GPL-3.0-only",
            "org.opencontainers.image.ref.name": "d88e223be6ce6f69fce9a2d2867a0e06b4c1cbf8",
            "org.opencontainers.image.revision": "d88e223be6ce6f69fce9a2d2867a0e06b4c1cbf8",
            "org.opencontainers.image.source": "https://github.com/linuxserver/docker-mariadb",
            "org.opencontainers.image.title": "Mariadb",
            "org.opencontainers.image.url": "https://github.com/linuxserver/docker-mariadb/packages",
            "org.opencontainers.image.vendor": "linuxserver.io",
            "org.opencontainers.image.version": "10.5.17-r0-ls68"
        },
        "OnBuild": null,
        "OpenStdin": false,
        "StdinOnce": false,
        "Tty": false,
        "User": "",
        "Volumes": {
            "/config": {}
        },
        "WorkingDir": ""
    },
    "Created": "2022-10-01T10:23:40.712799794Z",
    "Driver": "btrfs",
    "ExecIDs": null,
    "GraphDriver": {
        "Data": null,
        "Name": "btrfs"
    },
    "HostConfig": {
        "AutoRemove": false,
        "Binds": [
            "/srv/dev-disk-by-uuid-3d0770af-49cd-445c-911f-4c07ee490055/homeassistant/mariadb/config:/config"
        ],
        "BlkioDeviceReadBps": null,
        "BlkioDeviceReadIOps": null,
        "BlkioDeviceWriteBps": null,
        "BlkioDeviceWriteIOps": null,
        "BlkioWeight": 0,
        "BlkioWeightDevice": null,
        "CapAdd": [
            "AUDIT_WRITE",
            "CHOWN",
            "DAC_OVERRIDE",
            "FOWNER",
            "FSETID",
            "KILL",
            "MKNOD",
            "NET_BIND_SERVICE",
            "NET_RAW",
            "SETFCAP",
            "SETGID",
            "SETPCAP",
            "SETUID",
            "SYS_CHROOT"
        ],
        "CapDrop": [
            "AUDIT_CONTROL",
            "BLOCK_SUSPEND",
            "DAC_READ_SEARCH",
            "IPC_LOCK",
            "IPC_OWNER",
            "LEASE",
            "LINUX_IMMUTABLE",
            "MAC_ADMIN",
            "MAC_OVERRIDE",
            "NET_ADMIN",
            "NET_BROADCAST",
            "SYSLOG",
            "SYS_ADMIN",
            "SYS_BOOT",
            "SYS_MODULE",
            "SYS_NICE",
            "SYS_PACCT",
            "SYS_PTRACE",
            "SYS_RAWIO",
            "SYS_RESOURCE",
            "SYS_TIME",
            "SYS_TTY_CONFIG",
            "WAKE_ALARM"
        ],
        "Cgroup": "",
        "CgroupParent": "",
        "CgroupnsMode": "private",
        "ConsoleSize": [
            0,
            0
        ],
        "ContainerIDFile": "",
        "CpuCount": 0,
        "CpuPercent": 0,
        "CpuPeriod": 0,
        "CpuQuota": 0,
        "CpuRealtimePeriod": 0,
        "CpuRealtimeRuntime": 0,
        "CpuShares": 0,
        "CpusetCpus": "",
        "CpusetMems": "",
        "DeviceCgroupRules": null,
        "DeviceRequests": null,
        "Devices": [],
        "Dns": [],
        "DnsOptions": null,
        "DnsSearch": null,
        "ExtraHosts": [],
        "GroupAdd": null,
        "IOMaximumBandwidth": 0,
        "IOMaximumIOps": 0,
        "IpcMode": "private",
        "Isolation": "",
        "KernelMemory": 0,
        "KernelMemoryTCP": 0,
        "Links": null,
        "LogConfig": {
            "Config": {},
            "Type": "json-file"
        },
        "MaskedPaths": [
            "/proc/asound",
            "/proc/acpi",
            "/proc/kcore",
            "/proc/keys",
            "/proc/latency_stats",
            "/proc/timer_list",
            "/proc/timer_stats",
            "/proc/sched_debug",
            "/proc/scsi",
            "/sys/firmware"
        ],
        "Memory": 0,
        "MemoryReservation": 0,
        "MemorySwap": 0,
        "MemorySwappiness": null,
        "NanoCpus": 0,
        "NetworkMode": "homeassistant_v01_default",
        "OomKillDisable": null,
        "OomScoreAdj": 0,
        "PidMode": "",
        "PidsLimit": null,
        "PortBindings": {
            "3306/tcp": [
                {
                    "HostIp": "",
                    "HostPort": "3306"
                }
            ]
        },
        "Privileged": false,
        "PublishAllPorts": false,
        "ReadonlyPaths": [
            "/proc/bus",
            "/proc/fs",
            "/proc/irq",
            "/proc/sys",
            "/proc/sysrq-trigger"
        ],
        "ReadonlyRootfs": false,
        "RestartPolicy": {
            "MaximumRetryCount": 0,
            "Name": "unless-stopped"
        },
        "Runtime": "runc",
        "SecurityOpt": null,
        "ShmSize": 67108864,
        "UTSMode": "",
        "Ulimits": null,
        "UsernsMode": "",
        "VolumeDriver": "",
        "VolumesFrom": null
    },
    "HostnamePath": "/var/lib/docker/containers/1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2/hostname",
    "HostsPath": "/var/lib/docker/containers/1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2/hosts",
    "Id": "1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2",
    "Image": "sha256:04f279970118194e0c69e676a2f4e9709bb55844347328745c2c8873ef98ea0b",
    "LogPath": "/var/lib/docker/containers/1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2/1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2-json.log",
    "MountLabel": "",
    "Mounts": [
        {
            "Destination": "/config",
            "Mode": "",
            "Propagation": "rprivate",
            "RW": true,
            "Source": "/srv/dev-disk-by-uuid-3d0770af-49cd-445c-911f-4c07ee490055/homeassistant/mariadb/config",
            "Type": "bind"
        }
    ],
    "Name": "/homeassistant_mariadb",
    "NetworkSettings": {
        "Bridge": "",
        "EndpointID": "",
        "Gateway": "",
        "GlobalIPv6Address": "",
        "GlobalIPv6PrefixLen": 0,
        "HairpinMode": false,
        "IPAddress": "",
        "IPPrefixLen": 0,
        "IPv6Gateway": "",
        "LinkLocalIPv6Address": "",
        "LinkLocalIPv6PrefixLen": 0,
        "MacAddress": "",
        "Networks": {
            "homeassistant_v01_default": {
                "Aliases": [
                    "4ff0eb6f2180",
                    "1b73ae323b60"
                ],
                "DriverOpts": null,
                "EndpointID": "",
                "Gateway": "",
                "GlobalIPv6Address": "",
                "GlobalIPv6PrefixLen": 0,
                "IPAMConfig": {},
                "IPAddress": "",
                "IPPrefixLen": 0,
                "IPv6Gateway": "",
                "Links": null,
                "MacAddress": "",
                "NetworkID": "a89c585fe120c5e6458874cf4dac7608c523c5068d8d0adaae6c64431612344a"
            }
        },
        "Ports": {},
        "SandboxID": "8141506cfff870e00b0934b63a3d27db57bee2afe314e143b0b36d93371cd89e",
        "SandboxKey": "/var/run/docker/netns/8141506cfff8",
        "SecondaryIPAddresses": null,
        "SecondaryIPv6Addresses": null
    },
    "Path": "/init",
    "Platform": "linux",
    "Portainer": {
        "ResourceControl": {
            "Id": 28,
            "ResourceId": "1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2",
            "SubResourceIds": [],
            "Type": 1,
            "UserAccesses": [],
            "TeamAccesses": [],
            "Public": false,
            "AdministratorsOnly": true,
            "System": false
        }
    },
    "ProcessLabel": "",
    "ResolvConfPath": "/var/lib/docker/containers/1b73ae323b60e061416b41bdd0a33771234c1cca36e396fa1abb40579d5925c2/resolv.conf",
    "RestartCount": 0,
    "State": {
        "Dead": false,
        "Error": "",
        "ExitCode": 0,
        "FinishedAt": "2022-10-01T10:27:08.35117112Z",
        "OOMKilled": false,
        "Paused": false,
        "Pid": 0,
        "Restarting": false,
        "Running": false,
        "StartedAt": "2022-10-01T10:23:41.548570547Z",
        "Status": "exited"
    }
}

Can anyone help me starting the container with --tc-heuristic-recover or running that inside the container? Or is there another problem that I'm not aware of?

EDIT I found this guide: link And found the problem, I wasn't using mysqld but mysql...

But unfortunately I get another error and it doesn't seem to work:

root@83fed82393ef:/# mysqld --user=root --tc-heuristic-recover=rollback
2022-10-02  7:55:56 0 [Note] mysqld (mysqld 10.5.17-MariaDB-log) starting as process 39871 ...
2022-10-02  7:55:56 0 [Note] InnoDB: Uses event mutexes
2022-10-02  7:55:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-10-02  7:55:56 0 [Note] InnoDB: Number of pools: 1
2022-10-02  7:55:56 0 [Note] InnoDB: Using ARMv8 crc32 + pmull instructions
2022-10-02  7:55:56 0 [Note] InnoDB: Using Linux native AIO
2022-10-02  7:55:56 0 [Note] InnoDB: Initializing buffer pool, total size = 268435456, chunk size = 134217728
2022-10-02  7:55:56 0 [Note] InnoDB: Completed initialization of buffer pool
2022-10-02  7:55:56 0 [Note] InnoDB: 128 rollback segments are active.
2022-10-02  7:55:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-10-02  7:55:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-10-02  7:55:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-10-02  7:55:56 0 [Note] InnoDB: 10.5.17 started; log sequence number 35052; transaction id 8
2022-10-02  7:55:56 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-10-02  7:55:56 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-10-02  7:55:56 0 [ERROR] Could not open mysql.plugin table: "Table 'mysql.plugin' doesn't exist". Some plugins may be not loaded
2022-10-02  7:55:56 0 [Note] Heuristic crash recovery mode
2022-10-02  7:55:56 0 [Note] Please restart mysqld without --tc-heuristic-recover
2022-10-02  7:55:56 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1017: Can't find file: './mysql/' (errno: 2 "No such file or directory")
2022-10-02  7:55:57 0 [Note] InnoDB: Buffer pool(s) load completed at 221002  7:55:57
2022-10-02  7:55:57 0 [ERROR] Can't init tc log
2022-10-02  7:55:57 0 [ERROR] Aborting

Any ideas?

Answer 1

The answer of Michiel actually helped me solving the same issue.

I have the following docker compose config

 my_db:
    image: linuxserver/mariadb
    container_name: mydb
    restart: unless-stopped
    environment:
     - PUID=1001
     - PGID=1001
     - MYSQL_ROOT_PASSWORD=mypass
     - TZ=Europe/Amsterdam
     - MYSQL_DATABASE=mydb
     - MYSQL_USER=myuser
     - MYSQL_PASSWORD=mypass
    volumes:
     - /hostmachine/myapplication/mariadb:/config
    ports:
     - 3306:3306

I navigated inside the host to the folder of mariadbm, so to this path my case hostmachine/myapplication/mariadb

That folder contained a custom.cnf, which I modified according to the answer of Michiel by adding this line: tc-heuristic-recover=rollback under the [mysqld] section.

So it looked something like

[mysqld]
#
# * Basic Settings
#
user            = abc
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking
tc-heuristic-recover=rollback

I restarted my docker container docker restart mydatabase

I checked the err file which showed the original error which now reporting something like

euristic crash recovery mode
2023-03-18 15:43:57 0 [Note] Please restart without --tc-heuristic-recover

So I modified the cnf back to its original state, restarted the container again and the database started to work again.

Answer 2

Edit your MaridDB .cnf file (custom.cnf in my case) and add tc-heuristic-recover=rollback underneath the [mysqld] section. Restart your MariaDB docker.. Wait a minute.. Edit the .cnf file again and comment out the line with a # (for next time it happens) #tc-heuristic-recover=rollback. Restart your MariaDB docker again. Should be fixed now!