Deck: Check for full table consistency in first migration

Created on 17 Jun 2020 · 9Comments · Source: nextcloud/deck

Describe the bug
I'm unable to create a first board.

To Reproduce
Steps to reproduce the behavior:

Click "New Board"
Enter name of the new board
Click arrow the create the board
Nothing happens. The nextcloud log shows an error related to deck, see the end of the report.

Expected behavior
A new board should show up.

Client details:

OS: Ubuntu 20.04
Browser FirefoxChrome (bug occurs with both browsers)
Version 77.0.1
Device: Desktop

Server details

Operating system:
Linux 4.19.118-v7l+ #1311 SMP Mon Apr 27 14:26:42 BST 2020 armv7l
Web server:
Apache (fpm-fcgi)
Database:
mysql 10.3.15
PHP version:
7.3.4-2
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, sodium, standard, cgi-fcgi, mysqlnd, PDO, xml, bz2, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, gmp, iconv, igbinary, intl, json, ldap, exif, mysqli, pdo_mysql, Phar, posix, readline, redis, shmop, SimpleXML, smbclient, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, libsmbclient, Zend OPcache
Nextcloud version: (see Nextcloud admin page)
18.0.5 - 18.0.5.1
Where did you install Nextcloud from:
Nextcloudpi
Signing status:
Array
(
)

Login as admin user into your Nextcloud and access
http://example.com/index.php/settings/integrity/failed
paste the results here.

No errors have been found.
List of activated apps:

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder

Enabled:

accessibility: 1.4.0
activity: 2.11.0
calendar: 2.0.3
cloud_federation_api: 1.1.0
comments: 1.8.0
contacts: 3.3.0
dav: 1.14.0
deck: 1.0.2
federatedfilesharing: 1.8.0
federation: 1.8.0
files: 1.13.1
files_pdfviewer: 1.7.0
files_rightclick: 0.15.2
files_sharing: 1.10.1
files_trashbin: 1.8.0
files_versions: 1.11.0
files_videoplayer: 1.7.0
firstrunwizard: 2.7.0
issuetemplate: 0.6.0
logreader: 2.3.0
lookup_server_connector: 1.6.0
news: 14.1.11
nextcloud_announcements: 1.7.0
notifications: 2.6.0
oauth2: 1.6.0
password_policy: 1.8.0
photos: 1.0.0
previewgenerator: 2.3.0
privacy: 1.2.0
provisioning_api: 1.8.0
rainloop: 6.1.4
recommendations: 0.6.0
serverinfo: 1.8.0
settings: 1.0.0
sharebymail: 1.8.0
spreed: 8.0.9
support: 1.1.1
survey_client: 1.6.0
systemtags: 1.8.0
text: 2.0.0
theming: 1.9.0
twofactor_backupcodes: 1.7.0
updatenotification: 1.8.0
viewer: 1.2.0
workflowengine: 2.0.0
Disabled:
admin_audit
encryption
files_external
files_texteditor
gallery
nextcloudpi
notes
previewgenerator.orig
tasks
user_ldap

Nextcloud configuration:

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

or

Insert your config.php content here
Make sure to remove all sensitive content such as passwords. (e.g. database password, passwordsalt, secret, smtp password, …)

{
"passwordsalt": "REMOVED SENSITIVE VALUE",
"secret": "REMOVED SENSITIVE VALUE",
"trusted_domains": {
"0": "localhost",
"5": "nextcloudpi.local",
"7": "nextcloudpi",
"8": "nextcloudpi.lan",
"1": "192.168.3.40",
"2": "nextcloud.mydomain.de",
"11": "92.117.237.191",
"4": "nextcloud.mydomain.de"
},
"datadirectory": "REMOVED SENSITIVE VALUE",
"dbtype": "mysql",
"version": "18.0.5.1",
"overwrite.cli.url": "https:\/\/nextcloud.mydomain.de\/",
"dbname": "REMOVED SENSITIVE VALUE",
"dbhost": "REMOVED SENSITIVE VALUE",
"dbport": "",
"dbtableprefix": "oc_",
"mysql.utf8mb4": true,
"dbuser": "REMOVED SENSITIVE VALUE",
"dbpassword": "REMOVED SENSITIVE VALUE",
"installed": true,
"instanceid": "REMOVED SENSITIVE VALUE",
"memcache.local": "\OC\Memcache\Redis",
"memcache.locking": "\OC\Memcache\Redis",
"redis": {
"host": "REMOVED SENSITIVE VALUE",
"port": 0,
"timeout": 0,
"password": "REMOVED SENSITIVE VALUE"
},
"tempdirectory": "\/media\/SANKDISK128GB\/ncdata\/tmp",
"mail_smtpmode": "sendmail",
"mail_smtpauthtype": "LOGIN",
"mail_from_address": "REMOVED SENSITIVE VALUE",
"mail_domain": "REMOVED SENSITIVE VALUE",
"preview_max_x": "2048",
"preview_max_y": "2048",
"jpeg_quality": "60",
"overwriteprotocol": "https",
"loglevel": "2",
"log_type": "file",
"maintenance": false,
"theme": "",
"data-fingerprint": "caf11f6d9859ea7b113b611080eaa9c5",
"logfile": "\/media\/SANKDISK128GB\/ncdata\/nextcloud.log"
}
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
no

