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
Comment | File | Size | Author |
---|---|---|---|
#5 | Expand_groupBy_for_thumbnails-2078393-5.patch | 551 bytes | rob_johnston |
Comments
Comment #1
askildolsen CreditAttribution: askildolsen commentedI 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").
Comment #2
askildolsen CreditAttribution: askildolsen commentedAdding
$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...
Comment #3
lexerel CreditAttribution: lexerel commentedThanks!
It solved the problem for me too.
Comment #4
Dave ReidMaybe it's my PosgreSQL version, but I'm not able to reproduce this issue.
Comment #5
rob_johnston CreditAttribution: rob_johnston commentedI 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:
and that produces the error:
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,
So, maybe somebody is missing a primary key (fid) on the file_managed table?
Comment #6
mgiffordThat 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.
Comment #7
rob_johnston CreditAttribution: rob_johnston commentedThe way I read it, the media module calls the patched file here:
http://drupalcode.org/project/media.git/blob/HEAD:/media.module#l117
Comment #8
mgiffordDamn Drupal infrastructure grief. I can't see that page at the moment.
Comment #9
Devin Carlson CreditAttribution: Devin Carlson commentedMarked #2129425: GROUP BY error on files admin page as a duplicate.
Comment #10
mgiffordI 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
Comment #11
rob_johnston CreditAttribution: rob_johnston commented@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.
Comment #12
mgiffordYes, but as far as testing this patch, would be nice to do it in as simple an environment as possible on SimplyTest.me.
Comment #13
bellerophons_pegasus CreditAttribution: bellerophons_pegasus commentedI 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.
Comment #14
Dave ReidI'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.
Comment #15
Dave ReidDowngrading to major since this only happens for certain database systems, and not MySQL.
Comment #16
Dave ReidComment #17
jordanrussellsmith CreditAttribution: jordanrussellsmith commentedPatch in #5 worked for me on MS SQL Server.
Can we mark this RTBC?
Comment #18
punch CreditAttribution: punch commentedPatch #5 also fix the same problem on Oracle.
Comment #19
modestmoes CreditAttribution: modestmoes commentedPatch #5 worked for me as well - MS SQL Server.
Comment #20
mgifford@jordanrussellsmith - anyone can mark a patch RTBC. See http://www.bryanbraun.com/2013/01/12/what-does-rtbc-mean
Comment #21
szeder CreditAttribution: szeder commentedComment #22
szeder CreditAttribution: szeder commentedPatch #5 worked for me as well - Postgres.
Comment #23
hnygard CreditAttribution: hnygard as a volunteer commentedPatch #5 tested on MSSQL Server. Fixed the problem.
Comment #24
badrange CreditAttribution: badrange at Wunder commentedOn 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).
Comment #25
wasare CreditAttribution: wasare commentedPatch #5 works fine in PostgreSQL.
Comment #26
mlyno CreditAttribution: mlyno as a volunteer commentedPatch 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)
Comment #28
Dave ReidCommitted #5 to 7.x-2.x.