Bug #1170
Cannot migrate 0.8.0 to PostgreSQL
0%
Description
I tried to migrate my SQLite to PostgreSQL and it failed
[miohtama@lakka][21:42]
[~]% ./quasselcore-static-0.8.0 --select-backend=PostgreSQL
2012-05-30 21:43:03 Warning: SslServer: Certificate file /home/users/miohtama/.config/quassel-irc.org/quasselCert.pem does not exist
2012-05-30 21:43:03 Warning: SslServer: Unable to set certificate file
Quassel Core will still work, but cannot provide SSL for client connections.
Please see http://quassel-irc.org/faq/cert to learn how to enable SSL support.
2012-05-30 21:43:03 Warning: SslServer: Certificate file /home/users/miohtama/.config/quassel-irc.org/quasselCert.pem does not exist
("QPSQL7", "QPSQL", "QSQLITE")
2012-05-30 21:43:03 Info: SQLite Storage Backend is ready. Quassel Schema Version: 17
("QPSQL7", "QPSQL", "QSQLITE")
Default values are in brackets
Username (quassel): xxxx
Password: channel 3: open failed: connect failed: Connection refused
Hostname (localhost): db1.xxx.fi
Port (5432):
Database (quassel): xxx
Storage Schema is missing!
NOTICE: CREATE TABLE will create implicit sequence "quasseluser_userid_seq" for serial column "quasseluser.userid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "quasseluser_pkey" for table "quasseluser"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "quasseluser_username_key" for table "quasseluser"
NOTICE: CREATE TABLE will create implicit sequence "sender_senderid_seq" for serial column "sender.senderid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "sender_pkey" for table "sender"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "sender_sender_key" for table "sender"
NOTICE: CREATE TABLE will create implicit sequence "identity_identityid_seq" for serial column "identity.identityid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "identity_pkey" for table "identity"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "identity_userid_identityname_key" for table "identity"
NOTICE: CREATE TABLE will create implicit sequence "identity_nick_nickid_seq" for serial column "identity_nick.nickid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "identity_nick_pkey" for table "identity_nick"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "identity_nick_identityid_nick_key" for table "identity_nick"
NOTICE: CREATE TABLE will create implicit sequence "network_networkid_seq" for serial column "network.networkid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "network_pkey" for table "network"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "network_userid_networkname_key" for table "network"
NOTICE: CREATE TABLE will create implicit sequence "buffer_bufferid_seq" for serial column "buffer.bufferid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "buffer_pkey" for table "buffer"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "buffer_userid_networkid_buffercname_key" for table "buffer"
NOTICE: CREATE TABLE will create implicit sequence "backlog_messageid_seq" for serial column "backlog.messageid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "backlog_pkey" for table "backlog"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "coreinfo_pkey" for table "coreinfo"
NOTICE: CREATE TABLE will create implicit sequence "ircserver_serverid_seq" for serial column "ircserver.serverid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ircserver_pkey" for table "ircserver"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_setting_pkey" for table "user_setting"
2012-05-30 21:43:26 Info: PostgreSQL Storage Backend is ready. Quassel Schema Version: 16
Switched backend to: PostgreSQL
Migrating Storage backend SQLite to PostgreSQL...
Transferring QuasselUser...
Done.
Transferring Identity...
Done.
Transferring IdentityNick...
Done.
Transferring Network...
Done.
Transferring Buffer...
Migration Failed!
AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Buffer!
WriterError:
executed Query:
INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastseenmsgid, markerlinemsgid, key, joined)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
bound Values:
0 : 22
1 : 1
2 : 0
3 : 2
4 : #xxx
5 : #xxx
6 : 2
7 : 0
8 : 0
9 :
10 : true
Error Number: -1
Error Message: "ERROR: insert or update on table "buffer" violates foreign key constraint "buffer_networkid_fkey"
DETAIL: Key (networkid)=(2) is not present in table "network".
QPSQL: Unable to create query"
Possible causes of failure:
- Database is not fresh, but has had prior Quassel installation before. I dropped all tables before trying to migrate, but is there something else to drop
- The data is really old from 0.6 version and maybe below. It might not be sane in modern standards.
History
#1 Updated by dxbi about 13 years ago
Had the same problem on a new psql database. Turned out to be a single message from an abandoned buffer:
sqlite> select networkid from buffer where networkid not in (select networkid from network group by networkid); networkid 8 sqlite> select * from buffer where networkid=8; bufferid|userid|groupid|networkid|buffername|buffercname|buffertype|lastseenmsgid|key|joined|markerlinemsgid 187|1||8|||1|0||0|0 sqlite> select * from backlog where bufferid=187; messageid|time|bufferid|type|flags|senderid|message 1417052|1289419500|187|1024|0|1|Disconnecting. (http://quassel-irc.org - Chat comfortably. Anywhere.)
Deleting the offending rows fixed the issue. Wouldn't it make sense to do this automatically? Buffers connected to deleted networks can't be accessed anyway, right?
sqlite> delete from backlog where bufferid=187; sqlite> delete from buffer where networkid=8;
Also, after the failed migration attempt, I had to restore quasselCore.conf from backup because the storage backend had already been updated and the migration would always fail with
Switched backend to: PostgreSQL No currently active backend. Skipping migration. New backend does not support migration: PostgreSQL Add a new user: Username:
#2 Updated by Bombe about 11 years ago
It is also possible to simply switch back to the SQLite backend using
quasselcore --select-backend=sqlite
As the sqlite database already exists nothing is changed except for the configuration.