#1064 SQL table creation query fails when tables already exist

Reporter Mechman
Owner Nobody
Created
Updated
Stars ★ (1)  
Tags
  • Priority-Medium
  • Component-Persistence
  • Type-Defect
  • Milestone-0.10
  • Status-Accepted
  1. Mechman on

    What steps will reproduce the problem? 1. Install / upgrade to Prosody version 0.10 nightly build 458 (2017-12-29, 9b81c22d5b54) 2. Stop/start Prosody 3. Look at prosody.log oder prosody.err The same errors appear with prosodyctl adduser on the shell. What is the expected output? No SQL errors should be logged. What do you see instead? Jan 05 14:51:47 sql debug Connecting to [SQLite3] /var/lib/prosody/prosody.sqlite... Jan 05 14:51:47 sql debug SQL transaction begin [function(sql.lua:326)] Jan 05 14:51:47 sql debug SQL transaction success [function(sql.lua:326)] Jan 05 14:51:47 sql debug SQL transaction begin [function(mod_storage_sql.lua:439)] Jan 05 14:51:47 sql debug SQL transaction success [function(mod_storage_sql.lua:439)] Jan 05 14:51:47 sql debug SQL connection is up, so not retrying Jan 05 14:51:47 sql error Error in SQL transaction: commit failed Jan 05 14:51:47 sql debug SQL transaction begin [function(mod_storage_sql.lua:458)] Jan 05 14:51:47 sql debug SQL transaction success [function(mod_storage_sql.lua:458)] Jan 05 14:51:47 sql debug SQL connection is up, so not retrying Jan 05 14:51:47 sql error Error in SQL transaction: commit failed Jan 05 14:51:47 sql debug SQL transaction begin [function(mod_storage_sql.lua:57)] Jan 05 14:51:47 sql debug SQL transaction success [function(mod_storage_sql.lua:57)] What version of the product are you using? On what operating system? Prosody version 0.10 nightly build 458 (2017-12-29, 9b81c22d5b54) 3.16.0-4-amd64 #1 SMP Debian 3.16.51-3 (2017-12-13) x86_64 GNU/Linux Please provide any additional information below. Everything works fine so far, the errors don't seem to be a problem. Current trunk version logs the same errors. Nightly build 412 did not show the errors.

  2. Zash on

    Thanks for the report. Could you enable mod_debug_sql to show what query it is that is failing?

  3. Zash on

    Changes
    • tags Status-NeedInfo
  4. Mechman on

    Sure, this is the output with mod_debug_sql enabled: Jan 06 10:23:44 sql debug Connecting to [SQLite3] /var/lib/prosody/prosody.sqlite... Jan 06 10:23:44 sql debug SQL transaction begin [function(sql.lua:326)] Jan 06 10:23:44 sql debug [select] PRAGMA encoding; [0] Jan 06 10:23:44 sql debug SQL transaction success [function(sql.lua:326)] Jan 06 10:23:44 sql debug SQL transaction begin [function(mod_storage_sql.lua:439)] Jan 06 10:23:44 sql debug [create] CREATE TABLE "prosody" ("host" TEXT NOT NULL, "user" TEXT NOT NULL, "store" TEXT NOT NULL, "key" TEXT NOT NULL, "type" TEXT NOT NULL, "value" TEXT NOT NULL); [0] Jan 06 10:23:44 sql debug SQL transaction success [function(mod_storage_sql.lua:439)] Jan 06 10:23:44 sql debug SQL connection is up, so not retrying Jan 06 10:23:44 sql error Error in SQL transaction: commit failed Jan 06 10:23:44 sql debug SQL transaction begin [function(mod_storage_sql.lua:458)] Jan 06 10:23:44 sql debug [create] CREATE TABLE "prosodyarchive" ("sort_id" INTEGER PRIMARY KEY AUTOINCREMENT, "host" TEXT NOT NULL, "user" TEXT NOT NULL, "store" TEXT NOT NULL, "key" TEXT NOT NULL, "when" INTEGER NOT NULL, "with" TEXT NOT NULL, "type" TEXT NOT NULL, "value" TEXT NOT NULL); [0] Jan 06 10:23:44 sql debug SQL transaction success [function(mod_storage_sql.lua:458)] Jan 06 10:23:44 sql debug SQL connection is up, so not retrying Jan 06 10:23:44 sql error Error in SQL transaction: commit failed Jan 06 10:23:44 sql debug SQL transaction begin [function(mod_storage_sql.lua:57)] Jan 06 10:23:44 sql debug [select] SELECT "key","type","value" FROM "prosody" WHERE "host"='foo.bar' AND "user"='' AND "store"='persistent'; [3] Jan 06 10:23:44 sql debug SQL transaction success [function(mod_storage_sql.lua:57)]

  5. Zash on

    Ah, yes. The CREATE TABLE statements fail because the tables are already created. It is safe to ignore. We should add a bit of IF NOT EXISTS.

    Changes
    • tags Status-Accepted Priority-Low
  6. Mechman on

    That are good news, thanks for your work on Prosody!

  7. Zash on

    Looks like both SQLite3 and Postgres has CREATE TABLE / INDEX IF NOT EXISTS, but MySQL is missing CREATE INDEX IF NOT EXISTS.

  8. Zash on

    Changes
    • tags Priority-Medium Milestone-0.10
    • title SQL error on startup Table creation query fails when tables already exist
  9. Zash on

    Changes
    • tags Component-Persistence
    • title Table creation query fails when tables already exist SQL table creation query fails when tables already exist

New comment

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