- "Database Error
'{Database Name}.pm.id_pm' isn't in GROUP BY
File: {Server Path}/htdocs/forum/Sources/PersonalMessage.php
Line: 695"
8Taner8 -
Thanks for the report. Some questions -
What versions of php & mysql?
Steps to reproduce?
Is this the latest version from Github?
Where do you get that error? When you send pm, when you read them or performing some other action?
- "I Get The Error By Clicking On The Outbox. Php Version Is: 7.0 Latest Version By Github."
Is this the latest version from github?
Is this an upgrade or a clean install of 2.1?
We appreciate bug reports, but you have to learn to actually include details.....
i'm not able to reproduce,
but you had to be also in display mode 2 otherwise this code part will be not called.
Quote from: Kindred on March 18, 2018, 03:02:13 PM
Is this the latest version from github?
Is this an upgrade or a clean install of 2.1?
We appreciate bug reports, but you have to learn to actually include details.....
- "Error Path: Home Page > Profile Menu > Outbox
1- Yes. This Is Latest Version.
2- Upgraded. 2.0.15 To 2.1 Beta 4."
I've tried a few environments (upgraded & installed) & can't reproduce.
A few more questions:
- OS?
- php version?
- MySQL version? & engine?
- Mods installed?
To give your (the one how try reproduce the error) a insight:
We talk about this query: https://github.com/SimpleMachines/SMF2.1/blob/release-2.1/Sources/PersonalMessage.php#L675
if ($context['display_mode'] == 2)
{
if ($context['folder'] != 'sent' && $context['folder'] != 'inbox')
{
$labelJoin = '
INNER JOIN {db_prefix}pm_labeled_messages AS pl ON (pl.id_pm = pm.id_pm)';
$labelQuery = '';
$labelQuery2 = '
AND pl.id_label = ' . $context['current_label_id'];
}
$request = $smcFunc['db_query']('pm_conversation_list', '
SELECT MAX(pm.id_pm) AS id_pm, pm.id_pm_head
FROM {db_prefix}personal_messages AS pm' . ($context['folder'] == 'sent' ? ($context['sort_by'] == 'name' ? '
LEFT JOIN {db_prefix}pm_recipients AS pmr ON (pmr.id_pm = pm.id_pm)' : '') : '
INNER JOIN {db_prefix}pm_recipients AS pmr ON (pmr.id_pm = pm.id_pm
AND pmr.id_member = {int:current_member}
AND pmr.deleted = {int:deleted_by}
' . $labelQuery . ')') . $labelJoin . ($context['sort_by'] == 'name' ? ('
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = {raw:pm_member})') : '') . '
WHERE ' . ($context['folder'] == 'sent' ? 'pm.id_member_from = {int:current_member}
AND pm.deleted_by_sender = {int:deleted_by}' : '1=1') . (empty($pmsg) ? '' : '
AND pm.id_pm = {int:pmsg}') . $labelQuery2 . '
GROUP BY pm.id_pm_head'.($_GET['sort'] != 'pm.id_pm' ? ',' . $_GET['sort'] : '') . '
ORDER BY ' . ($_GET['sort'] == 'pm.id_pm' && $context['folder'] != 'sent' ? 'id_pm' : '{raw:sort}') . ($descending ? ' DESC' : ' ASC') . (empty($_GET['pmsg']) ? '
LIMIT ' . $_GET['start'] . ', ' . $maxPerPage : ''),
array(
'current_member' => $user_info['id'],
'deleted_by' => 0,
'sort' => $_GET['sort'],
'pm_member' => $context['folder'] == 'sent' ? 'pmr.id_member' : 'pm.id_member_from',
'pmsg' => isset($pmsg) ? (int) $pmsg : 0,
)
);
}
Which you can see is only called when the display mode is 2,
another important fact is that: pm.id_pm is in query already ther max(pm.id_pm),
since it's used in max function we want never that this part of the group by part:
reason for this line
GROUP BY pm.id_pm_head'.($_GET['sort'] != 'pm.id_pm' ? ',' . $_GET['sort'] : '') . '
The error message is a little bit odd,
'{Database Name}.pm.id_pm' isn't in GROUP BY
i never see that the database name is part of the column in smf.
They query he got should be look in this way (other member id)
SELECT MAX(pm.id_pm) AS id_pm, pm.id_pm_head FROM smf_personal_messages AS pm WHERE pm.id_member_from = 1 AND pm.deleted_by_sender = 0 GROUP BY pm.id_pm_head ORDER BY pm.id_pm ASC LIMIT 0, 15
in smf 2.1 we enforce this type sql_mode:
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
And this works fine in my mysql env,
when mysql version exists where the problem is real
than could the solution to change this line:
ORDER BY ' . ($_GET['sort'] == 'pm.id_pm' && $context['folder'] != 'sent' ? 'id_pm' : '{raw:sort}') . ($descending ? ' DESC' : ' ASC') . (empty($_GET['pmsg']) ? '
take the && $context['folder'] != 'sent' out.
Quote from: shawnb61 on March 18, 2018, 11:13:04 PM
I've tried a few environments (upgraded & installed) & can't reproduce.
A few more questions:
- OS?
- php version?
- MySQL version? & engine?
- Mods installed?
- "Modifications: PM To New Members, GroupDocs Viewer .NET 1.0, Who Downloaded Attachment, Weather In Posts v.1, Contact Page, Similar Topics, SEO Sitemap
Php Version: 7.0
MySQL: 5.6.35-81.0 - Percona Server (GPL), Release 81.0
Server: Byethost"
- "Screenshots: (https://resmim.net/f/ghpNFh.png)
(https://resmim.net/f/GLHeEp.png)
(https://resmim.net/f/mc3puA.png)
(https://resmim.net/f/i5atFw.png)"
Could you run in your mysql admin this both query at once?
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
SELECT MAX(pm.id_pm) AS id_pm, pm.id_pm_head FROM smf_personal_messages AS pm WHERE pm.id_member_from = 1 AND pm.deleted_by_sender = 0 GROUP BY pm.id_pm_head ORDER BY pm.id_pm ASC LIMIT 0, 15;
- "(https://resmim.net/f/dEvzzB.png)"
this should be than the fix: https://github.com/SimpleMachines/SMF2.1/pull/4638/commits/ff387a359075891f90515cef649523e66f728eff
@8taner8 could you give us feedback if this fix your issue?
A small deep dive for one how cares.
Mariadb (which i used and the most here) use the old mysql logic for only_full_groupe_by
which behave differently by this two queries:
select max(a) from asdf order by a; -- doesn't work because of violate the rule
select max(a), b from asdf group b order by a; -- work because not violate the rule
MySQL 5.6+ is here more consistence because,
it dectect that both violate the rule.
Which is in my pov the right behavior because, it's the standard behavior in database world.