Sql_tracker_store giving error

indranil180 · July 29, 2019, 11:29am

I want to use sql tracker store to store the conversation history in db but it seems it is not working for me. I am having MySQL database which i want to connect for that i am using below code in my endpoint.yml

tracker_store:
    type: SQL
    dialect: "sqlite"  # the dialect used to interact with the db
    url: "localhost"  # (optional) host of the sql db
    db: "chatbot"  # path to your db
    username: "root"  # username used for authentication
    password: "test"  # password used for authentication

when i am running rasa shell -m models --endpoints endpoints.yml i am getting below error.

" sqlite:////absolute/path/to/file.db" % (url,)
sqlalchemy.exc.ArgumentError: Invalid SQLite URL: sqlite://root:test@localhost/c
hatbot
Valid SQLite URL forms are:
 sqlite:///:memory: (or, sqlite://)
 sqlite:///relative/path/to/file.db
 sqlite:////absolute/path/to/file.db

When i tried to connect the same db using python code it worked for me. Below is my python code.

import mysql.connector

mydb = mysql.connector.connect(host="localhost",port="3306",user="root",database="chatbot",password="test")
mycursor = mydb.cursor()

sql = "Show tables;"

mycursor.execute(sql)
myresult = mycursor.fetchall()
print(myresult)
for x in myresult:
    print(x)

Please help me how can i resolve this.

MetcalfeTom · July 30, 2019, 8:59am

Hi there!

Can you try two things for me:

Replace the sqlite dialect with one for mysql. You can check the compatible ones here (i.e. just mysql should work)
Include the port with your url in the endpoint.yml

indranil180 · July 30, 2019, 9:04am

I tried the mentioned solutions it doesn’t solve my issue.

tracker_store:
    type: SQL
    dialect: "mysql"
    url: "localhost:3306"
    db: "chatbot"
    username: "root"
    password: "test"

This is what i did but i am getting below error.

