Selfhosted legacy, server.sh does not have dump-db command

[pki791]

Testing selfhosted legacy and want to update for v2.

Following the guide from https://standardnotes.com/help/self-hosting/legacy-migration the first command
./server.sh dump-db
gives error "Unknown command", and that seems there is no dump-db section in my server.sh.

How to proceed?

[karolsojko]

please update your self-hosted repository first. The command is present in latest code in there: https://github.com/standardnotes/self-hosted/blob/main/server.sh#L158

[pki791]

Thank You, I dumped the database and followed the guide https://standardnotes.com/help/self-hosting/legacy-migration

While starting the v2 i am permanently getting the errors like below in the logs/auth.log, repeated every second.

query: SELECT VERSION() AS `version`
info: All classes found using provided glob pattern "/opt/server/packages/auth/dist/src/Bootstrap/../../migrations/mysql/*.js" : "/opt/server/packages/auth/dist/migrations/mysql/1606470249553-init_database.js,/opt/server/packages/auth/dist/migrations/mysql/1610015065194-add_revoked_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1610025371088-add_foreign_key_to_revoked_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1612191669523-add_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1612255683992-add_roles_and_permissions_data.js,/opt/server/packages/auth/dist/migrations/mysql/1612433739754-add_more_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1614678016791-add_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1614771815912-add_encrypted_version.js,/opt/server/packages/auth/dist/migrations/mysql/1614775877590-add_encrypted_version_for_user.js,/opt/server/packages/auth/dist/migrations/mysql/1624434102642-update_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1625164984414-change_setting_timestamps.js,/opt/server/packages/auth/dist/migrations/mysql/1625227894975-change_setting_value_size.js,/opt/server/packages/auth/dist/migrations/mysql/1625767770284-change_setting_value_to_nullable.js,/opt/server/packages/auth/dist/migrations/mysql/1625807999951-add_unique_setting_index.js,/opt/server/packages/auth/dist/migrations/mysql/1626268390207-remove_unique_setting_index.js,/opt/server/packages/auth/dist/migrations/mysql/1626689139110-add_user_subscriptions.js,/opt/server/packages/auth/dist/migrations/mysql/1626717016896-fix_subscription_foreign_key.js,/opt/server/packages/auth/dist/migrations/mysql/1627638504691-move_mfa_items_to_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1629215600192-generate_user_server_key.js,/opt/server/packages/auth/dist/migrations/mysql/1629217630132-encrypt_encoded_mfa_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1629223072059-flatten_mfa_setting_and_encrypt.js,/opt/server/packages/auth/dist/migrations/mysql/1629703896382-add_markdown_math.js,/opt/server/packages/auth/dist/migrations/mysql/1629705289178-add_sensitive_flag.js,/opt/server/packages/auth/dist/migrations/mysql/1629972294975-fix_basic_and_core_user.js,/opt/server/packages/auth/dist/migrations/mysql/1630661830850-fix_encryption_version_on_mfa_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1630905831679-add-cancelled-flag.js,/opt/server/packages/auth/dist/migrations/mysql/1634064348750-add_offline_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1634102065310-add_offline_subscriptions.js,/opt/server/packages/auth/dist/migrations/mysql/1634102764797-add_offline_user_roles.js,/opt/server/packages/auth/dist/migrations/mysql/1635167238332-remove_spreadsheets_from_plus_plan.js,/opt/server/packages/auth/dist/migrations/mysql/1635172524403-add_subscription_id.js,/opt/server/packages/auth/dist/migrations/mysql/1635344737460-add_missing_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1635860707639-add_mandatory_subscription_id.js,/opt/server/packages/auth/dist/migrations/mysql/1638388151083-add_tags_and_focus_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1639998097029-add_role_version.js,/opt/server/packages/auth/dist/migrations/mysql/1640701224273-add-smart-tags.js,/opt/server/packages/auth/dist/migrations/mysql/1640862425427-remove_no_distraction_theme.js,/opt/server/packages/auth/dist/migrations/mysql/1645094434931-add_sign_in_alerts_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1646817642385-add_markdown_visual_editor_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1647253634773-fix_storage_quota_on_plans.js,/opt/server/packages/auth/dist/migrations/mysql/1647421277767-remove_user_agent.js,/opt/server/packages/auth/dist/migrations/mysql/1647862631224-add_readonly_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1648112718114-add_tag_nesting_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1648458841415-add_shared_subscription_invitations.js,/opt/server/packages/auth/dist/migrations/mysql/1648550676786-add_subscription_types.js,/opt/server/packages/auth/dist/migrations/mysql/1648629732139-add_beta_files_user_role.js,/opt/server/packages/auth/dist/migrations/mysql/1649660400536-add_subscription_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1649679945386-remove_files_settings_from_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1650890853447-change_upload_quota_tiers.js,/opt/server/packages/auth/dist/migrations/mysql/1651046286472-add_2fa.js,/opt/server/packages/auth/dist/migrations/mysql/1651064332146-remove_2fa_manager.js,/opt/server/packages/auth/dist/migrations/mysql/1652258146238-add_missing_plus_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1652786070920-remove_basic_user_role.js,/opt/server/packages/auth/dist/migrations/mysql/1652880249670-add_analytics_entities.js,/opt/server/packages/auth/dist/migrations/mysql/1654760926952-add_email_backup_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1654877423147-add_advanced_checklist_editor_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1661771230400-revoked_session_data.js,/opt/server/packages/auth/dist/migrations/mysql/1663073954000-add_subcription_sharing_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1663321030000-add_renewed_at_column.js,/opt/server/packages/auth/dist/migrations/mysql/1664971834974-groups.js,/opt/server/packages/auth/dist/migrations/mysql/1665047863774-remove-groups.js,/opt/server/packages/auth/dist/migrations/mysql/1667818539829-remove_analytics.js,/opt/server/packages/auth/dist/migrations/mysql/1671448907955-add_session_traces.js,/opt/server/packages/auth/dist/migrations/mysql/1671561748264-add_session_traces_compound_index.js,/opt/server/packages/auth/dist/migrations/mysql/1672223738686-add_authenticators.js,/opt/server/packages/auth/dist/migrations/mysql/1672227471677-add_authenticator_challenges.js,/opt/server/packages/auth/dist/migrations/mysql/1672232035280-fix_authenticator_data_types.js,/opt/server/packages/auth/dist/migrations/mysql/1672299743840-add_unique_index_on_challenges.js,/opt/server/packages/auth/dist/migrations/mysql/1672307975117-remove_compound_index.js,/opt/server/packages/auth/dist/migrations/mysql/1672317378817-add_authenticator_name.js,/opt/server/packages/auth/dist/migrations/mysql/1673951291148-add_super_editor.js,/opt/server/packages/auth/dist/migrations/mysql/1674739193816-fix-client-id-type-in-authenticators.js,/opt/server/packages/auth/dist/migrations/mysql/1678110075698-remove-sign-in-emails-on-free-acounts.js,/opt/server/packages/auth/dist/migrations/mysql/1678266947362-add-internal-team-user-role.js,/opt/server/packages/auth/dist/migrations/mysql/1678340701766-remove-authenticator-names-from-server.js,/opt/server/packages/auth/dist/migrations/mysql/1680597887475-emergency-access-invitations.js,/opt/server/packages/auth/dist/migrations/mysql/1681984540867-enable-u2f.js,/opt/server/packages/auth/dist/migrations/mysql/1682926032072-cache-entries.js,/opt/server/packages/auth/dist/migrations/mysql/1683017908845-change-cache-table-name.js,/opt/server/packages/auth/dist/migrations/mysql/1692348191367-add-transition-role.js,/opt/server/packages/auth/dist/migrations/mysql/1694000575425-add-shared-vault-users.js,/opt/server/packages/auth/dist/migrations/mysql/1694157482134-add_vaults_user.js,/opt/server/packages/auth/dist/migrations/mysql/1695283870612-add-designated-survivor.js,/opt/server/packages/auth/dist/migrations/mysql/1697704066569-remove-transition-role.js"
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'standard_notes_db' AND `TABLE_NAME` = 'migrations'
query: SELECT * FROM `standard_notes_db`.`migrations` `migrations` ORDER BY `id` DESC
107 migrations are already loaded in the database.
84 migrations were found in the source code.
RemoveNotifications1695643525793 is the last executed migration. It was executed on Mon Sep 25 2023 12:05:25 GMT+0000 (Coordinated Universal Time).
9 migrations are new migrations must be executed.
query: START TRANSACTION
query: CREATE TABLE `emergency_access_invitations` (`uuid` varchar(36) NOT NULL, `grantor_uuid` varchar(36) NOT NULL, `grantee_uuid` varchar(36) NOT NULL, `status` varchar(36) NOT NULL, `expires_at` datetime NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`uuid`)) ENGINE=InnoDB
query failed: CREATE TABLE `emergency_access_invitations` (`uuid` varchar(36) NOT NULL, `grantor_uuid` varchar(36) NOT NULL, `grantee_uuid` varchar(36) NOT NULL, `status` varchar(36) NOT NULL, `expires_at` datetime NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`uuid`)) ENGINE=InnoDB
error: Error: Table 'emergency_access_invitations' already exists
query: ROLLBACK

