Modify

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#4324 closed defect (invalid)

Sqlite to Postgresql migration invalidates id sequences

Reported by: kasper@… Owned by: Blackhex
Priority: high Component: DiscussionPlugin
Severity: critical Keywords: postgresql IntegrityError constraint message_pkey
Cc: kasper@… Trac Release: 0.11

Description

I'm experiencing a problem that I can see that should have been fixed before (#1715), but apparently it has not :(

Whenever I try to reply to a forum-topic I get an error like this:

IntegrityError: duplicate key value violates unique constraint "message_pkey"

From trac's log, here's the stack trace:

Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/web/main.py", line 432, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/web/main.py", line 204, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/core.py", line 77, in process_request
    return api.process_discussion(context) + (None,)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 62, in process_discussion
    is_moderator)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 697, in _do_action
    message['id'] or '-1', new_time, new_author, new_body)
  File "/usr/lib/python2.5/site-packages/TracDiscussion-0.6-py2.5.egg/tracdiscussion/api.py", line 1134, in add_message
    context.cursor.execute(sql, (forum, topic, replyto, time, author, body))
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.2.1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: duplicate key value violates unique constraint "message_pkey"

I recently migrated to use Postgresql and I think this is related(?).

Attachments (2)

debug.log (243.3 KB) - added by kasper@… 6 years ago.
debug.log
schema_and_content.zip (50.2 KB) - added by kasper@… 6 years ago.
schema and content for Topic and Message tables

Download all attachments as: .zip

Change History (9)

comment:1 Changed 6 years ago by kasper@…

Oh yes, and I'm using the latest version (I think?) of the DiscussionPlugin, I've installed using:

easy_install http://trac-hacks.org/svn/discussionplugin/0.11/

comment:2 Changed 6 years ago by Blackhex

  • Status changed from new to assigned

Then it would be useful to attach debug log from the event, schema of your database and content of 'topic' and 'message' table. Information about exact Trac and PostgreSQL version may help too. Thanks.

comment:3 Changed 6 years ago by kasper@…

I'm attaching the debug-log. I think this is perhaps what you're looking for?

2008-12-24 11:54:41,075 Trac[api] DEBUG: INSERT INTO message (forum, topic, replyto, time, author, body) VALUES (1, 43, 163, 1230116081, kasper, some message)

Trac version is: Trac-0.11.2.1-py2.5
Postgresql version is: PostgreSQL 8.3.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.2-1ubuntu11) 4.3.2

I'm also attaching topic.sql and message.sql, containing schema and content for the tables.

Changed 6 years ago by kasper@…

debug.log

Changed 6 years ago by kasper@…

schema and content for Topic and Message tables

comment:4 Changed 6 years ago by kasper@…

I've identified the bug myself now actually!

There's a sequence called "message_id_seq" in the Postgresql schema, and this is the sequence that is used to increment the "id"-column. When I migrated the database to use postgresql instead of SQLite then this sequence still had a starting-point of 1 instead of "SELECT MAX(id)+1 FROM message":

CREATE SEQUENCE "PUBLIC".message_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1   <--- !!!!
  CACHE 1;
ALTER TABLE "PUBLIC".message_id_seq OWNER TO tracuser;

After changing the START attribute it works fine, but I'm not sure if this is something that could be fixed for future migrations?

I used the SqliteToPgScript script for the migration and what's weird is, that the other sequences (such as "ticket_id_seq") have a correct starting-point.

comment:5 Changed 6 years ago by kasper@…

Here's some Postgresql SQL to fix the sequences:

forum_group_id_seq:

SELECT setval('"PUBLIC".forum_group_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".forum_group), true);

forum_id_seq:

SELECT setval('"PUBLIC".forum_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".forum), true);

message_id_seq:

SELECT setval('"PUBLIC".message_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".message), true);

comment:6 Changed 6 years ago by Blackhex

  • Resolution set to invalid
  • Status changed from assigned to closed

Great thanks, it's becase SqliteToPgScript migrate only Trac internal tables. There is nothing I can to about it in plugin, so I'm closing it.

comment:7 Changed 6 years ago by kasper@…

  • Keywords postgresql IntegrityError constraint message_pkey added
  • Summary changed from Cannot reply to topic! to Sqlite to Postgresql migration invalidates id sequences

Uh, forgot the topic_id_seq sequence:

SELECT setval('"PUBLIC".topic_id_seq', (SELECT MAX(id)+1 FROM "PUBLIC".topic), true);

I agree, I will tell the script guys about the problem and maybe they will come up with something.

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.