Nextcloud 31 Database Upgrades

README.md

Nextcloud 31 Database Upgrades

After upgrading from Nextcloud 30.x to 31.x I get the following warning in the admin interface:

Incorrect row format found in your database. ROW_FORMAT=Dynamic offers the best database performances for Nextcloud. Please update row format on the following list: oc_collres_collections, oc_phonetrack_geofences, oc_storages, ...

For more details see the documentation↗.

Databaase is MariaDB.

nc31_database_upgrade.sh

#!/usr/bin/env bash
# *****************************************************************************
# Backup and upgrade the database for Nextcloud 31 (row-format dynamic)
#
# Edited by Alain Wolf on Sat, 12. Apr 2025 18:06
# *****************************************************************************

set -e -u

# ---------------------------------------------------------
# Settings
# ---------------------------------------------------------

# Installed Nextcloud instance
_NEXTCLOUD_INSTALL_DIR="/var/www/urown.net/nextcloud"

# Database name of this Nextcloud instance
DB_NAME="nc_urown"

# Ensure PHP version of web-server matches
_PHP_CLI="/usr/bin/php8.2"

# Recomended PHP.INI options
_PHP_OPTIONS="
    always_populate_raw_post_data=-1
    apc.enable_cli=1
    apc.enabled=1
    default_charset=UTF-8
    error_reporting=22527
    mbstring.func_overload=0
    memory_limit=-1
    opcache.enable_cli=1
    opcache.enable=1
    output_buffering=0
"
# Database backup file name
DB_BACKUP="${HOME}/${DB_NAME}-$(date +%Y%m%d).sql"

# Compressed backup file name
DB_BACKUP_COMPRESSED="${DB_BACKUP}.gz"

