Mariadb locks up

sme2 · March 12, 2024, 7:52am

For the past two or so months I have been struggling with database problems. Out of the blue the database locks up and a systemctl restart mariadb will not do. It’ll do nothing. Also a reboot will not help as the mariadb service cannot properly shut down. All I can do is a power cycle to reboot my database server.

Looking into the current state of affairs with MariaDB [(none)]> show processlist; gives me a list of very long running processes that somehow get stuck. They vary. Sometimes it’s something related to preferences, sometimes to file caches but the list gets quite long as I think all of them were started at some point but couldn’t finish.

This behaviour started I think with NC 28 but could have been the last version before that. I always updgrade as soon as updates are available. Will give more details further on.

I started with Mariadb 10 on the same server as nextcloud but then moved my database to a different server when I thought my initial db was corrupt. I tehn upgraded to mariadb 11 assuming it might help.

Before I move on to migrate all my data to Postgresql I thought I might ask here if there is any help available to fix this.

You will see that I’m running the enterprise version which is correct. We have a license but it’s without support since we’re a school and don’t have paid support.

There are about 100 active users of the nextcloud.

My Mariadb:

Server version: 11.2.3-MariaDB-1:11.2.3+maria~ubu2204-log mariadb.org binary distribution

My NextCloud:

sudo -u www-data php occ config:list system
{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [ some servers ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "28.0.3.3",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "ldapIgnoreNamingRules": false,
        "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "maintenance": false,
        "theme": "",
        "loglevel": 0,
        "log_rotate_size": 104857600,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mysql.utf8mb4": true,
        "app_install_overwrite": [
            "external",
            "cms_pico",
            "files_readmemd",
            "githubmergetracker",
            "calendar",
            "bruteforcesettings",
            "bbb"
        ],
        "overwrite.cli.url": "https:\/\/nextcloud.example.de",
        "htaccess.RewriteBase": "\/",
        "apps_paths": [
            {
                "path": "\/var\/www\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/nextcloud\/apps2",
                "url": "\/apps2",
                "writable": true
            }
        ],
        "updater.release.channel": "enterprise",
        "default_phone_region": "DE",
        "updater.server.url": "https:\/\/updates.nextcloud.com\/customers\/J3CWG-94D1Q-QCLLJ-A6V9Y-PJSIR\/",
        "maintenance_window_start": "1"
    }
}

and my.cnf:

[client]
default-character-set = utf8mb4
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock
[mysqld]
# performance_schema=ON
basedir = /usr
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
binlog_format = ROW
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
datadir = /var/lib/mysql
default_storage_engine = InnoDB
expire_logs_days = 2
general_log_file = /var/log/mysql/mysql.log
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 32M
innodb_log_file_size = 512M
innodb_read_only_compressed=OFF
join_buffer_size = 2M
key_buffer_size = 512M
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
log_error = /var/log/mysql/mysql_error.log
log_slow_verbosity = query_plan
log_warnings = 2
long_query_time = 1
max_connections = 100
max_heap_table_size = 64M
myisam_sort_buffer_size = 512M
port = 3306
pid-file = /var/run/mysqld/mysqld.pid
query_cache_limit = 0
query_cache_size = 0
read_buffer_size = 2M
read_rnd_buffer_size = 2M
skip-name-resolve
socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 2M
table_open_cache = 400
table_definition_cache = 800
tmp_table_size = 32M
tmpdir = /tmp
transaction_isolation = READ-COMMITTED
user = mysql
wait_timeout = 600
[mysqldump]
max_allowed_packet = 16M
quick
quote-names
[isamchk]
key_buffer = 16M

If I forgot something plase let me know so I can update this post. Thank you very much for looking into this.

bb77 · March 12, 2024, 8:41am

Hi @sme2

I’m not sure if this is the same problem you’re experiencing, or what the exact reasons are for this behaviour in the first place, but I noticed something similar after installing NC28 on a test instance and then uploading a large folder with about 1800 files. Nextcloud/PHP-FPM literally bruteforced the backend until after a certain time, I got 500 or 503 errors in the WebUI and Nextcloud became completely unresponsive and the RAM was fully utilised with MariaDB processes.

I then changed pm = dynamic to pm = ondemand in /etc/php/$PHPVERSION/fpm/pool.d/www.conf and I was able to upload the same folder without any issues and the RAM usage never exceeded 30%.

