Error when starting Collabora: INSERT INTO `oc_richdocuments_wopi`

iexos · April 11, 2020, 5:51pm

Hi,

I cannot get Collabora to work. I set it up following the official docs, with the system:

Debian 10
Nextcloud 18.0.3
newest collabora/code container
certbot

When trying to open a document with Collabora, it doesn’t work and get the following entry in the Nextcloud logs:

[richdocuments] Error: Doctrine\DBAL\Exception\InvalidFieldNameException: An exception occurred while executing 'INSERT INTO `oc_richdocuments_wopi`(`fileid`,`owner_uid`,`editor_uid`,`version`,`canwrite`,`server_host`,`token`,`expiry`,`template_destination`,`hide_download`,`direct`,`is_remote_token`,`template_id`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)' with params [12034, "ncadmin", "ncadmin", 0, true, "https:\/\/cloud.mydomain.de\/", "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", 1586628256, 0, false, false, false, 0]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'template_id' in 'field list' 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 ... ]}, "INSERT INTO `oc ... )", {1: 12034,2: "nc ... 0})
 3. /var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php line 255
    Doctrine\DBAL\Statement->execute()
 4. /var/www/nextcloud/lib/public/AppFramework/Db/Mapper.php line 129
    OCP\AppFramework\Db\Mapper->execute("INSERT INTO `*P ... )", [12034,"ncadmin" ... 0])
 5. /var/www/nextcloud/apps/richdocuments/lib/Db/WopiMapper.php line 89
    OCP\AppFramework\Db\Mapper->insert(OCA\Richdocuments\Db\Wopi {id: null})
 6. /var/www/nextcloud/apps/richdocuments/lib/TokenManager.php line 205
    OCA\Richdocuments\Db\WopiMapper->generateFileToken("*** sensitive parameter replaced ***", "ncadmin", "ncadmin", "0", 1, "https://cloud.mydomain.de/", null, 0, false, false, false, 0, null)
 7. /var/www/nextcloud/apps/richdocuments/lib/Controller/DocumentController.php line 238
    OCA\Richdocuments\TokenManager->getToken("*** sensitive parameters replaced ***")
 8. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 170
    OCA\Richdocuments\Controller\DocumentController->index("*** sensitive parameter replaced ***")
 9. /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php line 99
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Richdocument ... {}, "index")
10. /var/www/nextcloud/lib/private/AppFramework/App.php line 125
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Richdocument ... {}, "index")
11. /var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php line 47
    OC\AppFramework\App::main("OCA\\Richdocume ... r", "index", OC\AppFramework\ ... {}, {_route: "richdocuments.document.index"})
12. <<closure>>
    OC\AppFramework\Routing\RouteActionHandler->__invoke({_route: "richdocuments.document.index"})
13. /var/www/nextcloud/lib/private/Route/Router.php line 299
    call_user_func(OC\AppFramework\ ... {}, {_route: "richdocuments.document.index"})
14. /var/www/nextcloud/lib/base.php line 1008
    OC\Route\Router->match("/apps/richdocuments/index")
15. /var/www/nextcloud/index.php line 38
    OC::handleRequest()

GET /apps/richdocuments/index?fileId=12034&requesttoken=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
from xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx by ncadmin at 2020-04-11T17:34:16+00:00

Magnus · April 13, 2020, 12:21pm

I just did an update on the docker image and getting the exact same error. Have no idea how to solve it though…

prebag · April 13, 2020, 2:18pm

Hello
I have the same problem.
I have upgraded from Nextcloud 16.0.8 to NextCloud 17.0.5 on Ubuntu 18.04 LTS.
I have also updated my docker collabora CODE image.

— Error –
==> /var/log/nextcloud/nextcloud.log <==
{“reqId”:“H9Xa4mvkwIGoBVUHjvNN”,“level”:3,“time”:“2020-04-13T14:11:31+00:00”,“remoteAddr”:“XXX.XX.XX.XXX”,“user”:“root”,“app”:“richdocuments”,“method”:“GET”,“url”:"/index.php/apps/richdocuments/index?fileId=5514606&requesttoken=pS6DvO%2FBJ3T9PGl3yc%2BHGBHNDtEWwATPCQWjnBecd1s%3D%3A6ma2%2B5mjREOPF1glsaXSanSXV7pEp12iSzzRpXuuBBw%3D",“message”:{“Exception”:“Doctrine\DBAL\Exception\InvalidFieldNameException”,“Message”:"An exception occurred while executing ‘INSERT INTO oc_richdocuments_wopi(fileid,owner_uid,editor_uid,version,canwrite,server_host,token,expiry,template_destination,hide_download,direct,is_remote_token,template_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)’ with params [5514606, “root”, “root”, 0, true, “https:\/\/myserver.com\/”, “zN7nXHlBAQOiMcTWb3Nihw8fYOFpbZ2a”, 1586788891, 0, false, false, false, 0]:\n\nSQLSTATE[42S22]:
…
— End —

~# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
collabora/code latest d04c1851c1a6 4 days ago 1.39GB

Thanks
Prebag

AndreG · April 13, 2020, 6:35pm

same here on Photon OS 3 as Dockerhost. Nextcloud 18.0.3, collabora/code:lastest just updated. Fresh generated loolwsd.xml.

AndreG · April 13, 2020, 8:44pm

Should we open an issue here? https://github.com/nextcloud/richdocuments/issues/new/choose

one_player · April 13, 2020, 10:11pm

I have the same problem.
Please, who has a solution.

Magnus · April 14, 2020, 5:46am

I guess it can’t hurt? Not sure if the issue is with the addon or the actual docker image though.

one_player · April 14, 2020, 11:05am

I had the same problem. The error message mentioned the absence of some columns in the ‘oc_richdocuments_wopi’ table. I manually added these columns after this the problem has changed. Then i discovered that the app store has a WOPI app. After installing it, it worked)))
Link app
https://apps.nextcloud.com/apps/wopi
Sorry for the bad English/ This is not my nativbe language

