"IntegrityError: UNIQUE constraint failed" for DSub storing ClientPrefs

[davebiffuk]

Hi, using DSub 5.5.2 with latest master of Supysonic, I see stacktraces and database errors in supysonic.log. Music playback is working OK though.

2021-09-03 10:34:57,568 [ERROR] Exception on /rest/getUser.view [GET]
Traceback (most recent call last):
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/flask/app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/flask/app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/flask/app.py", line 1511, in full_dispatch_request
    rv = self.preprocess_request()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/flask/app.py", line 1857, in preprocess_request
    rv = self.ensure_sync(func)()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/supysonic/api/__init__.py", line 87, in get_client_prefs
    commit()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 386, in commit
    rollback_and_reraise(sys.exc_info())
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 375, in rollback_and_reraise
    reraise(*exc_info)
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/utils/utils.py", line 95, in reraise
    try: raise exc.with_traceback(tb)
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 384, in commit
    cache.flush()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 1901, in flush
    if obj is not None: obj._save_()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 5437, in _save_
    if status == 'created': obj._save_created_()
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/orm/core.py", line 5279, in _save_created_
    throw(TransactionIntegrityError,
  File "/home/dave/supysonic-hake-venv/lib/python3.9/site-packages/pony/utils/utils.py", line 106, in throw
    raise exc
pony.orm.core.TransactionIntegrityError: Object ClientPrefs[User[UUID('41cc9b6b-0f49-4961-b2dc-5f9f7c17e00d')],'DSub'] cannot be stored in the database. IntegrityError: UNIQUE constraint failed: client_prefs.user_id, client_prefs.client_name

and similar backtraces for /rest/getBookmarks.view and /rest/getPlaylists.view

The client_prefs table looks like this:

sqlite> select * from client_prefs ;
user_id        client_name  format  bitrate
-------------  -----------  ------  -------
  DSub                        

[vincentDcmps]

HI you user ID seem wrong try to delete this line in SQL

delete from client_prefs;

I use dsub 5.5.1 install via F-droid and I have no issue

for information my client pref table

              user_id                |  client_name  | format | bitrate
--------------------------------------+---------------+--------+---------
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | DSub          |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | Ultrasonic    |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | Mopidy-Subidy |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | py-sonic      |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | myapp         |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | Jamstash      |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | beets         |        |
 8afd8947-4d0c-49da-a782-fd6bc1a3085e | myplayer      |        |

[spl0k]

Hello.

@davebiffuk do you see these errors quite often in the logs or only a few times around when you first connected your DSub? I suspect some kind of race condition with DSub sending several requests at once.

@vincentDcmps the absence of user id is most likely a false lead. He's using SQLite which doesn't have native support for UUIDs, they are then stored as binary data. The SQLite CLI is probably refusing to display such data.

[davebiffuk]

@davebiffuk do you see these errors quite often in the logs or only a few times around when you first connected your DSub?

Yes, it was only when I first connected DSub. Thanks for the quick fix!

Cheers, Dave