# List of tables to update
TABLES=(
  "oc_accounts_data"
  "oc_accounts"
  "oc_activity_mq"
  "oc_activity"
  "oc_addressbookchanges"
  "oc_addressbooks"
  "oc_announcements_map"
  "oc_announcements"
  "oc_appconfig"
  "oc_appointments_hash"
  "oc_appointments_pref"
  "oc_appointments_sync"
  "oc_authorized_groups"
  "oc_authtoken"
  "oc_bruteforce_attempts"
  "oc_calendar_appt_bookings"
  "oc_calendar_appt_configs"
  "oc_calendar_invitations"
  "oc_calendar_reminders"
  "oc_calendar_resources_md"
  "oc_calendar_resources"
  "oc_calendar_rooms_md"
  "oc_calendar_rooms"
  "oc_calendarchanges"
  "oc_calendarobjects_props"
  "oc_calendarobjects"
  "oc_calendars"
  "oc_calendarsubscriptions"
  "oc_cards_properties"
  "oc_cards"
  "oc_circle_circles"
  "oc_circle_clouds"
  "oc_circle_groups"
  "oc_circle_gsevents"
  "oc_circle_gsshares_mp"
  "oc_circle_gsshares"
  "oc_circle_links"
  "oc_circle_members"
  "oc_circle_shares"
  "oc_circle_tokens"
  "oc_circles_circle"
  "oc_circles_event"
  "oc_circles_member"
  "oc_circles_membership"
  "oc_circles_mount"
  "oc_circles_mountpoint"
  "oc_circles_remote"
  "oc_circles_share_lock"
  "oc_circles_token"
  "oc_collectives_pages"
  "oc_collectives_shares"
  "oc_collectives"
  "oc_collres_accesscache"
  "oc_collres_collections"
  "oc_collres_resources"
  "oc_comments_read_markers"
  "oc_comments"
  "oc_cookbook_categories"
  "oc_cookbook_keywords"
  "oc_cookbook_names"
  "oc_dav_cal_proxy"
  "oc_dav_shares"
  "oc_deck_assigned_labels"
  "oc_deck_assigned_users"
  "oc_deck_attachment"
  "oc_deck_board_acl"
  "oc_deck_boards"
  "oc_deck_cards"
  "oc_deck_labels"
  "oc_deck_stacks"
  "oc_direct_edit"
  "oc_directlink"
  "oc_federated_reshares"
  "oc_file_locks"
  "oc_filecache_extended"
  "oc_filecache"
  "oc_files_antivirus"
  "oc_files_avir_status"
  "oc_files_trash"
  "oc_flow_checks"
  "oc_flow_operations_scope"
  "oc_flow_operations"
  "oc_forms_v2_answers"
  "oc_forms_v2_forms"
  "oc_forms_v2_options"
  "oc_forms_v2_questions"
  "oc_forms_v2_submissions"
  "oc_group_admin"
  "oc_group_folders_acl"
  "oc_group_folders_groups"
  "oc_group_folders_manage"
  "oc_group_folders_trash"
  "oc_group_folders"
  "oc_group_user"
  "oc_groups"
  "oc_jitsi_rooms"
  "oc_jobs"
  "oc_known_users"
  "oc_login_address"
  "oc_login_flow_v2"
  "oc_mail_accounts"
  "oc_mail_aliases"
  "oc_mail_attachments"
  "oc_mail_coll_addresses"
  "oc_mail_local_messages"
  "oc_mail_mailboxes"
  "oc_mail_message_tags"
  "oc_mail_messages"
  "oc_mail_provisionings"
  "oc_mail_recipients"
  "oc_mail_tags"
  "oc_mail_trusted_senders"
  "oc_maps_address_geo"
  "oc_maps_apikeys"
  "oc_maps_device_points"
  "oc_maps_devices"
  "oc_maps_favorite_shares"
  "oc_maps_favorites"
  "oc_maps_photos"
  "oc_maps_tracks"
  "oc_migrations"
  "oc_mimetypes"
  "oc_mounts"
  "oc_news_feeds"
  "oc_news_folders"
  "oc_news_items"
  "oc_notes_meta"
  "oc_notifications_pushhash"
  "oc_notifications_settings"
  "oc_notifications"
  "oc_oauth2_access_tokens"
  "oc_oauth2_clients"
  "oc_phonetrack_devices"
  "oc_phonetrack_filtersb"
  "oc_phonetrack_geofences"
  "oc_phonetrack_points"
  "oc_phonetrack_proxims"
  "oc_phonetrack_pubshares"
  "oc_phonetrack_sessions"
  "oc_phonetrack_shares"
  "oc_phonetrack_tileserver"
  "oc_preferences"
  "oc_preview_generation"
  "oc_privacy_admins"
  "oc_profile_config"
  "oc_properties"
  "oc_ratelimit_entries"
  "oc_reader_bookmarks"
  "oc_reader_prefs"
  "oc_recent_contact"
  "oc_richdocuments_assets"
  "oc_richdocuments_direct"
  "oc_richdocuments_template"
  "oc_richdocuments_wopi"
  "oc_schedulingobjects"
  "oc_share_external"
  "oc_share"
  "oc_social_3_action"
  "oc_social_3_actor"
  "oc_social_3_cache_actor"
  "oc_social_3_cache_doc"
  "oc_social_3_client"
  "oc_social_3_follow"
  "oc_social_3_hashtag"
  "oc_social_3_instance"
  "oc_social_3_req_queue"
  "oc_social_3_stream_act"
  "oc_social_3_stream_dest"
  "oc_social_3_stream_queue"
  "oc_social_3_stream_tag"
  "oc_social_3_stream"
  "oc_storages_credentials"
  "oc_storages"
  "oc_suspicious_login_model"
  "oc_suspicious_login"
  "oc_systemtag_group"
  "oc_systemtag_object_mapping"
  "oc_systemtag"
  "oc_talk_attendees"
  "oc_talk_bridges"
  "oc_talk_commands"
  "oc_talk_internalsignaling"
  "oc_talk_rooms"
  "oc_talk_sessions"
  "oc_text_documents"
  "oc_text_sessions"
  "oc_text_steps"
  "oc_timemanager_client"
  "oc_timemanager_commit"
  "oc_timemanager_project"
  "oc_timemanager_share"
  "oc_timemanager_task"
  "oc_timemanager_time"
  "oc_timetracker_client"
  "oc_timetracker_goal"
  "oc_timetracker_locked_project_allowed_tag"
  "oc_timetracker_lpa_tags"
  "oc_timetracker_project"
  "oc_timetracker_tag"
  "oc_timetracker_timeline_entry"
  "oc_timetracker_timeline"
  "oc_timetracker_user_to_client"
  "oc_timetracker_user_to_project"
  "oc_timetracker_work_interval"
  "oc_timetracker_workint_to_tag"
  "oc_timetracker_workinterval_to_tag"
  "oc_trusted_servers"
  "oc_twofactor_admincodes"
  "oc_twofactor_backupcodes"
  "oc_twofactor_providers"
  "oc_twofactor_totp_secrets"
  "oc_twofactor_u2f_registrations"
  "oc_user_status"
  "oc_user_transfer_owner"
  "oc_users"
  "oc_vcategory_to_object"
  "oc_vcategory"
  "oc_webauthn"
  "oc_whats_new"
)