2019-07-30 14:32:35 ERROR    rasa.core.tracker_store  - Could not create tables:
 (MySQLdb._exceptions.OperationalError) (2005, "Unknown MySQL server host 'local
host:3306' (11001)")
(Background on this error at: http://sqlalche.me/e/e3q8)

indranil180 · August 2, 2019, 9:02am

@Juste please help if you can

Juste · August 5, 2019, 3:01pm

Hi @indranil180. Can you try your latest configuration without the port number, just keeping the localhost as your url?

indranil180 · August 6, 2019, 6:34am

I tried still not working

MetcalfeTom · August 8, 2019, 10:02am

Hi @indranil180, sorry for my delayed response

Can you post the full stacktrace from invoking the rasa shell --debug --endpoints endpoints.yml command please?

indranil180 · August 12, 2019, 12:42pm

Traceback (most recent call last):
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2847, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2879, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1498, in get_column_specification
    column.type, type_expression=column
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 400, in process
    return type_._compiler_dispatch(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 3344, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1965, in visit_VARCHAR
    "VARCHAR requires a length on dialect %s" % self.dialect.name
sqlalchemy.exc.CompileError: VARCHAR requires a length on dialect mysql

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

Traceback (most recent call last):
  File "c:\programdata\miniconda3\lib\site-packages\sanic\app.py", line 1096, in
 run
    serve(**server_settings)
  File "c:\programdata\miniconda3\lib\site-packages\sanic\server.py", line 742,
in serve
    trigger_events(before_start, loop)
  File "c:\programdata\miniconda3\lib\site-packages\sanic\server.py", line 604,
in trigger_events
    loop.run_until_complete(result)
  File "c:\programdata\miniconda3\lib\asyncio\base_events.py", line 467, in run_
until_complete
    return future.result()
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\run.py", line 176,
 in load_agent_on_start
    None, endpoints.tracker_store, _broker
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\tracker_store.py",
 line 42, in find_tracker_store
    domain=domain, host=store.url, event_broker=event_broker, **store.kwargs
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\tracker_store.py",
 line 326, in __init__
    self.Base.metadata.create_all(self.engine)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\schema.py", l
ine 4287, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 2033, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 1607, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 131, in traverse_single
    return meth(obj, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 781, in visit_metadata
    _is_metadata_operation=True,
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 131, in traverse_single
    return meth(obj, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 826, in visit_table
    include_foreign_key_constraints,
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 988, in execute
    return meth(self, multiparams, params)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 1043, in _execute_ddl
    else None,
  File "<string>", line 1, in <lambda>
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\elements.py",
 line 462, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2859, in visit_create_table
    % (table.description, column.name, ce.args[0])
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\util\compat.py",
line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\util\compat.py",
line 153, in reraise
    raise value.with_traceback(tb)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2847, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2879, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1498, in get_column_specification
    column.type, type_expression=column
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 400, in process
    return type_._compiler_dispatch(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 3344, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1965, in visit_VARCHAR
    "VARCHAR requires a length on dialect %s" % self.dialect.name
sqlalchemy.exc.CompileError: (in table 'events', column 'sender_id'): VARCHAR re
quires a length on dialect mysql
Traceback (most recent call last):
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2847, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2879, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1498, in get_column_specification
    column.type, type_expression=column
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 400, in process
    return type_._compiler_dispatch(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 3344, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1965, in visit_VARCHAR
    "VARCHAR requires a length on dialect %s" % self.dialect.name
sqlalchemy.exc.CompileError: VARCHAR requires a length on dialect mysql

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

Traceback (most recent call last):
  File "c:\programdata\miniconda3\lib\runpy.py", line 193, in _run_module_as_mai
n
    "__main__", mod_spec)
  File "c:\programdata\miniconda3\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:\ProgramData\Miniconda3\Scripts\rasa.exe\__main__.py", line 9, in <mod
ule>
  File "c:\programdata\miniconda3\lib\site-packages\rasa\__main__.py", line 70,
in main
    cmdline_arguments.func(cmdline_arguments)
  File "c:\programdata\miniconda3\lib\site-packages\rasa\cli\shell.py", line 97,
 in shell
    rasa.cli.run.run(args)
  File "c:\programdata\miniconda3\lib\site-packages\rasa\cli\run.py", line 70, i
n run
    rasa.run(**vars(args))
  File "c:\programdata\miniconda3\lib\site-packages\rasa\run.py", line 65, in ru
n
    **kwargs
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\run.py", line 150,
 in serve_application
    app.run(host="0.0.0.0", port=port)
  File "c:\programdata\miniconda3\lib\site-packages\sanic\app.py", line 1096, in
 run
    serve(**server_settings)
  File "c:\programdata\miniconda3\lib\site-packages\sanic\server.py", line 742,
in serve
    trigger_events(before_start, loop)
  File "c:\programdata\miniconda3\lib\site-packages\sanic\server.py", line 604,
in trigger_events
    loop.run_until_complete(result)
  File "c:\programdata\miniconda3\lib\asyncio\base_events.py", line 467, in run_
until_complete
    return future.result()
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\run.py", line 176,
 in load_agent_on_start
    None, endpoints.tracker_store, _broker
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\tracker_store.py",
 line 42, in find_tracker_store
    domain=domain, host=store.url, event_broker=event_broker, **store.kwargs
  File "c:\programdata\miniconda3\lib\site-packages\rasa\core\tracker_store.py",
 line 326, in __init__
    self.Base.metadata.create_all(self.engine)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\schema.py", l
ine 4287, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 2033, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 1607, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 131, in traverse_single
    return meth(obj, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 781, in visit_metadata
    _is_metadata_operation=True,
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 131, in traverse_single
    return meth(obj, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 826, in visit_table
    include_foreign_key_constraints,
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 988, in execute
    return meth(self, multiparams, params)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\engine\base.py",
line 1043, in _execute_ddl
    else None,
  File "<string>", line 1, in <lambda>
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\elements.py",
 line 462, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\ddl.py", line
 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2859, in visit_create_table
    % (table.description, column.name, ce.args[0])
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\util\compat.py",
line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\util\compat.py",
line 153, in reraise
    raise value.with_traceback(tb)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2847, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 2879, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1498, in get_column_specification
    column.type, type_expression=column
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 400, in process
    return type_._compiler_dispatch(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\visitors.py",
 line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\sql\compiler.py",
 line 3344, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "c:\programdata\miniconda3\lib\site-packages\sqlalchemy\dialects\mysql\ba
se.py", line 1965, in visit_VARCHAR
    "VARCHAR requires a length on dialect %s" % self.dialect.name
sqlalchemy.exc.CompileError: (in table 'events', column 'sender_id'): VARCHAR re
quires a length on dialect mysql

Please find the stacktrace.

indranil180 · August 12, 2019, 12:43pm

i am using below tracker_store.

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

MetcalfeTom · August 12, 2019, 6:23pm

Ahh OK, this was a nasty bug with sqlalchemy. Try this solution please. If that works then we can probably make a fix for this

indranil180 · August 13, 2019, 6:15am

Yes it worked for me. Thanks

indranil180 · August 13, 2019, 8:35am

But now it turns out my form action is having some problem with sql tracker store. Form action is working fine for the first time but 2nd time when i type values for required slots my two stage fallback policy is triggered.

eashan_27 · October 1, 2020, 11:38am

Hey @MetcalfeTom @Juste @indranil180 I am looking to connect my postgres sql database with RASA through the endpoints.yml file and when i run this command rasa shell --debug --endpoints endpoints.yml it shows that my SQL tracker store is connected with my database. But my main question is how should i check whether it is storing any conversations in the DataBase or not like i am not able to figure that out.Could anyone of you help me out here it’ll be appreciated. Thanks

Juste · October 2, 2020, 11:41am

Hi @eashan_27. If you run your assistant with rasa shell --debug you should see if your assistant is connected to your SQL tracker store. If that’s the case, the information should be stored there. To check that you could simply connect to the db and query the database to check the records

Topic		Replies	Views
Importing Conversations Rasa Open Source	2	1027	July 22, 2019
Save conversation sqlite Rasa Open Source	5	2144	November 8, 2019
SQL Tracker_store Rasa Open Source	4	1010	April 27, 2019
Rasa SQL DB configuration Rasa Open Source	2	881	April 28, 2020
SQL Tracker_store affecting conversation flow by giving "action_default_fallback" Rasa Open Source testing	0	209	March 1, 2023

Sql_tracker_store giving error

Related topics