Bug #1660: SQLite to PostgreSQL migration failure - column length and duplicate key errors. - Quassel IRC - Quassel IRC Issue Tracker

Bug #1660

SQLite to PostgreSQL migration failure - column length and duplicate key errors.

Added by infecticide almost 4 years ago. Updated about 2 months ago.

Status:

New

Priority:

Normal

Assignee:

Category:

Quassel Core

Target version:

Some future release

Start date:

02/01/2021

Due date:

% Done:

Estimated time:

Version:

0.13.0

OS:

Any

Description

Followed instructions from: https://bugs.quassel-irc.org/projects/1/wiki/PostgreSQL

I have vacuumed and dumped / reimported the SQLite DB as troubleshooting with the same results.

PostGRES Version:

$ psql -V
psql (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)

Quasselcore Version:

$ quasselcore -v
quassel v0.13.1 (dist-3778a12)

Error:

$ quasselcore d --configdir=/var/lib/quassel --select-backend=
2021-01-31 13:02:24 [Info ] SQLite storage backend is ready. Schema version: 31
2021-01-31 13:02:24 [Info ] Database authenticator is ready.
Default values are in brackets
Username [quassel]: quasselcore
Password []:
Hostname [localhost]: 50m-postgres-001.tuxsteve.net
Port [5432]: 5432
Database [quassel]: quasselcore
2021-01-31 13:02:52 [Error] unhandled Error in QSqlQuery!
2021-01-31 13:02:52 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:52 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: 42601
2021-01-31 13:02:52 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2021-01-31 13:02:52 [Error] Driver Message: QPSQL: Unable to create query
2021-01-31 13:02:52 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
2021-01-31 13:02:52 [Error] unhandled Error in QSqlQuery!
2021-01-31 13:02:52 [Error] last Query:
SELECT MAX FROM coreinfo
2021-01-31 13:02:52 [Error] executed Query:
SELECT MAX FROM coreinfo
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: 42601
2021-01-31 13:02:52 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2021-01-31 13:02:52 [Error] Driver Message: QPSQL: Unable to create query
2021-01-31 13:02:52 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
2021-01-31 13:02:52 [Error] Storage Schema is missing!
2021-01-31 13:02:52 [Error] last Query:
CREATE TABLE quasseluser (
userid serial NOT NULL PRIMARY KEY,
username varchar(64) UNIQUE NOT NULL,
password TEXT NOT NULL,
hashversion integer NOT NULL DEFAULT 0,
authenticator varchar(64) NOT NULL DEFAULT 'Database'
)
2021-01-31 13:02:52 [Error] executed Query:
CREATE TABLE quasseluser (
userid serial NOT NULL PRIMARY KEY,
username varchar(64) UNIQUE NOT NULL,
password TEXT NOT NULL,
hashversion integer NOT NULL DEFAULT 0,
authenticator varchar(64) NOT NULL DEFAULT 'Database'
)
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: -1
2021-01-31 13:02:52 [Error] Error Message:
2021-01-31 13:02:52 [Error] Driver Message:
2021-01-31 13:02:52 [Error] DB Message:
2021-01-31 13:02:52 [Error] last Query:
CREATE TABLE sender ( - THE SENDER OF IRC MESSAGES
senderid bigserial NOT NULL PRIMARY KEY,
sender varchar(128) NOT NULL,
realname TEXT,
avatarurl TEXT
);
2021-01-31 13:02:52 [Error] executed Query:
CREATE TABLE sender ( -- THE SENDER OF IRC MESSAGES
senderid bigserial NOT NULL PRIMARY KEY,
sender varchar(128) NOT NULL,
realname TEXT,
avatarurl TEXT
);
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: 1
2021-01-31 13:02:52 [Error] Error Message:
2021-01-31 13:02:52 [Error] Driver Message:
2021-01-31 13:02:52 [Error] DB Message:
2021-01-31 13:02:52 [Error] last Query:
CREATE TABLE identity (
identityid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
identityname varchar(64) NOT NULL,
realname varchar(128) NOT NULL,
awaynick varchar(64),
awaynickenabled boolean NOT NULL DEFAULT FALSE,
awayreason varchar(256),
awayreasonenabled boolean NOT NULL DEFAULT FALSE,
autoawayenabled boolean NOT NULL DEFAULT FALSE,
autoawaytime integer NOT NULL,
autoawayreason varchar(256),
autoawayreasonenabled boolean NOT NULL DEFAULT FALSE,
detachawayenabled boolean NOT NULL DEFAULT FALSE,
detachawayreason varchar(256),
detachawayreasonenabled boolean NOT NULL DEFAULT FALSE,
ident varchar(64),
kickreason varchar(256),
partreason varchar(256),
quitreason varchar(256),
sslcert bytea,
sslkey bytea,
UNIQUE (userid, identityname)
)
2021-01-31 13:02:52 [Error] executed Query:
CREATE TABLE identity (
identityid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
identityname varchar(64) NOT NULL,
realname varchar(128) NOT NULL,
awaynick varchar(64),
awaynickenabled boolean NOT NULL DEFAULT FALSE,
awayreason varchar(256),
awayreasonenabled boolean NOT NULL DEFAULT FALSE,
autoawayenabled boolean NOT NULL DEFAULT FALSE,
autoawaytime integer NOT NULL,
autoawayreason varchar(256),
autoawayreasonenabled boolean NOT NULL DEFAULT FALSE,
detachawayenabled boolean NOT NULL DEFAULT FALSE,
detachawayreason varchar(256),
detachawayreasonenabled boolean NOT NULL DEFAULT FALSE,
ident varchar(64),
kickreason varchar(256),
partreason varchar(256),
quitreason varchar(256),
sslcert bytea,
sslkey bytea,
UNIQUE (userid, identityname)
)
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: -1
2021-01-31 13:02:52 [Error] Error Message:
2021-01-31 13:02:52 [Error] Driver Message:
2021-01-31 13:02:52 [Error] DB Message:
2021-01-31 13:02:52 [Error] last Query:
CREATE TABLE identity_nick (
nickid serial PRIMARY KEY,
identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE,
nick varchar(64) NOT NULL,
UNIQUE (identityid, nick)
)
2021-01-31 13:02:52 [Error] executed Query:
CREATE TABLE identity_nick (
nickid serial PRIMARY KEY,
identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE,
nick varchar(64) NOT NULL,
UNIQUE (identityid, nick)
)
2021-01-31 13:02:52 [Error] bound Values:
2021-01-31 13:02:52 [Error] Error Number: -1
2021-01-31 13:02:52 [Error] Error Message:
2021-01-31 13:02:52 [Error] Driver Message:
2021-01-31 13:02:52 [Error] DB Message:
2021-01-31 13:02:53 [Error] last Query:
CREATE TABLE network (
networkid serial NOT NULL PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
networkname varchar(32) NOT NULL,
identityid integer REFERENCES identity (identityid) ON DELETE SET NULL,
encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
servercodec varchar(32),
userandomserver boolean NOT NULL DEFAULT FALSE,
perform TEXT,
useautoidentify boolean NOT NULL DEFAULT FALSE,
autoidentifyservice varchar(128),
autoidentifypassword varchar(128),
usesasl boolean NOT NULL DEFAULT FALSE,
saslaccount varchar(128),
saslpassword varchar(128),
useautoreconnect boolean NOT NULL DEFAULT TRUE,
autoreconnectinterval integer NOT NULL DEFAULT 0,
autoreconnectretries integer NOT NULL DEFAULT 0,
unlimitedconnectretries boolean NOT NULL DEFAULT FALSE,
rejoinchannels boolean NOT NULL DEFAULT FALSE,
connected boolean NOT NULL DEFAULT FALSE,
usermode varchar(32), - user mode to restore
awaymessage varchar(256), -- away message to restore (empty if not away)
attachperform text, -- perform list for on attach
detachperform text, -- perform list for on detach
usecustomessagerate boolean NOT NULL DEFAULT FALSE, -- Custom rate limiting
messagerateburstsize INTEGER NOT NULL DEFAULT 5, -- Maximum messages at once
messageratedelay INTEGER NOT NULL DEFAULT 2200, -- Delay between future messages (milliseconds)
unlimitedmessagerate boolean NOT NULL DEFAULT FALSE, -- Disable rate limits
UNIQUE (userid, networkname)
)
2021-01-31 13:02:53 [Error] executed Query:
CREATE TABLE network (
networkid serial NOT NULL PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
networkname varchar(32) NOT NULL,
identityid integer REFERENCES identity (identityid) ON DELETE SET NULL,
encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
servercodec varchar(32),
userandomserver boolean NOT NULL DEFAULT FALSE,
perform TEXT,
useautoidentify boolean NOT NULL DEFAULT FALSE,
autoidentifyservice varchar(128),
autoidentifypassword varchar(128),
usesasl boolean NOT NULL DEFAULT FALSE,
saslaccount varchar(128),
saslpassword varchar(128),
useautoreconnect boolean NOT NULL DEFAULT TRUE,
autoreconnectinterval integer NOT NULL DEFAULT 0,
autoreconnectretries integer NOT NULL DEFAULT 0,
unlimitedconnectretries boolean NOT NULL DEFAULT FALSE,
rejoinchannels boolean NOT NULL DEFAULT FALSE,
connected boolean NOT NULL DEFAULT FALSE,
usermode varchar(32), -- user mode to restore
awaymessage varchar(256), -- away message to restore (empty if not away)
attachperform text, -- perform list for on attach
detachperform text, -- perform list for on detach
usecustomessagerate boolean NOT NULL DEFAULT FALSE, -- Custom rate limiting
messagerateburstsize INTEGER NOT NULL DEFAULT 5, -- Maximum messages at once
messageratedelay INTEGER NOT NULL DEFAULT 2200, -- Delay between future messages (milliseconds)
unlimitedmessagerate boolean NOT NULL DEFAULT FALSE, -- Disable rate limits
UNIQUE (userid, networkname)
)
2021-01-31 13:02:53 [Error] bound Values:
2021-01-31 13:02:53 [Error] Error Number: 1
2021-01-31 13:02:53 [Error] Error Message:
2021-01-31 13:02:53 [Error] Driver Message:
2021-01-31 13:02:53 [Error] DB Message:
2021-01-31 13:02:53 [Error] last Query:
create TABLE buffer (
bufferid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
groupid integer,
networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
buffername varchar(128) NOT NULL,
buffercname varchar(128) NOT NULL, - CANONICAL BUFFER NAME (lowercase version)
buffertype integer NOT NULL DEFAULT 0,
lastmsgid bigint NOT NULL DEFAULT 0,
lastseenmsgid bigint NOT NULL DEFAULT 0,
markerlinemsgid bigint NOT NULL DEFAULT 0,
bufferactivity integer NOT NULL DEFAULT 0,
highlightcount integer NOT NULL DEFAULT 0,
key varchar(128),
joined boolean NOT NULL DEFAULT FALSE, -- BOOL
cipher TEXT,
UNIQUE,
CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)
)
2021-01-31 13:02:53 [Error] executed Query:
create TABLE buffer (
bufferid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
groupid integer,
networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
buffername varchar(128) NOT NULL,
buffercname varchar(128) NOT NULL, -- CANONICAL BUFFER NAME (lowercase version)
buffertype integer NOT NULL DEFAULT 0,
lastmsgid bigint NOT NULL DEFAULT 0,
lastseenmsgid bigint NOT NULL DEFAULT 0,
markerlinemsgid bigint NOT NULL DEFAULT 0,
bufferactivity integer NOT NULL DEFAULT 0,
highlightcount integer NOT NULL DEFAULT 0,
key varchar(128),
joined boolean NOT NULL DEFAULT FALSE, -- BOOL
cipher TEXT,
UNIQUE,
CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)
)
2021-01-31 13:02:53 [Error] bound Values:
2021-01-31 13:02:53 [Error] Error Number: 1
2021-01-31 13:02:53 [Error] Error Message:
2021-01-31 13:02:53 [Error] Driver Message:
2021-01-31 13:02:53 [Error] DB Message:
2021-01-31 13:02:53 [Error] last Query:
CREATE TABLE backlog (
messageid bigserial PRIMARY KEY,
time timestamp NOT NULL,
bufferid integer NOT NULL REFERENCES buffer (bufferid) ON DELETE CASCADE,
type integer NOT NULL,
flags integer NOT NULL,
senderid bigint NOT NULL REFERENCES sender (senderid) ON DELETE SET NULL,
senderprefixes TEXT,
message TEXT
)
2021-01-31 13:02:53 [Error] executed Query:
CREATE TABLE backlog (
messageid bigserial PRIMARY KEY,
time timestamp NOT NULL,
bufferid integer NOT NULL REFERENCES buffer (bufferid) ON DELETE CASCADE,
type integer NOT NULL,
flags integer NOT NULL,
senderid bigint NOT NULL REFERENCES sender (senderid) ON DELETE SET NULL,
senderprefixes TEXT,
message TEXT
)
2021-01-31 13:02:53 [Error] bound Values:
2021-01-31 13:02:53 [Error] Error Number: -1
2021-01-31 13:02:53 [Error] Error Message:
2021-01-31 13:02:53 [Error] Driver Message:
2021-01-31 13:02:53 [Error] DB Message:
2021-01-31 13:02:53 [Error] last Query:
CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update()
RETURNS trigger
AS $BODY$
BEGIN
UPDATE buffer
SET lastmsgid = new.messageid
WHERE buffer.bufferid = new.bufferid
AND buffer.lastmsgid < new.messageid;
RETURN new;
END
$BODY$
LANGUAGE plpgsql;
2021-01-31 13:02:53 [Error] executed Query:
CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update()
RETURNS trigger
AS $BODY$
BEGIN
UPDATE buffer
SET lastmsgid = new.messageid
WHERE buffer.bufferid = new.bufferid
AND buffer.lastmsgid < new.messageid;
RETURN new;
END
$BODY$
LANGUAGE plpgsql;
2021-01-31 13:02:53 [Error] bound Values:
2021-01-31 13:02:53 [Error] Error Number: -1
2021-01-31 13:02:53 [Error] Error Message:
2021-01-31 13:02:53 [Error] Driver Message:
2021-01-31 13:02:53 [Error] DB Message:
2021-01-31 13:02:53 [Error] last Query:
CREATE TRIGGER backlog_lastmsgid_update_trigger
AFTER INSERT OR UPDATE
ON public.backlog
FOR EACH ROW
EXECUTE PROCEDURE public.backlog_lastmsgid_update();
2021-01-31 13:02:53 [Error] executed Query:
CREATE TRIGGER backlog_lastmsgid_update_trigger
AFTER INSERT OR UPDATE
ON public.backlog
FOR EACH ROW
EXECUTE PROCEDURE public.backlog_lastmsgid_update();
2021-01-31 13:02:53 [Error] bound Values:
2021-01-31 13:02:53 [Error] Error Number: -1
2021-01-31 13:02:53 [Error] Error Message:
2021-01-31 13:02:53 [Error] Driver Message:
2021-01-31 13:02:53 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE TABLE coreinfo (
key TEXT NOT NULL PRIMARY KEY,
value TEXT
)
2021-01-31 13:02:54 [Error] executed Query:
CREATE TABLE coreinfo (
key TEXT NOT NULL PRIMARY KEY,
value TEXT
)
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE TABLE ircserver (
serverid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
hostname varchar(128) NOT NULL,
port integer NOT NULL DEFAULT 6667,
password varchar(64),
ssl boolean NOT NULL DEFAULT FALSE, - bool
sslversion integer NOT NULL DEFAULT 0,
useproxy boolean NOT NULL DEFAULT FALSE, -- bool
proxytype integer NOT NULL DEFAULT 0,
proxyhost varchar(128) NOT NULL DEFAULT 'localhost',
proxyport integer NOT NULL DEFAULT 8080,
proxyuser varchar(64),
proxypass varchar(64),
sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert
)
2021-01-31 13:02:54 [Error] executed Query:
CREATE TABLE ircserver (
serverid serial PRIMARY KEY,
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
hostname varchar(128) NOT NULL,
port integer NOT NULL DEFAULT 6667,
password varchar(64),
ssl boolean NOT NULL DEFAULT FALSE, -- bool
sslversion integer NOT NULL DEFAULT 0,
useproxy boolean NOT NULL DEFAULT FALSE, -- bool
proxytype integer NOT NULL DEFAULT 0,
proxyhost varchar(128) NOT NULL DEFAULT 'localhost',
proxyport integer NOT NULL DEFAULT 8080,
proxyuser varchar(64),
proxypass varchar(64),
sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert
)
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE INDEX backlog_bufferid_idx ON backlog(bufferid, messageid DESC)
2021-01-31 13:02:54 [Error] executed Query:
CREATE INDEX backlog_bufferid_idx ON backlog(bufferid, messageid DESC)
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE TABLE user_setting (
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
settingname TEXT NOT NULL,
settingvalue bytea,
PRIMARY KEY (userid, settingname)
)
2021-01-31 13:02:54 [Error] executed Query:
CREATE TABLE user_setting (
userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
settingname TEXT NOT NULL,
settingvalue bytea,
PRIMARY KEY (userid, settingname)
)
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
ALTER SEQUENCE sender_senderid_seq CACHE 100
2021-01-31 13:02:54 [Error] executed Query:
ALTER SEQUENCE sender_senderid_seq CACHE 100
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
ALTER SEQUENCE backlog_messageid_seq CACHE 100
2021-01-31 13:02:54 [Error] executed Query:
ALTER SEQUENCE backlog_messageid_seq CACHE 100
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
DECLARE
i buffer%rowtype;
BEGIN
FOR i IN SELECT * FROM buffer
LOOP
UPDATE buffer
SET lastmsgid = (
SELECT backlog.messageid
FROM backlog
WHERE backlog.bufferid = i.bufferid
ORDER BY messageid DESC LIMIT 1
)
WHERE buffer.bufferid = i.bufferid;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
2021-01-31 13:02:54 [Error] executed Query:
CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
DECLARE
i buffer%rowtype;
BEGIN
FOR i IN SELECT * FROM buffer
LOOP
UPDATE buffer
SET lastmsgid = (
SELECT backlog.messageid
FROM backlog
WHERE backlog.bufferid = i.bufferid
ORDER BY messageid DESC LIMIT 1
)
WHERE buffer.bufferid = i.bufferid;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE UNIQUE INDEX sender_sender_realname_avatarurl_uindex ON sender(sender, realname, avatarurl);
2021-01-31 13:02:54 [Error] executed Query:
CREATE UNIQUE INDEX sender_sender_realname_avatarurl_uindex ON sender(sender, realname, avatarurl);
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
CREATE TABLE core_state (
key TEXT NOT NULL,
value bytea,
PRIMARY KEY (key)
)
2021-01-31 13:02:54 [Error] executed Query:
CREATE TABLE core_state (
key TEXT NOT NULL,
value bytea,
PRIMARY KEY (key)
)
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)
2021-01-31 13:02:54 [Error] executed Query:
INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)
2021-01-31 13:02:54 [Error] bound Values: :version=29
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-01-31 13:02:54 [Error] bound Values:
2021-01-31 13:02:54 [Error] Error Number: -1
2021-01-31 13:02:54 [Error] Error Message:
2021-01-31 13:02:54 [Error] Driver Message:
2021-01-31 13:02:54 [Error] DB Message:
2021-01-31 13:02:54 [Info ] PostgreSQL storage backend is ready. Schema version: 29
2021-01-31 13:02:54 [Warn ] Switched storage backend to: PostgreSQL
2021-01-31 13:02:54 [Warn ] Migration Failed!
2021-01-31 13:02:54 [Warn ] AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Network!
2021-01-31 13:02:54 [Warn ] WriterError:
2021-01-31 13:02:54 [Warn ] executed Query:
2021-01-31 13:02:54 [Warn ] INSERT INTO network (networkid, userid, networkname, identityid, encodingcodec, decodingcodec,
servercodec, userandomserver, perform, useautoidentify, autoidentifyservice,
autoidentifypassword, useautoreconnect, autoreconnectinterval,
autoreconnectretries, unlimitedconnectretries, rejoinchannels, connected,
usermode, awaymessage, attachperform, detachperform, usesasl, saslaccount,
saslpassword, usecustomessagerate, messagerateburstsize, messageratedelay,
unlimitedmessagerate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-01-31 13:02:54 [Warn ] bound Values:
2021-01-31 13:02:54 [Warn ] 0 : 1
2021-01-31 13:02:54 [Warn ] 1 : 1
2021-01-31 13:02:54 [Warn ] 2 : Metanetwork.ca (irc.tuxsteve.net)
2021-01-31 13:02:54 [Warn ] 3 : 1
2021-01-31 13:02:54 [Warn ] 4 :
2021-01-31 13:02:54 [Warn ] 5 :
2021-01-31 13:02:54 [Warn ] 6 :
2021-01-31 13:02:54 [Warn ] 7 : false
2021-01-31 13:02:54 [Warn ] 8 : /oper username password
2021-01-31 13:02:54 [Warn ] 9 : false
2021-01-31 13:02:54 [Warn ] 10 : NickServ
2021-01-31 13:02:54 [Warn ] 11 :
2021-01-31 13:02:54 [Warn ] 12 : true
2021-01-31 13:02:54 [Warn ] 13 : 10
2021-01-31 13:02:54 [Warn ] 14 : 20
2021-01-31 13:02:54 [Warn ] 15 : true
2021-01-31 13:02:54 [Warn ] 16 : true
2021-01-31 13:02:54 [Warn ] 17 : true
2021-01-31 13:02:54 [Warn ] 18 : -
2021-01-31 13:02:54 [Warn ] 19 :
2021-01-31 13:02:54 [Warn ] 20 :
2021-01-31 13:02:54 [Warn ] 21 :
2021-01-31 13:02:54 [Warn ] 22 : false
2021-01-31 13:02:54 [Warn ] 23 :
2021-01-31 13:02:54 [Warn ] 24 :
2021-01-31 13:02:54 [Warn ] 25 : false
2021-01-31 13:02:54 [Warn ] 26 : 5
2021-01-31 13:02:54 [Warn ] 27 : 2200
2021-01-31 13:02:54 [Warn ] 28 : false
2021-01-31 13:02:54 [Warn ] Error Number: 22001
2021-01-31 13:02:54 [Warn ] Error Message: "ERROR: value too long for type character varying(32)\n(22001) QPSQL: Unable to create query" 
2021-01-31 13:02:54 [Warn ] Unable to migrate storage backend! (No migration writer for PostgreSQL)

It seems to have created at least some of the PostGRE table structure. But it doesn't appear to have migrated any of the data.

quasselcore-postgres-db.sql (24.6 KB) quasselcore-postgres-db.sql

Postgres dump

infecticide, 02/01/2021 11:12 PM

History

#1 Updated by genius3000 almost 4 years ago

Subject changed from SQLite to PostGRE migration failure to SQLite to PostgreSQL migration failure - column length error.
Target version changed from 0.13.1 to Some future release
OS changed from Linux to Any

After a couple of reads over the log, the first 7/8 of it look to be essentially normal. The -1 errors are from running in debug (it creates a bit of spam) and aren't actually errors. The failed query for schemaversion would likely be due to the table not existing in the PostgreSQL database yet, at those points in execution.

The failure is shown here:

2021-01-31 13:02:54 [Warn ] Migration Failed!
2021-01-31 13:02:54 [Warn ] AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Network!
2021-01-31 13:02:54 [Warn ] WriterError:
2021-01-31 13:02:54 [Warn ] executed Query:
2021-01-31 13:02:54 [Warn ] INSERT INTO network (networkid, userid, networkname, identityid, encodingcodec, decodingcodec,
servercodec, userandomserver, perform, useautoidentify, autoidentifyservice,
autoidentifypassword, useautoreconnect, autoreconnectinterval,
autoreconnectretries, unlimitedconnectretries, rejoinchannels, connected,
usermode, awaymessage, attachperform, detachperform, usesasl, saslaccount,
saslpassword, usecustomessagerate, messagerateburstsize, messageratedelay,
unlimitedmessagerate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-01-31 13:02:54 [Warn ] bound Values:
2021-01-31 13:02:54 [Warn ] 0 : 1
2021-01-31 13:02:54 [Warn ] 1 : 1
2021-01-31 13:02:54 [Warn ] 2 : Metanetwork.ca (irc.tuxsteve.net)
... (no need to show the rest again) ...
2021-01-31 13:02:54 [Warn ] Error Number: 22001
2021-01-31 13:02:54 [Warn ] Error Message: "ERROR: value too long for type character varying(32)\n(22001) QPSQL: Unable to create query"

Since SQLite has no limits on text length but the PostgreSQL setup sets the 'networkname' column to 32, you've hit an issue with a 33 character network name. Quick fix for you would be to modify that network name in SQLite to meet the requirements of the PostgreSQL setup.

Obviously, Quassel has a few ways to handle this scenario better:

Be a little more clear/verbose on which column and content caused the issue.
Modify the content to fit the needs, logging and outputting what was modified at the end.
In this case, 32 characters might be a little on the conservative side. While it's usually enough for most 'single word' network names, clearly adding more description to the name will be an issue. It may be worthwhile to bump this limit for future versions.

#2 Updated by infecticide almost 4 years ago

I've edited the network name down to a much smaller name and ran the migration again which prompted another error:

$ quasselcore -d --configdir=/var/lib/quassel --select-backend=PostgreSQL
2021-02-01 18:29:24 [Info ] SQLite storage backend is ready. Schema version: 31 
2021-02-01 18:29:24 [Info ] Database authenticator is ready. 
Default values are in brackets
Username [quassel]: quasselcore
Password []: 
Hostname [localhost]: 50m-postgres-001.tuxsteve.net
Port [5432]: 5432
Database [quassel]: quasselcore
2021-02-01 18:29:49 [Error] unhandled Error in QSqlQuery!
2021-02-01 18:29:49 [Error]                   last Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:49 [Error]               executed Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:49 [Error]                 bound Values: 
2021-02-01 18:29:49 [Error]                 Error Number: 42601
2021-02-01 18:29:49 [Error]                Error Message: ERROR:  syntax error at end of input
LINE 1: EXECUTE 
                ^
(42601) QPSQL: Unable to create query
2021-02-01 18:29:49 [Error]               Driver Message: QPSQL: Unable to create query
2021-02-01 18:29:49 [Error]                   DB Message: ERROR:  syntax error at end of input
LINE 1: EXECUTE 
                ^
(42601)
2021-02-01 18:29:49 [Error] unhandled Error in QSqlQuery!
2021-02-01 18:29:49 [Error]                   last Query:
 SELECT MAX(version) FROM coreinfo
2021-02-01 18:29:49 [Error]               executed Query:
 SELECT MAX(version) FROM coreinfo
2021-02-01 18:29:49 [Error]                 bound Values: 
2021-02-01 18:29:49 [Error]                 Error Number: 42601
2021-02-01 18:29:49 [Error]                Error Message: ERROR:  syntax error at end of input
LINE 1: EXECUTE 
                ^
(42601) QPSQL: Unable to create query
2021-02-01 18:29:49 [Error]               Driver Message: QPSQL: Unable to create query
2021-02-01 18:29:49 [Error]                   DB Message: ERROR:  syntax error at end of input
LINE 1: EXECUTE 
                ^
(42601)
2021-02-01 18:29:49 [Error] Storage Schema is missing!
2021-02-01 18:29:49 [Error]                   last Query:
 CREATE TABLE quasseluser (
       userid serial NOT NULL PRIMARY KEY,
       username varchar(64) UNIQUE NOT NULL,
       password TEXT NOT NULL,
       hashversion integer NOT NULL DEFAULT 0,
       authenticator varchar(64) NOT NULL DEFAULT 'Database'
)
2021-02-01 18:29:49 [Error]               executed Query:
 CREATE TABLE quasseluser (
       userid serial NOT NULL PRIMARY KEY,
       username varchar(64) UNIQUE NOT NULL,
       password TEXT NOT NULL,
       hashversion integer NOT NULL DEFAULT 0,
       authenticator varchar(64) NOT NULL DEFAULT 'Database'
)
2021-02-01 18:29:49 [Error]                 bound Values: 
2021-02-01 18:29:49 [Error]                 Error Number: -1
2021-02-01 18:29:49 [Error]                Error Message:  
2021-02-01 18:29:49 [Error]               Driver Message: 
2021-02-01 18:29:49 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE sender ( -- THE SENDER OF IRC MESSAGES
       senderid bigserial NOT NULL PRIMARY KEY,
       sender varchar(128) NOT NULL,
       realname TEXT,
       avatarurl TEXT
);
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE sender ( -- THE SENDER OF IRC MESSAGES
       senderid bigserial NOT NULL PRIMARY KEY,
       sender varchar(128) NOT NULL,
       realname TEXT,
       avatarurl TEXT
);
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE identity (
       identityid serial PRIMARY KEY,
       userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
       identityname varchar(64) NOT NULL,
       realname varchar(128) NOT NULL,
       awaynick varchar(64),
       awaynickenabled boolean NOT NULL DEFAULT FALSE,
       awayreason varchar(256),
       awayreasonenabled boolean NOT NULL DEFAULT FALSE,
       autoawayenabled boolean NOT NULL DEFAULT FALSE,
       autoawaytime integer NOT NULL,
       autoawayreason varchar(256),
       autoawayreasonenabled boolean NOT NULL DEFAULT FALSE,
       detachawayenabled boolean NOT NULL DEFAULT FALSE,
       detachawayreason varchar(256),
       detachawayreasonenabled boolean NOT NULL DEFAULT FALSE,
       ident varchar(64),
       kickreason varchar(256),
       partreason varchar(256),
       quitreason varchar(256),
       sslcert bytea,
       sslkey bytea,
       UNIQUE (userid, identityname)
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE identity (
       identityid serial PRIMARY KEY,
       userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
       identityname varchar(64) NOT NULL,
       realname varchar(128) NOT NULL,
       awaynick varchar(64),
       awaynickenabled boolean NOT NULL DEFAULT FALSE,
       awayreason varchar(256),
       awayreasonenabled boolean NOT NULL DEFAULT FALSE,
       autoawayenabled boolean NOT NULL DEFAULT FALSE,
       autoawaytime integer NOT NULL,
       autoawayreason varchar(256),
       autoawayreasonenabled boolean NOT NULL DEFAULT FALSE,
       detachawayenabled boolean NOT NULL DEFAULT FALSE,
       detachawayreason varchar(256),
       detachawayreasonenabled boolean NOT NULL DEFAULT FALSE,
       ident varchar(64),
       kickreason varchar(256),
       partreason varchar(256),
       quitreason varchar(256),
       sslcert bytea,
       sslkey bytea,
       UNIQUE (userid, identityname)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE identity_nick (
       nickid serial PRIMARY KEY,
       identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE,
       nick varchar(64) NOT NULL,
       UNIQUE (identityid, nick)
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE identity_nick (
       nickid serial PRIMARY KEY,
       identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE,
       nick varchar(64) NOT NULL,
       UNIQUE (identityid, nick)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE network (
       networkid serial NOT NULL PRIMARY KEY,
       userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
       networkname varchar(32) NOT NULL,
       identityid integer REFERENCES identity (identityid) ON DELETE SET NULL,
       encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
       decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
       servercodec varchar(32),
       userandomserver boolean NOT NULL DEFAULT FALSE,
       perform TEXT,
       useautoidentify boolean NOT NULL DEFAULT FALSE,
       autoidentifyservice varchar(128),
       autoidentifypassword varchar(128),
       usesasl boolean NOT NULL DEFAULT FALSE,
       saslaccount varchar(128),
       saslpassword varchar(128),
       useautoreconnect boolean NOT NULL DEFAULT TRUE,
       autoreconnectinterval integer NOT NULL DEFAULT 0,
       autoreconnectretries integer NOT NULL DEFAULT 0,
       unlimitedconnectretries boolean NOT NULL DEFAULT FALSE,
       rejoinchannels boolean NOT NULL DEFAULT FALSE,
       connected boolean NOT NULL DEFAULT FALSE,
       usermode varchar(32), -- user mode to restore
       awaymessage varchar(256), -- away message to restore (empty if not away)
       attachperform text, -- perform list for on attach
       detachperform text, -- perform list for on detach
       usecustomessagerate boolean NOT NULL DEFAULT FALSE,  -- Custom rate limiting
       messagerateburstsize INTEGER NOT NULL DEFAULT 5,     -- Maximum messages at once
       messageratedelay INTEGER NOT NULL DEFAULT 2200,      -- Delay between future messages (milliseconds)
       unlimitedmessagerate boolean NOT NULL DEFAULT FALSE, -- Disable rate limits
       UNIQUE (userid, networkname)
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE network (
       networkid serial NOT NULL PRIMARY KEY,
       userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
       networkname varchar(32) NOT NULL,
       identityid integer REFERENCES identity (identityid) ON DELETE SET NULL,
       encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
       decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15',
       servercodec varchar(32),
       userandomserver boolean NOT NULL DEFAULT FALSE,
       perform TEXT,
       useautoidentify boolean NOT NULL DEFAULT FALSE,
       autoidentifyservice varchar(128),
       autoidentifypassword varchar(128),
       usesasl boolean NOT NULL DEFAULT FALSE,
       saslaccount varchar(128),
       saslpassword varchar(128),
       useautoreconnect boolean NOT NULL DEFAULT TRUE,
       autoreconnectinterval integer NOT NULL DEFAULT 0,
       autoreconnectretries integer NOT NULL DEFAULT 0,
       unlimitedconnectretries boolean NOT NULL DEFAULT FALSE,
       rejoinchannels boolean NOT NULL DEFAULT FALSE,
       connected boolean NOT NULL DEFAULT FALSE,
       usermode varchar(32), -- user mode to restore
       awaymessage varchar(256), -- away message to restore (empty if not away)
       attachperform text, -- perform list for on attach
       detachperform text, -- perform list for on detach
       usecustomessagerate boolean NOT NULL DEFAULT FALSE,  -- Custom rate limiting
       messagerateburstsize INTEGER NOT NULL DEFAULT 5,     -- Maximum messages at once
       messageratedelay INTEGER NOT NULL DEFAULT 2200,      -- Delay between future messages (milliseconds)
       unlimitedmessagerate boolean NOT NULL DEFAULT FALSE, -- Disable rate limits
       UNIQUE (userid, networkname)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 create TABLE buffer (
        bufferid serial PRIMARY KEY,
        userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
        groupid integer,
        networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
        buffername varchar(128) NOT NULL,
        buffercname varchar(128) NOT NULL, -- CANONICAL BUFFER NAME (lowercase version)
        buffertype integer NOT NULL DEFAULT 0,
        lastmsgid bigint NOT NULL DEFAULT 0,
        lastseenmsgid bigint NOT NULL DEFAULT 0,
        markerlinemsgid bigint NOT NULL DEFAULT 0,
        bufferactivity integer NOT NULL DEFAULT 0,
        highlightcount integer NOT NULL DEFAULT 0,
        key varchar(128),
        joined boolean NOT NULL DEFAULT FALSE, -- BOOL
        cipher TEXT,
        UNIQUE(userid, networkid, buffercname),
        CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)
)
2021-02-01 18:29:50 [Error]               executed Query:
 create TABLE buffer (
        bufferid serial PRIMARY KEY,
        userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
        groupid integer,
        networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
        buffername varchar(128) NOT NULL,
        buffercname varchar(128) NOT NULL, -- CANONICAL BUFFER NAME (lowercase version)
        buffertype integer NOT NULL DEFAULT 0,
        lastmsgid bigint NOT NULL DEFAULT 0,
        lastseenmsgid bigint NOT NULL DEFAULT 0,
        markerlinemsgid bigint NOT NULL DEFAULT 0,
        bufferactivity integer NOT NULL DEFAULT 0,
        highlightcount integer NOT NULL DEFAULT 0,
        key varchar(128),
        joined boolean NOT NULL DEFAULT FALSE, -- BOOL
        cipher TEXT,
        UNIQUE(userid, networkid, buffercname),
        CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE backlog (
        messageid bigserial PRIMARY KEY,
        time timestamp NOT NULL,
        bufferid integer NOT NULL REFERENCES buffer (bufferid) ON DELETE CASCADE,
        type integer NOT NULL,
        flags integer NOT NULL,
        senderid bigint NOT NULL REFERENCES sender (senderid) ON DELETE SET NULL,
        senderprefixes TEXT,
        message TEXT
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE backlog (
        messageid bigserial PRIMARY KEY,
        time timestamp NOT NULL,
        bufferid integer NOT NULL REFERENCES buffer (bufferid) ON DELETE CASCADE,
        type integer NOT NULL,
        flags integer NOT NULL,
        senderid bigint NOT NULL REFERENCES sender (senderid) ON DELETE SET NULL,
        senderprefixes TEXT,
        message TEXT
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update()
RETURNS trigger
AS $BODY$
    BEGIN
        UPDATE buffer
        SET lastmsgid = new.messageid
        WHERE buffer.bufferid = new.bufferid
            AND buffer.lastmsgid < new.messageid;
        RETURN new;
    END
$BODY$
LANGUAGE plpgsql;
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update()
RETURNS trigger
AS $BODY$
    BEGIN
        UPDATE buffer
        SET lastmsgid = new.messageid
        WHERE buffer.bufferid = new.bufferid
            AND buffer.lastmsgid < new.messageid;
        RETURN new;
    END
$BODY$
LANGUAGE plpgsql;
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TRIGGER backlog_lastmsgid_update_trigger
AFTER INSERT OR UPDATE
ON public.backlog
FOR EACH ROW
EXECUTE PROCEDURE public.backlog_lastmsgid_update();
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TRIGGER backlog_lastmsgid_update_trigger
AFTER INSERT OR UPDATE
ON public.backlog
FOR EACH ROW
EXECUTE PROCEDURE public.backlog_lastmsgid_update();
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE coreinfo (
       key TEXT NOT NULL PRIMARY KEY,
       value TEXT
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE coreinfo (
       key TEXT NOT NULL PRIMARY KEY,
       value TEXT
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE ircserver (
    serverid serial PRIMARY KEY,
    userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
    networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
    hostname varchar(128) NOT NULL,
    port integer NOT NULL DEFAULT 6667,
    password varchar(64),
    ssl boolean NOT NULL DEFAULT FALSE, -- bool
    sslversion integer NOT NULL DEFAULT 0,
    useproxy boolean NOT NULL DEFAULT FALSE, -- bool
    proxytype integer NOT NULL DEFAULT 0,
    proxyhost varchar(128) NOT NULL DEFAULT 'localhost',
    proxyport integer NOT NULL DEFAULT 8080,
    proxyuser varchar(64),
    proxypass varchar(64),
    sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE ircserver (
    serverid serial PRIMARY KEY,
    userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
    networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE,
    hostname varchar(128) NOT NULL,
    port integer NOT NULL DEFAULT 6667,
    password varchar(64),
    ssl boolean NOT NULL DEFAULT FALSE, -- bool
    sslversion integer NOT NULL DEFAULT 0,
    useproxy boolean NOT NULL DEFAULT FALSE, -- bool
    proxytype integer NOT NULL DEFAULT 0,
    proxyhost varchar(128) NOT NULL DEFAULT 'localhost',
    proxyport integer NOT NULL DEFAULT 8080,
    proxyuser varchar(64),
    proxypass varchar(64),
    sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE INDEX backlog_bufferid_idx ON backlog(bufferid, messageid DESC)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE INDEX backlog_bufferid_idx ON backlog(bufferid, messageid DESC)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE user_setting (
    userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
    settingname TEXT NOT NULL,
    settingvalue bytea,
    PRIMARY KEY (userid, settingname)
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE user_setting (
    userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE,
    settingname TEXT NOT NULL,
    settingvalue bytea,
    PRIMARY KEY (userid, settingname)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 ALTER SEQUENCE sender_senderid_seq CACHE 100
2021-02-01 18:29:50 [Error]               executed Query:
 ALTER SEQUENCE sender_senderid_seq CACHE 100
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 ALTER SEQUENCE backlog_messageid_seq CACHE 100
2021-02-01 18:29:50 [Error]               executed Query:
 ALTER SEQUENCE backlog_messageid_seq CACHE 100
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
DECLARE
        i buffer%rowtype;
BEGIN
        FOR i IN SELECT * FROM buffer
        LOOP
                UPDATE buffer
                        SET lastmsgid = (
                                SELECT backlog.messageid
                                FROM backlog
                                WHERE backlog.bufferid = i.bufferid
                                ORDER BY messageid DESC LIMIT 1
                        )
                        WHERE buffer.bufferid = i.bufferid;
        END LOOP;
        RETURN;
END
$$ LANGUAGE plpgsql;
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
DECLARE
        i buffer%rowtype;
BEGIN
        FOR i IN SELECT * FROM buffer
        LOOP
                UPDATE buffer
                        SET lastmsgid = (
                                SELECT backlog.messageid
                                FROM backlog
                                WHERE backlog.bufferid = i.bufferid
                                ORDER BY messageid DESC LIMIT 1
                        )
                        WHERE buffer.bufferid = i.bufferid;
        END LOOP;
        RETURN;
END
$$ LANGUAGE plpgsql;
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE UNIQUE INDEX sender_sender_realname_avatarurl_uindex ON sender(sender, realname, avatarurl);
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE UNIQUE INDEX sender_sender_realname_avatarurl_uindex ON sender(sender, realname, avatarurl);
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 CREATE TABLE core_state (
    key TEXT NOT NULL,
    value bytea,
    PRIMARY KEY (key)
)
2021-02-01 18:29:50 [Error]               executed Query:
 CREATE TABLE core_state (
    key TEXT NOT NULL,
    value bytea,
    PRIMARY KEY (key)
)
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)
2021-02-01 18:29:50 [Error]               executed Query:
 INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)
2021-02-01 18:29:50 [Error]                 bound Values: :version=29
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]               executed Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]               executed Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]               executed Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Error]                   last Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]               executed Query:
 SELECT value FROM coreinfo WHERE key = 'schemaversion'
2021-02-01 18:29:50 [Error]                 bound Values: 
2021-02-01 18:29:50 [Error]                 Error Number: -1
2021-02-01 18:29:50 [Error]                Error Message:  
2021-02-01 18:29:50 [Error]               Driver Message: 
2021-02-01 18:29:50 [Error]                   DB Message: 
2021-02-01 18:29:50 [Info ] PostgreSQL storage backend is ready. Schema version: 29 
2021-02-01 18:29:50 [Warn ] Switched storage backend to: PostgreSQL 
2021-02-01 18:29:50 [Warn ] Migration Failed!
2021-02-01 18:29:50 [Warn ] AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Buffer!
2021-02-01 18:29:50 [Warn ] WriterError:
2021-02-01 18:29:50 [Warn ]   executed Query:
2021-02-01 18:29:50 [Warn ] INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, bufferactivity, highlightcount, key, joined, cipher)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-02-01 18:29:50 [Warn ]   bound Values:
2021-02-01 18:29:50 [Warn ] 0 :  57
2021-02-01 18:29:50 [Warn ] 1 :  1
2021-02-01 18:29:50 [Warn ] 2 :  0
2021-02-01 18:29:50 [Warn ] 3 :  1
2021-02-01 18:29:50 [Warn ] 4 :  skloon
2021-02-01 18:29:50 [Warn ] 5 :  skloon
2021-02-01 18:29:50 [Warn ] 6 :  4
2021-02-01 18:29:50 [Warn ] 7 :  528549
2021-02-01 18:29:50 [Warn ] 8 :  528549
2021-02-01 18:29:50 [Warn ] 9 :  528549
2021-02-01 18:29:50 [Warn ] 10 :  0
2021-02-01 18:29:50 [Warn ] 11 :  0
2021-02-01 18:29:50 [Warn ] 12 :  
2021-02-01 18:29:50 [Warn ] 13 :  false
2021-02-01 18:29:50 [Warn ] 14 :  
2021-02-01 18:29:50 [Warn ]   Error Number: 23505
2021-02-01 18:29:50 [Warn ]   Error Message: "ERROR:  duplicate key value violates unique constraint \"buffer_userid_networkid_buffercname_key\"\nDETAIL:  Key (userid, networkid, buffercname)=(1, 1, skloon) already exists.\n(23505) QPSQL: Unable to create query" 
2021-02-01 18:29:50 [Warn ] Unable to migrate storage backend! (No migration writer for PostgreSQL)

#3 Updated by infecticide almost 4 years ago

I merged two "skloon" buffers together and the process completed successfully.

#4 Updated by genius3000 almost 4 years ago

Subject changed from SQLite to PostgreSQL migration failure - column length error. to SQLite to PostgreSQL migration failure - column length and duplicate key errors.

infecticide wrote:

I merged two "skloon" buffers together and the process completed successfully.

Glad to hear you got it to succeed. Seems a bit strange how you'd end up with two "skloon" buffers, with matching user and network ids. I'm not sure if that's a limitation of SQLite or just some odd quirk that happened.

I'll leave this bug report open as you've encountered legit bugs, even being somewhat edge cases.

#5 Updated by jjakob over 2 years ago

The 32-character limit on network names was removed in 9fa6f4200 (all text limits in the database were removed).

Regarding the failed SQLite to PostgreSQL migration, the most likely scenario is that you had two rows in the buffer table that violated the unique index if that index was missing or somehow not working, so if your SQLite db was already in an inconsistent state. That unique index has been present in the quassel code since schemaversion 1 (year 2008). The other possibility is that the migration code tried to copy the table or row over twice.

Also available in: Atom PDF

Project

General

Profile

Quassel IRC

Issues