Logs

Nextcloud log (data/nextcloud.log)

[core] Error: Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT id,title,duedate,notified from `oc_deck_cards` WHERE duedate < NOW() AND NOT archived AND deleted_at = 0':

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause' at <<closure>>

0. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
   Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
1. /var/www/nextcloud/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/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php line 162
   Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "SELECT id,title ... 0", [])
3. /var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php line 255
   Doctrine\DBAL\Statement->execute()
4. /var/www/nextcloud/apps/deck/lib/Db/DeckMapper.php line 49
   OCP\AppFramework\Db\Mapper->execute("SELECT id,title ... 0", [], null, null)
5. /var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php line 343
   OCA\Deck\Db\DeckMapper->execute("SELECT id,title ... 0", [], null, null)
6. /var/www/nextcloud/apps/deck/lib/Db/CardMapper.php line 146
   OCP\AppFramework\Db\Mapper->findEntities("SELECT id,title ... 0")
7. /var/www/nextcloud/apps/deck/lib/Cron/ScheduledNotifications.php line 58
   OCA\Deck\Db\CardMapper->findOverdue()
8. /var/www/nextcloud/lib/private/BackgroundJob/Job.php line 61
   OCA\Deck\Cron\ScheduledNotifications->run(null)
9. /var/www/nextcloud/cron.php line 125
   OC\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})

at 2020-06-17T18:15:43+00:00

Full unformatted log entry

`{"reqId":"h06sTLLzoTYQTpwRL1jH","level":3,"time":"2020-06-17T18:15:43+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":{"Exception":"Doctrine\\DBAL\\Exception\\InvalidFieldNameException","Message":"An exception occurred while executing 'SELECT id,title,duedate,notified fromoc_deck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0':\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause'","Code":0,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":169,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\AbstractMySQLDriver","type":"->","args":["An exception occurred while executing 'SELECT id,title,duedate,notified fromoc_deck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0':\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause'",{"errorInfo":["42S22",1054,"Unknown column 'deleted_at' in 'where clause'"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"}]},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":145,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["42S22",1054,"Unknown column 'deleted_at' in 'where clause'"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"An exception occurred while executing 'SELECT id,title,duedate,notified fromoc_deck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0':\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause'"]},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php","line":162,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["42S22",1054,"Unknown column 'deleted_at' in 'where clause'"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"SELECT id,title,duedate,notified fromoc_deck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[]]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":255,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/DeckMapper.php","line":49,"function":"execute","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":343,"function":"execute","class":"OCA\\Deck\\Db\\DeckMapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/CardMapper.php","line":146,"function":"findEntities","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0"]},{"file":"/var/www/nextcloud/apps/deck/lib/Cron/ScheduledNotifications.php","line":58,"function":"findOverdue","class":"OCA\\Deck\\Db\\CardMapper","type":"->","args":[]},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":61,"function":"run","class":"OCA\\Deck\\Cron\\ScheduledNotifications","type":"->","args":[null]},{"file":"/var/www/nextcloud/cron.php","line":125,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->","args":[{"__class__":"OC\\BackgroundJob\\JobList"},{"__class__":"OC\\Log"}]}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php","Line":60,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause'","Code":"42S22","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php","line":153,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->","args":[null]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":255,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/DeckMapper.php","line":49,"function":"execute","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":343,"function":"execute","class":"OCA\\Deck\\Db\\DeckMapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/CardMapper.php","line":146,"function":"findEntities","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0"]},{"file":"/var/www/nextcloud/apps/deck/lib/Cron/ScheduledNotifications.php","line":58,"function":"findOverdue","class":"OCA\\Deck\\Db\\CardMapper","type":"->","args":[]},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":61,"function":"run","class":"OCA\\Deck\\Cron\\ScheduledNotifications","type":"->","args":[null]},{"file":"/var/www/nextcloud/cron.php","line":125,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->","args":[{"__class__":"OC\\BackgroundJob\\JobList"},{"__class__":"OC\\Log"}]}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":119,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'deleted_at' in 'where clause'","Code":"42S22","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","line":117,"function":"execute","class":"PDOStatement","type":"->","args":[null]},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php","line":153,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->","args":[null]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":255,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/DeckMapper.php","line":49,"function":"execute","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php","line":343,"function":"execute","class":"OCA\\Deck\\Db\\DeckMapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0",[],null,null]},{"file":"/var/www/nextcloud/apps/deck/lib/Db/CardMapper.php","line":146,"function":"findEntities","class":"OCP\\AppFramework\\Db\\Mapper","type":"->","args":["SELECT id,title,duedate,notified fromPREFIXdeck_cardsWHERE duedate < NOW() AND NOT archived AND deleted_at = 0"]},{"file":"/var/www/nextcloud/apps/deck/lib/Cron/ScheduledNotifications.php","line":58,"function":"findOverdue","class":"OCA\\Deck\\Db\\CardMapper","type":"->","args":[]},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":61,"function":"run","class":"OCA\\Deck\\Cron\\ScheduledNotifications","type":"->","args":[null]},{"file":"/var/www/nextcloud/cron.php","line":125,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->","args":[{"__class__":"OC\\BackgroundJob\\JobList"},{"__class__":"OC\\Log"}]}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":117}},"CustomMessage":"Error while running background job (class: OCA\\Deck\\Cron\\ScheduledNotifications, arguments: )"},"userAgent":"--","version":"18.0.5.1","id":"5eea5dcf70566"}


#### Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...
```

