Advertisement:

Author Topic: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”  (Read 2115 times)

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
- “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« on: March 18, 2018, 12:05:09 PM »
- “Database Error

'{Database Name}.pm.id_pm' isn't in GROUP BY
File: {Server Path}/htdocs/forum/Sources/PersonalMessage.php
Line: 695

Online shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 2,967
    • sbulen on GitHub
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #1 on: March 18, 2018, 12:30:01 PM »
8Taner8 -

Thanks for the report.  Some questions -
   What versions of php & mysql?
   Steps to reproduce? 
   Is this the latest version from Github?

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Doug Heffernan

  • Full Member
  • ***
  • Posts: 651
    • An Unofficial King of Queens Forum
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #2 on: March 18, 2018, 02:05:19 PM »
Where do you get that error? When you send pm, when you read them or performing some other action?

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #3 on: March 18, 2018, 02:58:53 PM »
- “I Get The Error By Clicking On The Outbox. Php Version Is: 7.0 Latest Version By Github.”

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 60,073
  • Gender: Male
    • Kindred-999 on GitHub
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #4 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.....
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 639
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #5 on: March 18, 2018, 03:38:36 PM »
i'm not able to reproduce,
but you had to be also in display mode 2 otherwise this code part will be not called.

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
- “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #6 on: March 18, 2018, 09:53:09 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.”

Online shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 2,967
    • sbulen on GitHub
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #7 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?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 639
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #8 on: March 19, 2018, 02:25:05 AM »
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
Code: [Select]
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
Code: [Select]
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)
Code: [Select]
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:
Code: [Select]
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:
Code: [Select]
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.

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
- “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #9 on: March 19, 2018, 02:33:32 AM »
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”

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
- “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #10 on: March 19, 2018, 02:59:02 AM »
- “Screenshots:


« Last Edit: March 19, 2018, 03:41:40 AM by 8Taner8 »

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 639
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #11 on: March 19, 2018, 05:38:51 AM »
Could you run in your mysql admin this both query at once?
Code: [Select]
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;

Offline 8Taner8

  • Semi-Newbie
  • *
  • Posts: 12
- “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #12 on: March 19, 2018, 11:49:23 AM »
- “

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 639

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 639
Re: - “Database Errors | 2.1 Beta 4 | Simple Machines Forum”
« Reply #14 on: March 20, 2018, 04:51:30 AM »
@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:
Code: [Select]
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.
« Last Edit: March 20, 2018, 05:06:44 AM by albertlast »