Posted by rdominy on January 16, 2005 at 12:58am
| Project: | Drupal core |
| Version: | 4.5.2 |
| Component: | postgresql database |
| Category: | bug report |
| Priority: | normal |
| Assigned: | puregin |
| Status: | closed (won't fix) |
Issue Summary
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)
);
Comments
#1
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?
#2
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.
#3
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
#4
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.
#5
FWIW...
alter table sessions alter column uid set default '0';
#6
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
#7
How to reproduce this bug?
#8
Would be nice to apply this for consistency. Needs a new number and isn't critical.
#9
Re-rolled the patch to include new update number.
#10
#11
I'll look at this later
#12
No point fixing a dinosaur.
--
Sammy Spets
Synerger
http://www.synerger.com