869482 - 'ERROR: null value in column "userid" violates not-null constraint' from postgresql database log when setting up a new firefox sync account.

Status: RESOLVED FIXED

(Cloud Services Graveyard :: Server: Sync, defect)

Description

[csimon]

User Agent: Mozilla/5.0 (X11; Linux x86_64; rv:20.0) Gecko/20100101 Firefox/20.0
Build ID: 20130412164157

Steps to reproduce:

After installing a new sync server (hg clone .../server-full) with the postgresql psycopg2 driver(./bin/pip   
install psycopg2), configuring sync.conf to use a postgresql v9.2 database as explained in the doc http://docs.services.mozilla.com/howtos/run-sync.html and http://blog.davekoelmeyer.co.nz/2011/12/11/set-up-firefox-sync-to-connect-to-a-postgresql-database/ (for the psycopg2 driver).

Os: FreeBSD 9.1-RELEASE-p3 amd64 running PostgreSQL 9.2.4


Actual results:

Everytime i try to create a new sync account with firefox the following error appears in the postgresql log :

postgres[35314]: [4-1] ERROR:  null value in column "userid" violates not-null constraint
postgres[35314]: [4-2] DETAIL:  Failing row contains (null, bwxra5ie7yguxpx74ozeb4jmzj7lv75b, {SSCRYPT}QdFhjXccOegya4rGZEfdVLOLR/iraZCHIRH/Lzhp2TC1KJq/EghGEku..., 1, [e-mail], 0, ).
postgres[35314]: [4-3] STATEMENT:  INSERT INTO "user" (userid, username, password, "accountStatus", mail, "mailVerified", "syncNode") VALUES (NULL, 'bwxra5ie7yguxpx74ozeb4jmzj7lv75b', '{SSCRYPT}QdFhjXccOegya4rGZEfdVLOLR/iraZCHIRH/Lzhp2TC1KJq/EghGEkuKRcvKzVAqAhJxq+aivbcxLlqvJnicl1FLZVN3RUww', 1, '[e-mail]', 0, '')




Expected results:

The account creation should have succeeded by adding the user data in the postgresql db.

Comment 1

[Ryan Kelly [:rfkelly]]

Attachment: sqluser: don't insert default values for columns that weren't specified

Comment 2

[Ryan Kelly [:rfkelly]]

I guess this is a bug introduced by the SQLUser changes in Bug 865107, which tries to insert NULL into the auto-increment userid column as an explicit "generate a new id" indicator.  Postgres apparently doesn't like this.

I don't have postgres set up at the moment, can you please try the attached patch (to ./deps/server-core/services/sql/user.py) and let me know if it fixes the problem?

Comment 3

[csimon]

Okay, i did some test and it looks like your patch fixed the account creation issue with postgres.
I made a few accounts and synced them, nothing to report so far, my error logs stay clean.

Thanks again.

Comment 4

[Toby Elliott [:telliott]]

Comment on attachment 746660 [details] [diff] [review]
sqluser: don't insert default values for columns that weren't specified

Fixes the bug, but needs to filter out any additional values in extra_fields. Otherwise hilarity may ensue.

Comment 5

[Ryan Kelly [:rfkelly]]

Attachment: sqluser: don't insert default values for columns that weren't specified

Fair; revised patch attached

Comment 6

[Toby Elliott [:telliott]]

Comment on attachment 747139 [details] [diff] [review]
sqluser: don't insert default values for columns that weren't specified

That works.

Comment 7

[Ryan Kelly [:rfkelly]]

Committed in http://hg.mozilla.org/services/server-core/rev/7c027bd73dbc