[Bug]: Migrating from Mysql to Postgres with occ throws 'column "last_activity" of relation "oc_talk_rooms" does not exist'

[tartard]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Context :

I am trying to migrate my current Nextcloud manually installed running MariaDB (10.11.11-MariaDB-0+deb12u1) to a new Nextcloud AIO running PostgreSQL.
I followed this documentation : https://github.com/nextcloud/all-in-one/blob/main/migration.md

According to this documentation, first I need first to convert the MariaDB into a PostgreSQL DB to be able to dump it and import it on the AIO.
So I installed Postgresql 15.12 on the former Nextcloud.

Then I executed the occ db:convert :
sudo -u www-data /usr/bin/php8.2 occ db:convert-type --all-apps --password "$PG_PASSWORD" pgsql "$PG_USER" localhost "$PG_DATABASE"

Here is the output :

root@nextcloud:/var/www/html# 
sudo -u www-data /usr/bin/php8.2 occ db:convert-type --all-apps --password "$PG_PASSWORD" pgsql "$PG_USER" localhost "$PG_DATABASE"
Creating schema in new database
 - cloud_federation_api
 - admin_audit
 - survey_client
 - privacy
 - recommendations
 - sharebymail
 - oauth2
 - calendar
 - firstrunwizard
 - spreed
 - welcome
 - theming
 - settings
 - raw
 - lookup_server_connector
 - app_api
 - workflowengine
 - encryption
 - richdocuments
 - announcementcenter
 - polls
 - nextcloud_announcements
 - support
 - user_ldap
 - systemtags
 - profile
 - files_downloadlimit
 - circles
 - files_versions
 - contactsinteraction
 - twofactor_backupcodes
 - user_status
 - provisioning_api
 - related_resources
 - bruteforcesettings
 - files_external
 - files_trashbin
 - dav
 - viewer
 - suspicious_login
 - dashboard
 - twofactor_totp
 - weather_status
 - password_policy
 - deck
 - twofactor_nextcloud_notification
 - files_reminders
 - comments
 - passwords
 - files
 - files_sharing
 - federatedfilesharing
 - contacts
 - webhook_listeners
 - files_pdfviewer
 - logreader
 - photos
 - federation
 - serverinfo
 - forms
 - notifications
 - text
 - user_saml
 - richdocumentscode
 - activity
 - updatenotification
The following tables will not be converted:
oc_appconfig_ex
oc_cengine_steps
oc_cengine_users
oc_cms_pico_websites
oc_ex_apps
oc_ex_apps_daemons
oc_ex_apps_routes
oc_ex_event_handlers
oc_ex_occ_commands
oc_ex_settings_forms
oc_ex_speech_to_text
oc_ex_speech_to_text_q
oc_ex_task_processing
oc_ex_text_processing
oc_ex_text_processing_q
oc_ex_translation
oc_ex_translation_q
oc_ex_ui_files_actions
oc_ex_ui_scripts
oc_ex_ui_states
oc_ex_ui_styles
oc_ex_ui_top_menu
oc_file_metadata
oc_onlyoffice_filekey
oc_onlyoffice_instance
oc_onlyoffice_permissions
oc_preferences_ex
oc_tables_columns
oc_tables_contexts_context
oc_tables_contexts_navigation
oc_tables_contexts_page
oc_tables_contexts_page_content
oc_tables_contexts_rel_context_node
oc_tables_favorites
oc_tables_log
oc_tables_rows
oc_tables_row_cells_datetime
oc_tables_row_cells_number
oc_tables_row_cells_selection
oc_tables_row_cells_text
oc_tables_row_cells_usergroup
oc_tables_row_sleeves
oc_tables_shares
oc_tables_tables
oc_tables_views
oc_talk_commands
Continue with the conversion (y/n)? [n] y
 - oc_migrations
