Spreed: Can not re-enable Talk on Postgres after installing Spreed on Nextcloud 12

[enoch85]

Steps to reproduce

1. Try to install Talk

Expected behaviour

No error

Actual behaviour

An exception occurred while executing 'INSERT INTO "oc_talk_participants" ("userId", "roomId", "lastPing", "sessionId", "participantType") VALUES(?, ?, ?, ?, ?)' with params ["[email protected]", 9, 1494860929, "0", 0]: SQLSTATE[42703]: Undefined column: 7 ERROR: column "userId" of relation "oc_talk_participants" does not exist LINE 1: INSERT INTO "oc_talk_participants" ("userId", "roomId", "las... ^

Server configuration detail

Operating system: Linux 4.4.0-109-generic #132-Ubuntu SMP Tue Jan 9 19:52:39 UTC 2018 x86_64

Webserver: Apache/2.4.18 (Ubuntu) (apache2handler)

Database: pgsql PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

PHP version: 7.0.22-0ubuntu0.16.04.1
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, PDO, xml, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, imagick, imap, intl, json, ldap, exif, mcrypt, pdo_pgsql, pdo_sqlite, pgsql, Phar, posix, readline, redis, shmop, SimpleXML, smbclient, sockets, sqlite3, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, libsmbclient, Zend OPcache

Nextcloud version: 13.0.0 Beta 4 - 13.0.0.9

Updated from an older Nextcloud/ownCloud or fresh install:

Where did you install Nextcloud from: unknown

Signing status

Array

List of activated apps

Enabled:
 - activity: 2.6.1
 - admin_notifications: 1.0.1
 - bruteforcesettings: 1.0.3
 - calendar: 1.5.7
 - comments: 1.3.0
 - contacts: 2.0.1
 - dav: 1.4.6
 - federatedfilesharing: 1.3.1
 - federation: 1.3.0
 - files: 1.8.0
 - files_downloadactivity: 1.2.0
 - files_pdfviewer: 1.2.0
 - files_sharing: 1.5.0
 - files_texteditor: 2.5.1
 - files_trashbin: 1.3.0
 - files_versions: 1.6.0
 - files_videoplayer: 1.2.0
 - firstrunwizard: 2.2.1
 - gallery: 18.0.0
 - impersonate: 1.0.2
 - issuetemplate: 0.3.0
 - logreader: 2.0.0
 - lookup_server_connector: 1.1.0
 - mail: 0.7.7
 - nextcloud_announcements: 1.2.0
 - notes: 2.3.2
 - notifications: 2.1.2
 - oauth2: 1.1.0
 - ocsms: 1.12.2
 - password_policy: 1.3.0
 - previewgenerator: 1.0.9
 - provisioning_api: 1.3.0
 - qownnotesapi: 17.5.0
 - richdocuments: 1.12.39
 - serverinfo: 1.3.0
 - sharebymail: 1.3.0
 - socialsharing_diaspora: 1.0.2
 - socialsharing_email: 1.0.3
 - socialsharing_facebook: 1.0.2
 - socialsharing_googleplus: 1.0.2
 - socialsharing_twitter: 1.0.2
 - survey_client: 1.1.0
 - systemtags: 1.3.0
 - theming: 1.4.1
 - twofactor_backupcodes: 1.2.3
 - twofactor_totp: 1.4.1
 - updatenotification: 1.3.0
 - workflowengine: 1.3.0
Disabled:
 - admin_audit
 - encryption
 - files_external
 - spreed
 - user_external
 - user_ldap

Configuration (config/config.php)

{
    "updatechecker": false,
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "localhost",
        "192.168.4.111",
        "cloud.techandme.se",
        "office.techandme.se"
    ],
    "datadirectory": "***REMOVED SENSITIVE VALUE***",
    "overwrite.cli.url": "https:\/\/cloud.techandme.se\/",
    "dbtype": "pgsql",
    "version": "13.0.0.9",
    "dbname": "***REMOVED SENSITIVE VALUE***",
    "dbhost": "***REMOVED SENSITIVE VALUE***",
    "dbtableprefix": "oc_",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "logtimezone": "Europe\/Stockholm",
    "installed": true,
    "instanceid": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpmode": "smtp",
    "mail_smtpauth": 1,
    "mail_smtpport": "587",
    "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpauthtype": "LOGIN",
    "mail_from_address": "***REMOVED SENSITIVE VALUE***",
    "mail_domain": "***REMOVED SENSITIVE VALUE***",
    "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
    "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
    "allowZipDownload": true,
    "session_lifetime": 1800,
    "forcessl": true,
    "logfile": "\/var\/log\/nfs-share\/nextcloud.log",
    "loglevel": 2,
    "memcache.local": "\\OC\\Memcache\\Redis",
    "filelocking.enabled": true,
    "memcache.distributed": "\\OC\\Memcache\\Redis",
    "memcache.locking": "\\OC\\Memcache\\Redis",
    "redis": {
        "host": "***REMOVED SENSITIVE VALUE***",
        "port": 0,
        "timeout": 0,
        "dbindex": 0
    },
    "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
    "forwarded_for_headers": [
        "HTTP_X_FORWARDED",
        "HTTP_FORWARDED_FOR"
    ],
    "maintenance": false,
    "mail_smtpsecure": "tls",
    "htaccess.RewriteBase": "\/",
    "theme": "",
    "updater.release.channel": "stable",
    "trashbin_retention_obligation": "auto, 30",
    "activity_expire_days": 60
}

