How to upgrade Metabase from v0.22.2 to v0.40.2

qingdaojunzuo · August 9, 2021, 2:19am

Hi,

I'm trying to upgrade metabase from v0.22.2 to v0.40.2, but the database migration showed error and the container exited,

ERROR changelog.ChangeSet :: Change Set migrations/000_migrations.yaml::1::agilliland failed. Error: (conn=1492291) Table 'core_user' already exists

ERROR changelog.ChangeSet :: Change Set migrations/000_migrations.yaml::1::agilliland failed. Error: (conn=1492291) Table 'core_organization' already exists

I'm using,

running docker with v0.22.2
using external database, mysql version 5.7.16, Liquibase version: 3.6.3

I set MB_DB_AUTOMIGRATE to false and got all the sqls to execute manually and see the sql to create table such as "core_user" or "core_organization". It seems that some sqls can't be executed which setup before v0.22.2

How can I do upgrade database manually without data lost from v0.22.2 to v0.40.2, I see some comments in the output sqls, such as "-- Added 0.25.0", shall I execute the sqls since v0.23.2 so that the upgrade will be OK and which sqls should I run?

Appreciate your help!!!

Thanks
Daniel

flamber · August 9, 2021, 8:59am

Hi @qingdaojunzuo
It seems like your application database is missing the table DATABASECHANGELOG, since it's trying to generate the entire Metabase schema again.
Are you sure the connection allows ALTER and REFERENCE queries? Make sure Metabase has enough privileges to modify it's own application database.

qingdaojunzuo · August 10, 2021, 12:56am

Hi @flamber,

Thanks very much for the reply. I checked the database the table DATABASECHANGELOG exists,

mysql> show tables;
+------------------------------+
| Tables_in_metabase |
+------------------------------+
| DATABASECHANGELOG |
| DATABASECHANGELOGLOCK |
| activity |
| card_label |
| collection |
| collection_revision |
| core_organization |
| core_organization_bak |
| core_session |
| core_user |
| dashboardcard_series |
| data_migrations |
| dependency |
| label |
| metabase_database |
| metabase_field |
| metabase_fieldvalues |
| metabase_table |
| metric |
| metric_important_field |
| permissions |
| permissions_group |
| permissions_group_membership |
| permissions_revision |
| pulse |
| pulse_card |
| pulse_channel |
| pulse_channel_recipient |
| query_queryexecution |
| raw_column |
| raw_table |
| report_card |
| report_cardfavorite |
| report_dashboard |
| report_dashboardcard |
| revision |
| segment |
| setting |
| view_log |
+------------------------------+
39 rows in set (0.00 sec)

The records seems to be created several years ago when we used v0.22.2,

