Serendipity Forums

The extended statistics plugin "serendipity_event_statistics" does not
seem to work properly when using postgresql as DB.
I use Postgresql 8.0.4 as DB backend.
I get the following errors:

Nov 2 23:26:50 atrbg11 postgres[57113]: [3-1] ERROR: relation "serendipity_visitors" already exists
Nov 2 23:26:50 atrbg11 postgres[57113]: [4-1] ERROR: relation "serendipity_refs" already exists

If I check these two tables, they are always empty:

serendipity=> SELECT * FROM serendipity_visitors;
counter_id | sessid | day | time | ref | browser | ip
------------+--------+-----+------+-----+---------+----
(0 rows)

serendipity=> SELECT * FROM serendipity_refs;
id | refs | count
----+------+-------
(0 rows)


I checked the code, and of course it wants to create the tables, if
it does not get at least one line from serendipity_visitors.

It seems the inserts fail, but I could not find the exact
reason where it fails to insert.

It seems somewhere in countVisitor() things could fail,
but I am not sure how to enabled better debugging
(I am no PHP expert). Is there a simple way to print
debug statements to the httpd's error log from PHP?

Cheers,
Daniel

I just committed a change to the plugin which should get it fixed. You can either download the plugin file from the next generate nightly, or fetch it via SVN:

http://svn.berlios.de/viewcvs/serendipi ... tatistics/

Best regards,
Garvin

I just downloaded and tried.
Works great!

Many thanks.

Cheers,
Daniel

i just tried to install 1.1-beta5 and the Statistics plugin with a PostgreSQL database and i get an error creating the tables.

The create table statement contains Integer definitions with screensize values (like: int(4)), which does not work this way. Instead something like integer, bigint or so should be used.

Hi ads!

On which table exactly does it fail? Serendipity usually replaces "int(4)" with "int4" internally. So I'd like to check out the place / exact table where the wrong statement is contained. Maybe it reads "int (4)" there and so the replacement routine does not work...

Best regards,
Garvin

Hello,

the problem in the first step was: i updated from a 1.0.0 version to 1.1-beta5, this worked almost well except the error that the serendipity_visitors_count table does not exist. Ok, i thought, one step back, installed my old backup, updated to 1.0.1, 1.0.2 and then to 1.1-beta5. Same error. So i did a look into the database and how impressive, this table was not there.

I can remember seeing an error during the updates but only for a moment, then the css overlayed the error with something else. I forgot to look at the source, sorry. Then i did a look into the php and found out, that the statistic module is creating/using this table. Deleting and reinstalling of this module did not work, after this i created the table by hand with standard PostgreSQL datatypes.

There is no space between the int and the following ( in the source (the file is: plugins/serendipity_event_statistics/serendipity_event_statistics.php ), but does Serendipity only replace int(4) and int(8)? There are even int(2), int(12) and int(11) fields.

Hi ads!

ah, okay, the dreaded visitors_count table stuff. Sadly the developer of that portion of the plugin is no longer around, but it definitely needs some polishing.

Serendipity only replaces int(11) and int(4), yes. Other integer types are currently not used by plugins...

Best regards,
Garvin

I could provide the other types for PostgreSQL:

int(2): smallint (or int2)
int(4): integer (or int4)
int(8): bigint (or int8)
int(11): bigint (or int8)
int(12): bigint (or int8)

In Mysql this is the screensize, in PostgreSQL this is the used number of bytes. Maybe you could add this as a short workaround in the core.

Just in case other bloggers want to use this fix

I looked for a way to fix "relation "{prefix}visitors_count" does not exist" errors showing up in my blog's postgresql log.

Since the plugin didn't correctly create {prefix}visitors_count table then the easiest was to run (just replace the prefix) the following in postgresql command-line (provided you have access):

CREATE TABLE {PREFIX}visitors_count (
year int4 not null,
month int2 not null,
day int2 not null,
visits int8 not null,
hits int8 not null
);

CREATE INDEX visitortimeb ON {PREFIX}visitors_count(year, month, day);

Hi!

Thanks for bringing this to my attention again! I just fixed the plugin in the recent SVN to use int11 instead of int12, so that it works on pgsql as well.

Best regards,
Garvin