1. to develop bug

Source

roteiro

👍1

All 9 comments

Did you have an older Version installed at some point in the past? It seems the database is somehow not having the expected table schema.

juliushaertl on 17 Jun 2020

You mean an older version of deck or of the main Nextcloud software?
I continuously updating Nextcloud through the Nextcloudpi updater.
I also tried deck in the past if i remember correctly. Whats the best way to repair the database?

roteiro on 17 Jun 2020

Just installed a fresh version. Same problem.
I have old data in the db and the column last_modified is not present.

This is the error I get:

Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'SELECT id, title, owner, color, archived, deleted_at, 0 as shared, last_modified FROM `oc_deck_boards` WHERE owner = ? AND last_modified > ? UNION SELECT boards.id, title, owner, color, archived, deleted_at, 1 as shared, last_modified FROM `oc_deck_boards` as boards JOIN `oc_deck_board_acl` as acl ON boards.id=acl.board_id WHERE acl.participant=? AND acl.type=? AND boards.owner != ? AND last_modified > ?' with params ["User", -1, "User", 0, "User", -1]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_modified' in 'field list'

Quick & dirty solution:
Added a column last_mofified with type int, unsigned, default: 0
to the following tables:

deck_boards
deck_stacks
... more maybe affected

amittel on 18 Jun 2020

This could happen if your last installed version was < 0.5 and you now installed 1.0. If you don't have any data in deck you want to keep, I'd generally recommend to do a fresh install of the deck app and cleanup the old database before as @HLFH has described it here https://github.com/nextcloud/deck/issues/1985#issuecomment-640740666

juliushaertl on 18 Jun 2020

But let me see if we can have a migration that checks for proper layout of the tables in such scenarios.

juliushaertl on 18 Jun 2020

👍1

This could happen if your last installed version was < 0.5 and you now installed 1.0. If you don't have any data in deck you want to keep, I'd generally recommend to do a fresh install of the deck app and cleanup the old database before as @HLFH has described it here #1985 (comment)

Since I have no important data, this worked for me.

amittel on 18 Jun 2020

👍1

@amittel Great that the workaround works!

@juliushaertl Yes, I guess adding _a migration that checks for proper layout of the tables in such scenarios_ would permanently fix this issue.

HLFH on 18 Jun 2020

Thanks! Manually removing deck tables and deck related entries in the migrations table as a workaround did the job.

roteiro on 18 Jun 2020

I run into a similar issue which makes it impossible to use labels:

"An exception occurred while executing 'INSERT INTO `oc_deck_labels`(`title`,`color`,`board_id`,`last_modified`) VALUES(?,?,?,?)' with params [\"\\u00dcbung\", \"7C31CC\", 19, 1604953292]:\n\nSQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_modified' in 'field list'"

As I already have data, I cannot use the workaround.
Something with the migrations is still not working. Please work on this.