#1064 SQL table creation query fails when tables already exist
Reporter
Mechman
Owner
Zash
Created
Updated
Stars
★ (1)
Tags
Milestone-0.11
Priority-Medium
Type-Defect
Component-Persistence
Status-Fixed
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.
Zash
on
Thanks for the report. Could you enable mod_debug_sql to show what query it is that is failing?
Zash
on
Changes
tags Status-NeedInfo
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)]
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
Mechman
on
That are good news, thanks for your work on Prosody!
Zash
on
Looks like both SQLite3 and Postgres has CREATE TABLE / INDEX IF NOT EXISTS, but MySQL is missing CREATE INDEX IF NOT EXISTS.
Zash
on
Changes
tags Priority-Medium Milestone-0.10
titleSQL error on startup Table creation query fails when tables already exist
Zash
on
Changes
tags Component-Persistence
titleTable creation query fails when tables already exist SQL table creation query fails when tables already exist
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.
Thanks for the report. Could you enable mod_debug_sql to show what query it is that is failing?
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)]
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.
ChangesThat are good news, thanks for your work on Prosody!
Looks like both SQLite3 and Postgres has CREATE TABLE / INDEX IF NOT EXISTS, but MySQL is missing CREATE INDEX IF NOT EXISTS.
SQL error on startupTable creation query fails when tables already existTable creation query fails when tables already existSQL table creation query fails when tables already existMilestone-0.10Fixed in https://hg.prosody.im/trunk/rev/7dd0dddd8e02 except for creating indices in MySQL which will still log the error.
Changes