Error when using a MySQL db

[maykar]

0.83.3 - External MySQL db using history.

Jupyter Error:

Successfully connected to database
Error with query: 
            SELECT entity_id, COUNT(*)
            FROM states
            GROUP BY entity_id
            ORDER by 2 DESC
            
Connection error, check your URL
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 

OperationalError: no such table: states

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

OperationalError                          Traceback (most recent call last)
<ipython-input-1-b0a168cdf11c> in <module>
      1 from detective.core import db_from_hass_config
----> 2 db = db_from_hass_config()

/usr/local/lib/python3.6/dist-packages/detective/core.py in db_from_hass_config(path, **kwargs)
     16 
     17     url = config.db_url_from_hass_config(path)
---> 18     return HassDatabase(url, **kwargs)
     19 
     20 

/usr/local/lib/python3.6/dist-packages/detective/core.py in __init__(self, url, fetch_entities)
     39             print("Successfully connected to database")
     40             if fetch_entities:
---> 41                 self.fetch_entities()
     42         except:
     43             print("Connection error, check your URL")

/usr/local/lib/python3.6/dist-packages/detective/core.py in fetch_entities(self)
     62             """
     63             )
---> 64         response = self.perform_query(query)
     65         entities = [e[0] for e in list(response)]
     66         print("There are {} entities with data".format(len(entities)))

/usr/local/lib/python3.6/dist-packages/detective/core.py in perform_query(self, query)
     47         """Perform a query, where query is a string."""
     48         try:
---> 49             return self.engine.execute(query)
     50         except:
     51             print("Error with query: {}".format(query))

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   2073 
   2074         connection = self.contextual_connect(close_with_result=True)
-> 2075         return connection.execute(statement, *multiparams, **params)
   2076 
   2077     def scalar(self, statement, *multiparams, **params):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    946             raise exc.ObjectNotExecutableError(object)
    947         else:
--> 948             return meth(self, multiparams, params)
    949 
    950     def _execute_function(self, func, multiparams, params):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    267     def _execute_on_connection(self, connection, multiparams, params):
    268         if self.supports_execution:
--> 269             return connection._execute_clauseelement(self, multiparams, params)
    270         else:
    271             raise exc.ObjectNotExecutableError(self)

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1058             compiled_sql,
   1059             distilled_params,
-> 1060             compiled_sql, distilled_params
   1061         )
   1062         if self._has_events or self.engine._has_events:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1411                 util.raise_from_cause(
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )
   1415             else:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    263     exc_type, exc_value, exc_tb = exc_info
    264     cause = exc_value if exc_value is not exception else None
--> 265     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    266 
    267 if py3k:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    246             value.__cause__ = cause
    247         if value.__traceback__ is not tb:
--> 248             raise value.with_traceback(tb)
    249         raise value
    250 

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    507 
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 
    511     def do_execute_no_params(self, cursor, statement, context=None):

OperationalError: (sqlite3.OperationalError) no such table: states [SQL: '\n            SELECT entity_id, COUNT(*)\n            FROM states\n            GROUP BY entity_id\n            ORDER by 2 DESC\n            '] (Background on this error at: http://sqlalche.me/e/e3q8)

HA addon log:

2018/12/14 15:01:12 [error] 607#607: *4 connect() failed (111: Connection refused) while connecting to upstream, client: 172.30.32.1, server: hassio.local, request: "GET /api/sessions?1544817671254 HTTP/1.1", upstream: "http://127.0.0.1:28459/api/sessions?1544817671254", host: "xxxxxx.duckdns.org", referrer: "https://xxxxxx.duckdns.org/lab?"
2018/12/14 15:01:12 [error] 607#607: *5 connect() failed (111: Connection refused) while connecting to upstream, client: 172.30.32.1, server: hassio.local, request: "GET /api/terminals?1544817671256 HTTP/1.1", upstream: "http://127.0.0.1:28459/api/terminals?1544817671256", host: "xxxxxx.duckdns.org", referrer: "https://xxxxxx.duckdns.org/lab?"
[W 15:01:15.262 LabApp] All authentication is disabled.  Anyone who can connect to this server will be able to run code.

[balloob]

@maykar uses MySQL, got reports from @bramkragten with similar issue running PostGres

[balloob]

This was caused by #58. It was loading SQLite as default because it couldn't process !include