TSP

The query that is quite consistently slow looks like this, and are generated with getItemsWithPermission. It takes 5+ seconds SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics` AS `forums_topics` LEFT JOIN `forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id LEFT JOIN `core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_topics.last_post > 1743120000 AND forums_topics.forum_id NOT IN( 472 ) AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(4,forums_forums.password_override) OR FIND_IN_SET(31,forums_forums.password_override) ) ) AND forums_forums.min_posts_view<=5776 AND forums_topics.approved=1 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) ORDER BY forums_topics.last_post desc LIMIT 0,25The explain for that query is: +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+---------------+---------+-------------------------------------------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+---------------+---------+-------------------------------------------+--------+----------+-----------------------------+ | 1 | SIMPLE | forums_topics | NULL | ref | forum_id,last_x_topics,approved,last_post,most_recent_post,last_true_post | approved | 1 | const | 822800 | 26.08 | Using where; Using filesort | | 1 | SIMPLE | forums_forums | NULL | eq_ref | PRIMARY,min_posts_view,forum_password,widget_performance | PRIMARY | 2 | dbname.forums_topics.forum_id | 1 | 100.00 | Using where | | 1 | SIMPLE | core_tags_cache | NULL | eq_ref | tag_cache_key | tag_cache_key | 128 | func | 1 | 100.00 | Using where | | 1 | SIMPLE | author | NULL | eq_ref | PRIMARY,mgroup | PRIMARY | 8 | dbname.forums_topics.starter_id | 1 | 100.00 | Using where | | 1 | SIMPLE | last_commenter | NULL | eq_ref | PRIMARY,mgroup | PRIMARY | 8 | dbname.forums_topics.last_poster_id. | 1 | 100.00 | Using where | +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+---------------+---------+-------------------------------------------+--------+----------+-----------------------------+Then I do the changes in the attached item.php-commit.txt. The query then becomes: SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics` AS `forums_topics` LEFT JOIN `forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id LEFT JOIN `core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_topics.last_post > 1743120000 AND forums_topics.forum_id NOT IN( 472 ) AND forums_topics.approved=1 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(19,20,23,24,25,26,27,28,30,35,41,44,46,53,57,60,61,62,63,64,65,67,72,73,75,76,78,79,94,96,101,104,107,111,112,113,119,125,126,131,132,150,153,160,161,177,178,179,182,184,192,194,199,200,203,204,208,209,210,214,216,217,218,219,220,222,223,224,233,235,238,239,241,249,250,254,258,270,275,276,277,279,280,281,284,285,286,287,288,290,292,298,299,303,304,306,309,310,314,316,326,327,328,329,331,332,333,334,335,336,337,338,339,340,341,342,343,348,352,355,356,357,358,362,364,365,366,367,380,388,390,391,395,396,407,414,415,416,420,421,422,423,424,425,428,432,433,436,437,438,439,440,441,442,443,444,445,446,447,448,450,451,452,453,454,455,456,457,466,468,471,472,473,474,475,481,482,485,486,489,499,500,505,510,513,518,521,523,524,526,527,528,529,530,531,532,533,534,535,536,540,542,544,545,547,548,550,555,557,560,561,562,563,564,567,568,571,574,584,589,590,591,592,593,594,597,599,601,602,603,609,624,625,636,639,640,641,649,660,662,663,681,682,686,691,699,700,701,703,709,710,712,718,722,726,727,728,730,731,736,739,740,741,742,743,744,745,746,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,771,773,774,775,776,781,782,784,786,787,788,789,790,791,792,793) ORDER BY forums_topics.last_post desc LIMIT 0,25 And the explain: +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+------------------+---------+-------------------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+------------------+---------+-------------------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | forums_topics | NULL | range | forum_id,last_x_topics,approved,last_post,most_recent_post,last_true_post | most_recent_post | 7 | NULL | 1705 | 100.00 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | forums_forums | NULL | eq_ref | PRIMARY | PRIMARY | 2 | dbname.forums_topics.forum_id | 1 | 100.00 | Using index | | 1 | SIMPLE | core_tags_cache | NULL | eq_ref | tag_cache_key | tag_cache_key | 128 | func | 1 | 100.00 | Using where | | 1 | SIMPLE | author | NULL | eq_ref | PRIMARY,mgroup | PRIMARY | 8 | dbname.forums_topics.starter_id | 1 | 100.00 | Using where | | 1 | SIMPLE | last_commenter | NULL | eq_ref | PRIMARY,mgroup | PRIMARY | 8 | dbname.forums_topics.last_poster_id. | 1 | 100.00 | Using where | +----+-------------+-----------------+------------+--------+---------------------------------------------------------------------------+------------------+---------+-------------------------------------------+------+----------+----------------------------------------------------+ This query is much faster, as it completes in less than 0.01 seconds. forum-queries-itemphp-inefficient.txt item.php-commit.txt

