Upgrade to v2 failed

[GuillaumeASSIER]

Hi,

I try to upgrade from v1 to v2RC, and I have an issue during the data migration. When I launch the migration process, it stuck indefinitely.

root@XXXXX:/home/dock/hosting# docker compose exec plausible bin/plausible rpc Plausible.DataMigration.NumericIDs.run
    check-replicas Done!

------------------------------------------------------------------------------
    list-partitions Done!

------------------------------------------------------------------------------
Got the following migration settings:

  - max_threads: 16
  - table_settings: SETTINGS index_granularity = 8192
  - db url: http:https://plausible_events_db:8123/plausible_events_db
  - cluster?: false
  - partitions to do: ["202302", "202303", "202304", "202305"]
  - start from: 202302
  - stop at:

Running drop-events-v2
  -> Query: DROP TABLE IF EXISTS events_v2  SYNC

The table contains 1204 rows. Execute? [y/N]: y

Following, the logs of the docker compose stack.

hosting-plausible-1            | Starting repos..
hosting-plausible-1            | create Plausible.Repo database if it doesn't exist
hosting-plausible-1            | create Plausible.IngestRepo database if it doesn't exist
hosting-plausible-1            | Creation of Db successful!
hosting-plausible-1            | Loading plausible..
hosting-plausible-1            | Starting dependencies..
hosting-plausible-1            | Starting repos..
hosting-plausible_events_db-1  | 4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptr<DB::Context>, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>, std::__1::optional<DB::FormatSettings> const&) @ 0x1519b8d0 in /usr/bin/clickhouse
hosting-plausible_db-1         |
hosting-plausible_db-1         | PostgreSQL Database directory appears to contain a database; Skipping initialization
hosting-plausible_db-1         |
hosting-plausible_db-1         | 2023-06-25 16:59:15.129 UTC [1] LOG:  starting PostgreSQL 14.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
hosting-plausible-1            | Running migrations for Elixir.Plausible.Repo
hosting-plausible-1            | Running migrations for Elixir.Plausible.IngestRepo
hosting-plausible-1            | Migrations successful!
hosting-plausible_db-1         | 2023-06-25 16:59:15.129 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
hosting-plausible_events_db-1  | 5. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0x15c2c289 in /usr/bin/clickhouse
hosting-plausible_db-1         | 2023-06-25 16:59:15.129 UTC [1] LOG:  listening on IPv6 address "::", port 5432
hosting-plausible_db-1         | 2023-06-25 16:59:15.131 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
hosting-plausible_db-1         | 2023-06-25 16:59:15.135 UTC [22] LOG:  database system was shut down at 2023-06-25 16:59:03 UTC
hosting-plausible_events_db-1  | 6. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x15c30394 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 7. DB::HTTPServerConnection::run() @ 0x15cabda2 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 8. Poco::Net::TCPServerConnection::start() @ 0x18a16b73 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 9. Poco::Net::TCPServerDispatcher::run() @ 0x18a17ecd in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 10. Poco::PooledThread::run() @ 0x18bda829 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 11. Poco::ThreadImpl::runnableEntry(void*) @ 0x18bd80e2 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 12. ? @ 0x7fd70e28d609 in ?
hosting-plausible_events_db-1  | 13. __clone @ 0x7fd70e1b2133 in ?
hosting-plausible_events_db-1  |
hosting-plausible_events_db-1  | 2023.06.25 16:59:26.496032 [ 50 ] {d13837af-b9f8-428c-95bd-7fc7f5688c60} <Error> DynamicQueryHandler: Code: 82. DB::Exception: Database plausible_events_db already exists. (DATABASE_ALREADY_EXISTS), Stack trace (when copying this message, always include the lines below):
hosting-plausible_events_db-1  |
hosting-plausible_events_db-1  | 0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xa3fb75a in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 1. DB::InterpreterCreateQuery::createDatabase(DB::ASTCreateQuery&) @ 0x14de153f in /usr/bin/clickhouse
hosting-plausible_db-1         | 2023-06-25 16:59:15.141 UTC [1] LOG:  database system is ready to accept connections
hosting-plausible_events_db-1  | 2. DB::InterpreterCreateQuery::execute() @ 0x14df9a3f in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 3. ? @ 0x15197429 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptr<DB::Context>, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>, std::__1::optional<DB::FormatSettings> const&) @ 0x1519b8d0 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 5. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0x15c2c289 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 6. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x15c30394 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 7. DB::HTTPServerConnection::run() @ 0x15cabda2 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 8. Poco::Net::TCPServerConnection::start() @ 0x18a16b73 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 9. Poco::Net::TCPServerDispatcher::run() @ 0x18a17ecd in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 10. Poco::PooledThread::run() @ 0x18bda829 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 11. Poco::ThreadImpl::runnableEntry(void*) @ 0x18bd80e2 in /usr/bin/clickhouse
hosting-plausible_events_db-1  | 12. ? @ 0x7fd70e28d609 in ?
hosting-plausible_events_db-1  | 13. __clone @ 0x7fd70e1b2133 in ?
hosting-plausible_events_db-1  |  (version 22.8.13.20 (official build))

