WebsiteBaker Community Forum
WebsiteBaker Support (2.13.x) => General Help & Support => Topic started by: seanie_morris on August 28, 2023, 09:44:09 PM
-
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.
-
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
-
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? (https://manage.accuwebhosting.com/knowledgebase/4342/How-do-I-disable-ONLY_FULL_GROUP_BY-in-MySQL-.html)
if you Disable Only_full_group_by from the PHPMyAdmin changes only active until mysql server is restartet
Dietmar
-
Or,
in /admin/pages/index.php
line 125 replace
. 's.`module`, MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '
With
. ' 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
-
Thanks for the replies folks.
I went for the quick option:
Or,
in /admin/pages/index.php
line 125 replace
. 's.`module`, MAX(s.`publ_start` + s.`publ_end`) published, p.`link`, '
With
. ' 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-)
-
not 100% accurate, my friend ;-)
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
(https://i.gyazo.com/91b9b83590f32343f614e9c6312c6f91.png)
Picture 2 - select with Only_full_group_by in sql_mode-settings && GROUP_CONCAT(s.`module`) instead of s.`module`
(https://i.gyazo.com/0e32ffcc63b51f53e301d58b467138d7.png)
-
But at the end I found another colum with entry
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.
-
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:
. 'GROUP_CONCAT(CAST(CONCAT(s.`section_id`, \' - \', s.`module`, \' \', s.`title`) AS CHAR) ORDER BY s.`position` SEPARATOR \'\n\') `section_list` '
(https://i.gyazo.com/bb257df66b718c74a456e91f3d6dd701.png)
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
-
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
-
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!
$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';
Dietmar
-
Thanks everyone for adding your bits and pieces to the topic! I find them very useful for me to learn more. 8-)
Seanie.