Bug description

Superset shows message "Unable to migrate query editor state to backend. Superset will retry later. Please contact your administrator if this problem persists." every second

How to reproduce the bug

Cereate a query in MySQL using multiple schemas.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.10

Node version

Not applicable

Browser

Chrome

Additional context

Custom project structure:

project/
├── docker-compose.yml
├── env
└── superset
    └── superset main repository files

Superset Logs

superset_app          | 2024-09-20 15:53:35,980:WARNING:superset.views.error_handling:Exception
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 177, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/sql_lab/views.py", line 94, in post
superset_app          |     database_id=query_editor["dbId"],
superset_app          | KeyError: 'dbId'
superset_app          | 2024-09-20 15:53:35,981:ERROR:superset.views.error_handling:'dbId'
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
superset_app          |     rv = self.dispatch_request()
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
superset_app          |     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 177, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/sql_lab/views.py", line 94, in post
superset_app          |     database_id=query_editor["dbId"],
superset_app          | KeyError: 'dbId'
superset_app          | 187.105.178.160 - - [20/Sep/2024:15:53:35 +0000] "POST /tabstateview/ HTTP/1.1" 500 205 "http://140.238.182.144:8088/sqllab" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"
superset_app          | 187.105.178.160 - - [20/Sep/2024:15:53:36 +0000] "GET /api/v1/me/ HTTP/1.1" 200 150 "http://140.238.182.144:8088/sqllab" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"

Custom docker-compose.yml


x-common-build: &common-build
  context: superset
  target: dev

x-depends-on: &depends-on
  redis:
    condition: service_started
  superset_init:
    condition: service_completed_successfully

x-superset-base: &superset-base
  restart: always
  env_file:
    - path: ./superset/docker/.env
      required: true
    - path: ./.env
      required: false
  build:
    <<: *common-build
  volumes:
    - superset:/app/superset_home
    - ./superset/docker:/app/docker
  networks:
    - superset
    - postgres


services:
  redis:
    image: redis:7-alpine
    container_name: superset_cache
    restart: always
    volumes:
      - redis:/data
    networks:
      - superset

  superset_init:
    <<: *superset-base
    container_name: superset_init
    command: ["/app/docker/docker-init.sh"]
    restart: "no"
    healthcheck:
      disable: true

  superset_app:
    <<: *superset-base
    container_name: superset_app
    command: ["/app/docker/docker-bootstrap.sh", "app-gunicorn"]
    ports:
      - 8088:8088
    depends_on:
     <<: *depends-on

  superset-worker:
    <<: *superset-base
    container_name: superset_worker
    command: ["/app/docker/docker-bootstrap.sh", "worker"]
    depends_on:
     <<: *depends-on
    healthcheck:
      test:
        [
          "CMD-SHELL",
          "celery -A superset.tasks.celery_app:app inspect ping -d celery@$$HOSTNAME",
        ]

  superset_worker_beat:
    <<: *superset-base
    container_name: superset_worker_beat
    command: ["/app/docker/docker-bootstrap.sh", "beat"]
    depends_on:
     <<: *depends-on
    healthcheck:
      disable: true

volumes:
  redis:
  superset:

networks:
  superset:
    driver: bridge
    name: superset
  postgres:
    name: postgres
    external: true

.env

DATABASE_HOST=postgres
DATABASE_PASSWORD=[...]
DATABASE_DB=superset

POSTGRES_PASSWORD=${DATABASE_PASSWORD}
POSTGRES_DB=${DATABASE_DB}

FLASK_DEBUG=false
SUPERSET_ENV=production
SUPERSET_LOAD_EXAMPLES=false

SUPERSET_SECRET_KEY=[...]
ADMIN_PASSWORD=[...]

