SQL Sensor adds non JSON serializable types to the state machine

[Martinvdm]

Home Assistant release (hass --version):
0.65

Python release (python3 --version):
3.6.4

Component/platform:
IOS and browser

Description of problem:
Can't open IOS app after upgrade. Keeps 'Loading data...'
Normal browser seems to be involved also.

Expected:
Opening

Logging:

2018-03-09 21:38:55 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/remote.py", line 127, in default
    return json.JSONEncoder.default(self, o)
  File "/usr/local/lib/python3.6/json/encoder.py", line 180, in default
    o.__class__.__name__)
TypeError: Object of type 'Decimal' is not JSON serializable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/remote.py", line 133, in default
    for child_obj in o]
TypeError: 'decimal.Decimal' object is not iterable

2018-03-09 21:40:47 ERROR (MainThread) [aiohttp.server] Error handling request
Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/websocket_api.py", line 331, in handle
    msg = await wsock.receive_json()
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_ws.py", line 397, in receive_json
    data = await self.receive_str(timeout=timeout)
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_ws.py", line 381, in receive_str
    msg = await self.receive(timeout)
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_ws.py", line 345, in receive
    msg = await self._reader.read()
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/streams.py", line 571, in read
    return await super().read()
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/streams.py", line 533, in read
    await self._waiter
  File "/usr/local/lib/python3.6/asyncio/futures.py", line 327, in __iter__
    yield self  # This tells Task to wait for completion.
  File "/usr/local/lib/python3.6/asyncio/tasks.py", line 250, in _wakeup
    future.result()
  File "/usr/local/lib/python3.6/asyncio/futures.py", line 238, in result
    raise CancelledError
concurrent.futures._base.CancelledError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_protocol.py", line 381, in start
    resp = await self._request_handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_app.py", line 310, in _handle
    resp = await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_middlewares.py", line 88, in impl
    return await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/http/static.py", line 68, in staticresource_middleware
    return await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/http/real_ip.py", line 27, in real_ip_middleware
    return await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/http/ban.py", line 68, in ban_middleware
    return await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/http/auth.py", line 52, in auth_middleware
    return await handler(request)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/http/view.py", line 100, in handle
    result = await result
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/websocket_api.py", line 210, in get
    return await ActiveConnection(request.app['hass'], request).handle()
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/websocket_api.py", line 393, in handle
    await self._writer_task
  File "/usr/local/lib/python3.6/asyncio/futures.py", line 329, in __iter__
    return self.result()  # May raise too.
  File "/usr/local/lib/python3.6/asyncio/futures.py", line 243, in result
    raise self._exception
  File "/usr/local/lib/python3.6/asyncio/tasks.py", line 180, in _step
    result = coro.send(None)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/components/websocket_api.py", line 243, in _writer
    await self.wsock.send_json(message, dumps=JSON_DUMP)
  File "/srv/homeassistant/lib/python3.6/site-packages/aiohttp/web_ws.py", line 263, in send_json
    await self.send_str(dumps(data), compress=compress)
  File "/usr/local/lib/python3.6/json/__init__.py", line 238, in dumps
    **kw).encode(obj)
  File "/usr/local/lib/python3.6/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/local/lib/python3.6/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/srv/homeassistant/lib/python3.6/site-packages/homeassistant/remote.py", line 136, in default
    return json.JSONEncoder.default(self, o)
  File "/usr/local/lib/python3.6/json/encoder.py", line 180, in default
    o.__class__.__name__)
TypeError: Object of type 'Decimal' is not JSON serializable
2018-03-09 21:45:25 ERROR (MainThread) [homeassistant.components.websocket_api] WS 1576122704: Client exceeded max pending messages [2]: 512

[Martinvdm]

Issue is also occuring in normal browser. downgrade to 0.64.3 solves it.

[point-4ward]

Ios app is working fine on my other half's phone since upgrade, don't know about location tracking as I haven't set it up properly yet, but interface is fine.

[balloob]

Your issue is not related to the iOS device tracker issue.

You have an integration that is writing non-JSON serializable info to the state machine. Could you try to disable your integrations one by one and see if you can load the frontend. My initial suspect would be the SQL sensor, are you using that by any chance?

[juan11perez]

Good day, I removed the sql sensor, updated to 65.1 and it worked. no errors. I then added the sensor back restarted and got this: "2018-03-10 09:48:49 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: Object of type 'Decimal' is not JSON serializable" and the frontend would not load. removed the sensor again, restarted and all fine.

[balloob]

CC @dgomes

It looks like we'll need to convert some types that SQL returns to types that can be JSON serialized. Decimal should be converted to float.

[juan11perez]

Thank you. The sensor I added is the mysql database size sample sensor in the documentation.

[Martinvdm]

@balloob ok that make sense. I am using the following relevant code:

- platform: sql
  db_url: !secret mysql_db_url
  queries:
  - name: ha_recorder_dbsize
    query: 'SELECT table_schema "homeassistant", Round(Sum(data_length + index_length) / 1024 / 1024, 0) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
    column: 'value'
    unit_of_measurement: MB
# HA DSMR Database Size
- platform: sql
  db_url: !secret dsmr_db_url
  queries:
  - name: dsmr_dbsize
    query: "SELECT (pg_database_size('dsmrreader')/1024/1024) as db_size;"
    column: "db_size"
    unit_of_measurement: MB

[Knapoc]

I'm having the same issue on 0.65.1.
Here's my log output.

And there's my sql sensor config:

  - platform: sql
    db_url: !secret mysql_db
    queries:
      - name: database size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hassdb" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: MB