An error with Conversations

[birdfeedr]

db is MySQLi, maybe other variables apply, but this is my current SQL Mode setting

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


Since I am not a database wizard, I am not sure of the pros/cons of ONLY_FULL_GROUP_BY directive.

I'd appreciate your scrutiny and suggestion for resolution.

Larry

Code:

Your copy of MyBB has experienced an error. Details of the error include:
---
Type: 20
File:  (Line no. 0)
Message
SQL Error: 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hlf_dbHLFv2.c.cid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Query:
SELECT * FROM (
              SELECT          c.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
              FROM            mybb_cnv_conversations c
              LEFT OUTER JOIN mybb_cnv_participants p
              ON              p.cid = c.cid
              WHERE          c.subject = 'New Reply to 6 years ago'
                              AND
                              c.uid IS NULL
              GROUP BY p.cid
              ) innerqry
WHERE puids = '98'
ORDER BY cid DESC
LIMIT 1
Back Trace: #0  errorHandler->email_error(20, SQL Error: 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hlf_dbHLFv2.c.cid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Query:
SELECT * FROM (
              SELECT          c.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
              FROM            mybb_cnv_conversations c
              LEFT OUTER JOIN mybb_cnv_participants p
              ON              p.cid = c.cid
              WHERE          c.subject = 'New Reply to 6 years ago'
                              AND
                              c.uid IS NULL
              GROUP BY p.cid
              ) innerqry
WHERE puids = '98'
ORDER BY cid DESC
LIMIT 1, , 0) called at [/home/hlf/public_html/forum/inc/class_error.php:195]
#1  errorHandler->error(20, Array ([error_no] => 1055,[error] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hlf_dbHLFv2.c.cid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,[query] =>
SELECT * FROM (
              SELECT          c.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
              FROM            mybb_cnv_conversations c
              LEFT OUTER JOIN mybb_cnv_participants p
              ON              p.cid = c.cid
              WHERE          c.subject = 'New Reply to 6 years ago'
                              AND
                              c.uid IS NULL
              GROUP BY p.cid
              ) innerqry
WHERE puids = '98'
ORDER BY cid DESC
LIMIT 1)) called at [/home/hlf/public_html/forum/inc/db_mysqli.php:597]
#2  DB_MySQLi->error(
SELECT * FROM (
              SELECT          c.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
              FROM            mybb_cnv_conversations c
              LEFT OUTER JOIN mybb_cnv_participants p
              ON              p.cid = c.cid
              WHERE          c.subject = 'New Reply to 6 years ago'
                              AND
                              c.uid IS NULL
              GROUP BY p.cid
              ) innerqry
WHERE puids = '98'
ORDER BY cid DESC
LIMIT 1) called at [/home/hlf/public_html/forum/inc/db_mysqli.php:337]
#3  DB_MySQLi->query(
SELECT * FROM (
              SELECT          c.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
              FROM            mybb_cnv_conversations c
              LEFT OUTER JOIN mybb_cnv_participants p
              ON              p.cid = c.cid
              WHERE          c.subject = 'New Reply to 6 years ago'
                              AND
                              c.uid IS NULL
              GROUP BY p.cid
              ) innerqry
WHERE puids = '98'
ORDER BY cid DESC
LIMIT 1) called at [/home/hlf/public_html/forum/inc/plugins/converse/converse-pm-datahandler-override.php:149]
#4  PMDataHandler->insert_pm() called at [/home/hlf/public_html/forum/inc/functions.php:8816]
#5  send_pm(Array ([subject] => New Reply to 6 years ago,[message] => dingo,

WiMStang has just replied to a thread which you have subscribed to. This thread is titled 6 years ago.

[Laird]

Hi Larry,

Great to hear from you. Sorry about this nuisance. If you can reliably repeat it, I wonder whether you'd be willing to try the following potential fix:

In `inc/plugins/converse/converse-pm-datahandler-override.php`, on line #137, replace:

Code:

              SELECT          c.cid,

with:

Code:

              SELECT          p.cid,

Please let me know how you go with that. Happy to work with you further if that doesn't resolve the problem!

Laird

[birdfeedr]

I'm not sure how to repeat the condition. It was an error popped up by other member's use of conversation.
I will be in touch with him.
Also, currently running 1.2.0-post release correction. If I update to 1.2.2, will this pose problem on myBB 1.8.26?

[birdfeedr]

This looks like it is an automated message sent by system that new reply is made to a subscribed thread.
WHERE clause identifies subject and c.uid is NULL

Am I guessing right?

[birdfeedr]

I changed line 137 as suggested, and replied to the thread.
No error generated, but also, there are no autogenerated messages for subscribed threads.
BTW, member 98 is subscribed to that thread "6 years ago",
3 errors of the type in the first post occurred, coincident with 3 replies.
My reply (the 4th one) did not generate an error, but there are no system messages in the database.

[birdfeedr]

I created a test thread which generated the error with the modification. I will drop sql_mode restrictions and test again.

Code:

Type: 20
File:  (Line no. 0)
Message
SQL Error: 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hlf_dbHLFv2.c.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Query:
SELECT * FROM (
               SELECT           p.cid,
                                c.uid,
                                GROUP_CONCAT(p.uid) AS puids,
                                c.subject
               FROM            mybb_cnv_conversations c
               LEFT OUTER JOIN mybb_cnv_participants p
               ON              p.cid = c.cid
               WHERE           c.subject = 'New Reply to thread subscription test 4'
                               AND
                               c.uid IS NULL
               GROUP BY p.cid
              ) innerqry
WHERE puids = '1384'
ORDER BY cid DESC
LIMIT 1

[birdfeedr]

Dropped sql_mode to '', and
1) I received an email notification BBEngine sent a conversation,
2) could see the conversation when I clicked the link in the email,
3) cannot see it when viewing all conversations,
4) not generating errors.

It's late, I can duplicate conditions when we are online at the same time.
In about 5 hours, I will update to 1.2.2 when I can take the forum offline, unless you advise otherwise.

It is related to sql_mode settings.

[birdfeedr]

Set sql_mode back to original in OP except for Only_Full_Group_By, and seeing conditions 1-4 in above post.

Good night. We'll get it solved, I'm sure. It's not critical. All else appears functional.

[birdfeedr]

Good morning. I have updated MyConversations to 1.2.2 to my live forum running 1.8.26.
sql_mode does not include Only_Full_Group_By.
PM notifications are being generated by BBEngine which is Guest.
PMs sent by BBEngine are showing up in the welcomeblock_member menu (currently 3 unread messages in 1 conversation).
When clicking the link, there are no Unread messages.
When clicking the link in the email notification when the conversation was started, the conversation is visible.

My environment is a locked down forum, i.e. Guests have no privileges, including sending PMs.
Can we give BBEngine non-guest status?

[Laird]

Good morning! Please pardon the delayed response - for some reason I had a mammoth session of sleep.

I've got a patch for you which I think resolves all of the issues you've described above, plus another one I discovered during testing. You should be OK in terms of MyConversations to then leave ONLY_FULL_GROUP_BY set, although I'm surprised your board was functional at all, because I encountered an error for a query in `showthread.php` itself when I enabled ONLY_FULL_GROUP_BY.

Are you comfortable applying patches?

In case you need them, here are some brief instructions:

1. Download the supplied patch file to your web server's filesystem.
   
2. Open a command shell on your web server and change directory to your forum root.
   
3. Then run
   Code:

   patch -p2 --dry-run -i path-to-the-patch/myconversations-bug-fixes-for-larry.patch
4. If all goes well with that, then you can remove the `--dry-run` argument and run the patch for real.