0.11.3: Mysql error, constantly restarting

[TheNomad11]

I just updated from 0.11.2 to 0.11.3 and got 502 Bad Gateway

A look at the logs reveals following:

`
2024-05-09 11:21:08 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(141).

2024-05-09 11:21:08 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

2024-05-09 11:21:08 0 [Note] mariadbd: ready for connections.
Version: '11.3.2-MariaDB-1:11.3.2+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution

2024-05-09 11:21:10 3 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').

2024-05-09 11:21:10 3 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
`

[simjanos-dev]

I have no idea what the issue could be. There was no change in the database at all since v11.0.

Did you make a backup before updating?

[TheNomad11]

Ok. I will check the backup.
By the way, how can I roll back via docker-compose? Often you just add an older version number to the image file and you are done, but it does not seem to work here. One needs an env-file? If so, how would I configure it?

[simjanos-dev]

@sergiolaverde0

I googled the issue and found a reddit comment.

I’m not sure but I think your database got corrupted. I’ve had the same problem but just started over. I have a feeling it happens because of the mount and not using a volume to persist data but I might be wrong ¯_(ツ)_/¯ because you do persist data this way just not in /var/lib/docker…

I have no experience and knowledge about this at all. Should we also use a volume for MySQL instead of a mount?

By the way, how can I roll back via docker-compose? Often you just add an older version number to the image file and you are done, but it does not seem to work here. One needs an env-file? If so, how would I configure it?

I think the image itself can be rolled back be creating a .env file with this line VERSION=v0.11, but sergiolaverde0 can say it for sure. But I did not develop linguacafe with the idea of rollbacks to older versions. Sometimes database or folder structure change a bit, and I only write database migrations to convert old data to the new version. I think I want to keep doing it this way at least until linguacafe gets more mature, and there will be less changes.

[TheNomad11]

Thanks so much @simjanos-dev How do I change the line in
image: ghcr.io/simjanos-dev/linguacafe-webserver:${VERSION:-latest} in order to use the env-file. I will try the roleback
I am still learning how to deal with env and variables

[simjanos-dev]

Do not change the docker-compose.yml file. It would cause an error with the update command's git pull part (it is being removed currently, we will have a proper docker process in the future).

in order to use the env-file. I will try the roleback

The line you copied here will already use the .env file's if it exists.

[sergiolaverde0]

Should we also use a volume for MySQL instead of a mount?

I don't think that's the issue, many selfhosted projects use bind mounts and it is listed in the page of the image we are using.

It might be related to the user switching to mariadb, be it the image itself or a change in the compose file that went wrong. OP, could you share the compose file you are using?

I think the image itself can be rolled back be creating a .env file with this line VERSION=v0.11, but sergiolaverde0 can say it for sure.

Yes this is the intended way, VERSION can be any of the tags since we tagged our releases back in v0.8 or so. Just changing the version in the compose file works too but we favour using the .env file since it allows us to distribute changes to it via git pulls.

[TheNomad11]

After rolling back I get this error for the mariadb-image:

Fatal glibc error: CPU does not support x86-64-v2

There was an docker update earlier today, maybe related to that?

I use the compose file from the deploy repo

[sergiolaverde0]

Have you been using mariadb from the beginning or did you switch later on?

Also, please give me your exact CPU model and the output from running the command docker image ls in the terminal.

[TheNomad11]

Have you been using mariadb from the beginning or did you switch later on?
Yes, I just used the provided docker compose file

CPU

lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Address sizes: 40 bits physical, 48 bits virtual
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Vendor ID: AuthenticAMD
Model name: Common KVM processor
CPU family: 15
Model: 6
Thread(s) per core: 1
Core(s) per socket: 2
Socket(s): 1
Stepping: 1
BogoMIPS: 4699.99
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx lm rep_
good nopl cpuid extd_apicid tsc_known_freq pni cx16 x2apic hypervisor cmp_legacy 3dnowprefetch vmmcall
Virtualization features:
Hypervisor vendor: KVM
Virtualization type: full
Caches (sum of all):
L1d: 128 KiB (2 instances)
L1i: 128 KiB (2 instances)
L2: 1 MiB (2 instances)
L3: 32 MiB (2 instances)
NUMA:
NUMA node(s): 1
NUMA node0 CPU(s): 0,1

[TheNomad11]

