DB error on update

ℹ️ Support
, , ,
1

Nextcloud version (eg, 29.0.5): 30.0.1
Operating system and version (eg, Ubuntu 24.04): FreeBSD 14.1-RELEASE-p5
Apache or nginx version (eg, Apache 2.4.25): nginx nginx-1.26.2_5
PHP version (eg, 8.3): 8.1.30

I have updated a server and am unable to access any nextcloud pages, and if I run occ I get the following:

[cain 16:38] ~ >sudo occ setupchecks
An unhandled exception has been thrown:
PDOException: SQLSTATE[HY000]: General error: 1 no such column: "type" - should this be a string literal in single-quotes? in /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/
PDO/Connection.php:59
Stack trace:
#0 /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php(59): PDO->prepare()
#1 /usr/local/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1095): Doctrine\DBAL\Driver\PDO\Connection->prepare()
#2 /usr/local/www/nextcloud/lib/private/DB/Connection.php(415): Doctrine\DBAL\Connection->executeQuery()
#3 /usr/local/www/nextcloud/lib/private/DB/ConnectionAdapter.php(50): OC\DB\Connection->executeQuery()
#4 /usr/local/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(292): OC\DB\ConnectionAdapter->executeQuery()
#5 /usr/local/www/nextcloud/lib/private/AppConfig.php(1218): OC\DB\QueryBuilder\QueryBuilder->executeQuery()
#6 /usr/local/www/nextcloud/lib/private/AppConfig.php(237): OC\AppConfig->loadConfig()
#7 /usr/local/www/nextcloud/lib/private/legacy/OC_App.php(695): OC\AppConfig->searchValues()
#8 /usr/local/www/nextcloud/lib/private/Server.php(654): OC_App::getAppVersions()
#9 /usr/local/www/nextcloud/lib/private/Memcache/Factory.php(98): OC\Server->OC\{closure}()
#10 /usr/local/www/nextcloud/lib/private/Memcache/Factory.php(110): OC\Memcache\Factory->getGlobalPrefix()                                                                               #11 /usr/local/www/nextcloud/lib/private/Server.php(1057): OC\Memcache\Factory->createLocking()
#12 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(153): OC\Server->OC\{closure}()
#13 /usr/local/www/nextcloud/3rdparty/pimple/pimple/src/Pimple/Container.php(122): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}()
#14 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(120): Pimple\Container->offsetGet()
#15 /usr/local/www/nextcloud/lib/private/ServerContainer.php(155): OC\AppFramework\Utility\SimpleContainer->query()
#16 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(42): OC\ServerContainer->query()
#17 /usr/local/www/nextcloud/lib/private/Files/View.php(71): OC\AppFramework\Utility\SimpleContainer->get()
#18 /usr/local/www/nextcloud/lib/private/Server.php(438): OC\Files\View->__construct()
#19 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(153): OC\Server->OC\{closure}()
#20 /usr/local/www/nextcloud/3rdparty/pimple/pimple/src/Pimple/Container.php(122): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}()
#21 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(120): Pimple\Container->offsetGet()
#22 /usr/local/www/nextcloud/lib/private/ServerContainer.php(155): OC\AppFramework\Utility\SimpleContainer->query()
#23 /usr/local/www/nextcloud/lib/private/AppFramework/Utility/SimpleContainer.php(42): OC\ServerContainer->query()
#24 /usr/local/www/nextcloud/lib/private/Server.php(1425): OC\AppFramework\Utility\SimpleContainer->get()
#25 /usr/local/www/nextcloud/lib/base.php(588): OC\Server->boot()
#26 /usr/local/www/nextcloud/lib/base.php(1132): OC::init()
#27 /usr/local/www/nextcloud/console.php(28): require_once('...')
#28 /usr/local/www/nextcloud/occ(11): require_once('...')
#29 {main}

I added some print debugging and it is trying to execute: SELECT "appid", "configkey", "configvalue", "type" FROM "oc_appconfig" WHERE "lazy" = ? however the DB schema is:

CREATE TABLE oc_appconfig (
	appid VARCHAR(32) DEFAULT '' NOT NULL, 
	configkey VARCHAR(64) DEFAULT '' NOT NULL,
	configvalue CLOB DEFAULT NULL, 
	PRIMARY KEY(appid, configkey)
);

So it looks like some migration is required…

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'apps_paths' =>
  array (
    0 =>
    array (
      'path' => '/usr/local/www/nextcloud/apps',
      'url' => '/apps',
      'writable' => true,
    ),
    1 =>
    array (
      'path' => '/usr/local/www/nextcloud/apps-pkg',
      'url' => '/apps-pkg',
      'writable' => false,
    ),
  ),
  'logfile' => '/var/log/nextcloud/nextcloud.log',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'instanceid' => 'xxx',
  'passwordsalt' => 'xxxx',
  'secret' => 'xxx',
  'trusted_domains' =>
  array (
    0 => 'nextcloud.xxx',
  ),
  'datadirectory' => '/usr/local/www/nextcloud/data',
  'dbtype' => 'sqlite3',
  'version' => '26.0.0.11',
  'overwrite.cli.url' => 'https://nextcloud.xxx',
  'installed' => true,
  'mail_from_address' => 'nextcloud',
  'mail_smtpmode' => 'smtp',
  'mail_sendmailmode' => 'smtp',
  'mail_domain' => 'xxx',
  'mail_smtphost' => 'xxx',
  'mail_smtpport' => '25',
  'user_backends' =>
  array (
    0 =>
    array (
      'class' => '\\OCA\\UserExternal\\BasicAuth',
      'arguments' =>
      array (
        0 => 'xxxxx',
      ),
    ),
  ),
  'maintenance' => false,
  'theme' => '',
);
2

I ended up modifying the database by hand:

sudo sqlite3 /usr/local/www/nextcloud/data/owncloud.db
sqlite> alter table oc_appconfig add type int(10) default 2 not null;
sqlite> alter table oc_appconfig add lazy smallint(5) default 0 not null;

Which gets further (ie it doesn’t immediately crash) but unfortunately I can’t upgrade because I have skipped a version.

This does seem like a fairly drastic limitation - I guess I will have to manually install each intermediate version and do the updates…

3

Normally, I’d expect a warning that you cannot run the upgrade.

Feature request to allow this with more background:

4

Yes, there is a warning that you can’t run the upgrade but of course by that point your install is doomed because there is no option to install the older packages.

5

How, precisely, did you upgrade? It’s more than a warning. Our Updater will refuse to even deploy an update if you’re jumping more than one major version. So the situation you’re describing is impossible unless you did a manual update or some third-party process (I see you are using FreeBSD… perhaps something there?)

'version' => '26.0.0.11',
This does seem like a fairly drastic limitation - I guess I will have to manually install each intermediate version and do the updates…

Yes, you would need to update to the last v26 maintenance release (one update) then to the last v27 maintenance release. And so on until you’re at whatever major version you’re targeting. For clarity, the documentation on this topic is here.

6

I installed (and updated) using the system package manager.

It is quite surprising to me that NextCloud requires updates this way - I have never had to do such a thing with another piece of software.

I guess I’ll just have to step through the versions manually from now on.