When I'm trying to reach /admin/content/file, it returns this error:

PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "fm.uid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT fm.fid AS fid, fm.uid AS uid, fm.timestamp AS fmtimes... ^: SELECT fm.fid AS fid, fm.uid AS uid, fm.timestamp AS fmtimestamp, SUM(fu.count) AS total_count FROM {file_managed} fm LEFT OUTER JOIN {file_usage} fu ON fm.fid = fu.fid GROUP BY fm.fid ORDER BY fm.timestamp DESC LIMIT 50 OFFSET 0; Array ( ) - PagerDefault->execute() (eilutė 79 iš /usr/local/www/apache22/data/www.joklausykite.lt/svetaine/drupal-7.23/includes/pager.inc).

Drupal 7.23
DB: PostgreSQL

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

askildolsen’s picture

I have the same problem, and a (temporary) solution is to create a view with "admin/content/file" as path.
I use SQL Server via sqlsrv

In my case i imported the view defined in

file_entity/admin_views_default/file.admin-content-file.inc

(remove all lines from "Display: System").

askildolsen’s picture

Adding

$query->groupBy('fm.uid');
$query->groupBy('fm.timestamp');

after line 319 in file_entity.admin.inc solves the problem for me.

Seems like MySQL differs from both PostgreSQL and SQL Server on how GROUP BY is handled:
http://stackoverflow.com/questions/1769361/postgresql-group-by-different...

lexerel’s picture

Thanks!

It solved the problem for me too.

Dave Reid’s picture

Maybe it's my PosgreSQL version, but I'm not able to reproduce this issue.

rob_johnston’s picture

Issue summary: View changes
Status: Active » Needs review
FileSize
551 bytes

I experienced this problem when on the thumbnails page (/admin/content/file/thumbnails).

I don't see it on Postgres either because it produces the correct query for me:
SELECT fm.fid AS fid, fm.uid AS uid, fm.timestamp AS fmtimestamp, SUM(fu.count) AS total_count FROM file_managed fm LEFT OUTER JOIN file_usage fu ON fm.fid = fu.fid GROUP BY fm.fid ORDER BY fm.timestamp DESC LIMIT 50 OFFSET 0

The problem is on MS SQL Server it produces the following query:

SELECT TOP(50) fm.[fid] AS [fid], fm.[uid] AS [uid], SUM(fu.count) AS total_count, fm.timestamp AS _field_0, fm.fid AS _field_1 
FROM file_managed fm LEFT OUTER JOIN file_usage fu ON fm.fid = fu.fid 
GROUP BY fm.fid
ORDER BY fm.timestamp DESC

and that produces the error:

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'file_managed.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.: SELECT TOP(50) fm.[fid] AS [fid], fm.[uid] AS [uid], SUM(fu.count) AS total_count, fm.timestamp AS _field_0, fm.fid AS _field_1 FROM {file_managed} fm LEFT OUTER JOIN {file_usage} fu ON fm.fid = fu.fid GROUP BY fm.fid ORDER BY fm.timestamp DESC; Array ( ) in PagerDefault->execute() (line 79 of D:\internet\drupal_WxT\includes\pager.inc).

This is essentially the same error that the original poster had.

The fix from #2 is the way to go. Lexerel says it works for PostGres, askildo and I say it works for MS SQL Server. I recommend this gets tested with mySql before it gets committed.

Also, I may have an explanation as to why one version of postgres requires the patch and others don't. From the section titled "Functional Dependencies" on http://www.postgresql.org/docs/current/interactive/sql-select.html#AEN80791,

PostgreSQL recognizes functional dependency (allowing columns to be omitted from GROUP BY) only when a table's primary key is included in the GROUP BY list. The SQL standard specifies additional conditions that should be recognized.

So, maybe somebody is missing a primary key (fid) on the file_managed table?

mgifford’s picture

Title: Can't access /admin/content/files » Can't access /admin/content/file
Issue summary: View changes

That URL in the description has to be singular, so just changing that to stop some confusion.

EDIT: I Tested this in SimplyTest.me and wasn't able to replicate the problem in /admin/content/file but I think that the thumbnail link /admin/content/file/thumbnails you mentioned @rob_johnston is probably produced by:
https://drupal.org/project/media

EDIT2: SimplyTest.me seems to fail when I add the media module.

rob_johnston’s picture

The way I read it, the media module calls the patched file here:
http://drupalcode.org/project/media.git/blob/HEAD:/media.module#l117

mgifford’s picture

Damn Drupal infrastructure grief. I can't see that page at the moment.

