Errors in Database - after backup restore

Snowest · October 10, 2020, 8:54am

Nextcloud version: 19.0.2
Operating system and version: Docker on Synology
Apache or nginx version: Apache don’t know waht version
PHP version: 7.4.9

The issue you are facing:
All settings worked fine until the 8th of October. Then I installed a new app with the app store of NC called Carnet. I tested this app until the 9th of October and the dicided to remove it. So I went to the app store again to uninstall it. But unfortunaly the app stor never opened because a session limit. So after trying to open the app stroe in diferen browser without success. I loaded a backup made with nextbackup from the 8th of October before installing this Carnet app. I restored all Table. So then, because it was not all okay, I restored a Backup that I make every day of my nextcloud folder.

Now I can access everything again, so it seams to work. But I still have this nasty error in the logs. So not everything is fine.

If anyone could help me get rid of this message and fix the tables, that would be greate. Thanks

Is this the first time you’ve seen this error? Yes

Steps to replicate it:

I don’t know how to replictae it, but ist apears in the log in diferent time slots, somtimes every ten minutes and sometime after 3 hours.

The output of your Nextcloud log in: Error Core

[core] Error: Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT `cr`.*, `co`.`calendardata`, `c`.`displayname`, `c`.`principaluri` FROM `oc_calendar_reminders` `cr` LEFT JOIN `oc_calendarobjects` `co` ON `cr`.`object_id` = `co`.`id` LEFT JOIN `oc_calendars` `c` ON `cr`.`calendar_id` = `c`.`id` WHERE `cr`.`notification_date` <= ?' with params [1602314402]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'co.calendardata' in 'field list' at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
 1. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... '")
 2. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 911
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "SELECT `cr`.*,  ... ?", {1: 1602314402})
 3. /var/www/html/lib/private/DB/Connection.php line 194
    Doctrine\DBAL\Connection->executeQuery("SELECT `cr`.*,  ... ?", [1602314402], [2], null)
 4. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 200
    OC\DB\Connection->executeQuery("SELECT `cr`.*,  ... 1", {dcValue1: 1602314402}, {dcValue1: 2})
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 216
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/apps/dav/lib/CalDAV/Reminder/Backend.php line 73
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/apps/dav/lib/CalDAV/Reminder/ReminderService.php line 113
    OCA\DAV\CalDAV\Reminder\Backend->getRemindersToProcess()
 8. /var/www/html/apps/dav/lib/BackgroundJob/EventReminderJob.php line 68
    OCA\DAV\CalDAV\Reminder\ReminderService->processReminders()
 9. /var/www/html/lib/private/BackgroundJob/Job.php line 62
    OCA\DAV\BackgroundJob\EventReminderJob->run(null)
10. /var/www/html/lib/private/BackgroundJob/TimedJob.php line 57
    OC\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})
11. /var/www/html/cron.php line 148
    OC\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

GET /cron.php
from 192.168.1.109 at 2020-10-10T07:20:02+00:00

The output of your Nextcloud log in Error WebDav

