I did a rather brutal upgrade from 27 to 31 and had some serious snags with the database layout changes, which did not seem to be nicely handled by the updater. The main problem was that my LDAP connection stopped working entirely, since the LDAP DB table was in the wrong format and had the wrong name. For anyone else with similar problems, here’s how I attacked the upgrade:
Ruthlessly repeat update until there are no more updates to be done.
sudo -u www-data php /var/www/nextcloud/updater/updater.phar --no-interaction --no-backup
occ db:add-missing-indices
occ db:add-missing-columns
occ db:add-missing-primary-keys
occ maintenance:repair --include-expensive
Make use of wonderful new db:schema functions that were just installed:
occ db:schema:expected > expected.txt
occ db:schema:export > actual.txt
Diff the expected and actual DB layouts and find that they are horrendously different. Work out some SQL commands to get everything into shape.
Drop all unexpected tables:
Lots of tables
DROP TABLE nextcloud.oc_circles_circle;
DROP TABLE nextcloud.oc_circles_event;
DROP TABLE nextcloud.oc_circles_member;
DROP TABLE nextcloud.oc_circles_membership;
DROP TABLE nextcloud.oc_circles_mount;
DROP TABLE nextcloud.oc_circles_mountpoint;
DROP TABLE nextcloud.oc_circles_remote;
DROP TABLE nextcloud.oc_circles_share_lock;
DROP TABLE nextcloud.oc_circles_token;
DROP TABLE nextcloud.oc_file_metadata;
DROP TABLE nextcloud.oc_files_reminders;
DROP TABLE nextcloud.oc_mail_accounts;
DROP TABLE nextcloud.oc_mail_aliases;
DROP TABLE nextcloud.oc_mail_attachments;
DROP TABLE nextcloud.oc_mail_classifiers;
DROP TABLE nextcloud.oc_mail_coll_addresses;
DROP TABLE nextcloud.oc_mail_mailboxes;
DROP TABLE nextcloud.oc_mail_message_tags;
DROP TABLE nextcloud.oc_mail_messages;
DROP TABLE nextcloud.oc_mail_provisionings;
DROP TABLE nextcloud.oc_mail_recipients;
DROP TABLE nextcloud.oc_mail_tags;
DROP TABLE nextcloud.oc_mail_trusted_senders;
DROP TABLE nextcloud.oc_migrations;
DROP TABLE nextcloud.oc_notifications;
DROP TABLE nextcloud.oc_notifications_pushhash;
DROP TABLE nextcloud.oc_photos_albums;
DROP TABLE nextcloud.oc_photos_albums_collabs;
DROP TABLE nextcloud.oc_photos_albums_files;
DROP TABLE nextcloud.oc_privacy_admins;
DROP TABLE nextcloud.oc_talk_attendees;
DROP TABLE nextcloud.oc_talk_bridges;
DROP TABLE nextcloud.oc_talk_commands;
DROP TABLE nextcloud.oc_talk_internalsignaling;
DROP TABLE nextcloud.oc_talk_rooms;
DROP TABLE nextcloud.oc_talk_sessions;
DROP TABLE nextcloud.oc_trusted_servers;
DROP TABLE nextcloud.oc_user_status;
DROP TABLE nextcloud.oc_recent_contact;
DROP TABLE nextcloud.oc_richdocuments_assets;
DROP TABLE nextcloud.oc_richdocuments_direct;
DROP TABLE nextcloud.oc_richdocuments_wopi;
Convert the LDAP tables to the expected format & name:
DROP TABLE nextcloud.oc_ldap_group_members;
DROP TABLE nextcloud.oc_ldap_group_members_bk;
USE nextcloud;
CREATE TABLE \`oc_ldap_group_membership\` (
\`id\` bigint(20) NOT NULL AUTO_INCREMENT,
\`groupid\` varchar(255) NOT NULL DEFAULT '',
\`userid\` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (\`id\`),
UNIQUE KEY \`user_ldap_membership_unique\` (\`groupid\`,\`userid\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Convert the Database to use dynamic row format as prompted in the Web UI:
Lots of ALTER TABLEs
USE nextcloud;
ALTER TABLE oc_flow_operations_scope ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_storages_credentials ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_whats_new ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_dav_shares ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_authtoken ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_external_mounts ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_appconfig ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_twofactor_providers ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendarobjects ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_rooms_md ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_collres_resources ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_collres_collections ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_reminders ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_collres_accesscache ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_user_transfer_owner ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_profile_config ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_ldap_user_mapping ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_filecache ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_comments ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_systemtag ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendarsubscriptions ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_flow_operations ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_activity ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_federated_reshares ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_properties ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_activity_mq ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_ratelimit_entries ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_file_locks ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_groups ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_systemtag_group ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_invitations ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_comments_read_markers ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_bruteforce_attempts ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendars ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_flow_checks ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_systemtag_object_mapping ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_twofactor_backupcodes ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_group_user ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_schedulingobjects ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_vcategory ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_group_admin ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_users ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_vcategory_to_object ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_accounts ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_dav_cal_proxy ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_authorized_groups ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_share_external ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendarchanges ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_filecache_extended ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_external_applicable ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_external_options ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_addressbookchanges ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_direct_edit ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_files_trash ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_rooms ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_cards ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_addressbooks ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_webauthn ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_resources ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_cards_properties ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_preferences ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_share ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_mounts ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_mimetypes ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_text_steps ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_accounts_data ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_directlink ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_external_config ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_ldap_group_mapping ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_known_users ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_jobs ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_text_documents ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_oauth2_access_tokens ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_login_flow_v2 ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_storages ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendar_resources_md ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_oauth2_clients ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_text_sessions ROW_FORMAT=DYNAMIC;
ALTER TABLE oc_calendarobjects_props ROW_FORMAT=DYNAMIC;
One last batch of occ for good measure:
occ db:add-missing-indices
occ db:add-missing-columns
occ db:add-missing-primary-keys
occ maintenance:repair --include-expensive
occ files:scan --all
So, quite a painful update in the end. Test all this in a VM first!
If the Nextcloud updater could do a bit more work on converting the database to be compatible in future, that would be great!