I recently upgraded several Drupal sites to 6.2 Some were 5.1, 5.2, and 6 previously. Upon completion of what I thought was a successul upgrade, they all suffered from the same common problem – trying to save a new post resulted in a “The post could not be saved” error. There were also various other database access errors in logs. On one site it was observed that after trying to make a post, Drupal would take it upon itself to take the site offline with the famous “Welcome to your new Drupal website” message. The problem there turned out to be a permission access problem, but only occurred after a failed post was attempted. To repair, go to admin/content/node-settings and select the “rebuild permissions” button. Once the permissions were repaired it was fine – until you made another post and then Drupal repeated the offline cycle and the beginning of page access SQL errors in the log again.
Fortunately there were some hints as to the problem, but the real problem is that they were mostly for MySQL, and I was using Postgres. Here’s my solution to the problem.
First, the most verbose error message I got was on this very site, where it looked like this after trying to post:
* warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint “node_revisions_pkey” in /path/to/drupal-6.2/includes/database.pgsql.inc on line 139.
* user warning: query: INSERT INTO node_revisions (nid, uid, title, body, teaser, log, timestamp, format) VALUES (0, 1, ‘test’, ‘test test test test test test test test test test test vv’, ‘test test test test test test test test test test test test test vv’, ”, 1294612270, 4) in /path/to/drupal-6.2/includes/common.inc on line 3538.
* warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint “node_vid_idx” in path/to/drupal-6.2/includes/database.pgsql.inc on line 139.
* user warning: query: INSERT INTO node (vid, type, language, title, uid, status, created, changed, comment, promote, moderate, sticky, tnid, translate) VALUES (0, ‘blog’, ”, ‘test’, 1, 1, 1294612270, 1294612270, 2, 1, 0, 0, 0, 0) in /path/to/drupal-6.2/includes/common.inc on line 3538.
* warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint “node_comment_statistics_pkey” in /path/to/drupal-6.2/includes/database.pgsql.inc on line 139.
* user warning: query: INSERT INTO node_comment_statistics (nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) VALUES (0, 1294612270, NULL, 1, 0) in /path/to/drupal-6.2/modules/comment/comment.module on line 607.
* The post could not be saved.
If you are using postgres on the command line via the psql client, you can examine all sequences using the command
\ds
You’ll see that both node_nid_seq and node_revisions_vid_seq have been created. If you’ve tried creating a number of new pages or posts and failed, you’ll notice if you use nextval() like:
SELECT nextval('node_nid_seq');
It will be quite a bit advanced from your actual last successful post’s nid, which you can see like this:
SELECT max(nid) FROM node;
However, if you do this:
SELECT nextval('node_revisions_vid_seq');
Make a note of it. Then find your actual last node_revisions max vid like this:
SELECT max(vid) FROM node_revisions;
And you’ll see it is probably only one higher.
At this point, examine the node table like this:
\d node
Table "public.node"
Column | Type | Modifiers
-----------+------------------------+----------------------------------------------------
nid | integer | not null default nextval('node_nid_seq'::regclass)
vid | integer | not null default 0
type | character varying(32) | not null default ''::character varying
uid | integer | not null default 0
status | integer | not null default 1
created | integer | not null default 0
changed | integer | not null default 0
comment | integer | not null default 0
promote | integer | not null default 0
moderate | integer | not null default 0
sticky | integer | not null default 0
mos_id | integer |
language | character varying(12) | not null default ''::character varying
tnid | integer | not null default 0
translate | integer | not null default 0
title | character varying(255) | not null default ''::character varying
And you can correctly see under “Modifiers” that the default value for nid is the next value in the node_nid_seq, which is the equivalent of the more familiar MySQL AUTO_INCREMENT attribute.
Now try the same on node_revisions:
\d node_revisions
Table "public.node_revisions"
Column | Type | Modifiers
-----------+------------------------+----------------------------------------
vid | integer | not null default 0
uid | integer | not null default 0
body | text | not null
teaser | text | not null
log | text | not null
timestamp | integer | not null default 0
format | integer | not null default 0
nid | integer | not null default 0
title | character varying(255) | not null default ''::character varying
And you’ll see that the default value for vid is zero (0). Of course it isn’t auto-incrementing and so you are getting errors trying to write because you already have a vid with the value 0 in it:
SELECT min(vid) FROM node_revisions; min ----- 0 (1 row)
So what is the solution?
ALTER TABLE node_revisions ALTER COLUMN vid SET DEFAULT nextval('node_revisions_vid_seq'::regclass);
Double check to make sure it went through:
\d node_revisions
Table "public.node_revisions"
Column | Type | Modifiers
-----------+------------------------+--------------------------------------------------------------
vid | integer | not null default nextval('node_revisions_vid_seq'::regclass)
uid | integer | not null default 0
body | text | not null
teaser | text | not null
log | text | not null
timestamp | integer | not null default 0
format | integer | not null default 0
nid | integer | not null default 0
title | character varying(255) | not null default ''::character varying
…And it looks good!
We almost switched to WordPress for one site because of this error, but thinking about converting 5 years and 1250 posts from PostgreSQL Drupal to MySQL WordPress didn’t sound like fun. Fortunately the fix was easy, but nailing down the problem took some time first. Hopefully this helped someone else.