however, at the beginning i see a different error:

localhost:3101 is unavailable yet - waiting for it to start
localhost:3101 is unavailable yet - waiting for it to start
localhost:3101 is up. Proceeding to startup.
query: SELECT VERSION() AS `version`
info: All classes found using provided glob pattern "/opt/server/packages/auth/dist/src/Bootstrap/../../migrations/mysql/*.js" : "/opt/server/packages/auth/dist/migrations/mysql/1606470249553-init_database.js,/opt/server/packages/auth/dist/migrations/mysql/1610015065194-add_revoked_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1610025371088-add_foreign_key_to_revoked_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1612191669523-add_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1612255683992-add_roles_and_permissions_data.js,/opt/server/packages/auth/dist/migrations/mysql/1612433739754-add_more_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1614678016791-add_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1614771815912-add_encrypted_version.js,/opt/server/packages/auth/dist/migrations/mysql/1614775877590-add_encrypted_version_for_user.js,/opt/server/packages/auth/dist/migrations/mysql/1624434102642-update_roles_and_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1625164984414-change_setting_timestamps.js,/opt/server/packages/auth/dist/migrations/mysql/1625227894975-change_setting_value_size.js,/opt/server/packages/auth/dist/migrations/mysql/1625767770284-change_setting_value_to_nullable.js,/opt/server/packages/auth/dist/migrations/mysql/1625807999951-add_unique_setting_index.js,/opt/server/packages/auth/dist/migrations/mysql/1626268390207-remove_unique_setting_index.js,/opt/server/packages/auth/dist/migrations/mysql/1626689139110-add_user_subscriptions.js,/opt/server/packages/auth/dist/migrations/mysql/1626717016896-fix_subscription_foreign_key.js,/opt/server/packages/auth/dist/migrations/mysql/1627638504691-move_mfa_items_to_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1629215600192-generate_user_server_key.js,/opt/server/packages/auth/dist/migrations/mysql/1629217630132-encrypt_encoded_mfa_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1629223072059-flatten_mfa_setting_and_encrypt.js,/opt/server/packages/auth/dist/migrations/mysql/1629703896382-add_markdown_math.js,/opt/server/packages/auth/dist/migrations/mysql/1629705289178-add_sensitive_flag.js,/opt/server/packages/auth/dist/migrations/mysql/1629972294975-fix_basic_and_core_user.js,/opt/server/packages/auth/dist/migrations/mysql/1630661830850-fix_encryption_version_on_mfa_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1630905831679-add-cancelled-flag.js,/opt/server/packages/auth/dist/migrations/mysql/1634064348750-add_offline_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1634102065310-add_offline_subscriptions.js,/opt/server/packages/auth/dist/migrations/mysql/1634102764797-add_offline_user_roles.js,/opt/server/packages/auth/dist/migrations/mysql/1635167238332-remove_spreadsheets_from_plus_plan.js,/opt/server/packages/auth/dist/migrations/mysql/1635172524403-add_subscription_id.js,/opt/server/packages/auth/dist/migrations/mysql/1635344737460-add_missing_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1635860707639-add_mandatory_subscription_id.js,/opt/server/packages/auth/dist/migrations/mysql/1638388151083-add_tags_and_focus_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1639998097029-add_role_version.js,/opt/server/packages/auth/dist/migrations/mysql/1640701224273-add-smart-tags.js,/opt/server/packages/auth/dist/migrations/mysql/1640862425427-remove_no_distraction_theme.js,/opt/server/packages/auth/dist/migrations/mysql/1645094434931-add_sign_in_alerts_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1646817642385-add_markdown_visual_editor_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1647253634773-fix_storage_quota_on_plans.js,/opt/server/packages/auth/dist/migrations/mysql/1647421277767-remove_user_agent.js,/opt/server/packages/auth/dist/migrations/mysql/1647862631224-add_readonly_sessions.js,/opt/server/packages/auth/dist/migrations/mysql/1648112718114-add_tag_nesting_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1648458841415-add_shared_subscription_invitations.js,/opt/server/packages/auth/dist/migrations/mysql/1648550676786-add_subscription_types.js,/opt/server/packages/auth/dist/migrations/mysql/1648629732139-add_beta_files_user_role.js,/opt/server/packages/auth/dist/migrations/mysql/1649660400536-add_subscription_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1649679945386-remove_files_settings_from_user_settings.js,/opt/server/packages/auth/dist/migrations/mysql/1650890853447-change_upload_quota_tiers.js,/opt/server/packages/auth/dist/migrations/mysql/1651046286472-add_2fa.js,/opt/server/packages/auth/dist/migrations/mysql/1651064332146-remove_2fa_manager.js,/opt/server/packages/auth/dist/migrations/mysql/1652258146238-add_missing_plus_permissions.js,/opt/server/packages/auth/dist/migrations/mysql/1652786070920-remove_basic_user_role.js,/opt/server/packages/auth/dist/migrations/mysql/1652880249670-add_analytics_entities.js,/opt/server/packages/auth/dist/migrations/mysql/1654760926952-add_email_backup_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1654877423147-add_advanced_checklist_editor_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1661771230400-revoked_session_data.js,/opt/server/packages/auth/dist/migrations/mysql/1663073954000-add_subcription_sharing_permission.js,/opt/server/packages/auth/dist/migrations/mysql/1663321030000-add_renewed_at_column.js,/opt/server/packages/auth/dist/migrations/mysql/1664971834974-groups.js,/opt/server/packages/auth/dist/migrations/mysql/1665047863774-remove-groups.js,/opt/server/packages/auth/dist/migrations/mysql/1667818539829-remove_analytics.js,/opt/server/packages/auth/dist/migrations/mysql/1671448907955-add_session_traces.js,/opt/server/packages/auth/dist/migrations/mysql/1671561748264-add_session_traces_compound_index.js,/opt/server/packages/auth/dist/migrations/mysql/1672223738686-add_authenticators.js,/opt/server/packages/auth/dist/migrations/mysql/1672227471677-add_authenticator_challenges.js,/opt/server/packages/auth/dist/migrations/mysql/1672232035280-fix_authenticator_data_types.js,/opt/server/packages/auth/dist/migrations/mysql/1672299743840-add_unique_index_on_challenges.js,/opt/server/packages/auth/dist/migrations/mysql/1672307975117-remove_compound_index.js,/opt/server/packages/auth/dist/migrations/mysql/1672317378817-add_authenticator_name.js,/opt/server/packages/auth/dist/migrations/mysql/1673951291148-add_super_editor.js,/opt/server/packages/auth/dist/migrations/mysql/1674739193816-fix-client-id-type-in-authenticators.js,/opt/server/packages/auth/dist/migrations/mysql/1678110075698-remove-sign-in-emails-on-free-acounts.js,/opt/server/packages/auth/dist/migrations/mysql/1678266947362-add-internal-team-user-role.js,/opt/server/packages/auth/dist/migrations/mysql/1678340701766-remove-authenticator-names-from-server.js,/opt/server/packages/auth/dist/migrations/mysql/1680597887475-emergency-access-invitations.js,/opt/server/packages/auth/dist/migrations/mysql/1681984540867-enable-u2f.js,/opt/server/packages/auth/dist/migrations/mysql/1682926032072-cache-entries.js,/opt/server/packages/auth/dist/migrations/mysql/1683017908845-change-cache-table-name.js,/opt/server/packages/auth/dist/migrations/mysql/1692348191367-add-transition-role.js,/opt/server/packages/auth/dist/migrations/mysql/1694000575425-add-shared-vault-users.js,/opt/server/packages/auth/dist/migrations/mysql/1694157482134-add_vaults_user.js,/opt/server/packages/auth/dist/migrations/mysql/1695283870612-add-designated-survivor.js,/opt/server/packages/auth/dist/migrations/mysql/1697704066569-remove-transition-role.js"
query: SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'standard_notes_db' AND `TABLE_NAME` = 'migrations'
query: SELECT * FROM `standard_notes_db`.`migrations` `migrations` ORDER BY `id` DESC
104 migrations are already loaded in the database.
84 migrations were found in the source code.
RemoveNotifications1695643525793 is the last executed migration. It was executed on Mon Sep 25 2023 12:05:25 GMT+0000 (Coordinated Universal Time).
12 migrations are new migrations must be executed.
query: START TRANSACTION
query: DELETE FROM `role_permissions` WHERE role_uuid="23bf88ca-bee1-4a4c-adf0-b7a48749eea7" AND permission_uuid="2074d312-78bc-4533-b008-38e1232226c0"
query: DELETE FROM `role_permissions` WHERE role_uuid="bde42e26-628c-44e6-9d76-21b08954b0bf" AND permission_uuid="2074d312-78bc-4533-b008-38e1232226c0"
query: INSERT INTO `standard_notes_db`.`migrations`(`timestamp`, `name`) VALUES (?, ?) -- PARAMETERS: [1678110075698,"removeSignInEmailsOnFreeAcounts1678110075698"]
Migration removeSignInEmailsOnFreeAcounts1678110075698 has been  executed successfully.
query: DELETE FROM `role_permissions` WHERE role_uuid="1cd9ee6e-bc95-4f32-957c-d8c41f94d4ef"
query: DELETE FROM `user_roles` WHERE role_uuid="1cd9ee6e-bc95-4f32-957c-d8c41f94d4ef"
query: DELETE FROM `roles` WHERE name="FILES_BETA_USER"
query: DELETE FROM `permissions` WHERE name="app:files-beta"
query: INSERT INTO `roles` (uuid, name, version) VALUES ("9f8d2313-e8d0-48ad-b19c-026601d0ddf4", "INTERNAL_TEAM_USER", 1)
query: INSERT INTO `permissions` (uuid, name) VALUES ("fb13e7d3-936f-4ded-a543-e1650cc99dfd", "server:universal-second-factor")
query: INSERT INTO `role_permissions` (role_uuid, permission_uuid) VALUES ("9f8d2313-e8d0-48ad-b19c-026601d0ddf4", "fb13e7d3-936f-4ded-a543-e1650cc99dfd")
query: INSERT INTO `standard_notes_db`.`migrations`(`timestamp`, `name`) VALUES (?, ?) -- PARAMETERS: [1678266947362,"addInternalTeamUserRole1678266947362"]
Migration addInternalTeamUserRole1678266947362 has been  executed successfully.
query: ALTER TABLE `authenticators` DROP COLUMN `name`
query: INSERT INTO `standard_notes_db`.`migrations`(`timestamp`, `name`) VALUES (?, ?) -- PARAMETERS: [1678340701766,"removeAuthenticatorNamesFromServer1678340701766"]
Migration removeAuthenticatorNamesFromServer1678340701766 has been  executed successfully.
query: CREATE TABLE `emergency_access_invitations` (`uuid` varchar(36) NOT NULL, `grantor_uuid` varchar(36) NOT NULL, `grantee_uuid` varchar(36) NOT NULL, `status` varchar(36) NOT NULL, `expires_at` datetime NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`uuid`)) ENGINE=InnoDB
query: ALTER TABLE `emergency_access_invitations` ADD CONSTRAINT `grantor_uuid_fk` FOREIGN KEY (`grantor_uuid`) REFERENCES `users`(`uuid`) ON DELETE CASCADE ON UPDATE NO ACTION
query failed: ALTER TABLE `emergency_access_invitations` ADD CONSTRAINT `grantor_uuid_fk` FOREIGN KEY (`grantor_uuid`) REFERENCES `users`(`uuid`) ON DELETE CASCADE ON UPDATE NO ACTION
error: Error: Referencing column 'grantor_uuid' and referenced column 'uuid' in foreign key constraint 'grantor_uuid_fk' are incompatible.
query: ROLLBACK
localhost:3101 is up. Proceeding to startup.

so it seems there is a problem, can You help me out?

[karolsojko]

Hmm 🤔 can you manually delete the emergency_access_invitations table? It's not used for anything at this point