2

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?

14
  • You dont need anyone - docker itself helps you docs.docker.com/engine/reference/commandline/exec
    – gapsf
    Oct 1, 2022 at 14:23
  • Thank you. I was aware of exec but mysqld --tc-heuristic-recover=ROLLBACK doesn't work inside the container: mysql: unknown variable 'tc-heuristic-recover=ROLLBACK' I found this guide, link but systemctl doesn't work inside the docker container either... I'm a bit lost.
    – derhannes
    Oct 2, 2022 at 5:40
  • > 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 . And now you say you know it. If you was know you should ask about real problem and dont fool others. Execute /bin/sh in container and execute it interactivly. There is no systemd in your docker container. You may find out what you have in your container from shell
    – gapsf
    Oct 2, 2022 at 5:59
  • I didn't want to fool anyone. I was just using the wrong command (mysql instead of mysqld). See my edit above. Unfortunately it still doesn't work...
    – derhannes
    Oct 2, 2022 at 6:04
  • What type mysqld and mysqld --version says?
    – gapsf
    Oct 2, 2022 at 6:18

2 Answers 2

2

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.

2
  • In my case the mysql.cnf file was located in .devilbox/cfg. Following your steps fixed my issue in my Devilbox container. Thanks! May 3, 2023 at 8:42
  • Good! It works for me. +1 to you
    – alfredo
    Nov 10, 2023 at 16:09
0

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!

1
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 4, 2023 at 20:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .