What steps will reproduce the problem? 1. Install prosody 11.2 on OpenBSD via `pkg_add prosody` 2. Configure: storage = "sql" -- Default is "internal" sql = { driver = "SQLite3", database = "prosody.sqlite" } -- Default. 'database' is the filename. 3. Configure the rest of the server. 4. Configure logging at debug level. What is the expected output? No errors. What do you see instead? Intermittent errors. I get this in my logs: ``` Aug 10 09:31:55 sql error Error in SQL transaction: commit failed Aug 10 09:31:55 sql error Error in SQL transaction: commit failed ``` These two lines happen on every boot, and intermittently thereafter. and sometimes I get ``` Aug 10 11:31:52 sql debug Error in SQL transaction: /usr/local/lib/prosody/util/sql.lua:163: Error preparing statement handle: near "ORDER": syntax error stack traceback: /usr/local/lib/prosody/util/sql.lua:163: in function </usr/local/lib/prosody/util/sql.lua:158> (tail call): ? [C]: in function 'xpcall' /usr/local/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/local/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/local/lib/prosody/modules/mod_storage_sql.lua:378: in function 'delete' .../local/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:835: in function 'set' /usr/local/lib/prosody/util/pubsub.lua:548: in function 'publish' .../local/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:589: in function 'handler' ... /usr/local/lib/prosody/util/events.lua:79: in function </usr/local/lib/prosody/util/events.lua:75> (tail call): ? (tail call): ? /usr/local/lib/prosody/modules/mod_iq.lua:38: in function '?' /usr/local/lib/prosody/util/events.lua:79: in function </usr/local/lib/prosody/util/events.lua:75> (tail call): ? /usr/local/lib/prosody/core/stanza_router.lua:180: in function 'core_post_stanza' /usr/local/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/local/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/local/lib/prosody/util/async.lua:127: in function </usr/local/lib/prosody/util/async.lua:125> Aug 10 11:31:52 sql debug SQL transaction failure [function(mod_storage_sql.lua:378)]: /usr/local/lib/prosody/util/sql.lua:163: Error preparing statement handle: near "ORDER": syntax error Aug 10 11:31:52 sql debug SQL connection is up, so not retrying Aug 10 11:31:52 sql error Error in SQL transaction: /usr/local/lib/prosody/util/sql.lua:163: Error preparing statement handle: near "ORDER": syntax error ``` What version of the product are you using? On what operating system? 11.2. OpenBSD 6.6. Please provide any additional information below. This doesn't seem to be hurting anything at all. The server is working. I can add and remove accounts, peer with other servers, use http_upload, etc. I don't know how concerned I should be about this.

I also get the first error one trying to use prosodyctl: comms3# prosodyctl deluser test9@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test8@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test7@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test6@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test5@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test4@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test3@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test2@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser test1@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser sdfdsfsd@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser rhdjjdjd@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed comms3# prosodyctl deluser tesyoodod@sparkles.ooo sql error Error in SQL transaction: commit failed sql error Error in SQL transaction: commit failed

Thanks for the report. High probability of the two transaction failures being a duplicate of #1416 And `near "ORDER": syntax error` might be #1359 Fixes for both those are in 0.11.3, please upgrade and try to see if that fixes it.

• owner Zash
• tags Status-NeedInfo

If you still see these errors, please enable mod_debug_sql to get better SQL-related logs, then report what queries trigger these.

No response, so assuming it is resolved.

• tags Milestone-0.11 Status-Fixed

I haven't upgraded yet! Sorry, I must have missed the email under a flood of others. But when I do upgrade I'll test and let you know how it goes :)

Okay, setting it back to needinfo then.

• tags Status-NeedInfo

No response, so assuming it is resolved. Again.

• tags Status-Fixed