Skip to main content

Presentation on collectd / Graphite for PLUG

Below is my presentation from April for the Portland Linux/Unix Group (PLUG) Computer Group Advanced Topics Meeting. I wish I could thank the second person who jumped in and help answer some of the questions that I didn't have answers to.

There is a Version 1.0 (my draft), as well as a Version 1.1 (what I presented from) LibreOffice Presentation attached. Please contact me if you have any questions. I'm hoping to add some more Articles on this website when time allows (when does it ever?).

[http://www.tbruce.com/PLUG%20-%20Monitoring%20Presentation%20v1.0.odp] [http://www.tbruce.com/PLUG%20-%20Monitoring%20Presentation%20v1.1.odp]

  • These files need to be moved into a subdirectory. *

PostgreSQL Sequences and Drupal

I was using Drupal and after one of my upgrades, something changed in the database (I wish I'd taken better notes of which upgrade and saved a copy of my backup, but I didn't do that quick enough so I've lost the data). In any case, my database sequences no longer matched up with what the database was expecting.

In fact, I was getting the following error:

* 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.

All my research led me to believe that it was an issue with my sequence not being bound to my column correctly. However, I could see through psql that my column in the table was bound correctly. Finally, tired of debugging on and off over several months, I turned full logging onto my database and saw another error (now, if I'd really looked at the message, I might not have had to do that as I was able to get a clue from this web site: http://www.devissues.com/tag/drupal)

By looking at the sequences and the tables (with the following commands), I discovered that my sequence was reset to a lower value.

tim=# SELECT nextval('node_nid_seq');
 nextval
---------
     150
(1 row)

tim=# SELECT max(nid) FROM node;
 max
-----
 149
(1 row)

tim=# SELECT nextval('node_revisions_vid_seq');
 nextval
---------
     109
(1 row)

tim=# SELECT max(vid) FROM node_revisions;
 max
-----
 144
(1 row)

Looking at the sequence for node_devisions_vid_seq and lookign at the max number of the vid from node_revisions...hey, I've got a mis-match. Resetting the sequence to be 146 solved my problem. Here is the actual SQL:

tim=# ALTER SEQUENCE node_revisions_vid_seq RESTART WITH 146;

And that seems to have fixed my problem for the database.

Change Table Ownership in PostgreSQL

I wanted to fix a problem with all the tables that were owned by a specific user. I looked up the user and found out it was a user with the ID (OID) of 16387.

Then I could use the following command to generate sql to fix the ownership in my database.

select 'ALTER TABLE ' || relname || ' OWNER TO web_user;' from pg_class where relkind = 'r' and relowner = 16387 order by relname;

This generated the following SQL for me (some people might recognize this as a way to generate dynamic SQL):

ALTER TABLE access OWNER TO web_user;
ALTER TABLE accesslog OWNER TO web_user;
ALTER TABLE actions OWNER TO web_user;
...
ALTER TABLE vocabulary OWNER TO web_user;
ALTER TABLE vocabulary_node_types OWNER TO web_user;
ALTER TABLE watchdog OWNER TO web_user;
ALTER TABLE webfm_attach OWNER TO web_user;
ALTER TABLE webfm_file OWNER TO web_user;
ALTER TABLE wysiwyg OWNER TO web_user;
ALTER TABLE wysiwyg_web_user OWNER TO web_user;

I could then run this output in my Postgresql client (psql as one example) to apply these ownership changes to all the tables (notice that my list above is NOT complete). (Just don't forget to be connected to the correct database or it will fail with a message like: ERROR:  relation "url_alias" does not exist.)

Fix Editing a Drupal Web Page

I had another recent challenge with Drupal (again, some other upgrade or change to my web server / database server) where I was no longer able to edit a page after loggging into my Drupal website.

Since I was using PostgreSQL I was able to update a few specific fields in a couple tables when I could no longer update my content on a particular page (node 145). I used pgadmin3 to run this query.

You need to update two different tables to fix the problem with unable to edit a page. Both the node and node_revisions tables need to be updated.

Then run the following code to update the page:

This allowed me to fix it so I could now EDIT the page inside Drupal.

NOTE: This worked for me with Drupal 7. This may not work for you. And any changes you make directly in the database could have other impacts. Validate your data and how Drupal is working (tracing queries, etc) before you directly make changes to your backend database.