Recent Topics

MySQL 4.1.21 bug leads to comment ordering problem

Started by on Sep 29, 2006 – Contents updated: Sep 29, 2006

Sep 29, 2006 15:20    

Posted FYI:

One of my blogs suddenly started displaying comments out of order. I'm running 1.8.2. I fixed the problem by changing line 67 of /blogs/inc/MODEL/comments/_commentlist.class.php from

$this->sql = 'SELECT DISTINCT T_comments.*

to

$this->sql = 'SELECT T_comments.*

I don't really use categories, so I assume this won't create problems with duplicate comments appearing. I'm guessing that if you use multiple categories, this fix may not work for you.

The Drupal folks were experiencing similar problems, as reported [url=http://drupal.org/node/78642]here[/url]. The problem seems to be a bug with MySQL 4.1.21, as reported [url=http://bugs.mysql.com/bug.php?id=21456]here[/url].

Sep 29, 2006 15:37

Looking at _commentlist.class.php a bit more closely, it looks to me like in this section

Code

$this->sql = 'SELECT DISTINCT T_comments.*
                  FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';
 
    if( !empty( $p ) )
    {  // Restrict to comments on selected post
      $this->sql .= " WHERE comment_post_ID = $p AND ";
    }
    elseif( $blog > 1 )
    {  // Restrict to viewable posts/cats on current blog
      $this->sql .= "INNER JOIN T_postcats ON post_ID = postcat_post_ID INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = $blog AND ";
    }
    else
    {  // This is blog 1, we don't care, we can include all comments:
      $this->sql .= ' WHERE ';
    }

the only place that the

Code

SELECT DISTINCT
is needed in the second clause (

Code

elseif( $blog >1 )
). If this is the case, should the

Code

SELECT DISTINCT
be pushed into that block?

Oct 10, 2006 20:25

AWESOME FIX!!!! I'm on 0.9.0.10 and I was having the exact same problem with haywire order of comments (that only started happening sometime around sep 7?)... I tried this simple fix here and VOILA! It's all good now!

SO THANK YOU!!!!

Nov 07, 2006 03:55

This seemed to work for me, as well.

I noticed the problem in our production v1.8 (Summer Beta) version and went back to compare in v0.9.1 (DAWN), which I keep around, for posterity. :| I noticed a similar juxtaposition of comments for the same post. Looked at the DB (all okay there). Removed the "distinct" and blammo ... the correct order. :D

Don't yet know if there is any fallout elsewhere, but all preliminary indications are - "This Rocks Dude!"

Thanks!!

Nov 08, 2006 20:43

Yes, it worked in my case as well, but now there are problems with my "recent comments". Every post appears 3-5 times

Nov 08, 2006 20:53

nomad,

Yer right. Same thing here. (Not something I have linked on my front page, but when I typed in the URL Parms ... I noticed the same thing).

Seems like a simple fix would be to put a branch in that function code?

Code

if($disp=='comments') {
old code
} else {
new code (w/out the distinct)
}

(untested, of course, because I need all the comments I can get ... even if they're a tad redundant) :p

Nov 08, 2006 21:18

It may be brutish, but it seems to work for me?

PHP

<?php
 
global $disp;
 
if($disp=='comments'){
        $this->sql 'SELECT DISTINCT T_comments.*
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
        } else {
        
        $this->sql 'SELECT T_comments.*
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
        }
?>

All, of course, in inc/MODEL/comments/_commentlist.class.php file.

Hope this helps.

Nov 08, 2006 22:21

Does it matter where I place the code?

Nov 09, 2006 02:04

Sorry, yes ...

Replace the original code:

PHP

<?php
 
$this->sql 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
 
 
?>

(From which, you MAY have already deleted the "DISTINCT" part).

with the stuff above (repeated here):

PHP

<?php
 
global $disp
 
if($disp=='comments'){ 
        $this->sql 'SELECT DISTINCT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } else { 
         
        $this->sql 'SELECT T_comments.* 
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } 
?>

That should do it for you. :D

Nov 09, 2006 02:19

Thanks but as I still use 0.91 and the syntax has changed, I've received a syntax error. I've tried adjusting it but without result

My original code that I should replace is:

$this->request = "SELECT $tablecomments.*
FROM (($tablecomments INNER JOIN $tableposts ON comment_post_ID = ID) ";

The error message:

MySQL error!

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN evo_postcats ON ID = postcat_post_ID) INNER JOIN evo_categories other' at line 1(Errno=1064)

Your query:

INNER JOIN evo_postcats ON ID = postc

Nov 09, 2006 04:21

OKAY ... (I didn't check v0.9.x)

The idea, however, should be the same (i.e., you originally had a problem with comment order, but when you eliminate DISTINCT, your lastcomments repeat ... right?)

So ..

WHATEVER statement you replaced DISTINCT on, works fine for recent comments

The concept is:

Code

global $disp;
 
if($disp=='comments') {
do the thing WITH DISTINCT in it
}else{
do the thing WITHOUT DISTINCT
}

Make sense?

I assume that the v0.9.x "thing" is

Code

$this->request = "SELECT $tablecomments.*
FROM (($tablecomments INNER JOIN $tableposts ON comment_post_ID = ID) ";

Nov 18, 2006 16:36

Thanks, i'd had this problem with comment appearing out of sequence on apparently random occasions. All fixed now, though I suppose I should have just pestered my hosts into upgrading MySQL.

Nov 21, 2006 17:33

Could someone please post the exact code to modify 0.9.0.10 (in file x replace ... with), the thread got a tad confusing and I don't want to mess stuff up.

Jan 12, 2007 04:42

I take it there wasn't anything to fix this in 1.8.6 and I have to apply the hack again?

Mar 15, 2007 14:30

I think it's more a "If-yer-host-is-running-mySQL-version-4.1.21-thing". I just noticed that v1.9.2 has the same problem for me and I had to redo the whole core-modification.

PHP

<?php 
        global $disp;                        
      if($disp=='comments') { 
       $this->sql 'SELECT DISTINCT T_comments.*
                                     FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
    } else { 
       $this->sql 'SELECT T_comments.*
                                       FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
    }
?>

Hope this helps.

Apr 03, 2007 04:00

well, I'm stumped. All of a sudden in 1.9.2 I get recent comments out of order again when I already made changes to _commentlist.class.php to fix the problem. Here is my _commentlist.class.php and I thank anyone that would point out exactly what I need to fix. Future bless you's to who responds. Thanks.

BTW I think my host is using PHP 5.

Code

<?php
/**
* This file implements the CommentList class.
*
* This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}.
* See also {@link http://sourceforge.net/projects/evocms/}.
*
* @copyright (c)2003-2006 by Francois PLANQUE - {@link http://fplanque.net/}.
* Parts of this file are copyright (c)2004-2005 by Daniel HAHLER - {@link http://thequod.de/contact}.
*
* @license http://b2evolution.net/about/license.html GNU General Public License (GPL)
*
* {@internal Open Source relicensing agreement:
* Daniel HAHLER grants Francois PLANQUE the right to license
* Daniel HAHLER's contributions to this file and the b2evolution project
* under any OSI approved OSS license (http://www.opensource.org/licenses/).
* }}
*
* @package evocore
*
* {@internal Below is a list of authors who have contributed to design/coding of this file: }}
* @author blueyed: Daniel HAHLER.
* @author fplanque: Francois PLANQUE
*
* @version $Id: _commentlist.class.php,v 1.5.2.2 2006/11/04 19:54:53 fplanque Exp $
*/
if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
 
/**
* Includes:
*/
require_once dirname(__FILE__).'/../dataobjects/_dataobjectlist.class.php';
 
/**
* CommentList Class
*
* @package evocore
*/
class CommentList extends DataObjectList
{
  var $blog;
 
  /**
   * Constructor
   */
  function CommentList(
    $blog = 1,
    $comment_types = "'comment'",
    $show_statuses = array( 'published' ),  // Restrict to these statuses
    $p = '',                              // Restrict to specific post
    $author = '',                          // Not used yet
    $order = 'DESC',                      // ASC or DESC
    $orderby = '',                        // list of fields to order by
    $limit = ''                           // # of comments to display on the page
    )
  {
    global $DB;
    global $cache_categories;
    global $pagenow;    // Bleh !
 
    // Call parent constructor:
    parent::DataObjectList( 'T_comments', 'comment_', 'comment_ID', 'Item', NULL, $limit );
 
    $this->blog = $blog;
 
 
    $this->sql = 'SELECT DISTINCT T_comments.*
                  FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';
 
    if( !empty( $p ) )
    {  // Restrict to comments on selected post
      $this->sql .= " WHERE comment_post_ID = $p AND ";
    }
    elseif( $blog > 1 )
    {  // Restrict to viewable posts/cats on current blog
      $this->sql .= "INNER JOIN T_postcats ON post_ID = postcat_post_ID INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID WHERE othercats.cat_blog_ID = $blog AND ";
    }
    else
    {  // This is blog 1, we don't care, we can include all comments:
      $this->sql .= ' WHERE ';
    }
 
    $this->sql .= "comment_type IN ($comment_types) ";
 
    /*
     * ----------------------------------------------------
     *  Restrict to the statuses we want to show:
     * ----------------------------------------------------
     */
    if( ! empty( $show_statuses ) )
    {
      $this->sql .= ' AND comment_status IN (\''.implode( "', '", $show_statuses ).'\')';
    }
 
    // This one restricts to post statuses, but it doesn't work completely right:
    // TODO: handle status dependencies with post
    $this->sql .= ' AND '.statuses_where_clause();
 
 
    // order by stuff
    if( (!empty($order)) && ((strtoupper($order) != 'ASC') && (strtoupper($order) != 'DESC')))
    {
      $order='DESC';
    }
 
    if(empty($orderby))
    {
      $orderby = 'comment_date '.$order;
    }
    else
    {
      $orderby_array = explode(' ',$orderby);
      $orderby = $orderby_array[0].' '.$order;
      if (count($orderby_array)>1)
      {
        for($i = 1; $i < (count($orderby_array)); $i++)
        {
          $orderby .= ', comment_'.$orderby_array[$i].' '.$order;
        }
      }
    }
 
 
    $this->sql .= "ORDER BY $orderby";
    if( !empty( $this->limit ) )
    {
      $this->sql .= ' LIMIT '.$this->limit;
    }
 
    // echo $this->sql;
 
    $this->rows = $DB->get_results( $this->sql, ARRAY_A );
 
    // Prebuild and cache objects:
    if( $this->result_num_rows = $DB->num_rows )
    {  // fplanque>> why this test??
 
      $i = 0;
      foreach( $this->rows as $row )
      {
        // Prebuild object:
        $this->Obj[$i] = new Comment( $row ); // COPY (function)
 
        // To avoid potential future waste, cache this object:
        // $this->DataObjectCache->add( $this->Obj[$i] );
 
        $i++;
      }
    }
  }
 
 
  /**
   * Template function: display message if list is empty
   *
   * @param string String to display if list is empty
   * @return true if empty
   */
  function display_if_empty( $message = '' )
  {
    if( empty($message) )
    {  // Default message:
      $message = T_('No comment yet...');
    }
 
    return parent::display_if_empty( $message );
  }
 
}
 
/*
nolog */
?>

Apr 03, 2007 04:40

Read the post right before yours and I *think* your solution is in there.

AFAIK, you need to change

PHP

<?php 
$this->sql 'SELECT DISTINCT T_comments.* 
FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
?>

to

PHP

<?php
$this->sql 'SELECT  T_comments.* 
FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID '
?>

Dunno about php versions (I'm on 5.0.5 and using the hack for 1.9.3 that I mention) without issue.

Give it a whirl. (Might need to do the IF thingie, if you're also showing recent comments).

Hope this helps.

Apr 03, 2007 04:59

OK, I removed any DISTINCTs so that everything should now be ok, I got confused with all the if with DISTINCT then if without DISTINCT so I don't know why those if codes were there in some examples when all needed was to delete all DISTINCTs (if I got that right)

Apr 03, 2007 06:21

bless me, bless me, bless me :D

Apr 03, 2007 08:57

Actually you're six times blessed, I was so happy that I doubled the thrice-blessing :-)

Apr 06, 2007 06:50

Thanks STK, I'm a little late to the game on this thread, but I just discovered the same problem, saw this thread and using your advice, got the fix.

Bless you again.

May 12, 2007 12:36

Hi, I am on 1.8.6 and have a problem with comment sorting. I assumed it was having our dates in reverse order 16/05 not 05/16, but maybe it is something else.

I just tried the code from Thu Nov 09, 2006 13:04
where I replaced:

Code

$this->sql = 'SELECT DISTINCT T_comments.*
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';

with

Code

global $disp;
 
if($disp=='comments'){
        $this->sql = 'SELECT DISTINCT T_comments.*
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        } else {
        
        $this->sql = 'SELECT T_comments.*
                                    FROM T_comments INNER JOIN T_posts ON comment_post_ID = post_ID ';  
        }

and it clanged with a php error.

I omitted the php opening and closing bits because they were not there already, unlike the example given.

I should mention that I only vaguely understand php (well even that is an exaggeration).

Jul 23, 2007 19:34

I applied the same solution after upgrading to 1.10.2. I had forgotten I needed to do this and expected to be fixed in this version of b2evo. Anyway, the fix works, Thanks!

--

Sorry, I forgot to check the backoffice before posting this. In the backoffice, comments appear duplicated or triplicated. (blog/admin.php?ctrl=browse&show_past=1&show_future=1&tab=comments) but in the "Last Commetns" in the public "face" of the blog they appear as intended, only once,

Aug 14, 2007 04:57

One thing I noticed just now, after implementing this edit to fix the sort order, is that comments appear multiple times in the admin area and under the "Most Recent Comments" link on the blog itself.... If a comment is made to post which has been crossposted in 2 categories, the comment will appear twice in both lists... if crossposted in 3 categories, it will appear 3 times.. etc...

Anyone else experience this? As soon as I added "DISTINCT" back into the /inc/MODEL/comments/_commentlist.class.php file, the multiple copies of the comments disappeared...

jj.


Form is loading...

Social CMS software – This forum is powered by b2evolution CMS, a complete engine for your website.