redash
redash copied to clipboard
Published
20 hours ago •
getredash
getredash
Delete data source - sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation)
Dear support,
I am writing to you about the following issue :
I have a redash application deployed into AWS fargate cluster ( web, worker, scheduler ) services based on redash/redash:preview docker image. Months ago i decided to shutdown the application so i created a db backup of redash database. Now i wanted to bring up the services again so i created all the required resources via terraform code and when i checked the containers i noticed that they are unhealthy status because of missing REDASH_COOKIE_SECRET environment variable which i didn't get asked to add it when i implemented the redash application the first time since 2 years ago. So i made some research and i found that i have to add it explicitly
as environment variable. After adding that new secret all the services started successfully. So i restored the data from the old sql dump into the newly created aws aurora postgresql database, the restore operation succeeded and i could access the database and check the data is there and also i could login into redash web application. From the redash UI i could see all the dashboard elements BUT when i tried to access the Settings tab i got an Internal Server Error with this trace back
[2023-11-01 12:09:08,442][PID:43][ERROR][redash.app] Exception on /api/data_sources [GET]
--
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/cryptography/fernet.py", line 134, in _verify_signature
h.verify(data[-32:])
cryptography.exceptions.InvalidSignature: Signature did not match digest.
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 489, in wrapper
resp = resource(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/flask_login/utils.py", line 277, in decorated_view
return current_app.ensure_sync(func)(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/flask/views.py", line 109, in view
return current_app.ensure_sync(self.dispatch_request)(**kwargs)
File "/app/redash/handlers/base.py", line 31, in dispatch_request
return super(BaseResource, self).dispatch_request(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/flask_restful/__init__.py", line 604, in dispatch_request
resp = meth(*args, **kwargs)
File "/app/redash/permissions.py", line 71, in decorated
return fn(*args, **kwargs)
File "/app/redash/handlers/data_sources.py", line 110, in get
for ds in data_sources:
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 100, in instances
cursor.close()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 80, in instances
rows = [proc(row) for row in fetch]
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 80, in <listcomp>
rows = [proc(row) for row in fetch]
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 579, in _instance
_populate_full(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 725, in _populate_full
dict_[key] = getter(row)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1278, in process
return process_value(impl_processor(value), dialect)
File "/app/redash/models/types.py", line 30, in process_result_value
super(EncryptedConfiguration, self).process_result_value(value, dialect)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 409, in process_result_value
decrypted_value = self.engine.decrypt(value)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 216, in decrypt
decrypted = self.fernet.decrypt(value)
File "/usr/local/lib/python3.8/site-packages/cryptography/fernet.py", line 91, in decrypt
return self._decrypt_data(data, timestamp, time_info)
File "/usr/local/lib/python3.8/site-packages/cryptography/fernet.py", line 152, in _decrypt_data
self._verify_signature(data)
File "/usr/local/lib/python3.8/site-packages/cryptography/fernet.py", line 136, in _verify_signature
raise InvalidToken
cryptography.fernet.InvalidToken
[2023-11-01 12:09:08,445][PID:43][INFO][metrics] method=GET path=/api/data_sources endpoint=data_sources status=500 content_type=application/json content_length=36 duration=12.79 query_count=5 query_duration=9.50
I tried this solution but when i executed this command # ./manage.py ds delete "Production DB" i got this error
[2023-11-01 12:07:52,303][PID:224][INFO][xmlschema] Resource 'XMLSchema.xsd' is already loaded
Deleting data source: Production DB (id=1)
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1256, in _execute_context
self.dialect.do_executemany(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 912, in do_executemany
cursor.executemany(statement, parameters)
psycopg2.errors.NotNullViolation: null value in column "data_source_id" of relation "query_results" violates not-null constraint
DETAIL: Failing row contains (29, 1, null, 1599037c50364166974d7cd58c16d0c4, SELECT COUNT(*) FROM stores_storeinventory WHERE store_id = 1 AN..., {"columns": [{"name": "count", "friendly_name": "count", "type":..., 0.021358966827392578, 2022-03-28 14:31:57.523855+00).
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "./manage.py", line 9, in <module>
manager()
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1130, in __call__
return self.main(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1055, in main
rv = self.invoke(ctx)
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1657, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1657, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 760, in invoke
return __callback(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 357, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/click/core.py", line 760, in invoke
return __callback(*args, **kwargs)
File "/app/redash/cli/data_sources.py", line 193, in delete
models.db.session.commit()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
self.transaction.commit()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 504, in commit
self._prepare_impl()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
self._flush(objects)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
flush_context.execute()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
rec.execute(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
persistence.save_obj(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 230, in save_obj
_emit_update_statements(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 994, in _emit_update_statements
c = cached_connections[connection].execute(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1256, in _execute_context
self.dialect.do_executemany(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 912, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "data_source_id" of relation "query_results" violates not-null constraint
DETAIL: Failing row contains (29, 1, null, 1599037c50364166974d7cd58c16d0c4, SELECT COUNT(*) FROM stores_storeinventory WHERE store_id = 1 AN..., {"columns": [{"name": "count", "friendly_name": "count", "type":..., 0.021358966827392578, 2022-03-28 14:31:57.523855+00).
[SQL: UPDATE query_results SET data_source_id=%(data_source_id)s WHERE query_results.id = %(query_results_id)s]
[parameters: ({'data_source_id': None, 'query_results_id': 29}, {'data_source_id': None, 'query_results_id': 69}, {'data_source_id': None, 'query_results_id': 88}, {'data_source_id': None, 'query_results_id': 107}, {'data_source_id': None, 'query_results_id': 166}, {'data_source_id': None, 'query_results_id': 407}, {'data_source_id': None, 'query_results_id': 469}, {'data_source_id': None, 'query_results_id': 1032} ... displaying 10 of 119 total bound parameter sets ... {'data_source_id': None, 'query_results_id': 192006}, {'data_source_id': None, 'query_results_id': 192007})]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
Can you please help me to fix this error and be able to create a new data sources from the web UI application ?
I will be very happy to provide more details about the setup/implementation of Redash application
Thank you in advance
Hi @lemaadi,
Thank you for reaching out, and yikes sorry the solution wasn't working. The error occurs because we are attempting to delete data sources, which are in relationships with query results. Because the relationships are non-nullable, deleting the data sources will cause corruption in query result data.
An idea I have to bypass this issue is, first drop the relationship column between data source and query results, then delete and data source and recreate them from the UI, and lastly restore the relationships between queries and data source.
No guarantee on when I'll be able to finish, but I'll work on more concrete steps in the next couple of days.