one_player · April 14, 2020, 11:13am

Who can dump the table oc_richdocuments_wopi to check their table for correct fields.
Owncloud: v18.0.3
DB: mysql
Collabora Online: v 3.5.3

AndreG · April 14, 2020, 11:30am

This is mine:

CREATE TABLE IF NOT EXISTS `oc_richdocuments_wopi` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner_uid` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `editor_uid` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `guest_displayname` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `fileid` int(11) NOT NULL,
  `version` int(11) NOT NULL DEFAULT 0,
  `canwrite` tinyint(1) NOT NULL DEFAULT 0,
  `server_host` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT 'localhost',
  `token` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `expiry` int(10) unsigned DEFAULT NULL,
  `template_destination` int(11) DEFAULT NULL,
  `hide_download` tinyint(1) NOT NULL DEFAULT 0,
  `direct` tinyint(1) NOT NULL DEFAULT 0,
  `is_remote_token` tinyint(1) NOT NULL DEFAULT 0,
  `remote_server` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `remote_server_token` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `share` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rd_wopi_token_idx` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;

one_player · April 14, 2020, 11:32am

Спасибо. То что нужно.

prebag · April 14, 2020, 11:48am

For me, the column template_id is missing on table “oc_richdocuments_wopi”

Thank you, for me the problem is solved ( thank you one_player )

i added the missing column template_id on table “oc_richdocuments_wopi”
From Apps Menu, i enable the module Wopi
From Settings Menu > Office Online > URL (and Port) of Office Online-server, i have put the URL of my collabora online server

one_player · April 14, 2020, 12:47pm

Attention!!! After installing the WOPI application, check if the modified files are saved. It turned out that the files are not saved. I had to uninstall the application and return Collabora back. But now everything works fine. No idea what is going on here.

AndreG · April 14, 2020, 12:50pm

can you give us your statement to create the column? I dont know the data type e.g.

one_player · April 14, 2020, 1:01pm

``template_id INT(11) NULL DEFAULT NULL,
This works, but I’m not sure if this is correct.

Spied in file /apps/richdocuments/lib/Migration/Version2060Date20200302131958.php
$table->addColumn(‘template_id’, ‘integer’, [
‘notnull’ => false,
length’ => 4,
]);

AndreG · April 14, 2020, 1:56pm

This is very good founding, one_player. This means for me that:

The bug is in the richdocuments app, not the docker image
The missing column was introduced in an update on March, 2th 2020
For some reason the db migration script did not run or did not run correctly.

i think with this knowledge we should open an issue in gitlab for richdocuments app.

EDIT: Im still not sure. Is it the fault of the app when the db migration script did not run? or maybe is it the docker images (nextcloud base installation) fault?

AndreG · April 14, 2020, 3:10pm

After inserting the missing column manually i got in collabora container output:

wsd-00006-00021 2020-04-14 15:08:33.875965 [ websrv_poll ] WRN  client - server version mismatch, disabling browser cache. Expected: a96fdaa| wsd/FileServer.cpp:280

anyone else the same?

one_player · April 14, 2020, 3:50pm

I got
Open file
WRN WOPI host did not pass optional access_token_ttl| wsd/FileServer.cpp:611
Edit, save adn close file
[ prisoner_poll ] WRN Waking up dead poll thread [docbroker_035], started: true, finished: true| ./net/Socket.hpp:551
[ prisoner_poll ] WRN Waking up dead poll thread [docbroker_035], started: true, finished: true| ./net/Socket.hpp:551
[ prisoner_poll ] WRN Waking up dead poll thread [docbroker_035], started: false, finished: true| ./net/Socket.hpp:551
[ prisoner_poll ] WRN Waking up dead poll thread [docbroker_035], started: false, finished: true| ./net/Socket.hpp:551
But everything works

prebag · April 15, 2020, 8:55am

prebag · April 15, 2020, 9:00am

no i have not this error