Are you using external storage, if yes which one: local/smb/sftp/...

Are you using encryption: no

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

LDAP configuration (delete this part if not used)

With access to your command line run e.g.:
sudo -u www-data php occ ldap:show-config
from within your Nextcloud installation folder

Without access to your command line download the data/owncloud.db to your local
computer or access your SQL server remotely and run the select query:
SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap';


Eventually replace sensitive data as the name/IP-address of your LDAP server or groups.

Client configuration

Browser: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36

Operating system:

Logs

Browser log

Insert your webserver log here 

Nextcloud log

Insert your Nextcloud log here

Error | core | An exception occurred while executing 'INSERT INTO "oc_talk_participants" ("userId", "roomId", "lastPing", "sessionId", "participantType") VALUES(?, ?, ?, ?, ?)' with params ["[email protected]", 9, 1494860929, "0", 0]: SQLSTATE[42703]: Undefined column: 7 ERROR: column "userId" of relation "oc_talk_participants" does not exist LINE 1: INSERT INTO "oc_talk_participants" ("userId", "roomId", "las... ^ | 2018-01-11T15:03:58+0100
-- | -- | -- | --
Error | PHP | Undefined index: participantType at /var/www/nextcloud/apps/spreed/lib/Migration/Version2001Date20171026134605.php#227 | 2018-01-11T15:03:58+0100
Error | core | An exception occurred while executing 'INSERT INTO "oc_talk_participants" ("userId", "roomId", "lastPing", "sessionId", "participantType") VALUES(?, ?, ?, ?, ?)' with params ["[email protected]", 6, 1494860929, "0", 0]: SQLSTATE[42703]: Undefined column: 7 ERROR: column "userId" of relation "oc_talk_participants" does not exist LINE 1: INSERT INTO "oc_talk_participants" ("userId", "roomId", "las... ^ | 2018-01-11T15:03:29+0100
Error | PHP | Undefined index: participantType at /var/www/nextcloud/apps/spreed/lib/Migration/Version2001Date20171026134605.php#227 | 2018-01-11T15:03:29+0100

Browser log

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

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

[enoch85]

https://github.com/nextcloud/server/issues/7794

cc @rullzer

[nickvergessen]

That was fixed with https://github.com/nextcloud/spreed/pull/537/files

Since than these columns should not exist in the final anymore. And I made sure myself, that installing with postgres works. In case you are updating, you could try to purge all tables before the update.

[enoch85]

I'll try to purge all the talk/spreed tables and try again when I patched with your PR.

[enoch85]

Or btw, I'll wait for the update instead. Pretty big PR.

[nickvergessen]

That is part of 3.0.0 which is why I wonder

[enoch85]

Aaha was fixed. Didn't notice.

I purged the DB entries yesterday. Will try to install it again clean.

[enoch85]

@nickvergessen Nope, still not installing.

An exception occurred while executing 'SELECT * FROM "oc_spreedme_rooms"': SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_spreedme_rooms" does not exist LINE 1: SELECT * FROM "oc_spreedme_rooms" ^

[nickvergessen]

Well this now is because you removed the database tables, but didn't remove the info that the table was created.

When removing "everything" of spreed in the database make sure to also:

1. Remove appconfig, activity and notifications for app spreed
2. Remove migrations starting with OCA\Spreed\ (might need to use \\ depending on your DB)

[nickvergessen]

btw am I right that you had spreed installed in 12 already?

[umgfoin]

Same issue with NC13RC1 and talk 3.0.0.
Tried a migration, first: The activation process complained about duplicate values in oc_talk_rooms.
Removed application and deleted oc_talk_* tables.
Upon app-installation the following error was reported:
An exception occurred while executing 'INSERT INTO "oc_talk_participants" ("userId", "roomId", "lastPing", "sessionId", "participantType") VALUES(?, ?, ?, ?, ?)' with params ["admin", 1, 0, "0", 0]: SQLSTATE[42703]: Undefined column: 7 FEHLER: Spalte »userId« von Relation »oc_talk_participants« existiert nicht LINE 1: INSERT INTO "oc_talk_participants" ("userId", "roomId", "las... ^
++ umgfoin.

[enoch85]

@nickvergessen I think it's kind of dumb that it won't work if you had spreed previously installed.

Couldn't you just put some repair steps (or something) instead of me (and other users) messing with the DB? Or could you please provide some command that I could use for PostgreSQL?

To me this is a bug as it don't work out of the box and requires manual input.

btw am I right that you had spreed installed in 12 already?

Yes, that's correct.

[nickvergessen]

Yeah, well I didn't think anyone with postgres had it installed, because it didnt install on 12.

I will add some skips on the migration to just ignore those update queries and purge everything to be sure.

[nickvergessen]

3.0.1 has been pushed to the app store which should work better when updating an old version

[umgfoin]

:-( negative: Upgrading 3.0.0. to 3.0.1. failed with:

An exception occurred while executing 'INSERT INTO "oc_talk_rooms" ("name", "token", "type", "password") VALUES(?, ?, ?, ?)' with params ["", "jjmg", 3, ""]: SQLSTATE[23505]: Unique violation: 7 FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »tr_room_token« DETAIL: Schlüssel »(token)=(jjmg)« existiert bereits

Translation: [...] ERROR: duplicate key-value violating Unique-Constraint »tr_room_token« DETAIL: key»(token)=(jjmg)« already exists

[umgfoin]

Upgrade finally succeed after a manual TRUNCATE oc_talk_rooms;

[enoch85]

Can confirm that it doesn't work out of the box. I get:

An exception occurred while executing 'SELECT * FROM "oc_spreedme_rooms"': SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_spreedme_rooms" does not exist LINE 1: SELECT * FROM "oc_spreedme_rooms" ^

Haven't tried to empty the tables though.

Still, I consider this a bug as you still have to manually fiddle with the DB, IMHO.

[nickvergessen]

@enoch85 can you please send me a database backup of an instance before you attempted the update? Appconfig and all spreed/talk related tables should be enough.

@umgfoin your issues are because you half migrated already. You need to revert or manually resolve the issue (delete all spreed/talk tables, all appconfigs of the spreed app and all entries in oc_migrations)

[enoch85]

@nickvergessen Sorry, just discovered that my DB backups failed since in August. Would need to restore a complete VM from Veeam. I'll do that later this weekend. Now it's Friday :)

[enoch85]

@nickvergessen OK so I have a backup from prior to the update attempt. Can you please give me the exact table names to restore?

[hapee]

I have the same issue, slightly different:
An exception occurred while executing 'INSERT INTO "oc_talk_rooms" ("name", "token", "type", "password") VALUES(?, ?, ?, ?)' with params ["", "2vkv", 3, null]: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "tr_room_token" DETAIL: Key (token)=(2vkv) already exists.
But also interested in a solution.

[enoch85]

@hapee Are you using PostgreSQL or MariaDB/MySQL?

[hapee]

I am using PostgreSQL

[nickvergessen]

@enoch85 all tables that contain the word spreed (mostlikely oc_spreedme_messages, oc_spreedme_rooms and oc_spreedme_room_participants) and oc_appconfig

email address is <github name> @nextcloud.com

[nickvergessen]

duplicate key value violates unique constraint "tr_room_token" DETAIL: Key (token)=(2vkv) already exists.

@hapee that sounds like you tried to update twice?

[hapee]

@nickvergessen thanks for your reaction. That was what I expected as well but if I do
nextcloud=> TRUNCATE oc_talk_rooms;
as suggested above somewhere, I get a:
ERROR: relation "oc_talk_rooms" does not exist

[nickvergessen]

@hapee that's strange, either the table exists, or there are can not be duplicate entires in that table. Both at the same time make no sense. Please make sure you are in the correct database.

[enoch85]

@nickvergessen Sorry, turned out to be harder than I thought retrieving a single table from my backup.

[enoch85]

@nickvergessen Is this fixed? :)

[nickvergessen]

Closed because of lack of feedback amd steps to reproduce

[enoch85]

@nickvergessen I still have the backup if you want to have the whole DB, it's just a matter of trust. :)

[nickvergessen]

You know my email ;)

[bjarkan]

An exception occurred while executing 'ALTER TABLE oc_talk_participants ADD inCall TINYINT(1) DEFAULT '0' NOT NULL': SQLSTATE[42S02]: Base table or view not found: 1932 Table 'nextcloud_db.oc_talk_participants' doesn't exist in engine

it's still an issue

[eldelacajita]

Exact same error as @bjarkan, on a fresh install via official VM.

As the error message is different than the one in the OP here, this could be a new bug, already reported both here and here (and being denied in both places).

If someone can confirm this is still the same bug as in the OP, we will keep reporting in this thread.

[bjarkan]

found a solution... just drop oc_talk_participants and recreate it from backup... and voila ... works

[mjanssens]

Just upgraded from Mysql to Postgresql and had same sort of issue during occ db:convert-type.
The error I got:
[Doctrine\DBAL\Exception\TableNotFoundException]
An exception occurred while executing 'SELECT * FROM oc_spreedme_rooms':
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'Nextcloud.oc_spreedme_rooms' doesn't exist

I know it's not the same error message as reported, but it's related enough to post this comment here I guess.
Begin situation: NC13.0.1 (upgraded many times, stable releases), Ubuntu 16.04.4, PHP7.0, Mariadb 10.0.34, Talk 3.1.0 app installed and enabled from store.
New situation: PostgreSQL 9.6.7 on Debian 9.4.
This is how I got the db convert working with info from earlier comments:

• delete folder spreed from /apps/
• delete folder spreed from /appdata_xxxxxxxxxxxx/css/
• delete tables: oc_talk_participants, oc_talk_rooms, oc_talk_signaling (no spreedme tables present)
• delete all rows mentioning spreed in the following tables: oc_activity,oc_appconfig,oc_filecache,oc_migrations,oc_notifications,oc_preferences.
• NOT occ maintenance:repair
• execute the db convert, (occ db:convert-type --all-apps pgsql ...) no errors
• occ maintenance:repair, no errors
• Install Talk app from store, no errors
• migrated to PostgreSQL

I'm willing to file a separate issue in case you want to split into re-enabling the app after NC upgrade vs DB convert from Mariadb to PostgreSQL.

[eldelacajita]

@bjarkan Is that something I could safely try without much specific knowledge of mysql? In that case, could you explain how to do that?

I haven't found any other solution so far. @nickvergessen is there any way I can help fixing this from a user perspective?

[nickvergessen]

Full reset manual should be here now: https://github.com/nextcloud/spreed/issues/772#issuecomment-384211357

[enoch85]

@nickvergessen If that is what it takes, shouldn't we do something codewise about it? I've seen numerous issues regarding this matter in the community.

On a second thought, is it even posisble?

[nickvergessen]

Well it works all good here, I still don't see where it's struggeling for you, so if you are willing to whip everything you can do that. But we are not going to whip everything for everyone.

[Grunthos]

@nickvergessen The list of actions linked above seems to have broken the installation of the person who tried it.

What is the current best way to remove spreed? I also am unable to convert to mysql from sqlite with the missing table errors on oc_spreedme_rooms.