FUDforum: Bug Reports » Error trying to mark messages read

Home » FUDforum Development » Bug Reports » Error trying to mark messages read

Show: Today's Messages :: Polls :: Message Navigator

Error trying to mark messages read [message #18557]

Thu, 27 May 2004 15:22

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

When clicking "mark all unread messages read" at the bottom of a list of threads within a forum, I get the following error:

Warning: pg_query(): Query failed: ERROR: parser: parse error at or near "SELECT" at character 108 . in /srv/www/forums.caliginous.net/root/index.php on line 141

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /srv/www/forums.caliginous.net/root/index.php on line 47

The URL within the browser at this time is http://forums.caliginous.net/index.php?t=markread&rid=4&S=3aafadef3 5e99aa0a59326e3cb5f5e41&id=4&SQ=2a1e33f4b7b8d20f8a35050e62ce561d. I've attached the output phpinfo phpinfo() on my machine. Using FUDforum 2.6.3 with the "db_li() missing second argument" warning patched out per http://fudforum.org/forum/index.php?t=msg&th=3795&start=0&. (Note that error sounds the same as mine but that user's error message didn't get close to my own.)

This error occurs sporadically, really. Sometimes I can log in and click it and get an error, one time I had to click twice, etc. The same error occurs when clicking "Show [...] Unread Messages" at the top of the interface. Again, it's not consistently reproducible, but you usually only have to click around two or three times before it happens.

When I attempted to troubleshoot this error when I click "Show [...] Unread Messages" I printed $q in q() before the call to pg_query(). I saw several SQL statements apparently concatenated; I'm guessing these are actually separated by null characters, then pg_query() splits on these null characters and sends each statement to the server individually. (What I don't understand is how the statements all got stuffed into $q.) Anyway, I don't know how this code works, nor PHP, so maybe I'm not observing what I think I'm observing. I did slap strace on the postmaster process and found this to be the offending statement:

UPDATE fud26_1_read SET user_id=4, thread_id=id, msg_id=last_post_id,
last_view=1085633859 WHERE user_id=4 SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4

It looks to me like the SQL statements got concatenated together without an intervening null character. After this is received by PostgreSQL, it tosses back "ERROR: parser: parse error at or near \"SELECT\" at character 108".

Thanks in advance, let me know if I can provide any other information.

Attachment: info.zip
(Size: 7.22KB, Downloaded 802 times)

darky
--

Report message to a moderator