Unfortunately switching over to MySQL 8.4 as recommended did not alleviate our issues with inefficences in Item.php. We experience the same challenges as we did with this new Item.php code before we switched. So I'll have to once again create some custom modifications to Item.php to incorporate the earlier approach. Let me know if you wanna investigate, @Matt . I don't know if our experiences are relevant in terms of the 5.x-version or not. That said, we're probably quite far off upgrading to 5, and will stay on 4.7 for now.

No issues have been observed in terms of errors with versions 4.7.17 or 4.7.20. The reason we originally decided to upgrade was due to performance problems caused by changes in Item.php introduced sometime after 4.7.13. @Matt pointed out that the optimization was specifically designed with MySQL 8 in mind. Since we couldn't switch to MySQL 8.4 right away, I had to backport some methods from Item.php to avoid serious database performance degradation. Unfortunately, I've been tied up with other projects, so I hadn't been able to upgrade the communities running 4.7.17 (with the backported Item.php code) until now. Today I upgraded one of them to 4.7.20 and removed my custom changes to Item.php for the time being. We'll be monitoring the performance closely—alongside our host—to see how things hold up on 4.7.20. Fingers crossed that I won’t need to reintroduce any of the backported changes.

I’m not logged in inkognito. It worked in the previous version to read the rss feed URL when not logged in, and my understanding, given the RSS link contains a key-element, is that it’s meant to work when not authenticated, given the key: https://invisioncommunity.com/discover/66.xml/?&member=135437&key=46f... (I have not pasted the full key here…)

@Idea-ahead I sent you a message on my modifications done to the Item.php file for 4.7.17. I expect one of those two methods is the culprit. Please note as I said that my customizations was made for 4.7.17, and I haven't reviewed the changes made to that file in newer versions 4.7.18 and 4.7.19 yet, so it may not be directly compatible.

Hi, We are currently considering switching from MariaDB to MySQL 8. However, we would prefer if we would then be able to go straight to MySQL 8.4. I see that in the requirements.json you list MySQL 8.0 as recommended. Are there any known issues with choosing MySQL 8.4 with Invision Community version 4.7? And what are the plans for supporting newer versions of MySQL?

We noticed similar issues going from 4.7.13 to 4.7.16. I was in contact with @Matt back then. In our case we ended up reverting to an the code in getItemsWithPermission from an earlier version where we hadn't had that issue with getItemsWithPermission method. We also got another issue upgrading to 4.7.17. In that case we ended up reverting some code in _comments() Using Mariadb 10.5. But we'll upgrade to either a newer version of MariaDB or Mysql 8.0 or Mysql 8.4 soon-ish, so we hopefully can remove these customizations.

I personally don't see a reason for such a confirmation box in that case, it's easy enough to just change the status again if you did a mistake. Guess I'll try to make a hook to remove it if no notifications are set up.

Cool, but if you don't want to set up notifications or use that system, it seems unneccessary to get a popup like this each time you want to mark a report complete or rejected. Hope this is a bug that can be corrected.

A long time ago I think I remember it being mentioned, when you first said display name logins would be removed, that you would have some system in place before that to ensure that people were aware of their registered email address, and be sure to keep it updated. Will something like that come to the 4.x line?

We have some. I've provided more details in a reply I just made to the ticket.

Steps to reproduce: Create a test topic in a forum where guests does NOT have permission to post new topics Reply with a test account, or temporarily allow guests to post topics and replies. If you replied with a test account: delete the test account so the reply becomes a guest post If you posted as a guest: remember to reset the forum permissions to once again disallow guest topics/replies As a moderator: select the reply and split it into a new topic in the same forum with your moderator tools You should be redirected to the new topic created by splitting the posts Instead you'll get an error page: "You do not have permission to view this topic." (Error code: 2F173/K) The reason is that the new topic is saved to the database with the approved column set to -3. Which in system/Content/Content.php is documented as: "@li -3 is a post made by a guest using the "post before register" feature" Please note that this will happen even if the system "Post before register" is disabled. It shouldn't happen in either cases. Also; since there is no way to get a list of topics and replies hidden for this reason, it's not possible to save the topic from the interface, you'll have to manually fix it in the database. I think this happens because of the following code for the function createItem in system/Content/Item.php. I'm pretty sure you'll have to fix the logic for both the approved and hidden switch. <?php case 'approved': if ( $hidden === NULL ) { if ( !$author->member_id and $container and !$container->can( 'add', $author, FALSE ) ) { $val = -3; } else { $val = static::moderateNewItems( $author, $container ) ? 0 : 1; } } else { $val = \intval( !$hidden ); } break; case 'hidden': if ( $hidden === NULL ) { if ( !$author->member_id and $container and !$container->can( 'add', $author, FALSE ) ) { $val = -3; } else { $val = static::moderateNewItems( $author, $container ) ? 1 : 0; } } else { $val = \intval( $hidden ); } break;

@Stuart Silvester does my reply about database version help at all? Is this a known issue that is being worked on?

Server version: 10.5.24-MariaDB-1:10.5.24+maria~ubu2004-log mariadb.org binary distribution