Checklist

  • [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: eduardopilatinuvemshop

Apparently all functions work normally. It just keeps displaying this error all the time.

Comment From: ideologysec

You do not appear to have a database connected, which Superset needs for metadata storage to my understanding (Postgres is the default in docker compose).

https://superset.apache.org/docs/installation/docker-compose/#docker-compose-tips--configuration

Comment From: zsw4579

Apparently all functions work normally. It just keeps displaying this error all the time.

May I ask if it has been solved at present? How was it solved?

Comment From: giucas-falchetto

I have the same problem/error. Any solution?

Comment From: rusackas

For those who are still facing this, what version are you running? Curious if this is happening after 5.0, or if indeed @ideologysec is correct that a metadata database is not configured (or is an unsuported db for that purpose).

Comment From: zsw4579

For those who are still facing this, what version are you running? Curious if this is happening after 5.0, or if indeed @ideologysec is correct that a metadata database is not configured (or is an unsuported db for that purpose).

I found it after version 4.0.2

Comment From: Veonms

We have just upgraded to version 4 (4.1.2 specifically) and can see this problem too: also see #33423 which is the same issue. It seems to be metadata related as this is where the message gets raised: https://github.com/apache/superset/blob/c74fae9663e4e4341baa106209219244eb5dc629/superset-frontend/src/SqlLab/actions/sqlLab.js#L486

And we can see this error repeatedly in Postgres (AWS RDS) v13.X: ``` RDS output ERROR: insert or update on table "tab_state" violates foreign key constraint "saved_query_id" DETAIL: Key (saved_query_id)=(602) is not present in table "saved_query".


**Comment From: bene-tleilax-werdna**

We get this same error using the [Bitnami helm chart's ](https://github.com/apache/superset/issues/30351) image, which runs 5.0.0. 

The user goes to use SQL Lab to see table schema on a postgresql db and while it populates all of the column info as expected, the "Unable to migrate query editor state to backend. Superset will retry later. Please contact your administrator if this problem persists." message appears in the lower right-hand corner.

At the same time, the logs on the web server pod also state:

ERROR:superset.commands.database.validate_sql:Unable to execute 'ecpg', you likely need to install it.' Traceback (most recent call last): File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/ecpg.py", line 12, in check_syntax proc = subprocess.Popen(args, shell=False, File "/opt/bitnami/python/lib/python3.10/subprocess.py", line 971, in init self._execute_child(args, executable, preexec_fn, close_fds, File "/opt/bitnami/python/lib/python3.10/subprocess.py", line 1863, in _execute_child raise child_exception_type(errno_num, err_msg, err_filename) FileNotFoundError: [Errno 2] No such file or directory: 'ecpg'

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/opt/bitnami/superset/superset/commands/database/validate_sql.py", line 69, in run errors = self._validator.validate(sql, catalog, schema, self._model) File "/opt/bitnami/superset/superset/sql_validators/postgres.py", line 42, in validate valid, error = check_string(sql, add_semicolon=True) File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/pgsanity.py", line 54, in check_string success, msg = ecpg.check_syntax(prepped_sql) File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/ecpg.py", line 20, in check_syntax raise OSError(msg) OSError: Unable to execute 'ecpg', you likely need to install it.' WARNING:superset.views.error_handling:SupersetErrorException Traceback (most recent call last): File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/ecpg.py", line 12, in check_syntax proc = subprocess.Popen(args, shell=False, File "/opt/bitnami/python/lib/python3.10/subprocess.py", line 971, in init self._execute_child(args, executable, preexec_fn, close_fds, File "/opt/bitnami/python/lib/python3.10/subprocess.py", line 1863, in _execute_child raise child_exception_type(errno_num, err_msg, err_filename) FileNotFoundError: [Errno 2] No such file or directory: 'ecpg'

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/opt/bitnami/superset/superset/commands/database/validate_sql.py", line 69, in run errors = self._validator.validate(sql, catalog, schema, self._model) File "/opt/bitnami/superset/superset/sql_validators/postgres.py", line 42, in validate valid, error = check_string(sql, add_semicolon=True) File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/pgsanity.py", line 54, in check_string success, msg = ecpg.check_syntax(prepped_sql) File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/pgsanity/ecpg.py", line 20, in check_syntax raise OSError(msg) OSError: Unable to execute 'ecpg', you likely need to install it.'

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request rv = self.dispatch_request() File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(view_args) File "/opt/bitnami/superset/venv/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps return f(self, *args, kwargs) File "/opt/bitnami/superset/superset/views/base_api.py", line 120, in wraps duration, response = time_function(f, self, args, kwargs) File "/opt/bitnami/superset/superset/utils/core.py", line 1369, in time_function response = func(args, kwargs) File "/opt/bitnami/superset/superset/utils/log.py", line 304, in wrapper value = f(*args, kwargs) File "/opt/bitnami/superset/superset/databases/api.py", line 1331, in validate_sql validator_errors = ValidateSQLCommand(pk, sql_request).run() File "/opt/bitnami/superset/superset/commands/database/validate_sql.py", line 88, in run raise ValidatorSQLError(superset_error) from ex superset.commands.database.exceptions.ValidatorSQLError: PostgreSQLValidator was unable to check your query. Please recheck your query. Exception: Unable to execute 'ecpg', you likely need to install it.' ```

This solution on 27893 probably would work but it's kind of a pain to do that without building my own container image or messing with init containers, which is a pain. You would think this would just work out of the gate.