Re: Error trying to mark messages read [message #18558 is a reply to message #18557]

Thu, 27 May 2004 15:52

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Check the forum's error log viewer and see what SQL errors it reports. Also, what version of PostgreSQL are you using?

FUDforum Core Developer

[Updated on: Thu, 27 May 2004 15:53]

Report message to a moderator

Re: Error trying to mark messages read [message #18559 is a reply to message #18558]

Thu, 27 May 2004 16:10

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

I've got a whole bunch of these, and the time stamp indicates they're mostly from me testing:

(/srv/www/forums.caliginous.net/root/index.php:141
/srv/www/forums.caliginous.net/root/index.php:1103
/srv/www/forums.caliginous.net/root/theme/default/markread.php:47
/srv/www/forums.caliginous.net/root/index.php:1183
) : ERROR: parser: parse error at or near "SELECT" at character 108
Query: UPDATE fud26_1_read SET user_id=4, thread_id=id, msg_id=last_post_id, last_view=1085666536 WHERE user_id=4 SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4
Server Version:
[Referring Page] http://forums.caliginous.net/

Then I've got some other SQL errors that I'm guessing are unrelated, but here's a selection of them anyway:

(/srv/www/forums.caliginous.net/root/index.php:141
/srv/www/forums.caliginous.net/root/index.php:208
/srv/www/forums.caliginous.net/root/theme/default/usrinfo.php:287
/srv/www/forums.caliginous.net/root/index.php:1183
) : ERROR: parser: parse error at or near ")" at character 128
Query: SELECT count(*) FROM fud26_1_poll p INNER JOIN fud26_1_forum f ON p.forum_id=f.id WHERE p.owner=17 AND f.cat_id>0 AND f.id IN()
Server Version:
[Referring Page] http://forums.caliginous.net/index.php?t=finduser&btn_submit=Find&rid=17&S=11b2f5b29cf9dcbeb055c7b02943393b

(/srv/www/forums.caliginous.net/root/index.php:141
/srv/www/forums.caliginous.net/root/index.php:134
/srv/www/forums.caliginous.net/root/theme/default/usrinfo.php:209
/srv/www/forums.caliginous.net/root/index.php:1183
) : ERROR: parser: parse error at or near ")" at character 144
Query: SELECT f.id, f.name FROM fud26_1_mod mm INNER JOIN fud26_1_forum f ON mm.forum_id=f.id INNER JOIN fud26_1_cat c ON f.cat_id=c.id WHERE f.id IN() AND mm.user_id=4
Server Version:
[Referring Page] http://forums.caliginous.net/index.php?t=online_today&rid=17&S=11b2f5b29cf9dcbeb055c7b02943393b

Those are the three queries that seem to be in the SQL error log. I'm running PostgreSQL 7.3.4 (Fedora Core 1 Linux, postgresql-server-7.3.4-11).

darky
--

Report message to a moderator

Re: Error trying to mark messages read [message #18560 is a reply to message #18557]

Thu, 27 May 2004 16:39

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

Just noticed that I'm not running the latest Fedora errata for PHP and Apache. Let me install all pending errata before anyone kills themselves on what might be a bug in PHP, etc. I'll reply once I've finished the update. (Update includes going from PHP 4.3.4 to 4.3.6.)

darky
--

Report message to a moderator

Re: Error trying to mark messages read [message #18561 is a reply to message #18559]

Thu, 27 May 2004 16:53

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

The sql errors inside usrinfo.php were due to a bug that I've just fixed, here is the patch:
http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=2800

The mark-read problem should be fixed by the following patch:
http://cvs.prohost.org/c/index.cgi/FUDforum/chngview?cn=2801

Apply those patches, rebuild the theme and the query failures should go away.

FUDforum Core Developer

Report message to a moderator

Re: Error trying to mark messages read [message #18562 is a reply to message #18560]

Thu, 27 May 2004 16:53

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

OK, latest errata installed and problem is not fixed: login, go to my first (and only) listed forum, click "mark all unread forum messages read" at the bottom, and get the same error.

Warning: pg_query(): Query failed: ERROR: parser: parse error at or near "SELECT" at character 108 in /srv/www/forums.caliginous.net/root/index.php on line 141

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /srv/www/forums.caliginous.net/root/index.php on line 47

phpinfo() now reporting PHP 4.3.6, Apache 2.0.49 (Fedora). I can repost phpinfo() output if need be.

For fun I fired up Konqueror, which I don't think I've ever used to log into my forums with, and followed exactly the steps given above. I got the same error.

darky
--

Report message to a moderator

Re: Error trying to mark messages read [message #18564 is a reply to message #18561]

Thu, 27 May 2004 17:19

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

Ilia wrote on Thu, 27 May 2004 12:53

Patches applied. Now I get a new error when I perform the same procedure:

Warning: pg_query(): Query failed: ERROR: Cannot insert a duplicate key into unique index fud26_1_read_i_tu in /srv/www/forums.caliginous.net/root/index.php on line 141

Fatal error: SQL Error has occurred, please contact the administrator of the forum and have them review the forum's SQL query log in /srv/www/forums.caliginous.net/root/index.php on line 47

Last message in the SQL error log:

(/srv/www/forums.caliginous.net/root/index.php:141
/srv/www/forums.caliginous.net/root/index.php:1103
/srv/www/forums.caliginous.net/root/theme/default/markread.php:47
/srv/www/forums.caliginous.net/root/index.php:1183
) : ERROR: Cannot insert a duplicate key into unique index fud26_1_read_i_tu
Query: UPDATE fud26_1_read SET user_id=4, thread_id=t.id, msg_id=t.last_post_id, last_view=1085673943 FROM (SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4) t WHERE user_id=4
Server Version:
[Referring Page] http://forums.caliginous.net/

darky
--

Report message to a moderator

Re: Error trying to mark messages read [message #18565 is a reply to message #18564]

Thu, 27 May 2004 17:29

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Does the query work if you change it to this:

UPDATE fud26_1_read SET user_id=4, thread_id=t.id, msg_id=t.last_post_id, last_view=1085673943 FROM (SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4) t WHERE user_id=4 AND thread_id=t.id;

FUDforum Core Developer

Report message to a moderator

Re: Error trying to mark messages read [message #18566 is a reply to message #18565]

Thu, 27 May 2004 17:35

darkness

Messages: 7
Registered: May 2004

Karma: 0

Junior Member

This query seems to work in pgsql:

fcn_fudforums=# UPDATE fud26_1_read SET user_id=4, thread_id=t.id, msg_id=t.last_post_id, last_view=1085673943 FROM (SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4) t WHERE user_id=4;
ERROR:  Cannot insert a duplicate key into unique index fud26_1_read_i_tu
fcn_fudforums=# UPDATE fud26_1_read SET user_id=4, thread_id=t.id, msg_id=t.last_post_id, last_view=1085673943 FROM (SELECT id, last_post_id FROM fud26_1_thread WHERE forum_id=4) t WHERE user_id=4 AND thread_id=t.id;
UPDATE 6

Is this a change I should make in my users.inc.t?

darky
--

Report message to a moderator