Sql_tracker_store giving error

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.

Hi there!

Can you try two things for me:

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

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)

@Juste please help if you can

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

I tried still not working :frowning_face:

Hi @indranil180, sorry for my delayed response

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

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.

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

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

Yes it worked for me. Thanks :slight_smile:

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.