Modify

Opened 15 years ago

Closed 15 years ago

Last modified 15 years ago

#4324 closed defect (invalid)

Sqlite to Postgresql migration invalidates id sequences

Reported by: kasper@… Owned by: Radek Bartoň
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@… 15 years ago.
debug.log
schema_and_content.zip (50.2 KB) - added by kasper@… 15 years ago.
schema and content for Topic and Message tables

Download all attachments as: .zip

Change History (9)

comment:1 Changed 15 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 15 years ago by Radek Bartoň

Status: newassigned

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 15 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 15 years ago by kasper@…

Attachment: debug.log added

debug.log

Changed 15 years ago by kasper@…

Attachment: schema_and_content.zip added

schema and content for Topic and Message tables

comment:4 Changed 15 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 15 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 15 years ago by Radek Bartoň

Resolution: invalid
Status: assignedclosed

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 15 years ago by kasper@…

Keywords: postgresql IntegrityError constraint message_pkey added
Summary: Cannot reply to topic!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.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Radek Bartoň.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


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

 
Note: See TracTickets for help on using tickets.