mysql> select * from DATABASECHANGELOG;
+----+------------+--------------------------------------------- | ID | AUTHOR | FILENAME +----+------------+--------------------------------------------- | 1 | agilliland | migrations/001_initial_schema.json | 2 | agilliland | migrations/002_add_session_table.json | 4 | cammsaul | migrations/004_add_setting_table.json | 5 | agilliland | migrations/005_add_org_report_tz_column.json | 6 | agilliland | migrations/006_disconnect_orgs.json | 7 | cammsaul | migrations/007_add_field_parent_id.json | 8 | tlrobinson | migrations/008_add_display_name_columns.json | 9 | tlrobinson | migrations/009_add_table_visibility_type_column.json | 10 | cammsaul | migrations/010_add_revision_table.yaml | 11 | agilliland | migrations/011_cleanup_dashboard_perms.yaml | 12 | agilliland | migrations/012_add_card_query_fields.yaml | 13 | agilliland | migrations/013_add_activity_table.yaml | 14 | agilliland | migrations/014_add_view_log_table.yaml | 15 | agilliland | migrations/015_add_revision_is_creation_field.yaml | 16 | agilliland | migrations/016_user_last_login_allow_null.yaml | 17 | agilliland | migrations/017_add_database_is_sample_field.yaml | 18 | camsaul | migrations/018_add_data_migrations_table.yaml | 19 | camsaul | migrations/019_add_schema_column_to_table.yaml | 20 | agilliland | migrations/020_add_pulse_tables.yaml | 21 | agilliland | migrations/021_add_segment_table.yaml | 22 | agilliland | migrations/022_add_revision_message_field.yaml | 23 | agilliland | migrations/023_modify_table_rows_to_bigint.yaml | 24 | agilliland | migrations/024_add_dependency_table.yaml | 25 | agilliland | migrations/025_add_metric_table.yaml | 26 | agilliland | migrations/026_add_database_is_full_sync_field.yaml | 27 | agilliland | migrations/027_add_dashcardseries_table.yaml | 28 | agilliland | migrations/028_add_user_is_qbnewb.yaml | 29 | agilliland | migrations/029_add_pulse_channel_schedule_frame.yaml | 30 | agilliland | migrations/030_add_field_visibility_type.yaml | 31 | agilliland | migrations/031_add_field_fk_target.yaml | 32 | camsaul | migrations/032_add_label_and_card_label_tables.yaml | 32 | agilliland | migrations/033_add_physical_schema_tables.yaml | 34 | tlrobinson | migrations/034_add_pulse_channel_enabled_field.yaml | 35 | agilliland | migrations/035_modify_setting_value_length.yaml | 36 | agilliland | migrations/036_add_dashboard_filters_columns.yaml | 37 | tlrobinson | migrations/037_add_query_hash_and_indexes.yaml | 38 | camsaul | migrations/038_getting_started_guide.yaml | 39 | camsaul | migrations/039_add_user_google_auth_column.yaml | 40 | camsaul | migrations/040_permissions_v1.yaml | 41 | camsaul | migrations/041_drop_field_field_type.yaml | 42 | camsaul | migrations/042_remove_unused_tables.yaml | 43 | camsaul | migrations/043_add_permissions_revision_table.yaml | 44 | camsaul | migrations/044_remove_public_perms_columns.yaml | 45 | tlrobinson | migrations/045_add_dashcard_visualization_se | 46 | camsaul | migrations/046_add_not_null_constraints_for_ | 47 | camsaul | migrations/047_add_collection_table.yaml | 48 | camsaul | migrations/048_add_collection_revision_table.yaml +----+------------+--------------------------------------------- 47 rows in set (0.00 sec) ----------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+
| DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
----------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+
| 2017-02-04 08:45:43 | 1 | EXECUTED | 7:1b0742414a26a9b52ed5622ce7516ad7 | createTable tableName=core_organization; createTable tableName=core_user; createTable tableName=core_userorgperm; addUniqueConstraint constraintName=idx_unique_user_id_organization_id, tableName=core_userorgperm; createIndex indexName=idx_userorgp... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:43 | 2 | EXECUTED | 7:e63cf4e800ec122c1a19c04b8c07cc3f | createTable tableName=core_session | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:43 | 3 | EXECUTED | 7:1ed887e91a846f4d6cbe84d1efd126c4 | createTable tableName=setting | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:43 | 4 | EXECUTED | 7:593149128c8f3a7e1f37a483bc67a924 | addColumn tableName=core_organization | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 5 | EXECUTED | 7:d24f2f950306f150d87c4208520661d5 | dropNotNullConstraint columnName=organization_id, tableName=metabase_database; dropForeignKeyConstraint baseTableName=metabase_database, constraintName=fk_database_ref_organization_id; dropNotNullConstraint columnName=organization_id, tableName=re... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 6 | EXECUTED | 7:baec0ec600ccc9bdadc176c1c4b29b77 | addColumn tableName=metabase_field | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 7 | EXECUTED | 7:ea2727c7ce666178cff436549f81ddbd | addColumn tableName=metabase_table; addColumn tableName=metabase_field | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 8 | EXECUTED | 7:c05cf8a25248b38e281e8e85de4275a2 | addColumn tableName=metabase_table | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 9 | EXECUTED | 7:97fec69516d0dfe424ea7365f51bb87e | createTable tableName=revision; createIndex indexName=idx_revision_model_model_id, tableName=revision | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 10 | EXECUTED | 7:c7ef8b4f4dcb3528f9282b51aea5bb2a | sql | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 11 | EXECUTED | 7:f78e18f669d7c9e6d06c63ea9929391f | addColumn tableName=report_card | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 12 | EXECUTED | 7:f27286894439bef33ff93761f9b32bc4 | createTable tableName=activity; createIndex indexName=idx_activity_timestamp, tableName=activity; createIndex indexName=idx_activity_user_id, tableName=activity; createIndex indexName=idx_activity_custom_id, tableName=activity | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 13 | EXECUTED | 7:2bc8ad9e775091db38a79ee610eafc97 | createTable tableName=view_log; createIndex indexName=idx_view_log_user_id, tableName=view_log; createIndex indexName=idx_view_log_timestamp, tableName=view_log | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 14 | EXECUTED | 7:50c72a51651af76928c06f21c9e04f97 | addColumn tableName=revision | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 15 | EXECUTED | 7:70484ee18e0067988963d3042343e300 | dropNotNullConstraint columnName=last_login, tableName=core_user | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 16 | EXECUTED | 7:5401ec35a5bd1275f93a7cac1ddd7591 | addColumn tableName=metabase_database; sql | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 17 | EXECUTED | 7:329d897d44ba9893fdafc9ce7e876d73 | createTable tableName=data_migrations; createIndex indexName=idx_data_migrations_id, tableName=data_migrations | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 18 | EXECUTED | 7:e8fa976811e4d58d42a45804affa1d07 | addColumn tableName=metabase_table | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 19 | EXECUTED | 7:e9f9ab6a3f7a54d3cf1ab70bf7f288b9 | createTable tableName=pulse; createIndex indexName=idx_pulse_creator_id, tableName=pulse; createTable tableName=pulse_card; createIndex indexName=idx_pulse_card_pulse_id, tableName=pulse_card; createIndex indexName=idx_pulse_card_card_id, tableNam... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:44 | 20 | EXECUTED | 7:10f47e47d4a6b5479702485355968d60 | createTable tableName=segment; createIndex indexName=idx_segment_creator_id, tableName=segment; createIndex indexName=idx_segment_table_id, tableName=segment | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 21 | EXECUTED | 7:cb6776ec86ab0ad9e74806a5460b9085 | addColumn tableName=revision | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 22 | EXECUTED | 7:43b9662bd798db391d4bbb7d4615bf0d | modifyDataType columnName=rows, tableName=metabase_table | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 23 | EXECUTED | 7:b77dea78a3645ab993b30d949abee28a | createTable tableName=dependency; createIndex indexName=idx_dependency_model, tableName=dependency; createIndex indexName=idx_dependency_model_id, tableName=dependency; createIndex indexName=idx_dependency_dependent_on_model, tableName=dependency;... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 24 | EXECUTED | 7:c40bd959d6a76be99e324a8a821c0bde | createTable tableName=metric; createIndex indexName=idx_metric_creator_id, tableName=metric; createIndex indexName=idx_metric_table_id, tableName=metric | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 25 | EXECUTED | 7:ac7f40d2a3fbf3fea7936aa79bb1532b | addColumn tableName=metabase_database; sql | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 26 | EXECUTED | 7:6daeade421b85d930b07f95d8d6a14c5 | createTable tableName=dashboardcard_series; createIndex indexName=idx_dashboardcard_series_dashboardcard_id, tableName=dashboardcard_series; createIndex indexName=idx_dashboardcard_series_card_id, tableName=dashboardcard_series | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 27 | EXECUTED | 7:335e7e6b32dcbeb392150b3c3db2d5eb | addColumn tableName=core_user | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 28 | EXECUTED | 7:7b0bb8fcb7de2aa29ce57b32baf9ff31 | addColumn tableName=pulse_channel | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 29 | EXECUTED | 7:7b5245de5d964eedb5cd6fdf5afdb6fd | addColumn tableName=metabase_field; addNotNullConstraint columnName=visibility_type, tableName=metabase_field | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 30 | EXECUTED | 7:347281cdb65a285b03aeaf77cb28e618 | addColumn tableName=metabase_field | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:45 | 31 | EXECUTED | 7:ff40b5fbe06dc5221d0b9223992ece25 | createTable tableName=label; createIndex indexName=idx_label_slug, tableName=label; createTable tableName=card_label; addUniqueConstraint constraintName=unique_card_label_card_id_label_id, tableName=card_label; createIndex indexName=idx_card_label... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 32 | EXECUTED | 7:143cf1041ee698943cc6d1c6650105b4 | createTable tableName=raw_table; createIndex indexName=idx_rawtable_database_id, tableName=raw_table; addUniqueConstraint constraintName=uniq_raw_table_db_schema_name, tableName=raw_table; createTable tableName=raw_column; createIndex indexName=id... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 33 | EXECUTED | 7:e65d70b4c914cfdf5b3ef9927565e899 | addColumn tableName=pulse_channel | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 34 | EXECUTED | 7:ab80b6b8e6dfc3fa3e8fa5954e3a8ec4 | modifyDataType columnName=value, tableName=setting | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 35 | EXECUTED | 7:de534c871471b400d70ee29122f23847 | addColumn tableName=report_dashboard; addNotNullConstraint columnName=parameters, tableName=report_dashboard; addColumn tableName=report_dashboardcard; addNotNullConstraint columnName=parameter_mappings, tableName=report_dashboardcard | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 36 | EXECUTED | 7:487dd1fa57af0f25edf3265ed9899588 | addColumn tableName=query_queryexecution; addNotNullConstraint columnName=query_hash, tableName=query_queryexecution; createIndex indexName=idx_query_queryexecution_query_hash, tableName=query_queryexecution; createIndex indexName=idx_query_querye... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 37 | EXECUTED | 7:5e32fa14a0c34b99027e25901b7e3255 | addColumn tableName=metabase_database; addColumn tableName=metabase_table; addColumn tableName=metabase_field; addColumn tableName=report_dashboard; addColumn tableName=metric; addColumn tableName=segment; addColumn tableName=metabase_database; ad... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:46 | 38 | EXECUTED | 7:a63ada256c44684d2649b8f3c28a3023 | addColumn tableName=core_user | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 39 | EXECUTED | 7:0ba56822308957969bf5ad5ea8ee6707 | createTable tableName=permissions_group; createIndex indexName=idx_permissions_group_name, tableName=permissions_group; createTable tableName=permissions_group_membership; addUniqueConstraint constraintName=unique_permissions_group_membership_user... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 40 | EXECUTED | 7:e1aa5b70f61426b29d74d38936e560de | dropColumn columnName=field_type, tableName=metabase_field; addDefaultValue columnName=active, tableName=metabase_field; addDefaultValue columnName=preview_display, tableName=metabase_field; addDefaultValue columnName=position, tableName=metabase_... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 41 | EXECUTED | 7:779407e2ea3b8d89092fc9f72e29fdaa | dropForeignKeyConstraint baseTableName=query_queryexecution, constraintName=fk_queryexecution_ref_query_id; dropColumn columnName=query_id, tableName=query_queryexecution; dropColumn columnName=is_staff, tableName=core_user; dropColumn columnName=... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 42 | EXECUTED | 7:dbc18c8ca697fc335869f0ed0eb5f4cb | createTable tableName=permissions_revision | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 43 | EXECUTED | 7:1d09a61933bbc5a01b0ddef7bd1b1336 | dropColumn columnName=public_perms, tableName=report_card; dropColumn columnName=public_perms, tableName=report_dashboard; dropColumn columnName=public_perms, tableName=pulse | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
ttings_field.yaml | 2017-02-04 08:45:47 | 44 | EXECUTED | 7:9198081e3329df7903d9016804ef0cf0 | addColumn tableName=report_dashboardcard; addNotNullConstraint columnName=visualization_settings, tableName=report_dashboardcard | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
dashboard_card_row_col.yaml | 2017-02-04 08:45:47 | 45 | EXECUTED | 7:aab12e940225b458986e15cf53d5d816 | addNotNullConstraint columnName=row, tableName=report_dashboardcard; addNotNullConstraint columnName=col, tableName=report_dashboardcard; addDefaultValue columnName=row, tableName=report_dashboardcard; addDefaultValue columnName=col, tableName=rep... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 46 | EXECUTED | 7:381e18d5008269e299f12c9726163675 | createTable tableName=collection; createIndex indexName=idx_collection_slug, tableName=collection; addColumn tableName=report_card; createIndex indexName=idx_card_collection_id, tableName=report_card | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2017-02-04 08:45:47 | 47 | EXECUTED | 7:b8957fda76bab207f99ced39353df1da | createTable tableName=collection_revision | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
----------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+

