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

RESOLVED FIXED

Status

Cloud Services
Server: Sync
RESOLVED FIXED
5 years ago
5 years ago

People

(Reporter: csimon, Unassigned)

Tracking

Firefox Tracking Flags

(Not tracked)

Details

Attachments

(1 attachment, 1 obsolete attachment)

(Reporter)

Description

5 years ago
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, test@bloodyhell.lan, 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, 'fake@email.com', 0, '')




Expected results:

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

Updated

5 years ago
OS: All → FreeBSD
Hardware: All → x86_64
Created attachment 746660 [details] [diff] [review]
sqluser: don't insert default values for columns that weren't specified
Attachment #746660 - Flags: review?(telliott)
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?
(Reporter)

Comment 3

5 years ago
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 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.
Attachment #746660 - Flags: review?(telliott) → review-
Created attachment 747139 [details] [diff] [review]
sqluser: don't insert default values for columns that weren't specified

Fair; revised patch attached
Attachment #747139 - Flags: review?(telliott)

Updated

5 years ago
Attachment #746660 - Attachment is obsolete: true
Comment on attachment 747139 [details] [diff] [review]
sqluser: don't insert default values for columns that weren't specified

That works.
Attachment #747139 - Flags: review?(telliott) → review+
Committed in http://hg.mozilla.org/services/server-core/rev/7c027bd73dbc
Status: UNCONFIRMED → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.