I can't really help you with this, but as a general rule, everything that isn't 'directly' related to b2evo should go in the [chat away] forum. ( a mod will move it when they see it )

Yeh, i'm not that good with sql, i just use phpmyadmin, get it to perform the query i want, then just stick the query it used into my php code :$

the archives plugin is included with 1.8, this post is fine here.

Ohh dam my skim reading.

I'm running MySQL 4.1.10 and seeing the same thing.

I've been having the same problem. I compared sites running v1.6 and v1.8, (archive mode = monthly) thinking it was a version problem, as the _results.class.php file has changed so much between the versions.

However, the mySql query spat out by v1.8 matches perfectly with the v1.6 run, but the results are buggered.

Same deal ... no limit ... no problem.

Apply ANY limit ... problem. (Counts are off, non-contiguous months, years off, wrong number of rows, etc.).

I'm wondering if it's a PHP/mySql version issue? (I know someone else who's running v1.8 and DOESN'T have this problem).

For me ... I'm on PHP 5.0.4 and mySql 4.1.21

For the guy NOT having the problem ... He's on (I think) PHP 5.1.2 and mySql 5.0.16

What versions of PHP are you on?

Thanks.

_results.class.php QUERY wrote:

SELECT YEAR(post_datestart) AS year,
MONTH(post_datestart) AS month,
COUNT(DISTINCT postcat_post_ID) AS count
FROM T_posts INNER JOIN T_postcats ON post_ID = postcat_post_ID
INNER JOIN T_categories ON postcat_cat_ID = cat_ID
WHERE (cat_blog_ID = 7)
AND ( ( post_status IN ('published') ) )
AND (post_datestart <= '2006-08-19 10:41:04')
GROUP BY year, month
ORDER BY year DESC, month DESC
LIMIT 0, 12

Print_r wrote:

Array
(
[0] => stdClass Object
(
[year] => 2005
[month] => 6
[count] => 5
)

[1] => stdClass Object
(
[year] => 2005
[month] => 5
[count] => 1
)

[2] => stdClass Object
(
[year] => 2005
[month] => 3
[count] => 1
)

[3] => stdClass Object
(
[year] => 2005
[month] => 2
[count] => 2
)

[4] => stdClass Object
(
[year] => 2005
[month] => 1
[count] => 1
)

[5] => stdClass Object
(
[year] => 2004
[month] => 8
[count] => 1
)

[6] => stdClass Object
(
[year] => 2004
[month] => 7
[count] => 1
)

)

Running PHP 4.4.4 here, although I was running 4.4.2 and had the same issues.

The same issues I've also had on b2e 0.9.1, 0.9.2 and 1.8.1. So it doesn't seem to be anything new introduced on b2e's side.

I just upgraded to PHP 5.1.5 and the problem still exists. Another thing I have noticed is that if you use a Limit of a greater value than the number of posts in a given cat_blog_ID it displays fine. Of course it is displaying the entire archive directory, but it shows correctly with a limit.

Ex:
I have 18 posts in cat_blog_ID = 4. If I set "Limit 0, 18" it displays all of the archives. If I set "Limit 0, 17" it displays odd results.

I don't know enough about sql to procceed further with this. Whether it is a mysql issue or the actual query itself, I have no idea.

The PHP version should not matter at all.

What do you get with adding ", post_ID" to the GROUP BY?

I've just tried it on MySQL 5.0.22-Debian_0ubuntu6.06-log and it works fine here.. :/

I didn't think the PHP version would matter but anyway... I added the suggested like so,

AND (
post_datestart <= '2006-08-18 01:06:21'
)
GROUP BY year, post_ID,
MONTH ORDER BY year DESC , 

and received a count of 1 on everything.

year    MONTH    count
2006    7        1
2006    5        1
2006    2        1
2006    2        1

For comparison, this is what the actual counts should be:

# August 2006 (3)
# July 2006 (6)
# June 2006 (2)
# May 2006 (4)
# April 2006 (9)

Sure, with grouping by ID the count is 1. I just wanted to see, if the list of year's and month's is correct then, which it is not (missing the posts from August, July, June, 3 from May and all from April again).

I have no idea.. the only solution looks like playing around with the SQL query to get a clue about what's wrong (by removing joins etc).

After doing some reading/learning on mysql.com, I have had some success. Using the following syntax I got the results I wanted.

SELECT YEAR( post_datestart ) AS year, MONTH( post_datestart ) AS month , COUNT( post_ID ) AS count
FROM evo_posts
JOIN evo_categories ON evo_posts.post_main_cat_id = evo_categories.cat_ID
WHERE cat_blog_ID = 2 AND post_status = 'published' AND post_datestart <= '2006-08-18 01:06:21'
GROUP BY year DESC, month DESC
LIMIT 0, 12

