Project

General

Profile

Bug #1170

Cannot migrate 0.8.0 to PostgreSQL

Added by miohtama almost 12 years ago. Updated over 9 years ago.

Status:
New
Priority:
Low
Assignee:
-
Category:
-
Target version:
-
Start date:
05/30/2012
Due date:
% Done:

0%

Estimated time:
Version:
0.8-pre
OS:
Any

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 over 11 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 over 9 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.

Also available in: Atom PDF