I did the sql like "alter table rename to" before so that I think the privileges are OK and all the tables in current mysql were created by metabase itself

Can I skip the sqls till v0.22.2 manually if the app can't skip automatically? Which sqls should I run? I see the max number in DATABASECHANGELOG is 48, does this mean I can run the sql since 49 defined in the output text which I got from log when MB_DB_AUTOMIGRATE to false?

BTW, I notice that the table DATABASECHANGELOGLOCK is not empty, does this cause the issue?
// truncate this table doesn't work

mysql> select * from DATABASECHANGELOGLOCK;
+----+--------+-------------+----------+
| ID | LOCKED | LOCKGRANTED | LOCKEDBY |
+----+--------+-------------+----------+
| 1 | | NULL | NULL |
+----+--------+-------------+----------+
1 row in set (0.00 sec)

-- Changeset migrations/000_migrations.yaml::48::camsaul
CREATE TABLE metabase.collection_revision (id INT AUTO_INCREMENT NOT NULL, before TEXT NOT NULL COMMENT 'Serialized JSON of the collections graph before the changes.', after TEXT NOT NULL COMMENT 'Serialized JSON of the collections graph after the changes.', user_id INT NOT NULL COMMENT 'The ID of the admin who made this set of changes.', created_at datetime NOT NULL COMMENT 'The timestamp of when these changes were made.', remark TEXT NULL COMMENT 'Optional remarks explaining why these changes were made.', CONSTRAINT PK_COLLECTION_REVISION PRIMARY KEY (id), CONSTRAINT fk_collection_revision_user_id FOREIGN KEY (user_id) REFERENCES metabase.core_user(id)) COMMENT='Used to keep track of changes made to collections.' ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE metabase.collection_revision COMMENT = 'Used to keep track of changes made to collections.';
INSERT INTO metabase.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('48', 'camsaul', 'migrations/000_migrations.yaml', current_timestamp(6), 94, '8:720ce9d4b9e6f0917aea035e9dc5d95d', 'createTable tableName=collection_revision', '', 'EXECUTED', NULL, NULL, '3.6.3', '8474407822');
-- Changeset migrations/000_migrations.yaml::49::camsaul
ALTER TABLE metabase.report_card ADD public_uuid CHAR(36) NULL COMMENT 'Unique UUID used to in publically-accessible links to this Card.';

