Refreshing Statistics broken in EE 3.2.0 --> comment_total is an unknown column, and irreparably resets comment count to zero in all threads.
News and General
Hello –I have a problem with Control Panel –> utilities –> Statistic when I attempt to refresh.
I get:
Exception Caught
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘comment_total’ in ‘field list’:
UPDATE exp_forums SET comment_total = 0, recent_comment_date = 0, forum_total_topics = ‘655’, forum_total_posts = ‘5069’, forum_last_post_id = ‘978’, forum_last_post_title = ‘I'm closing the forum until the new site is up!’, forum_last_post_date = ‘1415642200’, forum_last_post_author_id = ‘1’, forum_last_post_author = ‘OverMachoGrande’ WHERE forum_id=’2’
C:[removed]\system\ee\legacy\database\drivers\mysqli\mysqli_connection.php:119
So, I discovered this because I was attempting to move a comment using the “Move Comment” function in the CP’s comment manager. When I move a comment to a new thread, the comment count numbers don’t update. Fine and dandy… that’s what the statistic refresh is for!
However, when I refresh the statistics, I get that error, the comment counts on all threads reset to zero, and I have to restore the database.
[I also tried importing new comments in the database as well, and I get the same problem.]
Any help with this would be much appreciated!
Best regards, John Christian
I can confirm- it definitely looks like a bug- I entered it in the bug tracker and included a patch.
See if that gets it working. It did in local testing.
Hey, Robin –thanks for the help!
It only halfway fixed it, though. I no longer get the Exception error, but it resets the comment count to 0 still –even on threads that have comments.
I used the provided patch on the link (I didn’t manually add the line of code).
Let me know if there is anything else I can do!
Regards, John
Ah- try it now. Either this fresh patch file or you can do a find/replace in Stats.php for:
if (isset(ee()->cp->installed_modules['comment']))
replace with
if (ee()->config->item('enable_comments') == 'y')I think it’s in 3 spots. And make certain comments are enabled in Settings- Comment Settings.
That get it working?
It works!!!! Thank you so much! I used the new patch (3.2-stats-patch3.zip).
I can transfer comments to another entry via the “move comment” function as well as copy them directly in the database, then refresh the statistics, and indeed those comment numbers are correct!!!
The “end goal” for me here is to transfer comments from the main site over to a new site in the multisite manager (which unfortunately I can’t test yet). So, I’m going to do that by just changing the site_id/entry_id/channel_id in the exp_comments table of the database. The fact that it works when I simply copy the comments and change the channel_id then refresh the stats makes me pretty confident I won’t have problems getting them to appear on the other site when that time comes! I’ll let you know if I do have problems, but I can definitely rest a little easier tonight!
Thanks again!!! -John
Glad that got it working- thanks for the heads up!
And ouch- that sounds like no fun! But it’s also doable, just make good backups.
Looking at the stats code, here’s how it gets the total_comments for each member:
$member_comments_count = ee()->db->query('SELECT COUNT(*) AS count, author_id FROM exp_comments GROUP BY author_id ORDER BY count DESC');It’s just counting by the comment author_id (be sure you get rid of the old comments or you’ll inflate this number).
And for each entry:
$channel_comments_count = ee()->db->query('SELECT COUNT(comment_id) AS count, entry_id FROM exp_comments WHERE status = "o" GROUP BY entry_id ORDER BY count DESC');
$channel_comments_recent = ee()->db->query('SELECT MAX(comment_date) AS recent, entry_id FROM exp_comments WHERE status = "o" GROUP BY entry_id ORDER BY recent DESC');So it’s matching them up by entry_date and counting only open entries.
From here, looks like the stats will be ok.