# ---------------------------------------------------------

# Check for root privileges
if [[ ${EUID} -ne 0 ]]; then
  echo "This script must be run as root. Please use sudo."
  exit 1
fi

# Get the Linux system user who owns this Nextcloud instance
_nextcloud_owner=$(stat -c '%U' "${_NEXTCLOUD_INSTALL_DIR}")

# Preload opcache as the nextcloud user
_PHP_OPTIONS="${_PHP_OPTIONS} opcache.preload_user=${_nextcloud_owner}"

_php_cmd_opts=""
for _php_option in ${_PHP_OPTIONS}; do
  _php_cmd_opts="${_php_cmd_opts} --define ${_php_option}"
done

cd "${_NEXTCLOUD_INSTALL_DIR}" || exit 1

# Turn on Mainternace Mode
# shellcheck disable=SC2086
sudo -u "${_nextcloud_owner}" \
  "${_PHP_CLI}" ${_php_cmd_opts} -f ./occ -- --no-ansi --no-warnings maintenance:mode --on

# Backup the database first
# shellcheck disable=SC2024
sudo mysqldump --single-transaction --quick --routines --triggers --events \
  --add-drop-table --add-drop-database --extended-insert=FALSE \
  --skip-comments --skip-extended-insert \
  "${DB_NAME}" >"${DB_BACKUP}" || echo "Error: Failed to backup the database."
gzip -9 "${DB_BACKUP}" "${DB_BACKUP_COMPRESSED}" || echo "Error: Failed to compress the backup file."
echo "Database backup completed: ${DB_BACKUP_COMPRESSED}"

# Check if the backup file exists
if [[ ! -f ${DB_BACKUP_COMPRESSED} ]]; then
  echo "Error: Backup file not found."
  exit 1
fi
# Check if the backup file is empty
if [[ ! -s ${DB_BACKUP_COMPRESSED} ]]; then
  echo "Error: Backup file is empty."
  exit 1
fi

# Update row format for each table
for TABLE in "${TABLES[@]}"; do
  echo "Updating row format for table: ${TABLE}"
  sudo mysql -e "ALTER TABLE ${DB_NAME}.${TABLE} ROW_FORMAT=DYNAMIC;" || echo "Error: Failed to update table ${TABLE}."
done

echo "Row format update completed for all tables."

# Disable maintenance mode
echo "Disabling maintenance mode..."
sudo -u "${_nextcloud_owner}" \
  "${_PHP_CLI}" ${_php_cmd_opts} -f ./occ -- --no-ansi --no-warnings maintenance:mode --off
echo "Maintenance mode disabled."

echo "Database upgrade script completed successfully."

nc31_database_upgrade.sql

-- SQL script to update row format to DYNAMIC for all listed tables
SET SESSION sql_mode = '';

-- Replace 'your_database_name' with the name of your Nextcloud database
USE nc_urown;

