I just upgraded from 4.5.0 to 4.5.2 and was getting an error that the following line from session.inc was causing a database constraint violation:

db_query("INSERT INTO {sessions} (sid, hostname, timestamp) values('%s', '%s
', %d)", $key, $_SERVER["REMOTE_ADDR"], time());

This appears to have changed from 4.5.1 where UID was passed in. In the PostgreSQL session table, the UID must be not NULL, as shown below:

CREATE TABLE sessions (
uid integer NOT NULL,
sid varchar(32) NOT NULL default '',
hostname varchar(128) NOT NULL default '',
timestamp integer NOT NULL default '0',
session text,
PRIMARY KEY (sid)
);

CommentFileSizeAuthor
#9 pgsql_0.patch1.2 KBpuregin
#6 database.pgsql_8.patch1.11 KBpuregin

Comments

Zed Pobre’s picture

Zed Pobre commented

Upon checking my session table, all of the UIDs were 0. Was this column ever used to contain useful data? I've temporarily removed the NOT NULL constraint from the column to get things working again. Is that safe? Should I have simply set the default to 0 instead?

SubAtomic Toad’s picture

SubAtomic Toad commented

After upgrading from 4.5.1 to 4.5.2 I see this in my Apache2 Logs

[client 192.168.1.254] PHP Warning: pg_query(): Query failed: ERROR: null value in column "uid" violates not-null constraint in G:\\drupal-4.5.2\\includes\\database.pgsql.inc on line 104
[client 192.168.1.254] PHP Fatal error: ERROR: null value in column "uid" violates not-null constraint\nquery: INSERT INTO sessions (sid, hostname, timestamp) values('977697c8c91e064a36991a56c5fe0c38', '192.168.1.254', 1105927978) in G:\\drupal-4.5.2\\includes\\database.pgsql.inc on line 121

I think this is the same as what you are seeing.

JeffOllie’s picture

JeffOllie commented

If you carefully study the following diffs:

(HEAD)
http://cvs.drupal.org/viewcvs/drupal/drupal/includes/session.inc?r1=1.9&...

(DRUPAL-4-5)
http://cvs.drupal.org/viewcvs/drupal/drupal/includes/session.inc?r1=1.8&...

you'll see that the "uid" column of the sessions table should be set to "0" rather than not set at all.

Jeff

Steve Stock’s picture

Steve Stock commented

The root issue is mysql automatically assumes a default for not null columns and postgresql does not; see http://sql-info.de/mysql/gotchas.html#1_1 for details. This wasn't taken into account in database.pgsql (nor updates.inc).

I added default 0 to my sessions.uid column and it works correctly.

Anonymous’s picture

(not verified) commented

FWIW...

alter table sessions alter column uid set default '0';

puregin’s picture

puregin commented
Assigned: Unassigned » puregin
Status: Active » Needs review
StatusFileSize
new database.pgsql_8.patch1.11 KB

Looks like this change has not made it into database.pgsql. I've upped the version to 'cvs'.

The attached patch adds the 'DEFAULT 0' constraint, and provides an update for updates.inc

Cvbge’s picture

Cvbge commented

How to reproduce this bug?

killes@www.drop.org’s picture

killes@www.drop.org commented
Priority: Critical » Normal
Status: Needs review » Needs work

Would be nice to apply this for consistency. Needs a new number and isn't critical.

puregin’s picture

puregin commented
Title: Change to sesssion.inc violates UID database constraint » Change to session.inc violates UID database constraint
StatusFileSize
new pgsql_0.patch1.2 KB

Re-rolled the patch to include new update number.

puregin’s picture

puregin commented
Status: Needs work » Needs review
Cvbge’s picture

Cvbge commented

I'll look at this later

sammys’s picture

sammys commented
Version: » 4.5.2
Status: Needs review » Closed (won't fix)

No point fixing a dinosaur.

--
Sammy Spets
Synerger
http://www.synerger.com