Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Questions break when renaming a schema #11128

Closed
soltanianalytics opened this issue Oct 12, 2019 · 4 comments
Closed

Questions break when renaming a schema #11128

soltanianalytics opened this issue Oct 12, 2019 · 4 comments

Comments

@soltanianalytics
Copy link

@soltanianalytics soltanianalytics commented Oct 12, 2019

Describe the bug
When renaming the schema of a database, previously existing questions break.

To Reproduce
Steps to reproduce the behavior:

  1. Connect a data source e.g. DWH
  2. Create a question
  3. Rename the schema containing the table pertaining to that question
  4. Rename the schema in the corresponding connection in Metabase
  5. New questions correctly query the renamed schema, but existing question don't update! What's more, even additional changes within the question don't update the schema name. E.g. if I add a grouping, the GROUP BY clause will refer to the old schema name, not the new schema name.

Expected behavior
The questions should not have static information on the schema name saved but query the database connection for the current schema name.

Information about your Metabase Installation:

  • Your browser and the version: Chrome 77
  • Your operating system: Win 10
  • Your databases: Snowflake
  • Metabase hosting environment: Elastic Beanstalk
  • Metabase internal database: PostgreSQL

Severity
Well, what I assumed to be a trivial exercised invalidated all existing reports and questions. That's pretty severe... Though I could take a backup of the metabase internal database to undo the damage, so at least it's not crippling.

@soltanianalytics

This comment has been minimized.

Copy link
Author

@soltanianalytics soltanianalytics commented Oct 12, 2019

I made a wrong assumption in Expected behavior. I'll leave it as is and write what I found out in this comment: Metabase does actually query the database connection for the current schema, but it doesn't update the schema in the metadata database, instead it creates new table entries for each table instead of updating their schema field.

I was able to fix this for me using this query directly on the Metabase metadata database*:

DELETE FROM metabase_field -- This is required due to foreign key constraints
WHERE table_id IN (
	SELECT id FROM metabase_table
	WHERE db_id = 2 AND active
	AND NAME LIKE 'REP_%' -- this was specific to my case
)
;
DELETE from metabase_table
WHERE db_id = 2 AND active
AND NAME LIKE 'REP_%' -- this was specific to my case
;
UPDATE metabase_table
SET schema = 'ANALYTICS_REP'
WHERE db_id = 2 AND not active
AND NAME LIKE 'REP_%' -- this was specific to my case
  • Be careful to check your schema first, you might delete or update more tables than you want with this query!!
@mazameli

This comment has been minimized.

Copy link
Contributor

@mazameli mazameli commented Jan 16, 2020

Were your existing questions SQL queries or GUI queries?

@soltanianalytics

This comment has been minimized.

Copy link
Author

@soltanianalytics soltanianalytics commented Jan 17, 2020

GUI queries, if I remember correctly

@salsakran

This comment has been minimized.

Copy link
Contributor

@salsakran salsakran commented Feb 22, 2020

closing this as we're not practically going to guess when a schema was renamed vs dropped.

@salsakran salsakran closed this Feb 22, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.