I'm not familiar with clojure, does this code mean to execute the file migrations/000_migrations.yaml (containing all the sqls from initial version rather than get the max id from DATABASECHANGELOG to decide the start id?)directly when fresh-install is false?

[jdbc-spec]
(let [liquibase-table-name (if (#{:h2 :mysql} (:type jdbc-spec))
"DATABASECHANGELOG"
"databasechangelog")
fresh-install? (jdbc/with-db-metadata [meta jdbc-spec] ;; don't migrate on fresh install
(empty? (jdbc/metadata-query
(.getTables meta nil nil liquibase-table-name (u/varargs String ["TABLE"])))))
statement (format "UPDATE %s SET FILENAME = ?" liquibase-table-name)]
(when-not fresh-install?
(jdbc/execute! jdbc-spec [statement "migrations/000_migrations.yaml"]))))

Thanks

flamber · August 10, 2021, 8:20am

@qingdaojunzuo You should absolutely not truncate the table DATABASECHANGELOG - then Metabase has no idea about which migrations has been executed.
The problem is that Metabase for some reason sees your setup as a fresh setup and tries to run all migrations again.

It seems like you got all migrations up until this point:
https://github.com/metabase/metabase/blob/master/resources/migrations/000_migrations.yaml#L3373

Check the query log on MySQL. For some reason Metabase does not seem to find the table.