Devin Carlson’s picture

mgifford’s picture

I can run this patch and the Media module, but still having trouble finding a /admin/content/file/thumbnails

And ya, it looks like the right place in the code where it should be called. I am just getting the /admin/content/file page instead

rob_johnston’s picture

@mgifford, maybe this helps: #1992036: Empty page -> "/admin/content/file/thumbnails"? It's been part of the WetKit distribution (that I know you're using) for awhile.

mgifford’s picture

Yes, but as far as testing this patch, would be nice to do it in as simple an environment as possible on SimplyTest.me.

bellerophons_pegasus’s picture

I had the same error with Drupal 7.31, PostgreSQL and File entity 7.x-2.0-alpha3.
The patch in #2078393-5: Can't access /admin/content/file due to improper GROUP BY handling worked.

Dave Reid’s picture

I'm not sure why I see the need to add the groupBy for fm.timestamp? It's not added as a column returned in the results from the file_entity code, so I'm not sure why it's being added in the query? I see the need to add a groupBy('fm.uid') since we use it as a result.

Dave Reid’s picture

Priority: Critical » Major

Downgrading to major since this only happens for certain database systems, and not MySQL.

Dave Reid’s picture

Title: Can't access /admin/content/file » Can't access /admin/content/file due to improper GROUP BY handling
Version: 7.x-2.0-alpha2 » 7.x-2.x-dev
jordanrussellsmith’s picture

Patch in #5 worked for me on MS SQL Server.

Can we mark this RTBC?

punch’s picture

Patch #5 also fix the same problem on Oracle.

modestmoes’s picture

Patch #5 worked for me as well - MS SQL Server.

mgifford’s picture

@jordanrussellsmith - anyone can mark a patch RTBC. See http://www.bryanbraun.com/2013/01/12/what-does-rtbc-mean

szeder’s picture

Status: Needs review » Reviewed & tested by the community
szeder’s picture

Patch #5 worked for me as well - Postgres.

hnygard’s picture

Patch #5 tested on MSSQL Server. Fixed the problem.

badrange’s picture

On our MS SQL Server based environment /admin/content/file worked fine, but /admin/content/file/thumbnails threw an SQL-error. With the patch in #5 the problem went away.

For reference, here is the SQL error message that is shown:

PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Column 'file_managed.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.: SELECT TOP(50) fm.[fid] AS [fid], fm.[uid] AS [uid], SUM(fu.count) AS total_count, fm.timestamp AS _field_0, fm.fid AS _field_1 FROM {file_managed} fm LEFT OUTER JOIN {file_usage} fu ON fm.fid = fu.fid GROUP BY fm.fid ORDER BY fm.timestamp DESC; Array ( ) in PagerDefault->execute() (line 79 of D:\Web-Drupal\APP\www-root\includes\pager.inc).

wasare’s picture

Patch #5 works fine in PostgreSQL.

mlyno’s picture

Patch solves the access problem but if you want to sort in the list view you need to add 'sort' and $query in the code. (sorry do not know how to use a patch)

// Build the sortable table header.
  $header = array(
    'title' => array('data' => t('Title'), 'field' => 'fm.filename', 'sort' => 'desc'),
    'type' => array('data' => t('Type'), 'field' => 'fm.type', 'sort' => 'desc'),
    'size' => array('data' => t('Size'), 'field' => 'fm.filesize', 'sort' => 'desc'),
    'author' => t('Author'),
    'timestamp' => array(
      'data' => t('Updated'),
      'field' => 'fm.timestamp',
      'sort' => 'desc'),
    'usage' => array('data' => t('Used in'), 'field' => 'total_count'),
    'operations' => array('data' => t('Operations')),
  );

  $query = db_select('file_managed', 'fm')->extend('PagerDefault')->extend('TableSort');
  $query->leftJoin('file_usage', 'fu', 'fm.fid = fu.fid');
  $query->groupBy('fm.fid');
  $query->groupBy('fm.uid');
  $query->groupBy('fm.timestamp');
  $query->groupBy('fm.filesize');
  $query->groupBy('fm.filename');
  $query->groupBy('fm.type');
  $query->addExpression('SUM(fu.count)', 'total_count');
  file_entity_build_filter_query($query);

  • Dave Reid committed 63e81f5 on 7.x-2.x authored by rob_johnston
    Issue #2078393 by rob_johnston, mgifford, Dave Reid: Fixed cannot access...
Dave Reid’s picture

Status: Reviewed & tested by the community » Fixed

Committed #5 to 7.x-2.x.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.