Skipping migrations table because it was already filled by running the migrations
 - oc_collres_collections
    0 [>---------------------------] < 1 sec
 - oc_collres_resources
    0 [>---------------------------] < 1 sec
 - oc_appconfig
 299/299 [============================] 100% < 1 sec/< 1 sec
 - oc_storages
 28/28 [============================] 100% < 1 sec/< 1 sec
 - oc_login_flow_v2
    0 [>---------------------------] < 1 sec
 - oc_collres_accesscache
    0 [>---------------------------] < 1 sec
 - oc_mimetypes
 53/53 [============================] 100% < 1 sec/< 1 sec
 - oc_mounts
chunked query, 8 chunks
 7182/7182 [============================] 100% 3 secs/3 secs
 - oc_filecache
chunked query, 39 chunks
 38117/38117 [============================] 100% 22 secs/22 secs
 - oc_group_user
 134/134 [============================] 100% < 1 sec/< 1 sec
 - oc_group_admin
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_filecache_extended
chunked query, 3 chunks
 2803/2803 [============================] 100% < 1 sec/< 1 sec
 - oc_users
 27/27 [============================] 100% < 1 sec/< 1 sec
 - oc_groups
 20/20 [============================] 100% < 1 sec/< 1 sec
 - oc_direct_edit
    0 [>---------------------------] < 1 sec
 - oc_properties
 116/116 [============================] 100%  1 sec/1 sec 
 - oc_storages_credentials
    0 [>---------------------------] < 1 sec
 - oc_webauthn
    0 [>---------------------------] < 1 sec
 - oc_jobs
 81/81 [============================] 100% < 1 sec/< 1 sec
 - oc_accounts_data
 378/378 [============================] 100% < 1 sec/< 1 sec
 - oc_known_users
    0 [>---------------------------] < 1 sec
 - oc_authorized_groups
    0 [>---------------------------] < 1 sec
 - oc_authtoken
 111/111 [============================] 100% < 1 sec/< 1 sec
 - oc_profile_config
 29/29 [============================] 100% < 1 sec/< 1 sec
 - oc_ratelimit_entries
 4/4 [============================] 100% < 1 sec/< 1 sec
 - oc_reactions
chunked query, 5 chunks
 4602/4602 [============================] 100%  1 sec/1 sec 
 - oc_comments_read_markers
 3/3 [============================] 100% < 1 sec/< 1 sec
 - oc_accounts
 27/27 [============================] 100% < 1 sec/< 1 sec
 - oc_text2image_tasks
    0 [>---------------------------] < 1 sec
 - oc_files_metadata
 618/618 [============================] 100%  1 sec/1 sec 
 - oc_files_metadata_index
    0 [>---------------------------] < 1 sec
 - oc_textprocessing_tasks
    0 [>---------------------------] < 1 sec
 - oc_comments
chunked query, 20 chunks
 19866/19866 [============================] 100% 10 secs/10 secs
 - oc_taskprocessing_tasks
    0 [>---------------------------] < 1 sec
 - oc_sec_signatory
    0 [>---------------------------] < 1 sec
 - oc_file_locks
    0 [>---------------------------] < 1 sec
 - oc_preferences
chunked query, 3 chunks
 2769/2769 [============================] 100%  1 sec/1 sec 
 - oc_privacy_admins
    0 [>---------------------------] < 1 sec
 - oc_systemtag_group
    0 [>---------------------------] < 1 sec
 - oc_oauth2_clients
    0 [>---------------------------] < 1 sec
 - oc_oauth2_access_tokens
    0 [>---------------------------] < 1 sec
 - oc_systemtag_object_mapping
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_calendar_appt_configs
    0 [>---------------------------] < 1 sec
 - oc_calendar_appt_bookings
    0 [>---------------------------] < 1 sec
 - oc_bruteforce_attempts
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_talk_rooms
   0/417 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 68:
                                                                               
  An exception occurred while executing a query: SQLSTATE[42703]: Undefined c  
  olumn: 7 ERROR:  column "last_activity" of relation "oc_talk_rooms" does no  
  t exist                                                                      
  LINE 1: ...pe", "password", "active_since", "active_guests", "last_acti...   
                                                               ^               
                                                                               

