Tag Archives: drupal

Updating Drupal from 5 to 6 causes “The post could not be saved” – Postgres version fix

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


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;
(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.

PHP 5.2 and Drupal 4.7.4 don’t work together

While doing a software upgrade on a server today I ran into some problems. Apparently Drupal 4.7.4 and PHP 5.2.1 don’t work together.

After upgrading PHP, Apache and APC cache I couldn’t get Drupal to work on all 7 sites on one machine. I could login to any drupal site as the admin and get shown my user page, but when trying to do anything at all as an authenticated user it considered me logged out.

I initially thought APC had something to do with it and began to disable it. That didn’t fix it. On a haunch and after a lot of reading with no answer I decided to install a lesser version of PHP and that fixed it. PHP 5.1.1 was laying around and did the trick. Unfortunately I needed 5.2 to fix a mem leak!

I decided to update the Drupal sites to the latest version first to see if that helps. Lo and behold when I visit the Drupal download page I am greeted with:

PHP 5.2 compatibility is only available from Drupal 4.6.11 / 4.7.5 / 5.x.

That pretty much cinches it. I need PHP 5.2 to fix a memory leak so I’ll take time to upgrade to Drupal 5 now.

Be warned, if you are running Drupal 4.7.4 sites and attempt to upgrade to PHP 5.2 you may be faced with being unable to login or navigate as an authenticated user in Drupal. Downgrade to PHP 5.1, upgrade to > Drupal 4.7.4 and then continue your upgrade to PHP 5.2 and you should be alright.

Apache, Postgres and Drupal on a VPS

I really would prefer to have my own server but sticking a box in colo is expensive. Where I live, getting access to the physical colo space would be nearly impossible too. As a result I run on a VPS. Unfortunately VPS has some horrible limitations depending on who is on the box with you.

Recently I decided to move my personal blog off of b2evolution and stick it on Drupal. Too bad drupal is such a resource hog. Most CMS and blog software is though and it is really hard to find a minimalized, optimized blog software that uses any form of caching. Today, it hit the skids and my patience hit the wall. Argh!

I was converting my personal blog by hand because I only have about 30 entries so it didn’t pay to write a conversion script. Everytime I hit the ‘post’ button in Drupal I wound up with a blank screen, could not connect or worse, the results of any command in SSH terminal window showed a “could not allocate memory”. As a result, I had to do some fast tuning of something because I had to reboot the server after every blog post!

I chose to tackle Apache first because they have an Apache Performance Tuning Guide that helps a bunch. Postgres, well I’m running an old version that I really need to upgrade before I tackle configuration and optimization of it. That’s not a 30 minute job.

VPS, Apache and Low Memory

VPS has a low memory for sure. Even though you can sometimes burst more, in general it is pretty low. The very first thing in the Apache performance tuning guide is tackling memory.

You can, and should, control the MaxClients setting so that your server does not spawn so many children it starts swapping. This procedure for doing this is simple: determine the size of your average Apache process, by looking at your process list via a tool such as top, and divide this into your total available memory, leaving some room for other processes.

Using top and sorting by thread using ‘h’ I am able to see that the average Apache processes is using WAY TOO MUCH memory at 25M a piece – and 10 processes running. I don’t have time to tune the size now so I’ll tune the number of servers using very simple configuration parameters. Since we are using MPM Prefork, the directives can be found in extra/httpd-mpm.conf file under mpm_prefork_module.

Since I am supposed to be guaranteed 256M memory burstable to 1G I’ll optimize to the lower number. 256M / 25M is 10. Not including room for other processes. The current setting is 150!


StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0


StartServers 2
MinSpareServers 2
MaxSpareServers 5
MaxClients 10
MaxRequestsPerChild 0

I also changed it to only start 2 servers, only keep 2 spare servers instead of 5, and only allow 10 clients instead of 150. This will essentially create a queue if anyone is waiting but it shouldn’t dip into swap space and it will save a bunch of memory usage. This will of course be monitored. Once time permits and I am able to minimize the size of each Apache2 process (the other factor) then I will revisit this and likely increase the MaxClients accordingly.

Drupal multisite configuration problems

Drupal multisite setup configuration

I’ve adopted Drupal for a majority of my online activities in the past year. It has enough of the things I like and is much faster and more stable than previous CMS or forum software I’ve used. However, some documentation seems to be lacking. Multisite configuration with Apache using a single Drupal codebase is one area.

I finally decided to try a project in which I would use a common codebase for Drupal across all of my websites. That is, rather than having a directory for each website that has a Drupal installation in it, I thought I would take advantage of Drupal’s multisite functionality by having one drupal installation (codebase) in a central directory and have all the project websites point to it.

Drupal multisite advantages

The advantages of Drupal’s multisite feature are easy to spot.

First, a common codebase means only one codebase to change during an update – even though drupal requires you to update each websites database separately.
Second, using an intermediary cache like APC for PHP means that you can use less server memory caching files across multiple websites because the core files are the same.

Despite this great feature and its advantages, there is much confusion at the Drupal.org website from patrons as to what is the correct way to set this up. The Drupal install file goes into it pretty good, but leaves out one important detail – the web server configuration. Some purists say that is an Apache issue and we should leave that to the Apache mailing list but hey, PHP does a pretty good job of detailing the Apache configuration in their instructions so Drupal could too.

Most people who administer their own websites would follow a ritual in creating a new website. Create a directory for the new website, create the www subdirectory and then configure the Apache DirectoryRoot directive. With a single codebase used by multiple domains and websites you need a different approach.

The missing multisite ‘key’

The most important thing here is to make sure each website’s DocumentRoot in the Apache configuration points to the common drupal directory. This is an unusual configuration for most people but it works well.

The idea is that Drupal receives information from PHP (and Apache) to tell it which website it is supposed to serve. When it determines that, it will retrieve the correct configuration file (database passwords and URL base) from the Drupal ‘sites/’ subdirectory. That’s how it can determine which website to display.

Here is a quick breakdown.

Install Drupal in a common directory. Here I chose /var/lib/drupal-4.7.4


Some people use a symbolic link to point Drupal to the correct distribution. I’ve done this but I suggest not doing it. Instead install your Drupal installation in a single directory titled according to it’s version number. That is, use the same directory structure and names that Drupal gave when you extracted the zip or tarball.

Why? If you have a couple sites and it comes time to upgrade you can run into trouble. It is easy to just recreate a symbolic link pointing ‘drupal/’ to ‘drupal-4.7.4’ but unfortunately that affects ALL your websites instantly. Not good on a production server. If you have 50 websites this means that you have 50 sites using the new codebase and the same 50 websites awaiting your hand to manually update their databases using Drupal’s supplied script. If any of those 50 are accessed during this wait period you’ll be in trouble.

The other disadvantage of the symbolic link comes if you are using third-party supplied modules. You forgot that 5 out of your 50 websites are using a module that isn’t being maintained on the same schedule as the Drupal project and guess what? It breaks your website. I’ve found that frequently a misbehaving module or even an errant ‘files’ path in the Drupal settings will disable all other Drupal modules. Best to avoid this altogether.

You can save yourself from both of these negative scenarios by simply putting Drupal in the supplied directory name and then adjusting your Apache DocumentRoot directive as you update them. It is an extra step but very easy.

The final advantage of not using a symbolic link is that you can hold a stable website. That is, you can have a couple different versions of Drupal on your server used by different websites. Several hosting providers do this with PHP and that is a very good example. If you upgrade to PHP 6 for one project you’ll find it doesn’t work with Drupal so you need to keep an old version of PHP installed for non-compliant websites. With Drupal I’ve found that once in a while a website gets working just right and I don’t want to update it. Or I quit monitoring it. Or it is an informational content only website with no subscriptions allowed. Or a million other reasons. Basically I will ‘freeze’ that website and not allow anymore code updates. Or, say you have a module that you use but the author abandoned it long ago. It won’t work with Drupal 6 for example. Simply freeze the website and only update your other websites with the new Drupal codebase. Keeping a couple distributions around can be handy but it means that you can’t point them to the codebase with a symbolic link.

Unzip the new codebase in parallel to the old.

# tar zxvf drupal-4.7.x.tar.gz

Backup the database for the website you are about to update.
Exact commands vary with which database you are using.

Edit Apache config file to change one website at a time.

# vi extra/httpd-vhosts.conf


DocumentRoot /var/www/example.com/www
ServerName www.example.com


DocumentRoot /var/lib/drupal-4.7.4
ServerName www.example.com

Restart Apache so the config takes effect

# apachectl graceful

Visit the Drupal supplied database update URL for your website.


Watch for errors and check your logs. Visit the website and check the settings to make sure all modules still show up under the admin -> settings menu.

If successful, continue on with the next website on your server.