Unable to delete post: error SQL null value in column "log_id"

[sebastien maye]

Hi,

I'm facing a problem when i try to delete a posts with an admin user. Jform send me the following error message:
org.postgresql.util.PSQLException: ERROR: null value in column "log_id" violates not-null constraint
Détail : Failing row contains (null, 13, dd, réponse de test, 2013-09-26 16:57:06.129, 1, 49485, 0, 69).
I'm using Jforum 2.3.3 with Postgres 9 and all i know is that the fisrt field, which is null, is the result of a sequence "jforum_categories_seq".
The sequence works fine, the number returned is correct, so I can't figure how the insert in the "jforum_moderation_log" contains a null value.

Can somebody helps me ? Thanks in advance !

Smile

[Jeanne Boyarsky]

Sebastien,
Welcome to CodeRanch!

log_id is the primary key for that table and set using a sequence (or the like depending on your database). It shouldn't be possible for it to be null. Is it possible you are running a database type set that doesn't match your actual database?

[sebastien maye]

Thanks for the warm welcome and the quick answer !

The thing is that all the others features, and most of them use sequence, work fine: create posts, users, replies...
At first i though about a data problem cause the content of the database is the result of a migration from an Oracle Database, but i can't see how it could be related to my problem.
Could you be a bit more specific about the "type set" ? Is there some sort of incompatibility between Postgres and Jforum ?

thanks again.

Smile

[Jeanne Boyarsky]

I was thinking of the following lines in SystemGlobals.properties. In particular, I was wondering if maybe it didn't match your database name. We are using postgres so there certainly isn't a fundamental incompatibility!

# Database type to use
database.driver.name = postgresql

Data migration does raise a good point. Do you have any nulls in the log_id field now? And can you double check the database has the sequence set on the jforum_moderation_log table. I'm wondering if the key or constraint got dropped somehow.

[sebastien maye]

I am double checking the facts. Here is some more informations:
Jforum's version: "Powered by JForum 2.3.3"
Postgres' version:

> select version();
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit

The content of the SystemGlobal.properties

# Database type to use
database.driver.name = postgresql

The content of the jforum_moderation_log table, in which there is no null value in the column "log_id", the constraint is well inforced (CONSTRAINT jforum_moderation_log_pkey PRIMARY KEY (log_id)).

> select * from jforum_moderation_log order by log_id desc limit 10;
2091;49;vieux;Ce guide sorti par le ministère date de mai 2012 et permet de faire le point sur
        le vocabulaire spécifique du monde des déchets.;2012-08-24 15:33:01;1;5208;0;49
2090;49;VIEUX;Ce petit guide commercial de la société PELLET donne des infos intéressantes
        sur les détails de mise en accessibilité des sanitaires et salles d'eau.;2012-08-24 15:31:34;1;5210;0;21

The sequence which working fine:

CREATE SEQUENCE jforum_moderation_log_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 2092
  CACHE 1;
ALTER TABLE jforum_moderation_log_seq
  OWNER TO postgres;

> select nextval('jforum_moderation_log_seq');
2093

I was wondering, can it be a problem caused by the jdbc drivers ? We're using in the glassfish's lib directory "postgresql-9.1-902.jdbc4.jar".
Also, i deleted with SQL requests, thousands of anonymous posts from bots, can i broke something doing so ?

Gratefully,

Smile

[Jaikiran Pai]

You can do a quick check on the DB side. Just fire a valid insert query to that table from an SQL editor and see if the column gets an auto generated value from the sequence. If that works, then it's something on the JForum integration side that's wrong.

[Ulf Dittmer]

Has this ever worked, or is it a new phenomenon?

[sebastien maye]

Thanks all for your trails !
After trying to insert into this table, I received:

ERROR:  null value in column "log_id" violates not-null constraint

The table jforum_moderation_log is defined as follow in our database:

CREATE TABLE jforum_moderation_log
(
  log_id integer NOT NULL,
  user_id integer,
  log_description text,
  log_original_message text,
  log_date timestamp without time zone,
  log_type integer,
  post_id integer,
  topic_id integer,
  post_user_id integer,
  CONSTRAINT jforum_moderation_log_pkey PRIMARY KEY (log_id)
)

Whereas the update script from 2.1.7 to 2.1.8 states:

CREATE TABLE jforum_moderation_log (
	log_id INT NOT NULL PRIMARY KEY DEFAULT NEXTVAL('jforum_moderation_log_seq'),
	user_id INT NOT NULL,
	log_description TEXT NOT NULL,
	log_original_message TEXT,
	log_date timestamp NOT NULL,
	log_type INT DEFAULT 0,
	post_id INT DEFAULT 0,
	topic_id INT DEFAULT 0,
	post_user_id INT DEFAULT 0
);

Seems like the migration was half done...

Thanks again !!

Smile

[Ulf Dittmer]

Be aware that JForum 2.1.* (the last version by the original developer) is a different beast than JForum 2.3.* (a fork that is hosted at Google Code). While care was taken to maintain compatibility, you may not be able to transfer DBs between the two versions without further work.

[sebastien maye]

Roger.

I'll keep that in mind =)