I found some information about DISTINCT and LIMIT in the [url=http://mysql.com/doc/refman/5.0/en/limit-optimization.html]mysql docs[/url] that stated "When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows." After removing distinct from my query above it worked perfectly! So I removed DISTINCT from the original query on the archives plugin and it mostly worked. I think it might be returning duplicate entries or something else weird, but the results are a lot closer.

Archive Plugin Query Output without DISTINCT:

year    MONTH    count
2006    8        3
2006    7        7
2006    6        2
2006    5        4
2006    4        11

My Query Output which displays the correct results:

year    month    count
2006    8        3
2006    7        6
2006    6        2
2006    5        4
2006    4        9

blueyed wrote:

the only solution looks like playing around with the SQL query to get a clue about what's wrong

That's the rub. The query that fails in my v1.8 install is the SAME EXACT query that works fine in someone elses v1.6 install. (i.e., it doesn't seem to be the query? And other v1.8 ppl don't report a problem?).

I couldn't find a correlation between SQL versions and the issue (and couldn't think what else it might be). Stumped!

But it's "real", as a number of ppl (3 in this thread), are experiencing this problem.

It was working correctly for me in v1.6, and *I thought* it was working in v1.8, but I couldn't find a cached page to verify that. (Who knows if my host has bumped up the version of *anything* ... there's a definite advantage to being your own host) :p

Where it does not work: what are the MySQL versions and table types (MyISAM/InnoDB) and collations of the tables that get used?

Blueyed,

phpMyAdmin Home page shows the following:

it does not work with: (inserted --blueyed)

MySQL - 4.1.21-standard-log
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
Connection Collation: utf8_unicode_ci

b2evo tables are:
Type: MyISAM
Collation: latin1_swedish_ci

phpMyAdmin - v2.8.0.2
MySQL client version: 4.1.10

Hope this helps.

it works here with:

MySQL - 5.0.22-Debian_0ubuntu6.06-log
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
Connection Collation: utf8_unicode_ci

b2evo tables are:
Type: MyISAM
Collation: latin1_swedish_ci

phpMyAdmin - 2.8.2.2
MySQL client version: 4.1.15

It does NOT work with:

MySQL - 5.0.24
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: utf8_unicode_ci

b2evo tables are:
Type: MyISAM
Collation: latin1_swedish_ci

phpMyAdmin - 2.8.2.2
MySQL client version: 5.0.24
Used PHP extensions: mysqli

&&

MySQL - 4.1.21-log
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: utf8_unicode_ci

b2evo tables are:
Type: MyISAM
Collation: latin1_swedish_ci

phpMyAdmin - 2.8.0.2
MySQL client version: 4.1.20
Used PHP extensions: mysql

-------

I've read the mysql changelog on a few of the most recent releases and found that they have rewritten part of both LIMIT and DISTINCT for bug fixes. I have since rewritten the _archives.plugin.php code to use the query I wrote until it is fixed.

Can you please check, if you can reproduce the following bug: http://bugs.mysql.com/bug.php?id=21705

I cannot reproduce it here.. maybe that's the key?

Does it work for you, if you insert the SQL_CALC_FOUND_ROWS after "select" as proposed in the bug?

e.g.

SELECT SQL_CALC_FOUND_ROWS YEAR(post_datestart) AS year, 
 MONTH(post_datestart) AS month, 
 COUNT(DISTINCT postcat_post_ID) AS count 
 FROM T_posts INNER JOIN T_postcats ON post_ID = postcat_post_ID 
 INNER JOIN T_categories ON postcat_cat_ID = cat_ID 
 WHERE (cat_blog_ID = 7) 
 AND ( ( post_status IN ('published') ) ) 
 AND (post_datestart <= '2006-08-19 10:41:04') 
 GROUP BY year, month 
 ORDER BY year DESC, month DESC 
 LIMIT 0, 12

Yes, that worked on MySQL 5.0.24 and 4.1.21. The results returned 12 rows with the correct counts.

Reposting Query:

SELECT SQL_CALC_FOUND_ROWS YEAR( post_datestart ) AS year,
  MONTH( post_datestart ) AS MONTH ,
  COUNT( DISTINCT postcat_post_ID ) AS count
FROM evo_posts
INNER JOIN evo_postcats ON post_ID = postcat_post_ID
INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE ( cat_blog_ID =2 )
AND ( ( post_status IN ( 'published' ) ) )
AND ( post_datestart <= '2006-08-19 10:41:04' )
GROUP BY year, MONTH
ORDER BY year DESC , MONTH DESC
LIMIT 0 , 12

Just wanted to chime in and say that the "SQL_CALC_FOUND_ROWS" addition seemed to fix the problem on MySQL 5.0.24a although I haven't verified that the counts are 100% correct. Thanks for the fix everyone, might this become part of the next version?

This is a MySQL bug (introduced in
4.1.21/5.0.24 and fixed in 4.1.22/5.0.25).

IMHO we should not add the workaround (SELECT SQL_CALC_FOUND_ROWS) here, because it may fail miserably on MySQL 3.x or cause other problems in the end). Just upgrade MySQL.

Ok, since it is a bug, I do understand, but for some people who use a hosting provider, they don't have the option to upgrade to a newer version of MySQL. In the mean time I'll just keep this workaround and wait until my host upgrades MySQL.

Ok. Because of the popularity of this bug, it will be fixed in 1.8.3 (if it does not get rolled back that is).

Patch:
http://evocms.cvs.sourceforge.net/evocms/b2evolution/blogs/plugins/_archives.plugin.php?r1=1.20.2.5&r2=1.20.2.7&pathrev=v-1-8

Update: changed the link above.. the original patch did nothing.

I was having the same issues. Thanks for helping astridas and blueeyed.