database race conditions

[ineeee]

the seen and sieve plugins are constantly fighting for database access in different threads, so at some point we get a lot of different threads trying to write to the same database table and they crash eachother because the database is locked. this shows up constantly on slow hardware and it happens about monthly on the virtual machine we have.

a restart fixes it but thats a bullshit fix.

Jun 23 10:15:20 taiga env[324307]: [DB ERROR] start: database is locked
Jun 23 10:15:20 taiga env[324307]: Traceback (most recent call last):
Jun 23 10:15:20 taiga env[324307]:   File "/opt/taiga/uguubot/plugins/util/database.py", line 112, in get
Jun 23 10:15:20 taiga env[324307]:     result = db.execute("SELECT {} FROM {} WHERE {}='{}';".format(field, table, matchfield, matchvalue)).fetchone()
Jun 23 10:15:20 taiga env[324307]: sqlite3.OperationalError: database is locked
Jun 23 10:15:20 taiga env[324307]: [DB ERROR] end: database is locked

[ineeee]

i upgraded to python 3.11, so far this hasn't showed up again

[ineeee]

according to the analytics i shoved in 0815d603f14d1f9429fe16935f29ab2596a8c6b6 for #41, the database is being hit hard by these plugins:

 282859 tellinput       plugins/tell.py:28
 282022 ai_sieve        plugins/ai.py:39

these two also show up in the error log when the "sqlite3.OperationalError" stuff pops up, so its likely that these are responsible for the random database issues we've been having.

i think these two plugins trigger on every irc line which is a bit excessive (but i'm not sure because i dont know how uguubot's "sieve" works) so it means we have multiple threads using the same database at once on every irc line.

[ineeee]

for comparison, this is how commands look:

   1719 quote   plugins/quote.py:199
    974 gpt     plugins/openai.py:140
    814 tell    plugins/tell.py:74
    522 google  plugins/google.py:10
    481 youtube plugins/youtube.py:115
    466 weather plugins/weather.py:25
    348 gptunsafe       plugins/openai.py:165
    299 wolframalpha    plugins/wolframalpha.py:215
    263 hashtag plugins/hashtags.py:110
    250 lastfm  plugins/lastfm.py:29

[ineeee]

tonight the bot got stuck again. this issue still exists

[ineeee]

[07:03] <%wednesday> I tried to fix that before but was a pain in the ass
[07:03] <%wednesday> I think I had a way for taiga to auto restart when that happened

maybe we can just crash the process when the database locks up so systemd restarts taigabot (lol)

[676339784]

This happened again plz fix

[ineeee]

sure dude, i restarted but idk how to fix

[676339784]

sure dude, i restarted but idk how to fix

Ya that's ok, as long as I can reach you here I guess it's fine

[ineeee]

there are other db errors showing up in .quote <nick> and some other random fucking thing with a syntax error that i dont know how to reproduce

[ineeee]

i think i fixed

[ineeee]

i havent pushed the commit but its been running ok for like 6 weeks now