When enabling AdvAgg and all of the sub modules but CDN I get the following database error in postgreSQL.

PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "aav.aggregate_filenames_hash" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: INNER JOIN (SELECT aav.aggregate_filenames_hash AS aggregate... ^: SELECT af.filename AS filename, af.filesize AS filesize, af.mtime AS mtime, af.changes AS changes, af.linecount AS linecount, aa.counter AS counter, aa.aggregate_filenames_hash AS aggregate_filenames_hash, aa.porder AS aaporder FROM {advagg_files} af INNER JOIN (SELECT aav.aggregate_filenames_hash AS aggregate_filenames_hash, aa.filename_hash AS filename_hash, aa.porder AS porder, LPAD(CAST(COUNT(aav.aggregate_filenames_hash) AS char(8)), 8, '0') AS counter FROM {advagg_aggregates} aa INNER JOIN (SELECT aav.* FROM {advagg_aggregates_versions} aav WHERE (aav.root = :db_condition_placeholder_0) AND (aav.atime > :db_condition_placeholder_1) ) aav ON aav.aggregate_filenames_hash=aa.aggregate_filenames_hash GROUP BY aa.filename_hash) aa ON af.filename_hash=aa.filename_hash ORDER BY aa.counter DESC, aa.aggregate_filenames_hash ASC, aa.porder ASC; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 1374686780 ) in advagg_bundler_analysis() (line 190 of /mnt/www/html/wet-boew-drupal/sites/all/modules/advagg/advagg_bundler/advagg_bundler.module).
CommentFileSizeAuthor
#9 advagg-2059727-9-fix-postgresql.patch3.7 KBmikeytown2
#5 advagg-2059727-5-postgresql.patch814 bytesmikeytown2

Comments

sylus’s picture

sylus commented
Title: AdvAgg + PostgreSQL » Enabling AdvAgg in a PostgreSQL environment causes a PDOException

Making title more descriptive.

This stack overflow article seems to highlight the issue: http://stackoverflow.com/questions/1062158/converting-mysql-select-to-po...

mikeytown2’s picture

mikeytown2 commented
Title: Enabling AdvAgg in a PostgreSQL environment causes a PDOException » AdvAgg + PostgreSQL

I'm using db_select so that should take care of PostgreSQL issues correct; is this a PDO bug? The code in question involves multiple sub queries
http://drupalcode.org/project/advagg.git/blob/7769bca529e9f780ff8c5d773e...

mikeytown2’s picture

mikeytown2 commented
Title: AdvAgg + PostgreSQL » Enabling AdvAgg in a PostgreSQL environment causes a PDOException
mikeytown2’s picture

mikeytown2 commented

Adding in ->groupBy('aav.aggregate_filenames_hash') makes the query take 2.5 times as long to run on MySQL (10ms to 25ms, so something to be aware of).
Background issues #2006468: Slow Query in advagg_bundler_analysis. Run query in the background (via httprl) if at all possible. #1927578-9: Get end to end prototype working

mikeytown2’s picture

mikeytown2 commented
Status: Active » Needs review
StatusFileSize
new advagg-2059727-5-postgresql.patch814 bytes
sylus’s picture

sylus commented

Hey mikeytown2 thanks so much for taking a look at this.

I applied your patch and got the following error:

PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "aa.porder" must appear in the GROUP BY clause or be used in an aggregate function LINE 4: ...ilenames_hash, aa.filename_hash AS filename_hash, aa.porder ... ^: SELECT af.filename AS filename, af.filesize AS filesize, af.mtime AS mtime, af.changes AS changes, af.linecount AS linecount, aa.counter AS counter, aa.aggregate_filenames_hash AS aggregate_filenames_hash, aa.porder AS aaporder FROM {advagg_files} af INNER JOIN (SELECT aav.aggregate_filenames_hash AS aggregate_filenames_hash, aa.filename_hash AS filename_hash, aa.porder AS porder, LPAD(CAST(COUNT(aav.aggregate_filenames_hash) AS char(8)), 8, '0') AS counter FROM {advagg_aggregates} aa INNER JOIN (SELECT aav.* FROM {advagg_aggregates_versions} aav WHERE (aav.root = :db_condition_placeholder_0) AND (aav.atime > :db_condition_placeholder_1) ) aav ON aav.aggregate_filenames_hash=aa.aggregate_filenames_hash GROUP BY aa.filename_hash, aav.aggregate_filenames_hash) aa ON af.filename_hash=aa.filename_hash ORDER BY aa.counter DESC, aa.aggregate_filenames_hash ASC, aa.porder ASC; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 1374690510 ) in advagg_bundler_analysis() (line 191 of /mnt/www/html/wet-boew-drupal/sites/all/modules/advagg/advagg_bundler/advagg_bundler.module).

Modifying your patch slightly I got the everything to work again:

        ->groupBy('aa.filename_hash')
        ->groupBy('aa.porder')
        ->groupBy('aav.aggregate_filenames_hash');

Not sure why PostgreSQL enforces that the columns have to be contained in an aggregate or group by function though.

mikeytown2’s picture

mikeytown2 commented
Status: Needs review » Needs work

above patch isn't going to work. changes the output of the bundler. Will look into using the non-optimized version and see if I can get that to fly with this constraint in mind http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html

sylus’s picture

sylus commented

Thanks so much for taking a look at this. Also your right about the patch not working with bundler but at least am able to play around now :)

mikeytown2’s picture

mikeytown2 commented
Title: Enabling AdvAgg in a PostgreSQL environment causes a PDOException » AdvAgg Bundler submodule with PostgreSQL causes a PDOException
Component: Code » Bundler
Status: Needs work » Needs review
StatusFileSize
new advagg-2059727-9-fix-postgresql.patch3.7 KB

Can you test this patch out? It should do the trick :)

sylus’s picture

sylus commented
Status: Needs review » Reviewed & tested by the community

Based on an initial eval works great :) Really appreciate it!

mikeytown2’s picture

mikeytown2 commented
Status: Reviewed & tested by the community » Fixed

Patch in #9 has been committed. Thanks for testing the patch :)

Status: Fixed » Closed (fixed)

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