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 12 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 10 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.