Author Topic: "There was an uncatched exception" under Pages tab after new installation  (Read 235 times)

Offline seanie_morris

  • Posts: 293
  • Gender: Male
Hi,
I have a brand new installation today of WB 2.13.4 on a new website I am doing.
The unzip process worked except for the following error message, which is under the 'Pages' tab, so I cannot add pages:
There was an uncatched exception
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1363538_stemdb.s. module' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
in line (828) of (/framework/class.database.php):


When searching for a fix for this, I did find this as the closest match:
https://forum.WebsiteBaker.org/index.php/topic,32203.0.html
But nothing worked.

Here lines 825 to 832 in class.database.php

public function getQuery(string $sStatement)
    {
//echo \nl2br(\sprintf("---- [%04d] %s %s \n",__LINE__,$sStatement,$_SERVER["SCRIPT_NAME"]),false);
    if (false === ($this->result = $this->oDbHandle->query($sStatement))) {
        $this->error = $this->oDbHandle->error;
    }
        return $this->result;
    }



From my hosting provider, here is what I have:
PHP 8.1
MySQL5 Databases C (Server version: 5.7.42)
Apache Website

Any help is appreciated!

Thanks,
Seanie.
« Last Edit: August 28, 2023, 09:57:28 PM by seanie_morris »

Offline Luisehahne

  • WebsiteBaker Org e.V.
  • **
  • Posts: 4541
  • Gender: Male
Error can be found in my.ini file from mysql
sql_mode=only_full_group_by should be removed or changed with
sql_mode="STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION"
other solution i found on https://database.guide/6-ways-to-fix-error-1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-in-mysql/
i will be going to test and fix  it in  2.13.4 and will be publish a patch.
Sorry for the trouble 8-)

Dietmar



Note: Once the code has been generated, it is easy to debug. It's not a bug, it's a feature!

Offline Luisehahne

  • WebsiteBaker Org e.V.
  • **
  • Posts: 4541
  • Gender: Male
Best solution: Please ask your provider to drop only_full_group_by from sql_mode.

Read following blog: How do I disable ONLY_FULL_GROUP_BY in MySQL?

if you Disable Only_full_group_by from the PHPMyAdmin changes only active until mysql server is restartet

Dietmar
Note: Once the code has been generated, it is easy to debug. It's not a bug, it's a feature!

Offline crnogorac081

  • Posts: 2101
  • Gender: Male
Or,

in /admin/pages/index.php

line 125 replace
Code: [Select]
             .        's.`module`, MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '
With
Code: [Select]
              .        ' MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '

delete s.`module`,

I see no changes in db output, nor that s.module is needed somewhere in code below
Web developer

Offline seanie_morris

  • Posts: 293
  • Gender: Male
Thanks for the replies folks.

I went for the quick option:

Or,
in /admin/pages/index.php
line 125 replace
Code: [Select]
             .        's.`module`, MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '
With
Code: [Select]
              .        ' MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '
delete s.`module`,

This worked, and so far no other problems have surfaced. I can create new pages and everything else that goes with them.

Thank you!  8-)

Offline sternchen8875

  • Posts: 278
not 100% accurate, my friend ;-)

Quote
I see no changes in db output

s.`module` is the first entry (block#1) for this page_id in the section-table (see picture 1), but you're right, we dont use this information somewhere in the pages-list

Another solution for this case is: replace s.`module`, with GROUP_CONCAT(s.`module`),

GROUP_CONCAT(s.`module`) give a comma-separated list of all modules or sections for this page-id, but the same here: we dont use this informations somewhere in the pages-list


Picture 1 - original Select without  Only_full_group_by in sql_mode-settings




Picture 2 - select with Only_full_group_by in sql_mode-settings && GROUP_CONCAT(s.`module`) instead of s.`module`





Offline crnogorac081

  • Posts: 2101
  • Gender: Male
But at the end I found another colum with entry

Code: [Select]
1. wysiwyg
2. wysiwyg
3. code
like sections list.

And this s.module is throwing only first section module, I cant find usefull this information.
Web developer

Offline sternchen8875

  • Posts: 278
this section list (your code) is used in the page overview for the onhover-function on the section-icon and is a result from this code in the select:
Code: [Select]
  .        'GROUP_CONCAT(CAST(CONCAT(s.`section_id`, \' - \', s.`module`, \' \', s.`title`) AS CHAR) ORDER BY s.`position` SEPARATOR \'\n\') `section_list` '




Quote
I cant find usefull this information.

if you mean s.`module` in the select,  100% confirm   :-P


P.S.: i remember a section list like my picture in a former wb-version, but i think, it was WB 2.8.4 with a complete different code for the pages list

Offline crnogorac081

  • Posts: 2101
  • Gender: Male
Lol never knew about this on hover.


Another solution for this case is: replace s.`module`, with GROUP_CONCAT(s.`module`),

So this is accepted solution rather than begging provider to chamge system settings
Web developer

Offline Luisehahne

  • WebsiteBaker Org e.V.
  • **
  • Posts: 4541
  • Gender: Male
I have tested all the suggestions, unfortunately without success
The solution that works for me is to add to the GROUP BY all listed columns.

The sql statement looks like this for me now!
Code: [Select]

        $sql  = 'SELECT ( SELECT COUNT(*) '
              .          'FROM `'.$oDb->TablePrefix.'pages` `x` '
              .          'WHERE x.`parent`=p.`page_id`'
              .        ') `children`, '
              .        'MAX(s.`publ_start` + s.`publ_end`) published, '
              .        '(SELECT MAX(`position`) FROM `'.$oDb->TablePrefix.'pages` '
              .        'WHERE `parent`='.$parent.') max_position, '
              .        '0 min_position, '
              .        'p.`page_id`, '
              .        'p.`position`, p.`parent`, p.`link`, p.`level`, p.`language`, p.`admin_groups`, '
              .        'p.`admin_users`, p.`viewing_groups`, p.`viewing_users`, p.`visibility`, '
              .        'p.`menu_title`, p.`page_title`, p.`page_trail`, '
              .        'GROUP_CONCAT(CAST(CONCAT(s.`section_id`, \' - \', s.`module`, \' \', s.`title`) AS CHAR) ORDER BY s.`position` SEPARATOR \'\n\') `section_list` '
              . 'FROM `'.$oDb->TablePrefix.'pages` p '
              .    'INNER JOIN `'.$oDb->TablePrefix.'sections` s '
              .    'ON p.`page_id`=s.`page_id` '
              . 'WHERE `parent`='.$parent.' '
              .    (($oReg->PageTrash != 'inline') ? 'AND `visibility`!=\'deleted\' ' : '')
              . 'GROUP BY p.`page_id`,p.`position`, p.`parent`, p.`link`, p.`level`, p.`language`, p.`admin_groups`, '
              .        'p.`admin_users`, p.`viewing_groups`, p.`viewing_users`, p.`visibility`, '
              .        'p.`menu_title`, p.`page_title`, p.`page_trail` '
              . 'ORDER BY p.`position` ASC';

If you like it, I will release a patch.

Dietmar
Note: Once the code has been generated, it is easy to debug. It's not a bug, it's a feature!

Offline seanie_morris

  • Posts: 293
  • Gender: Male
Re: "There was an uncatched exception" under Pages tab after new installation
« Reply #10 on: September 08, 2023, 10:39:53 AM »
Thanks everyone for adding your bits and pieces to the topic! I find them very useful for me to learn more.  8-)
Seanie.