Any idea to understand why migration failed ?

[ruslandoga]

👋 @Sykursen

Sorry if it's a stupid question, but did you press <enter> after y?

Also, you probably don't want to drop that table since then you'd lose those 1200 events.

[GuillaumeASSIER]

👋 @ruslandoga

I have pressed after but the migration still broken.

And Yes, I really want to migrate these 1204 events 😅

[ruslandoga]

@Sykursen would you be able to get on a call? I haven't encountered that problem before and it might be easier to work through it in real time :)

[dehlirious]

Any solutions yet? This is a ridiculous bug.
docker-compose down --remove-orphans && docker-compose up -d and now plausible won't run

executeQuery: Code: 82. DB::Exception: Database plausible_events_db already exists. (DATABASE_ALREADY_EXISTS) (version 23.3.7.5 (official build)) (from 192.168.48.5:53712) (in query: CREATE DATABASE "plausible_events_db")

[dehlirious]

I wish I knew what the issue was. Completely working instance, After adding "GOOGLE_CLIENT_ID"&stuff, doing docker down remove orphans and back up, Bad gateway (nginx reverse proxy) & could not connect, even with the port up (ss -tupln) - it was a illogical issue. Completely deleted everything(data volumes and all), retried, no luck. This error of database already exists was the only thing I could see, the night of the issue. After seeing your comment, I gave it another shot, and what do you know.. it works. Wish I didn't have to delete the data but not much of a loss. Thanks for your help -- your presence alone seemed to have resolved whatever I had going on.

[mebeim]

@ruslandoga oh interesting, I was seeing the same error and I thought it might be related to that. Glad to see that it's harmless. Should the startup script be upgraded to check if the db exists first to avoid generating the error? I suspect a lot of people might get spooked by seeing that in the logs.

[ruslandoga]

👋 @mebeim

If it doesn't make things complicated, sure! PR is welcome!

[mebeim]

@ruslandoga would love to help but I don't think I have ever read a line of elixir in my life, let alone written one :'). All I can see is that you call into Ecto code for the createdb and migrate commands. Maybe querying storage_status() before storage_up() here might help? Not sure how to write it though.

analytics/lib/plausible_release.ex

Lines 153 to 161 in c81cb16

	defp ensure_repo_created(repo) do
	IO.puts("create #{inspect(repo)} database if it doesn't exist")
	case repo.__adapter__.storage_up(repo.config) do
	:ok -> :ok
	{:error, :already_up} -> :ok
	{:error, term} -> {:error, term}
	end
	end

[mebeim]

@dehlirious have you by any chance updated the postgresql version in your docker-compose.yml? You should avoid that as updating major breaks the db since postgresql does not want to open a db created by an older major.