-- List of tables to update
SET @tables = 'oc_collres_collections, oc_phonetrack_geofences, oc_storages, oc_social_3_follow, oc_maps_photos, oc_circles_mount, oc_richdocuments_template, oc_calendar_rooms_md, oc_dav_cal_proxy, oc_authorized_groups, oc_calendar_appt_bookings, oc_collectives_pages, oc_calendar_rooms, oc_group_admin, oc_social_3_client, oc_authtoken, oc_phonetrack_pubshares, oc_notifications_pushhash, oc_mail_messages, oc_whats_new, oc_news_items, oc_text_steps, oc_twofactor_admincodes, oc_circle_gsshares_mp, oc_timetracker_timeline_entry, oc_notifications, oc_twofactor_u2f_registrations, oc_phonetrack_proxims, oc_profile_config, oc_timemanager_task, oc_vcategory_to_object, oc_mail_provisionings, oc_timetracker_user_to_client, oc_mounts, oc_files_antivirus, oc_circles_membership, oc_share, oc_group_folders_manage, oc_storages_credentials, oc_notes_meta, oc_social_3_actor, oc_directlink, oc_deck_cards, oc_deck_stacks, oc_timetracker_user_to_project, oc_timetracker_locked_project_allowed_tag, oc_deck_attachment, oc_social_3_action, oc_timemanager_project, oc_maps_favorites, oc_collres_resources, oc_timetracker_work_interval, oc_deck_board_acl, oc_jobs, oc_group_folders_trash, oc_share_external, oc_user_status, oc_mail_tags, oc_flow_operations_scope, oc_news_feeds, oc_phonetrack_tileserver, oc_calendar_resources_md, oc_circles_token, oc_collres_accesscache, oc_systemtag, oc_circle_gsshares, oc_text_sessions, oc_timetracker_project, oc_timetracker_goal, oc_forms_v2_options, oc_migrations, oc_circle_links, oc_announcements_map, oc_talk_commands, oc_mail_message_tags, oc_calendar_reminders, oc_deck_assigned_labels, oc_systemtag_group, oc_twofactor_totp_secrets, oc_twofactor_providers, oc_phonetrack_shares, oc_ratelimit_entries, oc_files_trash, oc_circles_mountpoint, oc_talk_attendees, oc_groups, oc_social_3_cache_doc, oc_timetracker_client, oc_maps_devices, oc_cookbook_categories, oc_files_avir_status, oc_timemanager_share, oc_timetracker_workint_to_tag, oc_privacy_admins, oc_circle_circles, oc_mail_local_messages, oc_mail_mailboxes, oc_calendar_appt_configs, oc_suspicious_login_model, oc_timetracker_workinterval_to_tag, oc_reader_prefs, oc_suspicious_login, oc_timemanager_time, oc_known_users, oc_social_3_stream, oc_preferences, oc_circle_clouds, oc_collectives_shares, oc_social_3_stream_dest, oc_calendarchanges, oc_appointments_sync, oc_cards, oc_preview_generation, oc_richdocuments_assets, oc_flow_checks, oc_jitsi_rooms, oc_timetracker_tag, oc_timemanager_client, oc_phonetrack_points, oc_social_3_cache_actor, oc_deck_labels, oc_forms_v2_forms, oc_cookbook_names, oc_richdocuments_direct, oc_oauth2_clients, oc_federated_reshares, oc_mail_attachments, oc_properties, oc_calendar_invitations, oc_calendars, oc_filecache_extended, oc_circles_remote, oc_social_3_hashtag, oc_addressbooks, oc_calendarsubscriptions, oc_comments, oc_appconfig, oc_flow_operations, oc_login_flow_v2, oc_social_3_stream_queue, oc_circles_share_lock, oc_forms_v2_questions, oc_forms_v2_submissions, oc_mail_trusted_senders, oc_social_3_req_queue, oc_group_user, oc_talk_bridges, oc_timemanager_commit, oc_activity_mq, oc_timetracker_timeline, oc_webauthn, oc_addressbookchanges, oc_announcements, oc_calendar_resources, oc_deck_assigned_users, oc_file_locks, oc_mail_recipients, oc_systemtag_object_mapping, oc_user_transfer_owner, oc_users, oc_comments_read_markers, oc_cards_properties, oc_cookbook_keywords, oc_social_3_instance, oc_appointments_hash, oc_mimetypes, oc_maps_favorite_shares, oc_notifications_settings, oc_circle_members, oc_trusted_servers, oc_deck_boards, oc_forms_v2_answers, oc_schedulingobjects, oc_mail_aliases, oc_circles_circle, oc_reader_bookmarks, oc_circles_member, oc_maps_device_points, oc_collectives, oc_direct_edit, oc_talk_sessions, oc_maps_apikeys, oc_recent_contact, oc_social_3_stream_act, oc_accounts, oc_vcategory, oc_maps_address_geo, oc_calendarobjects_props, oc_bruteforce_attempts, oc_login_address, oc_phonetrack_filtersb, oc_accounts_data, oc_oauth2_access_tokens, oc_phonetrack_devices, oc_talk_rooms, oc_mail_accounts, oc_circle_shares, oc_phonetrack_sessions, oc_calendarobjects, oc_circle_tokens, oc_dav_shares, oc_richdocuments_wopi, oc_twofactor_backupcodes, oc_social_3_stream_tag, oc_circle_groups, oc_circle_gsevents, oc_talk_internalsignaling, oc_maps_tracks, oc_activity, oc_mail_coll_addresses, oc_filecache, oc_text_documents, oc_circles_event, oc_timetracker_lpa_tags, oc_group_folders_groups, oc_group_folders, oc_news_folders, oc_group_folders_acl, oc_appointments_pref';

-- Split the table names and iterate through them
PREPARE stmt FROM 'ALTER TABLE ? ROW_FORMAT=DYNAMIC';
SET @table_list = REPLACE(@tables, ',', ' ');

-- Execute the prepared statement for each table
-- (You will need to implement a loop or manually execute for each table)