Rasa with MySQL Database

Rasa Open Source
1

Hi! I run rasa on the server with MySQL Database and everything is fine, but when I not contact with bot for 8 hours and then write a message - an error occurs.

2021-08-22 17:19:11 ERROR rasa.core.tracker_store - Error happened when trying to save conversation tracker to ‘SQLTrackerStore’. Falling back to use the ‘InMemoryTrackerStore’. Please investigate the following error: (pymysql.err.OperationalError) (2006, “MySQL server has gone away (BrokenPipeError(32, ‘Broken pipe’))”) [SQL: INSERT INTO events (sender_id, data) VALUES (%(sender_id)s, %(data)s)] [parameters: {‘sender_id’: ‘761f83a0a14f4198807cbc303eab8be2’, ‘data’: ‘{“sender_id”: “761f83a0a14f4198807cbc303eab8be2”, “event”: “action”, “timestamp”: 1629670751.6087387, “name”: “action_session_start”, “policy”: null, “confidence”: 1.0, “action_text”: null, “hide_rule_turn”: false}’}] (Background on this error at: http://sqlalche.me/e/13/e3q8).

I find solution in sqlalchemy on create_engine, need add two parameters, use pool_pre_ping and pool_recycle .

# Example
import sqlalchemy
connect_string = dialect + '://{}:{}@{}:{}/{}?charset=utf8mb4'.format(user, passwd, host, port, db)
engine = sqlalchemy.create_engine(connect_string,pool_size=50,pool_recycle=3600,pool_pre_ping=True)

The problem is that SQLTrackerStore not receive parameters when dialect is MySQL only if dialect is postgresql

How set this config parameters and avoid connection lost?

Tracker Stores , Why MySQL is not official support by RASA?

Thanks.

2

image
image718×222 5.69 KB
These are the databases supported right now. About, why MySQL is not supported I will keep that for Rasa team to answer

3

@Horizon733 MySQL Workbench, MongoDB and Reddis is also supported. I had worked with these PostgreSQL, Oracle, SQLite is default (rasa), MySQL Workbench and MongoDB.

1 Like
4

@cristianccq I hope you looking this? or I got you wrong? For storing the bot/user conversation you can use tracker store:

Update this code in endpoints.yml

For Postgres:

tracker_store:
   type: SQL
   dialect: "postgresql"  # the dialect used to interact with the db
   url: "localhost"  # (optional) host of the sql db, e.g. "localhost"
   db: "chatbot"  # path to your db
   username: "postgres"  # username used for authentication
   password: "123"  # password used for authentication
   # query: # optional dictionary to be added as a query string to the connection URL
   #   driver: my-driver

For MySQL:

tracker_store:
   type: SQL
   dialect: "mysql+pymysql"  # the dialect used to interact with the db
   url: "localhost"  # (optional) host of the sql db, e.g. "localhost"
   db: "rasa"  # path to your db
   username: "root"  # username used for authentication
   password: "root"  # password used for authentication

Note: You also can need to install psycopg2-binary and pymysql. You will see when it will thrown error.

5

thanks for answers, my configuration endpoint is

tracker_store:
  type: SQL
  dialect: "mysql+pymysql"  # the dialect used to interact with the db
  url: "192.168.21.76"  # (optiona$
  port: 3306
  db: "rasa_core"  # path to your db
  username: "root" # username used for authentication
  password: "root"

With this configuration after 8 hours I have a connection loss in mysql.

I have no way to set the configuration in sqlalchemy for mysql.

6

@cristianccq please mention whom you replying, mention @ and name of the user/ aliases. @cristianccq are using docker or docker-compose?

7

thanks for recomendation @nik202

I am in a local development environment, later I will use docker for my MySQL instance together with another Docker Rasa-Core and Docker Rasa-Action-Server

8

@cristianccq If the above solution/suggestion solve your issue, I’d like to request please close this thread for others.

@cristianccq If you need any assistance in docker and docker compose environment installation with MySQL do let me know.

9

Hi @nik202 , I’m testing for production environment.

As usual production-ready programs need to override these defaults (to fine-tune pools to their needs), most of the different implementations of connection pools provide a similar set of configuration options. The following list shows the most common options with their descriptions:

  • pool_size: Sets the number of connections that the pool will handle.
  • max_overflow: Specifies how many exceeding connections (relative to pool_size) the pool supports.
  • pool_recycle: Configures the maximum age (in seconds) of connections in the pool.
  • pool_timeout: Identifies how many seconds the program will wait before giving up on getting a connection from the pool.

I need configure this options with MySQL, only Postgres dialect allows me to configure it in create_engine (rasa/tracker_store.py at main · RasaHQ/rasa · GitHub)