Bug #1245
Some senders are ignored due to PostgreSQL constraint errors
Status:
Confirmed
Priority:
Urgent
Assignee:
-
Category:
Quassel Core
Target version:
-
Start date:
10/09/2013
Due date:
% Done:
0%
Estimated time:
Version:
0.9-pre
OS:
Any
Description
Error on the sender table:
2013-10-09 09:58:25 CEST ERROR: duplicate key value violates unique constraint "sender_sender_key"
2013-10-09 09:58:25 CEST DETAIL: Key (sender)=(blah!~blah@blah) already exists.
2013-10-09 09:58:25 CEST STATEMENT: EXECUTE quassel_insert_sender ('blah!~blah@blah')
Error on the backlog table:
2013-10-09 09:58:25 CEST ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey"
2013-10-09 09:58:25 CEST DETAIL: Key (senderid)=(0) is not present in table "sender".
2013-10-09 09:58:25 CEST STATEMENT: EXECUTE quassel_insert_message ('2013-10-9 07:58:25.389', 164, 128, 0, 0, 'Remote host closed the connection')
Sender does have a uniqueness constraint.
Those senders end up silently ignored in the UI and the DB, which can cause confusion and unintended rudeness.
Quassel 0.9.0-0ubuntu1
PostgreSQL 9.3
History
#1 Updated by davidstrauss about 11 years ago
This affects migrations, too.
Transferring Sender...
********Migration Failed!
AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Sender!
WriterError:
executed Query:
INSERT INTO sender (senderid, sender)
VALUES (?, ?)
bound Values:
0 : 8554
1 : \{^_^}!nyuszika7h@trekweb/user/nyuszika7h
Error Number: -1
Error Message: "ERROR: duplicate key value violates unique constraint "sender_sender_key"
DETAIL: Key (sender)=({^_^}!nyuszika7h@trekweb/user/nyuszika7h) already exists.
(23505) QPSQL: Unable to create query"
#2 Updated by Anonymous about 11 years ago
- Status changed from New to Resolved
#3 Updated by Tobu about 11 years ago
Fixed by https://github.com/quassel/quassel/commit/aa1008be162cb27da938cce93ba533f54d228869 (which also fixes #1244), according to Tucos.
#4 Updated by Anonymous about 11 years ago
- Status changed from Resolved to Feedback
- Target version deleted (
0.9.1)
#5 Updated by Anonymous about 11 years ago
- Status changed from Feedback to Confirmed
#6 Updated by Tobu about 11 years ago
Bug still present in 0.9.2:
2013-11-27 12:48:59 UTC ERROR: duplicate key value violates unique constraint "sender_sender_key"
2013-11-27 12:48:59 UTC DETAIL: Key (sender)=(blah!~blah@blah) already exists.
2013-11-27 12:48:59 UTC STATEMENT: EXECUTE quassel_insert_sender ('blah!~blah@blah')
2013-11-27 12:48:59 UTC ERROR: duplicate key value violates unique constraint "sender_sender_key"
2013-11-27 12:48:59 UTC DETAIL: Key (sender)=(blah!~blah@blah) already exists.
2013-11-27 12:48:59 UTC STATEMENT: EXECUTE quassel_insert_sender ('blah!~blah@blah')
2013-11-27 12:48:59 UTC ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey"
2013-11-27 12:48:59 UTC DETAIL: Key (senderid)=(0) is not present in table "sender".
2013-11-27 12:48:59 UTC STATEMENT: EXECUTE quassel_insert_message ('2013-11-27 12:48:59.879', 184, 1, 0, 0, 'blah')
2013-11-27 12:48:59 UTC ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey"
2013-11-27 12:48:59 UTC DETAIL: Key (senderid)=(0) is not present in table "sender".
2013-11-27 12:48:59 UTC STATEMENT: EXECUTE quassel_insert_message ('2013-11-27 12:48:59.879', 184, 1, 0, 0, 'blah')
#7 Updated by undu about 9 years ago
Tobu wrote:
Fixed by https://github.com/quassel/quassel/commit/aa1008be162cb27da938cce93ba533f54d228869 (which also fixes #1244), according to Tucos.
Still present in 0.12.2-3 (archlinux)
Right now it seems to affect mostly my own messages, although some other events cause an error too:
Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: unhandled Error in QSqlQuery! Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: last Query: Nov 04 16:57:13 piripi quasselcore[2018]: EXECUTE quassel_insert_message (TIMESTAMP WITH TIME ZONE '2015-11-04T16:57:13.622Z', 8, 128, 0, 142, 'Connection closed') Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: executed Query: Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: bound Values: Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: Error Number: 23503 Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: Error Message: ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey" Nov 04 16:57:13 piripi quasselcore[2018]: DETAIL: Key (senderid)=(142) is not present in table "sender". Nov 04 16:57:13 piripi quasselcore[2018]: (23503) QPSQL: Unable to create query Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: Driver Message: QPSQL: Unable to create query Nov 04 16:57:13 piripi quasselcore[2018]: 2015-11-04 16:57:13 Error: DB Message: ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey" Nov 04 16:57:13 piripi quasselcore[2018]: DETAIL: Key (senderid)=(142) is not present in table "sender". Nov 04 16:57:13 piripi quasselcore[2018]: (23503)
#8 Updated by Tobu almost 9 years ago
It's a problem with the '!' character.
PostgreSQL 9.5 now logs the exact character with the error.
I suppose it needs to be escaped.
#9 Updated by Tobu over 7 years ago
This seems to have fixed it:
psql quasselcore
REINDEX INDEX sender_hashidx;#10 Updated by jjakob about 4 years ago
I'm getting the same errors after migrating from PostgreSQL 9.5 to 12. I reindexed all indexes except "sender_sender_realname_avatarurl_uindex" which returns an error:
ERROR: could not create unique index "sender_sender_realname_avatarurl_uindex" DETAIL: Key (sender, realname, avatarurl)=(foo!~bar@abcd, foobar, ) is duplicated. CONTEXT: parallel worker
There is no index sender_hashidx
#11 Updated by jjakob about 4 years ago
Some more error logs. I verified that I have standard_conforming_strings = on.
Oct 22 18:21:32 gentoo quasselcore[2274]: SignalProxy::handleInitRequest() received initRequest for unregistered Object: "IrcUser" "1/someuser" Oct 22 18:21:32 gentoo quasselcore[2274]: SignalProxy::handleInitRequest() received initRequest for unregistered Object: "IrcUser" "1/anotheruser" Oct 22 18:21:32 gentoo quasselcore[2274]: SignalProxy::handleInitRequest() received initRequest for unregistered Object: "IrcUser" "1/someuser" Oct 22 18:29:12 gentoo quasselcore[2274]: QSqlQuery::value: not positioned on a valid record Oct 22 18:29:12 gentoo quasselcore[2274]: unhandled Error in QSqlQuery! Oct 22 18:29:12 gentoo quasselcore[2274]: last Query: EXECUTE quassel_insert_message (TIMESTAMP WITH TIME ZONE '2020-10-22T16:29:12.996Z', 14, 1, 0, 0, NULL, 'this message is replaced with a placeholder for the purpuse of this bug') Oct 22 18:29:12 gentoo quasselcore[2274]: executed Query: Oct 22 18:29:12 gentoo quasselcore[2274]: bound Values: Oct 22 18:29:12 gentoo quasselcore[2274]: Error Number: 23503 Oct 22 18:29:12 gentoo quasselcore[2274]: Error Message: ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey" DETAIL: Key (senderid)=(0) is not present in table "sender". (23503) QPSQL: Unable to create query Oct 22 18:29:12 gentoo quasselcore[2274]: Driver Message: QPSQL: Unable to create query Oct 22 18:29:12 gentoo quasselcore[2274]: DB Message: ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey" DETAIL: Key (senderid)=(0) is not present in table "sender". (23503)
2020-10-22 18:29:12.997 CEST [2283] ERROR: duplicate key value violates unique constraint "sender_sender_realname_avatarurl_uindex"
2020-10-22 18:29:12.997 CEST [2283] DETAIL: Key (sender, realname, avatarurl)=(abc!~def@xyz, abc, ) already exists.
2020-10-22 18:29:12.997 CEST [2283] STATEMENT: EXECUTE quassel_insert_sender ('abc!~def@xyz', 'abc', '')
2020-10-22 18:29:12.997 CEST [2283] ERROR: duplicate key value violates unique constraint "sender_sender_realname_avatarurl_uindex"
2020-10-22 18:29:12.997 CEST [2283] DETAIL: Key (sender, realname, avatarurl)=(abc!~def@xyz, abc, ) already exists.
2020-10-22 18:29:12.997 CEST [2283] STATEMENT: EXECUTE quassel_insert_sender ('abc!~def@xyz', 'abc', '')
2020-10-22 18:29:12.998 CEST [2283] ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey"
2020-10-22 18:29:12.998 CEST [2283] DETAIL: Key (senderid)=(0) is not present in table "sender".
2020-10-22 18:29:12.998 CEST [2283] STATEMENT: EXECUTE quassel_insert_message (TIMESTAMP WITH TIME ZONE '2020-10-22T16:29:12.996Z', 14, 1, 0, 0, NULL, 'this message is replaced with a placeholder for the purpuse of this bug')
2020-10-22 18:29:12.999 CEST [2283] ERROR: insert or update on table "backlog" violates foreign key constraint "backlog_senderid_fkey"
2020-10-22 18:29:12.999 CEST [2283] DETAIL: Key (senderid)=(0) is not present in table "sender".
2020-10-22 18:29:12.999 CEST [2283] STATEMENT: EXECUTE quassel_insert_message (TIMESTAMP WITH TIME ZONE '2020-10-22T16:29:12.996Z', 14, 1, 0, 0, NULL, 'this message is replaced with a placeholder for the purpuse of this bug')
#12 Updated by jjakob over 2 years ago
When trying to migrate this database that is producing the above errors constantly throughout its lifetime, I get this error:
2022-06-08 22:44:56.731 CEST [23885] ERROR: could not create unique index "sender_sender_realname_avatarurl_uindex" 2022-06-08 22:44:56.731 CEST [23885] DETAIL: Key (sender, realname, avatarurl)=(HashMap!~HashMap@192.0.2.1, HashMap, ) is duplicated. 2022-06-08 22:44:56.731 CEST [23885] STATEMENT: CREATE UNIQUE INDEX sender_sender_realname_avatarurl_uindex ON public.sender USING btree (sender, realname, avatarurl);
#13 Updated by jjakob over 2 years ago
I think there are a couple different bugs mixed together in this issue.
I've found one (rows in sender that violate "sender_sender_realname_avatarurl_uindex") and am almost done fixing it (testing the migration SQL).
It was was caused by https://github.com/quassel/quassel/commit/20f446a492d8e681156423f0dc3637db78c45bae so it's present since 0.13-rc1, not before.
Other errors not related to this index (for example "backlog_senderid_fkey", "initRequest for unregistered Object") may or may not be related, I'll see when I get this one bug sorted.