#1578 sqlite errors

Reporter kousu
Owner Zash
Created
Updated
Stars ★ (1)
Tags
  • Status-NeedInfo
  • Type-Defect
  • Priority-Medium
  1. kousu on

    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.

  2. kousu on

    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

  3. Zash on

    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.

    Changes
    • owner Zash
    • tags Status-NeedInfo

New comment

Not published. Used for spam prevention and optional update notifications.