Project

General

Profile

Bug #1245

Some senders are ignored due to PostgreSQL constraint errors

Added by Tobu over 10 years ago. Updated 7 months ago.

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 over 10 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 over 10 years ago

  • Status changed from New to Resolved

#4 Updated by Anonymous over 10 years ago

  • Status changed from Resolved to Feedback
  • Target version deleted (0.9.1)

#5 Updated by Anonymous over 10 years ago

  • Status changed from Feedback to Confirmed

#6 Updated by Tobu over 10 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 over 8 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 about 8 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 6 years ago

This seems to have fixed it:

psql quasselcore 
REINDEX INDEX sender_hashidx;

#10 Updated by jjakob over 3 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 over 3 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 almost 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 almost 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.

Also available in: Atom PDF