classquiz-frontend latest a383cc6f7f0c 2 hours ago 969MB
classquiz-worker latest 839c3c83d589 2 hours ago 621MB
classquiz-api latest 2a35f29496f2 2 hours ago 621MB
ghcr.io/simjanos-dev/linguacafe-python-service latest f7cb703ecae4 30 hours ago 1.08GB
ghcr.io/simjanos-dev/linguacafe-webserver latest 8d972594ae0e 30 hours ago 683MB
lscr.io/linuxserver/nextcloud latest 844878bb7033 2 days ago 974MB
linuxserver/mariadb latest 119199faefdb 2 days ago 333MB
jzohrab/lute3 latest 6a8118710fdb 3 days ago 958MB
mysql 8 f3df03e3cfc9 8 days ago 585MB
mysql latest f3df03e3cfc9 8 days ago 585MB
mysql lts f3df03e3cfc9 8 days ago 585MB
mysql 8.0 9a39efdaa0e6 8 days ago 574MB
etherpad/etherpad latest 5ab58fffd97c 9 days ago 273MB
ghcr.io/simjanos-dev/linguacafe-python-service v0.11.2 0ee0acfe0615 11 days ago 1.08GB
ghcr.io/simjanos-dev/linguacafe-webserver v0.11.2 7b4b4eb4ae79 11 days ago 683MB
ghcr.io/simjanos-dev/linguacafe-python-service v0.11 d8d286722dc9 2 weeks ago 1.08GB
ghcr.io/simjanos-dev/linguacafe-webserver v0.11 f525b27262ef 2 weeks ago 683MB
lscr.io/linuxserver/nextcloud develop 2dfaef90f1ca 2 weeks ago 894MB
ghcr.io/b310-digital/teammapper latest 1fed50eb190c 3 weeks ago 1.68GB
cryptpad/cryptpad version-2024.3.0 3a07202ff8ba 4 weeks ago 860MB
redis latest 9509c4dd19fb 4 weeks ago 116MB
hwgilbert16/scholarsome latest e29fb50c4032 6 weeks ago 930MB
ghcr.io/wekan/wekan latest 9a72afc95cde 2 months ago 844MB
mariadb latest 465bc4da7f09 2 months ago 405MB
cryptpad/cryptpad version-5.7.0 6c63f95b08d4 2 months ago 2.38GB
postgres 14-alpine 54ae7e6e096b 3 months ago 239MB
postgres 12-alpine db2247d3e23c 3 months ago 234MB
mongo 6 0b496c33ce48 3 months ago 689MB
mongo latest c47dc3359f88 3 months ago 755MB
cryptpad/cryptpad version-5.6.0 ee54f8c08926 3 months ago 2.38GB
ghcr.io/simjanos-dev/linguacafe-python-service main 5cf9e584ac8e 3 months ago 1.32GB
ghcr.io/simjanos-dev/linguacafe-webserver main 228c89f90269 3 months ago 1.13GB
caddy alpine 66978cebfbcc 3 months ago 49.1MB
hwgilbert16/scholarsome v1.1.0 ee302ff990dd 3 months ago 852MB
redis alpine 435993df2c8d 4 months ago 41MB
nabo.codimd.dev/hackmdio/hackmd 2.5.3 9d122e8a46aa 4 months ago 1.59GB
nabo.codimd.dev/hackmdio/hackmd latest 9d122e8a46aa 4 months ago 1.59GB
nertworkweb/mongodb-no-avx latest e5a13eee06ee 13 months ago 685MB
lerntools/lerntools latest 2a690830a43b 14 months ago 909MB
mongo 4.4.18 f0bbeaaea8c3 15 months ago 438MB
getmeili/meilisearch v0.28.0 9e8344e03fce 22 months ago 139MB
postgres 11.6-alpine 89ae06c2ad76 4 years ago 152MB

[simjanos-dev]

Can you please also run docker container ls -a | grep lingua? This would show which image does it use. I found no mentions of mariadb in my database logs.

[TheNomad11]

ed39ead58a49   ghcr.io/simjanos-dev/linguacafe-webserver:v0.11.2        "/var/www/html/entry…"   52 minutes ago   Up 45 minutes                     0.0.0.0:9191->80/tcp, :::9191->80/tcp                                           linguacafe-webserver
69aecba41a82   ghcr.io/simjanos-dev/linguacafe-python-service:v0.11.2   "python3 /app/tokeni…"   52 minutes ago   Up 45 minutes                                                                                                     linguacafe-python-service
5622bdc102f1   mysql:8.0                                                "docker-entrypoint.s…"   52 minutes ago   Restarting (127) 15 seconds ago                                                                                   linguacafe-database

[simjanos-dev]

The image seem to be the correct one. My mysql:8.0 image has the same hash: 9a39efdaa0e614fa654568e3db75c847ccf834872bb6dd19da932d83e01c1884.

I have really no idea what the problem is. If you have a full backup, I would just do a fresh install, copy back my storage files, import back the .sql file, and restart my server. If the error persists... I'm not sure yet.

[TheNomad11]

ok, will do, thanks a lot for your help, really appeciate it! @simjanos-dev and @sergiolaverde0

[simjanos-dev]

Please let us know how it goes, and if you need any more help. I really hope it will work after the fresh install.

[TheNomad11]

yes, will try later, but it might be a CPU issue, I might have to contact the VPS provider

[TheNomad11]

Same issue after reinstall, will contact the provider

[TheNomad11]

I made it work! Yeah! (without contacting the provider)

I tried a different image: mysql:8.0-debian, that was the solution.

First I got an error message, though:
mbind: Operation not permitted
I googled it and found the solution here https://stackoverflow.com/questions/55559386/how-to-fix-mbind-operation-not-permitted-in-mysql-error-log
As suggested I added

cap_add:
            - SYS_NICE  # CAP_SYS_NIC

and then Linguacafe was working again!

[simjanos-dev]

Im glad it worked. But please keep in mind that the docker pull update command now can produce a conflict error if theres a change in the file. That command will be removed probably in the next update or the one after that, so i dont think it will cause any issues.

[simjanos-dev]

Also please keep a backup from the regular mysql8 database just in case.

[sergiolaverde0]

It seems there is something wrong with the default image that is built from checks docs Oracle Linux sighs. I will see deeper into it, maybe we should make the Debian base the default if it affects enough cpus.

[simjanos-dev]

It seems there is something wrong with the default image that is built from checks docs Oracle Linux sighs. I will see deeper into it, maybe we should make the Debian base the default if it affects enough cpus.

Thank you so much! There are a lot of docker issues, I would be completely lost without your help.

[TheNomad11]

Support replied and wrote it was a rather new processor AMD EPYC 7452 32-Core, but he made a small change. He changed the host type CPU from x86-64-v2 to host. Then I stopped the server and started it again, and the error message disappeared when I used the default mysql:8.0 image. Maybe this might be useful for others with similar issues