PostgreSQL error when trying to delete a conversation

[Deprecated] Rasa X Community Edition
1

Hi, I’m getting a PostgreSQL error when I try to delete some conversations. I’m using Rasa 2.8.0 and Rasa X 0.42.0 installed with docker-compose.

rasa_error

I thought it might be because these conversations were using an old version of the chatbot with intents that no longer exist, but I recently encountered this problem with a tester’s conversation on the current version and I can’t figure out what’s causing the problem, or what the “message_log_to_tag_mapping” table is for.

I would also like to know if there is a way to access this db from outside the server, I did some tests with psycopg2 in a python script but I get a “could not translate host name to address: Name or service not known” error using the server domain as host with port 5432 port and the credentials found in the .env and docker-compose.yml files.

Here are the logs obtained when trying to delete one of these conversations:

rasa-x_1           | Traceback (most recent call last):
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
rasa-x_1           |     self.dialect.do_execute(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 717, in do_execute
rasa-x_1           |     cursor.execute(statement, parameters)
rasa-x_1           | psycopg2.errors.ForeignKeyViolation: update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           |
rasa-x_1           | The above exception was the direct cause of the following exception:
rasa-x_1           |
rasa-x_1           | Traceback (most recent call last):
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sanic/app.py", line 973, in handle_request
rasa-x_1           |     response = await response
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/decorators.py", line 216, in decorated_function
rasa-x_1           |     return await await_and_return_response(args, kwargs, request)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/decorators.py", line 146, in await_and_return_response
rasa-x_1           |     response = await response
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/api/blueprints/stack.py", line 260, in delete_conversation_by_id
rasa-x_1           |     EventService.from_request(request).delete_conversation_by_id(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/services/event_service.py", line 1642, in delete_conversation_by_id
rasa-x_1           |     self.commit()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/rasax/community/database/service.py", line 24, in commit
rasa-x_1           |     self.session.commit()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 1428, in commit
rasa-x_1           |     self._transaction.commit(_to_root=self.future)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 829, in commit
rasa-x_1           |     self._prepare_impl()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
rasa-x_1           |     self.session.flush()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3298, in flush
rasa-x_1           |     self._flush(objects)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3438, in _flush
rasa-x_1           |     transaction.rollback(_capture_exception=True)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
rasa-x_1           |     compat.raise_(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
rasa-x_1           |     raise exception
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 3398, in _flush
rasa-x_1           |     flush_context.execute()
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rasa-x_1           |     rec.execute(self)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 667, in execute
rasa-x_1           |     util.preloaded.orm_persistence.delete_obj(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 340, in delete_obj
rasa-x_1           |     _emit_delete_statements(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/persistence.py", line 1440, in _emit_delete_statements
rasa-x_1           |     c = connection._execute_20(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20
rasa-x_1           |     return meth(self, args_10style, kwargs_10style, execution_options)
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
rasa-x_1           |     return connection._execute_clauseelement(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
rasa-x_1           |     ret = self._execute_context(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
rasa-x_1           |     self._handle_dbapi_exception(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
rasa-x_1           |     util.raise_(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
rasa-x_1           |     raise exception
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
rasa-x_1           |     self.dialect.do_execute(
rasa-x_1           |   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 717, in do_execute
rasa-x_1           |     cursor.execute(statement, parameters)
rasa-x_1           | sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           | [SQL: DELETE FROM conversation WHERE conversation.sender_id = %(sender_id)s]
rasa-x_1           | [parameters: {'sender_id': 'fe52208348d94350903b9a5ad758a896'}]
rasa-x_1           | (Background on this error at: https://sqlalche.me/e/14/gkpj)
rasa-x_1           | (psycopg2.errors.ForeignKeyViolation) update or delete on table "message_log" violates foreign key constraint "message_log_to_tag_mapping_message_log_id_fkey" on table "message_log_to_tag_mapping"
rasa-x_1           | DETAIL:  Key (id)=(391) is still referenced from table "message_log_to_tag_mapping".
rasa-x_1           |
rasa-x_1           | [SQL: DELETE FROM conversation WHERE conversation.sender_id = %(sender_id)s]
rasa-x_1           | [parameters: {'sender_id': 'fe52208348d94350903b9a5ad758a896'}]
rasa-x_1           | (Background on this error at: https://sqlalche.me/e/14/gkpj)

Any help is appreciated :smiley:

Thank you!

2

@Flo are you able to see the data in database?

3

No I can’t access the database at all and I don’t know how to try directly on the server (I’m new to docker and I tried from outside the server first), I only manipulate the data using the Rasa X UI or API.

And when trying with psycopg2 :

conn = psycopg2.connect(
    host="http://mydomain.com",
    port=5432,
    database="rasa",
    user="admin",
    password="XXXXX" 
)

I get

could not translate host name "http://mydomain.com" to address: Name or service not known

And looking at the conversations affected by the deletion error, I don’t see what they have in common or how they differ from the others that I can delete…

4

@Flo can you mention the server IP in host?

5

@nik202 So yes, using the IP the server seems to be found (it had to be host='mydomain.com' and not host='http://mydomain.com') but now I have a new error message:

Connection refused
Is the server running on host "mydomain.com" (<ServerIP>) and accepting TCP/IP connections on port 5432?

And the $ sudo lsof -i -P -n | grep LISTEN command shows that there is nothing listening on port 5432, but the DB_PORT variable found in docker-compose.yml is 5432 as shown here: Customize Your Deployment.

I’m also wondering if the DB_HOST variable (default value is “db”) should be used somewhere or if if I should link the container port 5432 to a server port (which I don’t know how to do either …)

6

By adding the following entry to docker-compose.override.yml:

db:
  ports:
    - "5432:5432"

The $ sudo lsof -i -P -n | grep LISTEN command shows that my server is now listening on port 5432:

docker-pr 3833863            root    4u  IPv4 102922123      0t0  TCP *:5432 (LISTEN)
docker-pr 3833880            root    4u  IPv6 102923063      0t0  TCP *:5432 (LISTEN)

But I still can’t connect to the db, I get the same error as in my previous post, which prevents me from fixing the initial problem of deleting some conversations.

7

I gave up on this problem for a while. And recently I updated Rasa X to version 1.0.1, keeping the db entry in the docker-compose.override.yml file.

After the upgrade, the same conversations were still impossible to delete, however connecting to the db using Psycopg2 from an external Python script started working!

The problematic conversations had been tagged as they were discovered, so it was easy to retrieve their id automatically using the RasaX API. Then I just had to delete the rows concerning these conversations in the message_log_to_tag_mapping table with the following query:

q = f"DELETE FROM message_log_to_tag_mapping WHERE message_log_id IN (SELECT id FROM message_log WHERE conversation_id={conv_id});"

After that the deletion of these conversations works in the UI or using the API ! :tada:

The message_log_to_tag_mapping table seems to associate messages with tags, but I still don’t understand how some messages ended up in this table… I’m sure I’ve never used the API to tag a message and the RasaX UI only seems to allow you to tag an entire conversation (which doesn’t stop me from deleting that conversation afterwards)