In Exception.php line 24:
                                                                               
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "last_activity" of rela  
  tion "oc_talk_rooms" does not exist                                          
  LINE 1: ...pe", "password", "active_since", "active_guests", "last_acti...   
                                                               ^               
                                                                               

In Statement.php line 130:
                                                                               
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "last_activity" of rela  
  tion "oc_talk_rooms" does not exist                                          
  LINE 1: ...pe", "password", "active_since", "active_guests", "last_acti...   
                                                               ^               
                                                                               

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

root@nextcloud:/var/www/html# ^C

When I compare the table oc_talk_roomw from MariaDB and PostgreSQL I see that the schema created by the convert tool doesn't match the original schema from Nextcloud both in MariaDB and in Postgres AIO :

MariaDB

MariaDB [nx1697124964]> describe oc_talk_rooms
-> ;
+----------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | | |
| token | varchar(32) | YES | UNI | | |
| type | int(11) | NO | | NULL | |
| password | varchar(255) | YES | | | |
| active_since | datetime | YES | | NULL | |
| active_guests | int(10) unsigned | NO | | 0 | |
| last_activity | datetime | YES | MUL | NULL | |
| last_message | bigint(20) | YES | | 0 | |
| object_type | varchar(64) | YES | | | |
| object_id | varchar(64) | YES | | | |
| read_only | int(11) | NO | | 0 | |
| lobby_state | int(11) | NO | | 0 | |
| lobby_timer | datetime | YES | | NULL | |
| assigned_hpb | int(11) | YES | | NULL | |
| sip_enabled | smallint(5) unsigned | NO | | 0 | |
| description | longtext | YES | | NULL | |
| listable | smallint(5) unsigned | YES | MUL | 0 | |
| call_flag | int(11) | NO | | 0 | |
| default_permissions | int(11) | NO | | 0 | |
| call_permissions | int(11) | NO | | 0 | |
| remote_server | varchar(512) | YES | MUL | NULL | |
| remote_token | varchar(32) | YES | | NULL | |
| message_expiration | int(11) | NO | | 0 | |
| breakout_room_mode | int(11) | NO | | 0 | |
| breakout_room_status | int(11) | NO | | 0 | |
| avatar | varchar(24) | YES | | | |
| call_recording | int(11) | NO | | 0 | |
| recording_consent | smallint(5) unsigned | NO | | 0 | |
| has_federation | smallint(5) unsigned | NO | | 0 | |
| mention_permissions | int(11) | NO | | 0 | |
+----------------------+----------------------+------+-----+---------+----------------+
31 rows in set (0.002 sec)

PostgreSQL

nextcloud_db-# \d+ oc_talk_rooms
Table "public.oc_talk_rooms"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+--------------------------------+-----------+----------+-------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('oc_talk_rooms_id_seq'::regclass) | plain | | |
name | character varying(255) | | | ''::character varying | extended | | |
token | character varying(32) | | | ''::character varying | extended | | |
type | integer | | not null | | plain | | |
password | character varying(255) | | | ''::character varying | extended | | |
active_since | timestamp(0) without time zone | | | NULL::timestamp without time zone | plain | | |
active_guests | integer | | not null | 0 | plain | | |
sip_enabled | smallint | | not null | 0 | plain | | |
description | text | | | ''::text | extended | | |
listable | smallint | | | 0 | plain | | |
call_flag | integer | | not null | 0 | plain | | |
default_permissions | integer | | not null | 0 | plain | | |
call_permissions | integer | | not null | 0 | plain | | |
remote_server | character varying(512) | | | NULL::character varying | extended | | |
remote_token | character varying(32) | | | NULL::character varying | extended | | |
message_expiration | integer | | not null | 0 | plain | | |
breakout_room_mode | integer | | not null | 0 | plain | | |
breakout_room_status | integer | | not null | 0 | plain | | |
avatar | character varying(24) | | | ''::character varying | extended | | |
call_recording | integer | | not null | 0 | plain | | |
recording_consent | smallint | | not null | 0 | plain | | |
has_federation | smallint | | not null | 0 | plain | | |
mention_permissions | integer | | not null | 0 | plain | | |
Indexes:
"oc_talk_rooms_pkey" PRIMARY KEY, btree (id)
"remote_id" btree (remote_server, remote_token)
"tr_listable" btree (listable)
"tr_room_token" UNIQUE, btree (token)
Access method: heap

