Running Acquia Drupal 1.2.6

Only the first 30 search phrases are shown and there is no pager to let you see the rest of the results. If sorting by Count (ascending instead of the default descending) or by Message (either way) the behaviour is similar - you get the right "first" page of results (as expected) but no pager. I've checked the page source and the pager is definitely not there. The other "top xxx" reports all work fine. Also works fine in 5.x.

Problem looks to be in http://api.drupal.org/api/function/dblog_top/6:

In

$result = pager_query("SELECT COUNT(wid) AS count, message, variables FROM {watchdog} WHERE type = '%s' GROUP BY message, variables ". tablesort_sql($header), 30, 0, "SELECT COUNT(DISTINCT(message)) FROM {watchdog} WHERE type = '%s'", $type);

the count query should probably be

"SELECT COUNT(DISTINCT(message, variables)) FROM {watchdog} WHERE type = '%s'"

since message is always
%keys (@type).

CommentFileSizeAuthor
#9 dblog_query_fix.patch865 bytesSouvent22
#6 dlog-pager.png9.25 KBlilou
#2 dblog_top.patch862 bytesgpk

Comments

gpk’s picture

gpk commented

Actually the count query might need to be something like:

"SELECT COUNT(*) FROM (SELECT COUNT(*) FROM {watchdog} WHERE type = '%s' GROUP BY message, variables) AS w"

which seems to work. There's probably a neater way of doing it i.e. without a subquery - my SQL isn't exactly cutting edge :P

gpk’s picture

gpk commented
Status: Active » Needs review
StatusFileSize
new dblog_top.patch862 bytes

Now with patch. The top page not found and top access denied reports (which also use dblog_top()) still work also with this patch.

gpk’s picture

gpk commented
Version: 6.10 » 7.x-dev

Looks like this will affect 7.x also, which has

  $count_query = db_select('watchdog');
  $count_query->addExpression('COUNT(DISTINCT(message))');
  $count_query->condition('type', $type);
gpk’s picture

gpk commented
Status: Needs review » Needs work

No patch for 7.x.

lilou’s picture

lilou commented
Version: 7.x-dev » 6.x-dev
Status: Needs work » Needs review

Pager works on D7 (i test it) :

$query = db_select('watchdog', 'w')->extend('PagerDefault')->extend('TableSort');
  $query->addExpression('COUNT(wid)', 'count');
  $query = $query
    ->fields('w', array('message', 'variables'))
    ->condition('w.type', $type)
    ->groupBy('message')
    ->groupBy('variables')
    ->limit(30)
    ->orderByHeader($header);
  $query->setCountQuery($count_query);
  $result = $query->execute();
lilou’s picture

lilou commented
StatusFileSize
new dlog-pager.png9.25 KB
gpk’s picture

gpk commented

Which report is that? It doesn't look like the top search phrases report.

lilou’s picture

lilou commented
Version: 6.x-dev » 7.x-dev
Status: Needs review » Needs work

@gpk : you're right, pager only work for dblog module (admin/reports/access-denied and admin/reports/page-not-found).

Souvent22’s picture

Souvent22 commented
Version: 7.x-dev » 6.x-dev
Assigned: Unassigned » Souvent22
StatusFileSize
new dblog_query_fix.patch865 bytes

I'm re-rolling a patch for D6. I think this ticket is for the D6 issue, as the issue seems to be fixed for D7.

gpk’s picture

gpk commented
Status: Needs work » Needs review

>the issue seems to be fixed for D7
Ah, that must be #302268: TableSorts and PagerDefault queries broken.

Not sure that #9, which seems to be just a re-rolled #2, is the best way of fixing this in 6.x. Anyone more familiar with SQL care to chip in?

thedavidmeister’s picture

thedavidmeister commented
Status: Needs review » Closed (won't fix)

patch doesn't apply:

error: dblog.admin.inc: No such file or directory

Regardless, I don't think this will be fixed in D6 and it's not an issue in D7+