Metabase does a check on every startup to execute missing migrations. That's what the file 000_migrations.yaml contains.

I would recommend that you try a step-by-step upgrade, so first to 0.23.x, then 0.24.x (which unified all migration yamls), then 0.32.x and then the latest 0.40.x
It would hopefully help figuring out if the problem is with some bad migration somewhere or if your application database has a corruption.

qingdaojunzuo · August 11, 2021, 3:30am

@flamber,

Thanks very much for your help The issue is resolved.

I compared the DATABASECHANGELOG between 40.2 and 22.2, metabase should find the table DATABASECHANGELOG when tracing the mysql's execution history log(SQL query history), the issue is that the value of column FILENAME is not correct for migration,

v0.22.2 migrations/001_initial_schema.json

v0.40.2 migrations/000_migrations.yaml

The files are separated in 22.2 rather than 1 file in 40.2 so that all the sqls in 000_migrations.yaml will be executed

I updated the value as 000_migrations.yaml, the upgrade continued to execute with legacy data

mysql> SELECT * FROM metabase.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC;
+-----+------------------+--------------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+------+-----------+----------+--------+---------------+
| ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+------------------+--------------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+------+-----------+----------+--------+---------------+
| 1 | agilliland | migrations/000_migrations.yaml | 2017-02-04 08:45:43 | 1 | EXECUTED | 8:29a8f482877466643f65adb20c6d2139 | createTable tableName=core_organization; createTable tableName=core_user; createTable tableName=core_userorgperm; addUniqueConstraint constraintName=idx_unique_user_id_organization_id, tableName=core_userorgperm; createIndex indexName=idx_userorgp... | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
| 2 | agilliland | migrations/000_migrations.yaml | 2017-02-04 08:45:43 | 2 | EXECUTED | 8:983477ec51adb1236dd9d76ebf604be9 | createTable tableName=core_session | | NULL | 3.5.2 | NULL | NULL | 6197922970 |
...
173 |
| 309 | dpsutton | migrations/000_migrations.yaml | 2021-08-11 03:19:41 | 297 | EXECUTED | 8:26cc1f3ba949d8ce0d56350caacffbd8 | addColumn tableName=collection | Added 0.40.0 - Add type to collections | NULL | 3.6.3 | NULL | NULL | 8651941173 |
| 310 | howonlee | migrations/000_migrations.yaml | 2021-08-11 03:19:41 | 298 | EXECUTED | 8:eeba2296f23236d035812360122fd065 | update tableName=setting | Added 0.40.0 Migrate friendly field names | NULL | 3.6.3 | NULL | NULL | 8651941173 |
| 311 | howonlee | migrations/000_migrations.yaml | 2021-08-11 03:19:41 | 299 | EXECUTED | 8:a26e31914822a5176848abbb7c5415bd | sql; sql | Added 0.40.0 Migrate friendly field names, not noop | NULL | 3.6.3 | NULL | NULL | 8651941173 |
+-----+------------------+--------------------------------+---------------------+---------------+----------+------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+------+-----------+----------+--------+---------------+
299 rows in set (0.00 sec)

