Running occ db:convert-filecache-bigint fails with "MySQL server has gone away"

🚧 Installation
1

Hi,

I am running NextCloud 15.0.14.1 (we are in the process of upgrading to 22) with a MariaDB backend. Running the occ script db:convert-filecache-bigint, it fails after roughly 5 minutes:

$ sudo -u apache php occ db:convert-filecache-bigint
Following columns will be updated:

  • filecache.mtime
  • filecache.storage_mtime
  • mounts.storage_id
  • mounts.root_id
  • mounts.mount_id

This can take up to hours, depending on the number of files in your instance!
Continue with the conversion (y/n)? [n] y

In AbstractMySQLDriver.php line 115:

An exception occurred while executing ‘ALTER TABLE oc_filecache CHANGE mtime mtime BIGINT DEFAULT 0 NOT NULL, CHANGE storage_mtime storage_mtime BIGINT DEFAULT 0 NOT NULL’:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

In PDOConnection.php line 106:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

In PDOConnection.php line 104:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

db:convert-filecache-bigint

According to the output, the script is failing during the ALTER operation on filecache table and then doesn’t do the ALTER on table mounts. However when I check the table structure of table filecache, my columns mtime and storage_mtime have the correct type BIGINT even though I can’t guarantee that my table is in a stable state.

Searching for this error message “MySQL server has gone away”, I found a troubleshooting suggestion with increasing some MariaDB settings like “wait_timeout” or “max_allowed_packet”. wait_timeout is already set to 8 hours but I increased max_allowed_packet to max value (1G) but this doesn’t work better:
https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html#db-troubleshooting-label

Has anyone had the same issue, especially with a significant number of rows in filecache table (I have ~11 million rows)?

Is there any way to get a more verbose error message from occ apart from the generic “MySQL server has gone away”? All I get from the logs are those 2 entries:
{“reqId”:“xKzMxQryuOjnG5H5LcDO”,“level”:3,“time”:“2021-10-28T04:18:46+00:00”,“remoteAddr”:"",“user”:"–",“app”:“PHP”,“method”:"",“url”:"–",“message”:“PDO::query(): MySQL server has gone away at /opt/nextcloud/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php#104”,“userAgent”:"–",“version”:“15.0.14.1”}
{“reqId”:“xKzMxQryuOjnG5H5LcDO”,“level”:3,“time”:“2021-10-28T04:18:46+00:00”,“remoteAddr”:"",“user”:"–",“app”:“PHP”,“method”:"",“url”:"–",“message”:“PDO::query(): Error reading result set’s header at /opt/nextcloud/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php#104”,“userAgent”:"–",“version”:“15.0.14.1”}

2

This turned out to be a network timeout set to 5 mins on the database server itself. Nothing related to MariaDB configuration or NextCloud. We ran the upgrade successfully - up to version 22

1 Like