db:create erroneously states database already exists when connecting to timescaledb

[jhogendorn]

How Shlink is set up

• Shlink Version: stable
• PHP Version: docker provided
• How do you serve Shlink: Docker image
• Database engine used: PostgreSQL

Summary

When connecting to a timescaledb postgresql instance the db:create cli function states the database already exists, which it does not. db:migrate then fails due to missing tables.

Expected behavior

db:create would create the tables

How to reproduce

Run the stable container pointing at a timescaledb (pg14) instance.

I suspect its seeing something in the non public schemas thats causing it to stop? timescaledb creates a number of other schemas alongside public.

[acelaya]

What Shlink does in order to determine if the database exists is checking if any table which is not the migrations table itself, already exists in the database.

I have considered a few times changing the approach, as it's a bit naive, because the database could have other tables (which is probably what is happening here), but checking exactly for Shlink tables has some considerations:

• Should it check all Shlink tables? Just some? If so, which ones?
• Checking all Shlink tables implies having a list hardcoded somewhere, which can quickly become obsolete and cause other problems in the future. I haven't found a way in which it can be inferred from other elements where the tables are handled.

[jhogendorn]

Yeah its a tricky one.

I think the interesting thing in this case is that in the public schema, there are no other tables. So its doing its check for tables in the other schemas. if we just restrict the scope to public this logic will be correct again.

[acelaya]

Yeah, that's a good point. I will check if that's actually what's happening.

[acelaya]

I have found a way to make Shlink check for its actual tables only, in a dynamic way (I don't need to change anything if the tables change, more are added, etc), which should cover this problem, as foreign tables will not affect this command anymore.

[acelaya]

Shlink 3.5.2 was released yesterday, including this fix.