BTW, another issue encountered I think it's a legacy bug with the SQL fixed, the database deleted in metabase_database would not delete other data cascade, such as report_card and the related table, and the fk constraint related to this migrated failed, that's the data issue, we updated the relationship and resovled. And the table truncated mentioned above is not DATABASECHANGELOG, it's DATABASECHANGELOGLOCK:)

Thanks
Daniel

flamber · August 11, 2021, 8:45am

@qingdaojunzuo Excellent. I'm not entirely sure why the references wasn't updated, since all migrations were merged in 0.24.0.

It's correct that there were issues with cascading deletes not happening correctly in earlier versions, which was why constraints were added, but for a few setups, they already had out-of-sync data, which then meant the constraints wasn't added and this could lead to some really strange/annoying problems.

We still have a couple of issues open about migration, and we are looking into creating a tool, which validates the schema (and constraints), so you can check if your application database is "healthy".
For reference: https://github.com/metabase/metabase/issues?q=is%3Aissue+is%3Aopen+label%3A"Operation%2FDatabase+Migrations"

qingdaojunzuo · August 12, 2021, 2:33am

@flamber, thanks for the information

BTW, we try to move the auth from local to LDAP after upgrade, it seems OK except the group mapping, we are using groupOfUniqueNames as role in LDAP, such as,

dn: cn=Operator,ou=roles,dc=test,dc=com
cn: Operator
objectclass: groupOfUniqueNames
objectclass: top
uniqueMember: uid=operator@test.com,ou=people,dc=test,dc=com

I see the document, the group in LDAP defined by groupOfNames

dn: cn=readers,ou=users,dc=example,dc=org
cn: readers
objectClass: groupOfNames
member: cn=user01@metabase.com cn\3Duser02@metabase.com,ou=users,dc=example,dc=org

Does this support by metabase group mapping?

Thanks

flamber · August 12, 2021, 9:02am

@qingdaojunzuo Please start a new topic, since it's completely unrelated to this. Try searching a bit in the forum and on Github.
Yes, Metabase supports group mapping:
https://www.metabase.com/docs/latest/administration-guide/10-single-sign-on.html#enabling-ldap-authentication
The Enterprise Edition has more capabilities in the LDAP implementation.

qingdaojunzuo · August 12, 2021, 2:12pm

@flamber，OK Thanks