I also made this change on my production instance after upgrading it to 28.0.3 and did not notice any significant performance impact, if any at all, so I think I will leave it that way, at least for now. Note: I only run a home server with two users, so your mileage may vary

sme2 · March 12, 2024, 9:00am

I’m using mod_php instead of PHP-FPM. Just followed the regular setup on this. So I don’t think this applies here.

Nevertheless I don’t think this is what’s causing any issues here. The MariaDB server is fully responsive, no memory or cpu problems at all. top doesn’t show anything out of the ordinary. It just crashes the database and completely locks it up. I do have another postgres server running along which keeps serving my other sites just fine all the while.

Thank you for your reply

bb77 · March 12, 2024, 9:22am

Could still be the same underlying issue (or maybe a change/bug in Nextcloud?) that shows somewhat different symptoms with mod_php. Unfortunately, I’m not expert enough to provide definitive answers, but perhaps others here have some more ideas…

sme2 · March 12, 2024, 11:09am

BTW, this is how a typical lockup process list looks like:


MariaDB [(none)]> show processlist;
+------+-----------+------------------+-----------+---------+------+-----------------+------------------------------------------------------------------------------------------------------+----------+
| Id   | User      | Host             | db        | Command | Time | State           | Info                                                                                                 | Progress |
+------+-----------+------------------+-----------+---------+------+-----------------+------------------------------------------------------------------------------------------------------+----------+
| 3618 | nextcloud | 100.100.100.100:34870 | nextcloud | Query   | 4408 | Update          | INSERT INTO `oc_preferences` (`userid`, `appid`, `configkey`, `configvalue`) VALUES('user2', 'calendar |    0.000 |
| 3672 | nextcloud | 100.100.100.100:47208 | nextcloud | Query   | 4165 | Statistics      | SELECT `userid` FROM `oc_preferences` WHERE (`appid` = 'settings') AND (`configkey` = 'email') AND ( |    0.000 |
| 3683 | nextcloud | 100.100.100.100:35800 | nextcloud | Query   | 4124 | Statistics      | SELECT `userid`, `configvalue` FROM `oc_preferences` WHERE (`appid` = 'core') AND (`configkey` = 'ti |    0.000 |
| 3706 | nextcloud | 100.100.100.100:45858 | nextcloud | Query   | 4018 | init for update | DELETE FROM `oc_preferences` WHERE (`userid` = 'user1') AND (`appid` = 'login_token') AND (`configkey` |    0.000 |
| 3747 | nextcloud | 100.100.100.100:43100 | nextcloud | Query   | 3867 | init for update | DELETE FROM `oc_preferences` WHERE (`userid` = 'user1') AND (`appid` = 'login_token') AND (`configkey` |    0.000 |
| 3808 | nextcloud | 100.100.100.100:44312 | nextcloud | Query   | 3529 | Update          | INSERT INTO `oc_preferences` (`userid`, `appid`, `configkey`, `configvalue`) VALUES('user3', 'user_l |    0.000 |
| 3833 | nextcloud | 100.100.100.100:37306 | nextcloud | Query   | 3455 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3848 | nextcloud | 100.100.100.100:53370 | nextcloud | Query   | 3450 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3852 | nextcloud | 100.100.100.100:53412 | nextcloud | Query   | 3447 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3859 | nextcloud | 100.100.100.100:57432 | nextcloud | Query   | 3406 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3874 | nextcloud | 100.100.100.100:49292 | nextcloud | Query   | 3327 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3894 | nextcloud | 100.100.100.100:40150 | nextcloud | Query   | 3207 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3899 | nextcloud | 100.100.100.100:53522 | nextcloud | Query   | 3170 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3909 | nextcloud | 100.100.100.100:36166 | nextcloud | Query   | 3104 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3923 | nextcloud | 100.100.100.100:47196 | nextcloud | Query   | 3038 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3961 | nextcloud | 100.100.100.100:44566 | nextcloud | Query   | 2882 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3977 | nextcloud | 100.100.100.100:57420 | nextcloud | Query   | 2791 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 3987 | nextcloud | 100.100.100.100:43296 | nextcloud | Query   | 2734 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4002 | nextcloud | 100.100.100.100:49558 | nextcloud | Query   | 2649 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4013 | nextcloud | 100.100.100.100:37938 | nextcloud | Query   | 2578 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4029 | nextcloud | 100.100.100.100:33692 | nextcloud | Query   | 2473 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4063 | nextcloud | 100.100.100.100:54914 | nextcloud | Query   | 2282 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4077 | nextcloud | 100.100.100.100:58138 | nextcloud | Query   | 2204 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4085 | nextcloud | 100.100.100.100:32910 | nextcloud | Query   | 2152 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4097 | nextcloud | 100.100.100.100:51348 | nextcloud | Query   | 2089 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4101 | nextcloud | 100.100.100.100:32930 | nextcloud | Query   | 2063 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4128 | nextcloud | 100.100.100.100:42542 | nextcloud | Query   | 1970 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4218 | nextcloud | 100.100.100.100:57442 | nextcloud | Query   | 1737 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4223 | nextcloud | 100.100.100.100:38002 | nextcloud | Query   | 1731 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4228 | nextcloud | 100.100.100.100:60380 | nextcloud | Query   | 1696 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4230 | nextcloud | 100.100.100.100:40130 | nextcloud | Query   | 1680 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4252 | nextcloud | 100.100.100.100:42854 | nextcloud | Query   | 1494 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |
| 4269 | nextcloud | 100.100.100.100:59144 | nextcloud | Query   | 1374 | Statistics      | SELECT COUNT(*) FROM `oc_preferences` WHERE (`appid` = 'login') AND (`configkey` = 'lastLogin')      |    0.000 |

chw0 · May 6, 2024, 9:10am

For the past two or so months I have been struggling with database problems. Out of the blue the database locks up and a systemctl restart mariadb will not do. It’ll do nothing. Also a reboot will not help as the mariadb service cannot properly shut down. All I can do is a power cycle to reboot my database server.

I have exactly the same problem. Were you able to solve the problem?

I am currently making do with “kill -9 ” and start mariadb manually.

Debian 12
MariaDb 10.11.6-0+deb12u1
Apache mod_php / PHP 8.3.6
Nextcloud 28.0.5

The problem occurred after the upgrade to Debian12/PHP8.3/Nextcloud28.

The “show processlist;” when the last error occurs:

MariaDB [(none)]> show processlist;
+------+------+-----------+----------+---------+------+-----------------+------------------------------------------------------------------------------------------------------+----------+
| Id   | User | Host      | db       | Command | Time | State           | Info                                                                                                 | Progress |
+------+------+-----------+----------+---------+------+-----------------+------------------------------------------------------------------------------------------------------+----------+
| 3699 | nc  | localhost | nc | Query   | 7783 | Update          | INSERT INTO `oc_calendarobjects_props` (`calendarid`, `calendartype`, `objectid`, `name`, `parameter |    0.000 |
| 3725 | nc  | localhost | nc | Query   | 7660 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3727 | nc  | localhost | nc | Query   | 7645 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3768 | nc  | localhost | nc | Query   | 7494 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3773 | nc  | localhost | nc | Query   | 7476 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3790 | nc  | localhost | nc | Query   | 7360 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3802 | nc  | localhost | nc | Query   | 7285 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3827 | nc  | localhost | nc | Query   | 7194 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3832 | nc  | localhost | nc | Query   | 7176 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3840 | nc  | localhost | nc | Query   | 7139 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3858 | nc  | localhost | nc | Query   | 7135 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3859 | nc  | localhost | nc | Query   | 7134 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3874 | nc  | localhost | nc | Query   | 7128 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 3875 | nc  | localhost | nc | Query   | 7127 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4059 | nc  | localhost | nc | Query   | 7080 | init for update | DELETE FROM `oc_calendarobjects_props` WHERE (`objectid` = '42402') AND (`calendarid` = '80')        |    0.000 |
| 4066 | nc  | localhost | nc | Query   | 7060 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4087 | nc  | localhost | nc | Query   | 7014 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4104 | nc  | localhost | nc | Query   | 6968 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4111 | nc  | localhost | nc | Query   | 6965 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4112 | nc  | localhost | nc | Query   | 6965 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4188 | nc  | localhost | nc | Query   | 6935 | init for update | DELETE FROM `oc_calendarobjects_props` WHERE (`objectid` = '42403') AND (`calendarid` = '80')        |    0.000 |
| 4190 | nc  | localhost | nc | Query   | 6925 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4208 | nc  | localhost | nc | Query   | 6894 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4210 | nc  | localhost | nc | Query   | 6893 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4212 | nc  | localhost | nc | Query   | 6876 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4287 | nc  | localhost | nc | Query   | 6833 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4315 | nc  | localhost | nc | Query   | 6760 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4320 | nc  | localhost | nc | Query   | 6732 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4341 | nc  | localhost | nc | Query   | 6601 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4344 | nc  | localhost | nc | Query   | 6576 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4346 | nc  | localhost | nc | Query   | 6565 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4358 | nc  | localhost | nc | Query   | 6460 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4373 | nc  | localhost | nc | Query   | 6276 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4380 | nc  | localhost | nc | Query   | 6205 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4386 | nc  | localhost | nc | Query   | 6145 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4442 | nc  | localhost | nc | Query   | 5983 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4444 | nc  | localhost | nc | Query   | 5976 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4470 | nc  | localhost | nc | Query   | 5845 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4472 | nc  | localhost | nc | Query   | 5845 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4501 | nc  | localhost | nc | Query   | 5485 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4503 | nc  | localhost | nc | Query   | 5472 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4546 | nc  | localhost | nc | Query   | 5166 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4550 | nc  | localhost | nc | Query   | 5125 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4554 | nc  | localhost | nc | Query   | 5100 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4593 | nc  | localhost | nc | Query   | 4800 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4595 | nc  | localhost | nc | Query   | 4765 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4651 | nc  | localhost | nc | Query   | 4460 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4655 | nc  | localhost | nc | Query   | 4405 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4670 | nc  | localhost | nc | Query   | 4320 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4697 | nc  | localhost | nc | Query   | 4160 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4717 | nc  | localhost | nc | Query   | 4045 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4732 | nc  | localhost | nc | Query   | 3951 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4753 | nc  | localhost | nc | Query   | 3860 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4780 | nc  | localhost | nc | Query   | 3685 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4818 | nc  | localhost | nc | Query   | 3559 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4843 | nc  | localhost | nc | Query   | 3325 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4864 | nc  | localhost | nc | Query   | 3302 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4865 | nc  | localhost | nc | Query   | 3302 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4886 | nc  | localhost | nc | Query   | 3298 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4887 | nc  | localhost | nc | Query   | 3298 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4955 | nc  | localhost | nc | Query   | 3157 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 4956 | nc  | localhost | nc | Query   | 3157 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5005 | nc  | localhost | nc | Query   | 3121 | init for update | DELETE FROM `oc_calendarobjects_props` WHERE (`objectid` = '42404') AND (`calendarid` = '56')        |    0.000 |
| 5025 | nc  | localhost | nc | Query   | 2997 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5030 | nc  | localhost | nc | Query   | 2965 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5055 | nc  | localhost | nc | Query   | 2856 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5111 | nc  | localhost | nc | Query   | 2697 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5116 | nc  | localhost | nc | Query   | 2670 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5140 | nc  | localhost | nc | Query   | 2605 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5149 | nc  | localhost | nc | Query   | 2556 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5155 | nc  | localhost | nc | Query   | 2516 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5174 | nc  | localhost | nc | Query   | 2503 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5175 | nc  | localhost | nc | Query   | 2503 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5219 | nc  | localhost | nc | Query   | 2488 | init for update | DELETE FROM `oc_calendarobjects_props` WHERE (`objectid` = '42327') AND (`calendarid` = '56')        |    0.000 |
| 5265 | nc  | localhost | nc | Query   | 2429 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5266 | nc  | localhost | nc | Query   | 2429 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5324 | nc  | localhost | nc | Query   | 2397 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5371 | nc  | localhost | nc | Query   | 2245 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5383 | nc  | localhost | nc | Query   | 2129 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5387 | nc  | localhost | nc | Query   | 2120 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5391 | nc  | localhost | nc | Query   | 2072 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5406 | nc  | localhost | nc | Query   | 1885 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5412 | nc  | localhost | nc | Query   | 1829 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5425 | nc  | localhost | nc | Query   | 1770 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5451 | nc  | localhost | nc | Query   | 1525 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5465 | nc  | localhost | nc | Query   | 1306 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5474 | nc  | localhost | nc | Query   | 1165 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5492 | nc  | localhost | nc | Query   |  805 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5501 | nc  | localhost | nc | Query   |  688 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5539 | nc  | localhost | nc | Query   |  445 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5576 | nc  | localhost | nc | Query   |  108 | init for update | DELETE FROM `oc_calendarobjects_props` WHERE (`objectid` = '42405') AND (`calendarid` = '22')        |    0.000 |
| 5577 | nc  | localhost | nc | Query   |   85 | Statistics      | SELECT `c`.`id`, `c`.`calendardata`, `c`.`componenttype`, `c`.`uid`, `c`.`uri` FROM `oc_calendarobje |    0.000 |
| 5584 | root | localhost | NULL     | Query   |    0 | starting        | show processlist                                                                                     |    0.000 |
+------+------+-----------+----------+---------+------+-----------------+------------------------------------------------------------------------------------------------------+----------+

The first “hanging” query is always different (UPDATE or INSERT oc_filecache, oc_calendarobjects_props, etc)

tsueri1 · May 15, 2024, 1:13pm

I see this errror quite often. It’s very annoying. I don’t know what to do… No errors on the database (mariadb 10.6.16).

jtr · May 15, 2024, 2:54pm

What does systemctl status mariadb say during these events?

Also, what do your mysql_error.log and mysql.log show around these times?

Also, just to confirm, you have no database warnings in Nextcloud under Administration settings->Overview, correct?

I guess you might try turning log_warnings up.

Also since these days many of the defaults are a good baseline (and the defaults do change across db versions) , it may be useful to compare your running config against your installation’s defaults out of the box by running the following query to see what isn’t the default:

SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE
  FROM information_schema.SYSTEM_VARIABLES
 WHERE GLOBAL_VALUE != DEFAULT_VALUE
   AND GLOBAL_VALUE NOT LIKE '%home%'
   AND VARIABLE_NAME LIKE 'INNODB%'
 ORDER BY VARIABLE_NAME;

You may want to check/query MariaDB/MySQL forums/documentation and StackOverflow/etc a bit too. Db troubleshooting and optimizing is a specialty of its very own.

Possibly trying setting optimizer_search_depth to 0 (auto) and seeing if things improve.

pupax · August 6, 2024, 6:30am

I have the same issue as you. Latest nextcloud on Debian 12 with MariabDB 10.11.6.
The database seems to lock up. High CPU usage on the mariadbd process and any SELECT query hangs and gives no output.

We had a similar problem on an other VM with the same software (php, mariadb, etc.). It seems like a MariaDB bug. Maybe it would be better if we report on the MariaDB tracker?

Tomas_Simek · September 27, 2024, 6:04am

I have same problem on actual Debian (12.7), mariadb-server/stable,now 1:10.11.6-0+deb12u1 amd64. I haven’t nextcloud on this server, error is outside this.

Everything going fine, but suddenly mariadb totally hangs. Nothing in any log (journal, mysql error, info etc). Connection to mysql client hangs too, so i can’t list running commands etc. Service mysql restart hangs too, reboot command hangs too.

when i wait very log time, about 5 minute for connection, it wrote something like “resource unavailable or busy” and fails. I did power cycle and forgot to save the message.

It seems to me like similar case, when mariadb is out of filesystem space, it blocks most of operations (but not connection) and wait for space. This can be similar, waiting for file descriptors?, memory?, or other idk? What i can raise?

This is hapening to me on cable TV produsction and it is VERY unpleasant.

Tomas_Simek · September 27, 2024, 7:14am

Hello again, maybye this pending Debian bug number can be solution for us all

#1069895 mariadb-server: InnoDB to hang on systems with very intensive write loads when running out of I/O slots. This problem is fixed with MariaDB Server 10.11.7. Can it be put in stable?

This package is not yet available, 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@bootstrap.service.d, directory not empty)

I checked and found only one some stupid unused cloud config file. I deleted it, retry and it works, i have Maria 1:11.4.3-1 now.

So, lets pray that this ticket is real cause and we have solution

Dashamir_Hoxha · October 6, 2024, 5:24pm

I had the same weird problem with mariadb (but in another context, not related to nextcloud). After working for some time it would hang and freeze for no apparent reasons. I have struggled with this issue for a month or so.

Recently I came across this discussion and I tried mariadb version 10.11.7 :

It seems that it solved the problem. I still don’t understand what the problem was, but as long as it is fixed, who cares.