Modify

Opened 3 years ago

Closed 21 months ago

#9079 closed defect (fixed)

PostgreSQL: Database error when creating new user with attributes

Reported by: olistudent Owned by: hasienda
Priority: normal Component: AccountManagerPlugin
Severity: normal Keywords: database PostgreSQL IntegrityError
Cc: Trac Release: 0.12

Description

Hi.

We are running trac-0.12.3 with postgres db. Since one of the last updates of account manager plugin users cause this error message when creating a new account:

2011-08-10 08:33:05,339 Trac[api] INFO: Created new user: foobar
2011-08-10 08:33:05,370 Trac[main] ERROR: Internal Server Error:.
Traceback (most recent call last):
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/web/main.py", line 522, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/web/main.py", line 243, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 439, in process_request
    _create_user(req, self.env)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 154, in _create_user
    set_user_attribute(env, username, attribute, value)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 581, in set_user_attribute
    """, (username, attribute, value))
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: insert or update on table "session_attribute" violates foreign key constraint "fk_session_sid"
DETAIL:  Key (sid)=(foobar) is not present in table "session".
IntegrityError: insert or update on table "session_attribute" violates foreign key constraint "fk_session_sid"
DETAIL:  Key (sid)=(foobar) is not present in table "session".

This happens only when one of the additional fields (name, email) is filled out. Otherwise everything ist fine. Despite the error message the user is created.

Do you have an idea what is going on here?

Regards
Oliver

p.s. Thank you for your great work on this plugin.

Attachments (2)

temp.patch (872 bytes) - added by hasienda 3 years ago.
diff between r10585 and r10519 (reversed)
debug.patch (1.5 KB) - added by hasienda 3 years ago.
debug output and more for testing as requested

Download all attachments as: .zip

Change History (16)

comment:1 in reply to: ↑ description Changed 3 years ago by hasienda

  • Keywords database PostgreSQL IntegrityError added
  • Status changed from new to assigned
  • Summary changed from Database error when creating a new user to PostgreSQL: Database error when creating new user with attributes

Replying to olistudent:

We are running trac-0.12.3 with postgres db. Since one of the last updates of account manager plugin users cause this error message when creating a new account:
(snip)
This happens only when one of the additional fields (name, email) is filled out. Otherwise everything ist fine. Despite the error message the user is created.

Do you have an idea what is going on here?

Yes I do, this fit's perfectly with the changes in [10520], as you may see yourself from the commit message there.

I wasn't aware of the use of sid column from Trac db table session as a db table key, since this isn't the case with SQLite backend. Even more I meant adding a fake session is obsolete, since I tested and it's created on first login anyway. So I left it out starting with aforementioned changeset, and now - consequently - your PostgreSQL misses the sid = foreign key (obviously required from your db schema) when trying to insert any user attributes (to session_attribute) on registration time.

Password is still set, so bare user credentials are "created". After first login the expected sid gets added by Trac core to db table session, and everything is fine then, you see?

So now I understand the need and will revert that change ASAP.

And of course you're welcome regarding your appreciation of my maintenance work. Great to have followers/testers/users like you, that help to improve by meaningful reports. So thank you too.

comment:2 Changed 3 years ago by hasienda

Beware: User attributes will be set againg after reverting changeset [10520], but will still not be shown before first login in the users (accounts) admin page. This is another issue, I'm already aware of. See another comment at #9051 and follow there, if you care.

comment:3 Changed 3 years ago by hasienda

I meant comment 5 for #9051, sorry

comment:4 Changed 3 years ago by hasienda

(In [10585]) AccountManagerPlugin: Almost revert changeset [10520], refs #9079.

Continue to add a fake user session to enable use of sid column in Trac
db table session as foreign key as required by some db schemata (not SQLite).
This re-enables setting user attributes to Trac db table session_attribute
on registration time, i.e. for PostgreSQL compatibility. But beware,
the session added on registration is no longer marked as 'authenticated'.

comment:5 Changed 3 years ago by olistudent

The error still occurs. Can you please post 2-3 lines of debugging code to add? So we can see what is going on in changes from r10585.

Changed 3 years ago by hasienda

diff between r10585 and r10519 (reversed)

comment:6 Changed 3 years ago by hasienda

1st patch is about making sure, the remaining difference is not responsible for the still bad behavior.

Changed 3 years ago by hasienda

debug output and more for testing as requested

comment:7 Changed 3 years ago by hasienda

2nd patch has some debug lines (roughly estimated about as much as you requested ;-). Pay attention to the following commented-out line too:

#db.commit()

I suggest you try to uncomment it for another test, since I figured out the new user entry might just not get committed to the db before the other, transaction (adding attributes - this is not atomic by intention) happens.

While I don't expect all this debug stuff to resolve it magically, it should at least push the matter bit further. Thank you very much in advance for testing.

comment:8 follow-up: Changed 3 years ago by olistudent

Tested with temp.patch applied, debug.patch applied and uncommented commit line. None of them works. Log is from last version with all patches:

2011-09-03 10:33:34,107 Trac[api] INFO: Created new user: test
2011-09-03 10:33:34,108 Trac[web_ui] WARNING: _create_user: user exists? (0L,)
2011-09-03 10:33:34,120 Trac[main] ERROR: Internal Server Error:.
Traceback (most recent call last):
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/web/main.py", line 522, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/web/main.py", line 243, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 476, in process_request
    _create_user(req, self.env)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 191, in _create_user
    set_user_attribute(env, username, attribute, value)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 583, in set_user_attribute
    """, (username, attribute, value))
  File "/usr/local/lib/python2.6/dist-packages/Trac-0.12.3dev_r10719-py2.6.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
