[debian-mysql] Bug#1069895: mariadb-server: Similar, very bad reproductible hangs on important production server after upgrade.

[Tomas Simek]

Package: mariadb-server
Followup-For: Bug #1069895

Hello Debian guys. I'm reporting this AFTER upgrade from (problematic?) 10.11.6, so IDK if it still apply on me, but it is very important to make remedy avalilable for all, 
becouse i had to manually search problem and upgrade from testing by unclean way.
Find and reproduce this hang is very hard. First hang was 3 weeks after upgrade, second hang week after first, third hang 3 days after second.

This problem causes us lot trouble on production so i deeply encourage You to move official version at least to 10.11.7 which should be fixed, as stated in originat trouble ticket.
Becouse i havent clue how to get 10.11.7, this package is not yet available for mortals even I search by packages.debian.org, so i put sources.list into testing repo and did: 

apt install mariadb-server

My instalation broke on error (unable to delete /lib/systemd/system/mariadb at bootstrap.service.d, directory not empty - i have CZ locale, translating this back to eng, so this text is may not be exact)

I checked and found only one some stupid newer used cloud config file. I deleted it, retried. Instalation went OK, but start broke again, on some stupid plugins unavailability.
After i inspected .so names, I removed garbage configs (which I newer requested) from conf.d (provider_bzip2.cnf  provider_lzma.cnf  provider_lzo.cnf  provider_lz4.cnf  provider_snappy.cnf)

Heureka, I have Maria 1:11.4.3-1 now and it seems to work.

Please further inspect if Our bugs are same. If yes, please for sake of other users speedup new package replacement in bookworm repo, there are plenty of similar desperate people in the world :)
Like https://help.nextcloud.com/t/mariadb-locks-up/185131/9

If errors are not the same, please try to check things like systemd limitations which can cause MySQL silently locks and wait for some resources, like it does when no disk space available (its not 
locking for new connection and maintenance in theese cases) etc.
Our production solution would be bloody fucking downgrade to oldstable, this hangs affects 20k IPTV customers, which tearing our support apart :)

We can put even some bounty reasonable bounty, depending on solution difficulty if You contact me on tomas.simek at starnet.cz
Even with new version move, i can buy a Pizza to someone lol

Thank you Guys




-- System Information:
Debian Release: trixie/sid
  APT prefers testing
  APT policy: (500, 'testing')
Architecture: amd64 (x86_64)

Kernel: Linux 6.1.0-25-amd64 (SMP w/16 CPU threads; PREEMPT)
Locale: LANG=cs_CZ.UTF-8, LC_CTYPE=cs_CZ.UTF-8 (charmap=UTF-8), LANGUAGE not set
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages mariadb-server depends on:
ii  adduser                3.134
ii  debconf [debconf-2.0]  1.5.82
ii  galera-4               26.4.18-0+deb12u1
ii  gawk                   1:5.2.1-2
ii  init-system-helpers    1.65.2
ii  iproute2               6.1.0-3
ii  libc6                  2.40-2
ii  libdbi-perl            1.643-4
ii  libpam0g               1.5.2-6+deb12u1
ii  libssl3t64             3.3.2-1
ii  libstdc++6             14.2.0-3
ii  lsof                   4.95.0-1
ii  mariadb-client         1:11.4.3-1
ii  mariadb-common         1:11.4.3-1
ii  mariadb-server-core    1:11.4.3-1
ii  passwd                 1:4.13+dfsg1-1+b1
ii  perl                   5.36.0-7+deb12u1
ii  procps                 2:4.0.2-3
ii  psmisc                 23.6-1
ii  rsync                  3.2.7-1
ii  socat                  1.7.4.4-2
ii  zlib1g                 1:1.2.13.dfsg-1

Versions of packages mariadb-server recommends:
ii  libhtml-template-perl           2.97-2
ii  mariadb-plugin-provider-bzip2   1:10.11.6-0+deb12u1
ii  mariadb-plugin-provider-lz4     1:10.11.6-0+deb12u1
ii  mariadb-plugin-provider-lzma    1:10.11.6-0+deb12u1
ii  mariadb-plugin-provider-lzo     1:10.11.6-0+deb12u1
ii  mariadb-plugin-provider-snappy  1:10.11.6-0+deb12u1
ii  pv                              1.6.20-1

Versions of packages mariadb-server suggests:
ii  bsd-mailx [mailx]  8.1.2-0.20220412cvs-1
pn  mariadb-test       <none>
pn  netcat-openbsd     <none>

-- Configuration Files:
/etc/mysql/mariadb.conf.d/50-server.cnf changed:
[server]
[mysqld]
user		= mysql
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
skip-external-locking
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address		= *
key_buffer_size		= 12G
max_allowed_packet	= 32M
thread_stack		= 300K
thread_cache_size       = 128
myisam_recover_options  = BACKUP
max_connections        = 400
table_cache            = 8192
table-definition-cache         = 4096
table-open-cache               = 3200
table_open_cache_instances      = 4
max_heap_table_size     = 10G
tmp_table_size          = 10G
skip_name_resolve       = ON
query_cache_size	= 0
query_cache_type        = 0
general_log_file        = /var/log/mysql/mysql.log
general_log             = 0
log_error = /var/log/mysql/error.log
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
slow_query_log = 1
long_query_time = 2
log_slow_rate_limit	= 1000
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 34G
innodb_buffer_pool_instances = 4
innodb_online_alter_log_max_size = 268435456
innodb_ft_min_token_size=4
ft_min_word_len=3
ft_max_word_len=40
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.1]


-- debconf information:
  mariadb-server/old_data_directory_saved:
  mariadb-server/nis_warning:
  mariadb-server/postrm_remove_databases: false