FormIt Manager cause error 'SELECT list is not in GROUP BY clause'

[jolichter]

Hi, since I use mySQL 5.7+ (with default sql_mode), I always get this error by using the FormIt-Manager:

[2018-06-06 11:11:42] (ERROR @ /var/www/web42.myServer.lu/httpdocs/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: Array ( [0] => 42000 [1] => 1055 [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'usr_web42.FormItForm.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by )

Environment:

• MODX 2.6.4-pl
• FormIt 4.1.0-pl
• PHP 7.2.6
• MySQL 5.7.22

btw: no error if I use MariaDB 10.0.34

[studiovlijmscherp]

Same error here:

[2018-10-24 14:37:07] (ERROR @ /home/***/***.nl/public/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: Array ( [0] => 42000 [1] => 1055 [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'truckzoekt.FormItForm.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by )

[WestCoastSwing]

Same issue,

(ERROR @ /home//.nl/public/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: Array ( [0] => 42000 [1] => 1055 [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aw.FormItForm.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by )`

and the 'Export' fonction does not working in the manager ('Form' list does not appear)

Environment:

• MODX 2.7.0-pl
• PHP 7.3.0
• MySQL 5.7.23
• FormIt 4.2.1-pl

Fixed it by editing 2 files (the best way):

The first:

core/components/formit/processors/mgr/forms/getlist.class.php

Near line 59, just after: public function prepareQueryBeforeCount(xPDOQuery $criteria) { Add:

  /* ********************** CORRECTIF - GROUP BY ********************** */
  $criteria->select("MIN(`FormItForm`.`id`) AS `FormItForm_id`,`FormItForm`.`form` AS `FormItForm_form`");
  /* ********************** FIN DE CORRECTIF - GROUP BY ********************** */

The second:

core/components/formit/processors/mgr/forms/getforms.class.php

Near line 59, just after: public function prepareQueryBeforeCount(xPDOQuery $criteria) { Add:

  /* ********************** CORRECTIF - GROUP BY ********************** */
  $criteria->select("MIN(`FormItForm`.`id`) AS `FormItForm_id`,`FormItForm`.`form` AS `FormItForm_form`");
  /* ********************** FIN DE CORRECTIF - GROUP BY ********************** */

An another way to fix it:

Since MySQL 5.7, SQL params are set with "ONLY_FULL_GROUP_BY" option by default. Execute this command to set your mysql params without "ONLY_FULL_GROUP_BY" option:

  SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But you must have the authorization to do this. Sometimes you just don't have it !

[bwente]

What is the fix for this? Change the mysql config?

sql_mode=only_full_group_by

[bwente]

For UBUNTU 16.04 LTS

commands: cd /etc/mysql

sudo nano my.cnf add these two lines:

[mysqld] sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

[matdave]

@WestCoastSwing thanks, your solution fixed the issue

[matdave]

As an aside, for processors like this where it just retrieves a single result, it should just be written as a prepared SQL statement instead of using the getObject processor. It shouldn't request the full objects with getCollection to just get a simple list of data.

[jenswittmann]

Hello, i had the same issue on a client website today. I cant change the sql_mode, because it is a shared host. Is there a way to fix it?

Thanks a lot

[jenswittmann]

I moved from MySQL 5.7 to MariaDB 10.4 on Variomedia Webhosting. It do the trick.

[jolichter]

Hi, it is an old issue and still appears for me on sites that use MySQL. In my current case a shared host, unfortunately I can't switch to MariaDB or edit my.cnf (if this file still exists in the new MySQL 8.x).

To reproduce: Open Extras -> Formit (view all submitted forms) Get this Error:

(ERROR @ .../vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement:
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'web42.FormItForm.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)

My environment:

• MODX 3.0.2-pl
• FormIt 5.0.1-pl
• PHP Version 8.1.12
• Database type: mysql
• Database version: 8.0.31-0ubuntu0.20.04.1
• Database charset: utf8mb4

[jolichter]

I tried to apply the fix from WestCoastSwing in FormIt 5.0.1, which unfortunately does not fix it:

1) .../core/components/formit/src/FormIt/Processors/Mgr/Forms/GetList.php

Near line 61, just after:

    public function prepareQueryBeforeCount(xPDOQuery $criteria)
    {

Add:

    /* ********************** CORRECTIF - GROUP BY ********************** */
    $criteria->select("MIN(`FormItForm`.`id`) AS `FormItForm_id`,`FormItForm`.`form` AS `FormItForm_form`");
    /* ********************** FIN DE CORRECTIF - GROUP BY ********************** */

2) .../core/components/formit/src/FormIt/Processors/Mgr/Forms/GetForms.php

Near line 60, just after:

    public function prepareQueryBeforeCount(xPDOQuery $criteria)
    {

Add:

    /* ********************** CORRECTIF - GROUP BY ********************** */
    $criteria->select("MIN(`FormItForm`.`id`) AS `FormItForm_id`,`FormItForm`.`form` AS `FormItForm_form`");
    /* ********************** FIN DE CORRECTIF - GROUP BY ********************** */

ERROR LOG:

(ERROR @ /var/www/MyDomain.tld/httpdocs/modx3/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1140
    [2] => In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'web42.FormItForm.form'; this is incompatible with sql_mode=only_full_group_by
)

ERROR @ /var/www/MyDomain.tld/httpdocs/modx3/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 227) Error 42000 executing statement: 
Array
(
    [0] => 42000
    [1] => 1055
    [2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'web42.FormItForm.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)

[jolichter]

OK, I got it and fixed it:

.../core/components/formit/src/FormIt/Processors/Mgr/Encryption/GetList.php

Near line 62, just after: $criteria->groupby('context_key'); Add: $criteria->groupby('id');

This is the function with the new line and should now look with FormIt 5.0.1-pl like:

    /**
     * @access public.
     * @param xPDOQuery $criteria.
     * @return xPDOQuery.
     */
    public function prepareQueryBeforeCount(xPDOQuery $criteria)
    {
        $criteria->where([
            'context_key:IN' => $this->getAvailableContexts(),
        ]);

        $query = $this->getProperty('query');
        if (!empty($query)) {
            $criteria->where([
                'form:LIKE' => '%' . $query . '%'
            ]);
        }

        $criteria->groupby('form');
        $criteria->groupby('context_key');
        $criteria->groupby('id');

        return $criteria;
    }