IntegrityError: insert or update on table "session_attribute" violates foreign key constraint "fk_session_sid"
DETAIL:  Key (sid)=(test) is not present in table "session".
IntegrityError: insert or update on table "session_attribute" violates foreign key constraint "fk_session_sid"
DETAIL:  Key (sid)=(test) is not present in table "session".

comment:9 in reply to: ↑ 8 Changed 3 years ago by hasienda

Replying to olistudent:

Tested with temp.patch applied, debug.patch applied and uncommented commit line. None of them works. Log is from last version with all patches: ![...]

Ok, you see it: The user (sid) is not seen in the session table when attempting to add some attributes for it to the session_attribute Trac db table. Why it fails (silently) is still beyond my imagination. To prove, it really is (not) there, is the next step.

I've checked several possibilities ahead, but I'm afraid there's no such thing like a shortcut in sight right now. Further step-by-step approach instead, and I'll provide the next patch to continue with, again purely for debugging purposes.

comment:10 Changed 3 years ago by olistudent

I assume the problem is here: 2011-09-03 10:33:34,108 Trac[web_ui] WARNING: _create_user: user exists? (0L,)

So the fix should be this?

  • acct_mgr/web_ui.py

     
    157157        WHERE   sid=%s 
    158158        """, (username,)) 
    159159    exists = cursor.fetchone() 
    160     if not exists: 
     160    if not exists[0]: 
    161161        cursor.execute(""" 
    162162            INSERT INTO session 
    163163                    (sid,authenticated,last_visit) 

comment:11 Changed 3 years ago by hasienda

Sure, silly me.

The cursor results are always wrapped, even if it's a single item. So your solution is correct. I've checked with SQLite here; fails/works without/with the indexed item expression too. Must be due to not using foreign keys in the db schema, why I've never noticed this before. Bless god we have PostgreSQL. And at least the debug code has been sufficient to detect the flaw. ;-)

comment:12 Changed 3 years ago by hasienda

(In [10660]) AccountManagerPlugin: Fix a test in the _create_user function, refs #9079.

Despite of initial rumor this has never been a PostgreSQL compatibility issue.
Only usage of foreign keys in the db schema has made the bug more visible.
Thanks to Oliver for good suggestion and much patience while testing.

comment:13 Changed 2 years ago by hasienda

(In [11826]) AccountManagerPlugin: Hotfix for pending user-registration issue, refs #9079, #9252, #9843 and #9090.

Now the reversion of [10520] done by [10585] is completed, although there are
many related changes and improvements in-between, so this is not quite the
same code as before.

Taking the chance to update now obsolete configuration examples in README too.

This has already open for much too long, so let's ditch the rather esoteric
considerations of authenticated user entries in Trac db table session
for now and move on.

Big sorry, that this took that much time, and thanks to Peter Stuge for
finally pushing me to it tonight.

comment:14 Changed 21 months ago by hasienda

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

(In [12398]) AccountManagerPlugin: Releasing version 0.4, pushing development to acct_mgr-0.5dev.

Availability of that code as stable release
closes #874, #3459, #4677, #5295, #5691, #6616, #7577, #8076, #8685, #8770, #8791, #8990, #9052, #9079, #9090, #9139, #9246, #9252, #9547, #9618, #9676, #9843, #9852, #9940, #10023, #10028, #10123, #10142, #10204, #10276, #10397, #10412, #10594, #10625 and #10644.

Some more issues have been worked-on, yet without confirmed resolution,
refs #5464 (for JiraToTracIntegration), #8927 and #10134.

And finally there are some issues and enhancement requests showing progress,
but known to require more work to resolve them satisfactorily,
refs #843, #1600, #5964, #8217, #8933.

Thanks to all contributors and followers, that enabled and encouraged a good
portion of this development work.

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.