Here is the comparison between the table oc_talk_rooms from MariaDB, PostgreSQL created by the convert tool, and PostgreSQL created by AIO, some columns are missing from the table created by the convert tool :

MariaDB	AIO Postgres	Convert tool Postgres
id	id	id
name	name	name
token	token	token
type	type	type
password	password	password
active_since	active_since	active_since
active_guests	active_guests	active_guests
last_activity	last_activity	sip_enabled
last_message	last_message	description
object_type	object_type	listable
object_id	object_id	call_flag
read_only	read_only	default_permissions
lobby_state	lobby_state	call_permissions
lobby_timer	lobby_timer	remote_server
assigned_hpb	assigned_hpb	remote_token
sip_enabled	sip_enabled	message_expiration
description	description	breakout_room_mode
listable	listable	breakout_room_status
call_flag	call_flag	avatar
default_permissions	default_permissions	call_recording
call_permissions	call_permissions	recording_consent
remote_server	remote_server	has_federation
remote_token	remote_token	mention_permissions
message_expiration	message_expiration
breakout_room_mode	breakout_room_mode
breakout_room_status	breakout_room_status
avatar	avatar
call_recording	call_recording
recording_consent	recording_consent
has_federation	has_federation
mention_permissions	mention_permissions

Steps to reproduce

1. Install Nextcloud with MariaDB/MySQL or probably any DB other than Postgresql, and install the Talk app on Nextcloud.
2. Follow the documentation here https://github.com/nextcloud/all-in-one/blob/main/migration.md#migrate-the-files-and-the-database
3. Run occ db:convert-type --all-apps --password "$PG_PASSWORD" pgsql "$PG_USER" localhost "$PG_DATABASE"

Expected behavior

Migration should work fine.

Nextcloud Server version

31

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

Upgraded to a MAJOR version (ex. 31 to 32)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": {
            "0": "localhost",
            "2": "nextcloud.rochefort-numerique.fr"
        },
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "31.0.2.1",
        "overwrite.cli.url": "https:\/\/nextcloud.rochefort-numerique.fr",
        "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***",
        "default_phone_region": "FR",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "maintenance": false,
        "app_install_overwrite": [
            "mindmap_app",
            "cms_pico",
            "whiteboard",
            "passwords"
        ],
        "theme": "",
        "loglevel": 0,
        "maintenance_window_start": 2
    }
}

List of activated Apps

Enabled:
  - activity: 4.0.0
  - announcementcenter: 7.1.0
  - app_api: 5.0.2
  - bruteforcesettings: 4.0.0
  - calendar: 5.2.0
  - circles: 31.0.0
  - cloud_federation_api: 1.14.0
  - comments: 1.21.0
  - contacts: 7.0.4
  - contactsinteraction: 1.12.0
  - dashboard: 7.11.0
  - dav: 1.33.0
  - deck: 1.15.0
  - federatedfilesharing: 1.21.0
  - federation: 1.21.0
  - files: 2.3.1
  - files_downloadlimit: 4.0.0
  - files_pdfviewer: 4.0.0
  - files_reminders: 1.4.0
  - files_sharing: 1.23.1
  - files_trashbin: 1.21.0
  - files_versions: 1.24.0
  - firstrunwizard: 4.0.0
  - forms: 5.0.4
  - logreader: 4.0.0
  - lookup_server_connector: 1.19.0
  - nextcloud_announcements: 3.0.0
  - notifications: 4.0.0
  - oauth2: 1.19.1
  - password_policy: 3.0.0
  - passwords: 2025.2.10
  - polls: 7.4.1
  - privacy: 3.0.0
  - profile: 1.0.0
  - provisioning_api: 1.21.0
  - raw: 0.1.0
  - recommendations: 4.0.0
  - related_resources: 2.0.0
  - richdocuments: 8.6.3
  - richdocumentscode: 24.4.1302
  - serverinfo: 3.0.0
  - settings: 1.14.0
  - sharebymail: 1.21.0
  - spreed: 21.0.1
  - support: 3.0.0
  - survey_client: 3.0.0
  - systemtags: 1.21.1
  - text: 5.0.0
  - theming: 2.6.1
  - twofactor_backupcodes: 1.20.0
  - updatenotification: 1.21.0
  - user_saml: 6.5.0
  - user_status: 1.11.0
  - viewer: 4.0.0
  - webhook_listeners: 1.2.0
  - welcome: 1.2.1
  - workflowengine: 2.13.0