[webdav] Fatal: Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT `id`, `uri`, `lastmodified`, `etag`, `calendarid`, `size`, `calendardata`, `componenttype`, `classification` FROM `oc_calendarobjects` WHERE (`calendarid` = ?) AND (`uri` = ?) AND (`calendartype` = ?)' with params ["2", "81b30f37-8c72-48c6-8bc0-1cb2033a13f6.ics", 0]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'calendardata' in 'field list' at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
 1. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... '")
 2. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 911
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "SELECT `id`, `u ... )", {1: "2",2: "81b3 ... 0})
 3. /var/www/html/lib/private/DB/Connection.php line 194
    Doctrine\DBAL\Connection->executeQuery("SELECT `id`, `u ... )", ["2","81b30f37-8 ... 0], [2,2,2], null)
 4. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 200
    OC\DB\Connection->executeQuery("SELECT `id`, `u ... )", {dcValue1: "2",d ... 0}, {dcValue1: 2,dcValue2: 2,dcValue3: 2})
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 216
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/apps/dav/lib/CalDAV/CalDavBackend.php line 961
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/apps/dav/lib/CalDAV/Calendar.php line 326
    OCA\DAV\CalDAV\CalDavBackend->getCalendarObject("2", "81b30f37-8c72-4 ... s")
 8. /var/www/html/3rdparty/sabre/dav/lib/DAV/Tree.php line 113
    OCA\DAV\CalDAV\Calendar->childExists("81b30f37-8c72-4 ... s")
 9. /var/www/html/3rdparty/sabre/dav/lib/DAVACL/Plugin.php line 841
    Sabre\DAV\Tree->nodeExists("calendars/Nicol ... s")
10. /var/www/html/apps/dav/lib/Connector/Sabre/DavAclPlugin.php line 99
    Sabre\DAVACL\Plugin->beforeMethod(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
11. /var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php line 89
    OCA\DAV\Connector\Sabre\DavAclPlugin->beforeMethod(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
12. /var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php line 458
    Sabre\DAV\Server->emit("beforeMethod:PUT", [Sabre\HTTP\Requ ... }])
13. /var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php line 251
    Sabre\DAV\Server->invokeMethod(Sabre\HTTP\Request {}, Sabre\HTTP\Response {})
14. /var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php line 319
    Sabre\DAV\Server->start()
15. /var/www/html/apps/dav/lib/Server.php line 320
    Sabre\DAV\Server->exec()
16. /var/www/html/apps/dav/appinfo/v2/remote.php line 35
    OCA\DAV\Server->exec()
17. /var/www/html/remote.php line 167
    require_once("/var/www/html/a ... p")

PUT /remote.php/dav/calendars/Nicolas/personal/81b30f37-8c72-48c6-8bc0-1cb2033a13f6.ics
from 178.197.234.167 by Nicolas at 2020-10-09T20:49:40+00:00

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

<?php
$CONFIG = array (
  'htaccess.RewriteBase' => '/',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/var/www/html/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 => 
    array (
      'path' => '/var/www/html/custom_apps',
      'url' => '/custom_apps',
      'writable' => true,
    ),
  ),
  'instanceid' => 'oc22swys51ev',
  'passwordsalt' => 'Snesitiv',
  'secret' => 'Snesitiv',
  'trusted_domains' => 
  array (
    0 => 'https://MYDOMAIN:PORT',
    1 => '192.168.XXX.XXX:POSRT',
    2 => 'MYDOMAIN:PORT',
  ),
  'datadirectory' => '/var/www/html/data',
  'dbtype' => 'mysql',
  'version' => '19.0.2.2',
  'overwrite.cli.url' => 'http://MYDOMAIN:49155',
  'dbname' => 'nextcloud',
  'dbhost' => '192.168.XXX.XXX:PORT',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'Snesitiv',
  'dbpassword' => 'Snesitiv',
  'installed' => true,
  'ldapIgnoreNamingRules' => false,
  'ldapProviderFactory' => 'OCA\\User_LDAP\\LDAPProviderFactory',
  'twofactor_enforced' => 'false',
  'twofactor_enforced_groups' => 
  array (
    0 => 'User',
  ),
  'twofactor_enforced_excluded_groups' => 
  array (
  ),
  'mail_smtpmode' => 'smtp',
  'mail_smtpsecure' => 'ssl',
  'mail_sendmailmode' => 'smtp',
  'mail_from_address' => 'Snesitiv',
  'mail_domain' => 'Snesitiv',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtpauth' => 1,
  'mail_smtphost' => 'Snesitiv',
  'mail_smtpport' => 'Snesitiv',
  'mail_smtpname' => 'Snesitiv',
  'mail_smtppassword' => 'Snesitiv',
  'app_install_overwrite' => 
  array (
    0 => 'occweb',
  ),
  'maintenance' => false,
  'loglevel' => 2,
  'config_is_read_only' => false,
  'overwriteprotocol' => 'https',
);

The output of your Apache/nginx/system log in /var/log/____:

I would love to give you those logs, if I would know how I can get to them in docker apache. I'll keep searching and hopefully post it.

PancakeConnaisseur · October 10, 2020, 9:02am

i think it was a mistake to use old backups just because you can’t disable an app over the UI. There was/is a problem with the Nextcloud servers which are very slow at the moment. When you go to the app list in the UI, it tries to connect to the Nextcloud servers and this fails. This is why, it didn’t finish loading. You can also disable apps using the occ command. You should do this next time. This method does not contact the app store in any way AFAIK and immediately disables/enables an app.

I don’t understand at what backup you are right now. Most likely your Nextcloud files version don’t match the Nextcloud version of the db. You should restore a backup that both includes the db and the php files.

Snowest · October 10, 2020, 9:08am

Thanks for the very fast answere. That’s what I thought I did. I restored the latest version before installing carnet and restored the files before carnet was instaled.

So I need to play with the backups and hope to finde the right one.

Would a update to NC19.0.3 or 19.0.4 help fixing the database?

PancakeConnaisseur · October 10, 2020, 9:17am

I would not update at the moment. I don’t think it will solve anything. I can see that 19.0.3 does contain the column calenderdata so maybe your nextcloud files are 19.0.3 but the db is older. You would need to to check each minor version of nextcloud and see whether it’s install files create a celenderdata, but even if you find out, you will be only able to solve this by upgrading. You can’t downgrade your Nextcloud version.

I don’t know what nextbackup is, therefore I can’t say unfortunately.

Restoring a working backup is still your best bet, IMHO.

Snowest · October 10, 2020, 9:21am

okay, thanks, I will give it an other try. Just to make sure, my system run on 19.0.2. I do not have 19.0.3

How do you backup your nextcloud if you don’t know nextbackup?

PancakeConnaisseur · October 10, 2020, 9:27am

Borg Backup

PancakeConnaisseur · October 10, 2020, 9:29am

i quickly checked nextbackup. The documentation says that it only backups the database. So I think, this is the problem, as I suspected. Your files and database don’t match.

Snowest · October 10, 2020, 9:31am

The errors are searching for the table calendardata and oc_calendardata

but by searching older databse backup, I can’t finde this table.
I will do some more research about it, and if I’m lost, I will ask here again

Thanks you for your time and quick answers

Snowest · October 11, 2020, 8:41am

No matter what backup I restore, the database is broken. So it seams to be inpossible to fix it. I will do a new instalation of NC.

But I would like to learn from my mistakes. So after installing carnet (this app broke everything) I couldn’t access the appstore anymore.
First error mention by @PancakeConnaisseur was to not deinstall the app with occ. I “panicked” and loaded a nextbackup backup. This did not help at all, WHY?
Then I resored the whole Nextcloud folder from my Backup which I do on my NAS. This did not help either.

So I don’t know how to restore a functional Backup the next time this would happen. This scares my the moste. If someone could give me some adiveces, this would be great.

Reinstalling a fresh NC installation should not be a big deal.

PancakeConnaisseur · October 11, 2020, 8:57am

I think, you need to have backups that both include all files and the db from the same moment in time. I personally find it easier to use something file based that simply copies your database and nextcloud php files. You could do it better and more efficient but since storage is cheap, this is fine for me personally.

Snowest · October 11, 2020, 8:59am

And this would be done by borg backup right? Is it working on a synology nas as well? Maybe as docker? I will read in details the documentation.
Now I’m about to make a fresh install of nc19.4.

Snowest · October 11, 2020, 10:08am

I juste installed nc19.4 and it is very slow. Is this what you mentioned, right? Is there a soltution for that?

Snowest · October 11, 2020, 10:11am

mybe you know that. Can I copy the data folder from nc 19.2 to the new 19.4? Is this making problems?

Snowest · October 11, 2020, 10:29am

I got this on a absolutly fresh install

[appstoreFetcher] Warning: Could not connect to appstore: cURL error 28: Connection timed out after 60000 milliseconds (see https://curl.haxx.se/libcurl/c/libcurl-errors.html)

GET /cron.php
from 192.168.1.109 at 2020-10-11T10:27:01+00:00