Attempting to migrate from v0.21.1 to v0.22.0 and v0.22.0 to v0.23.0 results in postgres error


#1

Iโ€™m trying to migrate versions from v0.21.1 and v0.22.0. Iโ€™m running Metabase in Docker on AWS EC2 Container Service.

When I specify the v0.21.1 image, it starts up just fine. But when I attempt to run the v0.22.0 image, it complains with the following error:

03-29 16:13:41 INFO metabase.core :: Starting Metabase version v0.22.0 (fd04424 release-0.22.0) ...
03-29 16:13:41 INFO metabase.core :: System timezone is 'GMT' ...
03-29 16:13:41 DEBUG metabase.driver :: Registered driver :bigquery ๐Ÿšš
03-29 16:13:41 DEBUG metabase.driver :: Registered driver :crate ๐Ÿšš
03-29 16:13:41 DEBUG metabase.driver :: Registered driver :druid ๐Ÿšš
03-29 16:13:41 DEBUG metabase.driver :: Registered driver :googleanalytics ๐Ÿšš
03-29 16:13:41 DEBUG metabase.driver :: Registered driver :h2 ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :mongo ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :mysql ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :postgres ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :redshift ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :sqlite ๐Ÿšš
03-29 16:13:42 DEBUG metabase.driver :: Registered driver :sqlserver ๐Ÿšš
03-29 16:13:42 INFO metabase.db :: Verifying postgres Database Connection ...
03-29 16:13:42 INFO metabase.db :: Verify Database Connection ...  โœ…
03-29 16:13:42 INFO metabase.db :: Running Database Migrations...
03-29 16:13:42 INFO metabase.db :: Setting up Liquibase...
03-29 16:13:42 INFO metabase.db :: Liquibase is ready.
03-29 16:13:42 INFO metabase.db :: Checking if Database has unran migrations...
03-29 16:14:02 INFO metabase.db :: Database has unran migrations. Waiting for migration lock to be cleared...
03-29 16:14:02 INFO metabase.db :: Migration lock is cleared. Running migrations...
org.postgresql.util.PSQLException: ERROR: relation "collection" already exists
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)
        at clojure.java.jdbc$db_do_execute_prepared_statement$fn__2743.invoke(jdbc.clj:784)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:637)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:598)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:783)
        at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:778)
        at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:814)
        at clojure.java.jdbc$db_do_prepared.invoke(jdbc.clj:795)
        at clojure.java.jdbc$execute_BANG_$execute_helper__2786.invoke(jdbc.clj:961)
        at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:963)
        at clojure.java.jdbc$execute_BANG_.invoke(jdbc.clj:943)
        at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:954)
        at clojure.java.jdbc$execute_BANG_.invoke(jdbc.clj:943)
        at metabase.db$migrate_up_if_needed_BANG_.invokeStatic(db.clj:168)
        at metabase.db$migrate_up_if_needed_BANG_.invoke(db.clj:155)
        at metabase.db$migrate_BANG_$fn__7741.invoke(db.clj:232)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:613)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:629)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:598)
        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:585)
        at metabase.db$migrate_BANG_.invokeStatic(db.clj:221)
        at metabase.db$migrate_BANG_.invoke(db.clj:204)
        at metabase.db$setup_db_BANG_.invokeStatic(db.clj:365)
        at metabase.db$setup_db_BANG_.doInvoke(db.clj:352)
        at clojure.lang.RestFn.invoke(RestFn.java:421)
        at metabase.core$init_BANG_.invokeStatic(core.clj:113)
        at metabase.core$init_BANG_.invoke(core.clj:93)
        at metabase.core$start_normally.invokeStatic(core.clj:196)
        at metabase.core$start_normally.invoke(core.clj:190)
        at metabase.core$_main.invokeStatic(core.clj:288)
        at metabase.core$_main.doInvoke(core.clj:283)
        at clojure.lang.RestFn.invoke(RestFn.java:397)
        at clojure.lang.AFn.applyToHelper(AFn.java:152)
        at clojure.lang.RestFn.applyTo(RestFn.java:132)
        at metabase.core.main(Unknown Source)
03-29 16:14:03 ERROR metabase.core :: Metabase Initialization FAILED:  ERROR: relation "collection" already exists
03-29 16:14:03 INFO metabase.core :: Metabase Shutting Down ...
03-29 16:14:03 INFO metabase.core :: Metabase Shutdown COMPLETE

I checked the database and collection did indeed exist. So I dropped that table and restarted Metabase. This time I got the same error, except for the collection_revision table. So I dropped that too and restarted. And this time it started up fine, and I see that both of the tables (collection and collection_revision) were back in the database.

Then I tried upgrading to v0.23.0 and again, it complained about a table (query_execution), and I looked at the table in the database and saw that it had a lot of rows (>100,000). I also saw a similar table, query_queryexecution, with >100,000 rows as well. Iโ€™m not sure what to do now since itโ€™s probably not a good idea to just drop a table with so many rows.

I suppose something is wrong with the database migrations since it shouldnโ€™t be complaining about a table existing, when it already existed back in v0.20.3. Can someone advise as to what I should do to get the database migrations working when upgrading from v0.22.0 to v0.23.0?


#2

This is a topic that @camsaul might be able to help with.


#3

@ysim were you doing something like running Metabase from the master branch or some other custom way? The Collections tables (collection and collection_revision) were officially shipped as part of Metabase 0.22.0 so if you hadnโ€™t been running that version or a pre-release version thereโ€™s no way they could have been created.

We highly recommend you follow the instructions on our website when running Metabase. Stick to the official JARs, Docker Images, and EBS scripts and you wonโ€™t run into these kinds of issues.

Either way you can go ahead and drop those tables and restart. The query_execution and query_queryexecution tables are just used to calculate historic query durations so dropping them is ok (theyโ€™ll repopulate soon enough).


#4

All I did was: pull the latest image indicating the version, remove the old container and add the metabase version to be run.

sudo docker run -d -p 3000:3000 -e "JAVA_TIMEZONE=Pacific/Auckland" --restart always --name metabase31 metabase/metabase:v0.31.1