1 Topic by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Topic: Double posts and online users

Have done a search of the forum, and the last thread I found relating to this was this one from the start of the year:

http://punbb.org/forums/viewtopic.php?id=15480

However, running 1.2.15 here, (modified), and I've been having the exact smae problem the last couple of days. A user may appear in the online list twice, randomly, and when they do, all their posts are doubled. I just found out the awkward way that deleting the second copy post also deletes the original post. Have checked include/functions.php, and that code in the SVN link from above appears fine and intact.

Any suggestions as to where I should start looking to trace this one?


Thanks,

Matt

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

Did you run the database update script?

3 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Smartys wrote:

Did you run the database update script?

Yup. Initially installed the forums from 1.2.14. Ran the db update script when I updated the scripts to 1.2.15.

MattF's Website

4 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Just as a slight sidetrack thought, is it possible that if the user theoretically manages to somehow login twice within the same second, that they would have two logged entries from the check_cookie function? Must admit, can't quite grasp that function, big_smile but that is the one which sees to logging the user?

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

If you used the database update script, this situation is impossible wink
The database index added to the online table precludes multiple entries for the same user from existing.

6 Reply by MattF (edited by MattF 2007-08-09 21:23)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

You are referring to the db update script in 1.2.15? When 1.2.14 was installed, (that is the version the forum was conceived from), install.php was run, as normal. I can also remember running the db update script when 1.2.15 came out because I saw it mentioned on here about a week after I'd upgraded and hadn't realised there was a db update script that needed running until I saw that thread. Have I still missed something? This is running on a pgsql db, btw, just incase that info is relevant. smile

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

Oh, that is quite relevant. wink
The index was only created for MySQL databases because pgsql should be using transactions tongue

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

Looking at how PostgreSQL transactions work, it appears that the bug is not fixed for PostgreSQL users
Moved to Bug Reports

9 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Cheers. smile Apologies for not mentioning the db type earlier. If it's of any use, the specific version running here is:

PostgreSQL 7.4.16

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

Thanks smile
If you have any suggestions for how to fix it, feel free to make them. I've been looking around and it seems fairly difficult, especially compared to MySQL tongue

11 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Must admit, I'm not exactly great with db's. Can manage the general stuff fine, but I have only limited knowledge in that area. big_smile

MattF's Website

12 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Just a quick question on this one again. smile Could this actually be related to a problem with the version of pgsql I'm running, (with it being an older version), or is the problem likely to be external to pgsql? i.e: should pgsql itself stop this condition occuring if/when it's behaving correctly? Merely asking because I can try updating the db version, (if that might be of any use), to see if the condition still exists.


Thanks again,

Matt

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

No, the problem was that we (the developers) assumed that the allowed levels of isolation for transactions in PostgreSQL would solve the issue by themselves. They don't wink

14 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Ah, right. Cheers. smile I'm useless at this level of db understanding, so just wanted to make sure I wasn't causing unnecessary problems due to versions. smile


Thanks again.

Matt

MattF's Website

15 Reply by Smartys (edited by Smartys 2007-09-05 14:44)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

OK, I may have a solution for you, we'll have to test it out smile
FIND

                // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
                switch ($db_type)
                {
                    case 'mysql':
                    case 'mysqli':
                        $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    default:
                        $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;
                }

REPLACE WITH

                // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
                switch ($db_type)
                {
                    case 'mysql':
                    case 'mysqli':
                        $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    case 'pgsql':
                        $db->query('IF EXISTS(SELECT 1 FROM '.$db->prefix.'online WHERE user_id = '.$pun_user['id'].') THEN UPDATE '.$db->prefix.'online SET logged='.$now.' WHERE user_id='.$pun_user['id'].' ELSE INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].') END IF') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    default:
                        $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;
                }

FIND

        // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
        switch ($db_type)
        {
            case 'mysql':
            case 'mysqli':
                $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            default:
                $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;
        }

REPLACE WITH

        // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
        switch ($db_type)
        {
            case 'mysql':
            case 'mysqli':
                $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            case 'pgsql':
                $db->query('IF EXISTS(SELECT 1 FROM '.$db->prefix.'online WHERE ident = \''.$remote_addr.'\') THEN UPDATE '.$db->prefix.'online SET logged='.time().' WHERE ident=\''.$db->escape($remote_addr).'\' ELSE INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].') END IF') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            default:
                $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;
        }

Edit: Thanks Bekko for pointing out my faulty logic wink

16 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Cheers. I'll update the code later today for testing. Thanks ever so much for putting your time into this problem. smile smile Just one question if I may? Are there any circumstances/conditions that may cause this condition, for testing it with? It hasn't reared it's head for awhile, so wondered if I may be able to instigate the problem in any way?

MattF's Website

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Double posts and online users

Try resetting your IP during a session.

18 Reply by MattF (edited by MattF 2007-09-05 18:37)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Just given it a quick test. It bombs out with the 'Unable to insert user into online list' error message with the pgsql clause added.

This is from the pgsql log:

ERROR:  syntax error at or near "IF" at character 1
STATEMENT:  IF EXISTS(SELECT 1 FROM punbb_online WHERE user_id = 3) UPDATE punbb_online SET logged=1189015769 WHERE user_id=3 ELSE INSERT INTO punbb_online (user_id, ident, logged) VALUES(3, 'Matt', 1189015769)


Edit: Forgot to add, (even though it's probably obvious), that the message only appears once logged in.

MattF's Website

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Double posts and online users

From the pgsql site:

37.7.2.2. IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition evaluates to false.

Examples:

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

20 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Know what you mean. It's somewhat confusing. big_smile I've been having a look, and one doc I saw seems to suggest it follows a command but goes before the condition, (if that's the correct way of phrasing it)?

http://developer.postgresql.org/pgdocs/ … table.html

MattF's Website

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Double posts and online users

My point was that the query was lacking the THEN and END IF; parts. I don't know how pgsql parses queries tho, so can't tell if that's the error.

22 Reply by MattF (edited by MattF 2007-09-05 19:52)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Apologies. Missed that completely. I'm having a diddle on a test db/forum at the mo, so I'll try adapting it with those. Must admit, I'm naff at sql personally. big_smile

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

OK, I've edited my code above, try that

24 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: Double posts and online users

Just given that new code a whirl. Still throwing an error. Pgsql log line is below. Just incase it's needed, btw, the version of pgsql running on this machine is 8.2.4.

ERROR:  syntax error at or near "IF" at character 1
STATEMENT:  IF EXISTS(SELECT 1 FROM punbb_online WHERE user_id = 3) THEN UPDATE punbb_online SET logged=1189031054 WHERE user_id=3 ELSE INSERT INTO punbb_online (user_id, ident, logged) VALUES(3, 'Matt', 1189031054) END IF


Thanks again. smile

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Double posts and online users

OK, ignore me, it appears that the stuff I'm talking about is for stored procedures, not regular usage. wink