Disabled:
  - admin_audit: 1.21.0
  - encryption: 2.19.0
  - files_external: 1.23.0
  - photos: 4.0.0-dev.1 (installed 2.3.0)
  - suspicious_login: 9.0.1
  - twofactor_nextcloud_notification: 5.0.0
  - twofactor_totp: 13.0.0-dev.0
  - user_ldap: 1.22.0
  - weather_status: 1.11.0 (installed 1.7.0)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

Additional info

No response

[biguenique]

Very same issue here. Perhaps someone knowledgeable could throw in some indications on a way to circumvent the issue to complete the migration until a fix is published?

I guess uninstalling or skipping the migration of the Talk app altogether would work, with some obvious down side (loosing all existing Talk data)...

[daukus]

Same issue here when migrating even with Talk app disabled.

[Timond]

Exact same issue for me. I had to remove the Talk app for the database migration to work.

[biguenique]

Same issue here when migrating even with Talk app disabled.

I was also able to complete the database conversion by deactivating the Talk app and running the occ db:convert-type command without the  --all-apps option. This then only converts the tables of activated apps, skipping deactivated apps altogether.

(Got the cue from #24884 (comment))

[tartard]

Goot to know that this workaround exists but in my case I cannot disable the Talk app as we have a lot of data in Talk chats and need to migrate it.

[fabwal]

Same problem here, and also not able to deactivate Talk.
As a workaround I simply injected an ALTER TABLE statement after the schema was build (before migration):

ALTER TABLE oc_talk_rooms
ADD COLUMN last_activity DATETIME NULL,
ADD COLUMN last_message BIGINT(20) NULL DEFAULT 0,
ADD COLUMN object_type VARCHAR(64) NULL,
ADD COLUMN object_id VARCHAR(64) NULL,
ADD COLUMN read_only INT(11) DEFAULT 0,
ADD COLUMN lobby_state INT(11) DEFAULT 0,
ADD COLUMN lobby_timer DATETIME NULL,
ADD COLUMN assigned_hpb INT(11) NULL;

This went through and the migration worked.

[tartard]

Of course! You are right, creating the columns by hand is a good workaround. Luckily this was the only table concerned with this problem in my case.

[joshtrichards]

Maybe be same as #50667. If so, #51866 and backports may be the solution. Test if possible.

[Espo02]

biguenique Thank you for this Information.
If there will be an other postgresql "starter"
Login als user postgres, run

psql

\c DATABASENAME

ALTER TABLE oc_talk_rooms
ADD COLUMN last_activity TIMESTAMP NULL,
ADD COLUMN last_message BIGINT DEFAULT 0,
ADD COLUMN object_type VARCHAR(64) NULL,
ADD COLUMN object_id VARCHAR(64) NULL,
ADD COLUMN read_only INTEGER DEFAULT 0,
ADD COLUMN lobby_state INTEGER DEFAULT 0,
ADD COLUMN lobby_timer TIMESTAMP NULL,
ADD COLUMN assigned_hpb INTEGER NULL;

---

Thats it.