IPS\core\Achievements\_Badge::getBadgeStats Hogging Database

[David..]

hello!

every time this sql command runs (IPS task?) it completely spams my database and renders the entire server unresponsive.

 

why did this start happening? this issue started after updating the software on my server to the latest versions available. also, for a while now, if i visit the badges setting in ACP, it takes a very long time to load but eventually it does.

Edited July 3 by David..

[Marc]

We would need to look further into this for you, however the access details on file appear to be incorrect or missing. Could you please update these details by visiting your client area, selecting the relevant purchase, then clicking "Review/Update Access Information" under the "Stored Access Information" section. 

We look forward to further assisting you. 

 

[David..]

7 minutes ago, Marc Stridgen said:

We would need to look further into this for you, however the access details on file appear to be incorrect or missing. Could you please update these details by visiting your client area, selecting the relevant purchase, then clicking "Review/Update Access Information" under the "Stored Access Information" section. 

We look forward to further assisting you. 

 

ready!

[Marc]

I would advise first of all addressing items that are showing in the support section of your admin CP, and encuring you have your 3rd Party items up to date if you are going to use them. Of course you really need to test without them running. For example, at least one of those deals with badges, and is showing out of date.

[David..]

@Nathan Explosion it appears this issue is caused by your app "Display My Badges 1.4.0". can you look into this please?

[Marc]

On 7/12/2024 at 9:10 PM, David.. said:

@Nathan Explosion it appears this issue is caused by your app "Display My Badges 1.4.0". can you look into this please?

Thank you for confirming the cause, as that may well help others who come across the same issue 🙂 

[David..]

@Marc after further investigation, it seems the issue is still happening, even after removing the badge application by @Nathan Explosion which leads me to believe it is an issue with IPS itself?

are we able to look into this?

[Marc]

Please update login details. Your htaccess details are present, but there is no password for you ACP present

[David..]

5 hours ago, Marc said:

Please update login details. Your htaccess details are present, but there is no password for you ACP present

added!

[Marc]

You still appear to have many issues on there you need to ensure you address before we begin looking at this to ensure these are not the issue. You need to at the very least ensure everything is up to date (apps and plugins for example), address the issues with files that are edited showing in the support area of your admin CP, along with also the database items. 

[David..]

6 hours ago, Marc said:

You still appear to have many issues on there you need to ensure you address before we begin looking at this to ensure these are not the issue. You need to at the very least ensure everything is up to date (apps and plugins for example), address the issues with files that are edited showing in the support area of your admin CP, along with also the database items. 

i can resolve the database issues and update apps & plugins, would that suffice for a review?

[Jim M]

1 hour ago, David.. said:

i can resolve the database issues and update apps & plugins, would that suffice for a review?

You would want to resolve all that is mentioned in Support

[David..]

2 hours ago, Jim M said:

You would want to resolve all that is mentioned in Support

the rest are not real issues and i need them modified for my community to work properly.

[Marc]

You have an issue on your community. There is no way of telling if these are causing the issue until they are removed from the equation. If we review the issue, we would be disabling them entirely in order to check this. But the others absolutely have to be addressed. The file changes for example, are unsupported

[David..]

can we set a timeframe for when this can be troubleshooted? i can restore the files & fix the database issues so support can check.

the file modifications are needed for me because IPS4 does not support URI (example: myapp://callback) redirects in the API and that simple file modification allows it.

Edited July 26 by David..

[Marc]

I can take a look now. However there is no guarentee it wont need to be escalated, and if that is the case it would need to be left out until the issue is resolved

[David..]

On 7/26/2024 at 1:29 PM, Marc said:

if that is the case it would need to be left out until the issue is resolved

does it need to be left out if you can confirm the issue when it is left out?

[Marc]

Yes, as the developers will need it left out while they are resolving

[David..]

10 hours ago, Marc said:

Yes, as the developers will need it left out while they are resolving

considering it may take days, this is not a feasible solution for us as it would render our community unusable..

[Stuart Silvester]

Can you copy and paste 2-3 of those queries into here? Please make sure they're the full query and not cut off.

[David..]

17 hours ago, Stuart Silvester said:

Can you copy and paste 2-3 of those queries into here? Please make sure they're the full query and not cut off.

| 64259882 | database-user | localhost | database-name | Execute |    4 | Sending data | /*database-name::database-user::IPS\core\Achievements\_Badge::getBadgeStats:235*/ SELECT COUNT(*) FROM `ipb__core_member_badges` AS `core_member_badges` WHERE badge=43

| 64259927 | database-user | localhost | database-name | Execute |    1 | Sending data | /*database-name::database-user::IPS\core\Achievements\_Badge::getBadgeStats:235*/ SELECT COUNT(*) FROM `ipb__core_member_badges` AS `core_member_badges` WHERE badge=32

| 64260279 | database-user | localhost | database-name | Execute |    0 | Sending data | /*database-name::database-user::IPS\core\Achievements\_Badge::getBadgeStats:235*/ SELECT COUNT(*) FROM `ipb__core_member_badges` AS `core_member_badges` WHERE badge=20   

| 64261032 | database-user | localhost | database-name | Execute |    5 | Sending data | /*database-name::database-user::IPS\core\modules\front\members\_profile::badges:107*/ SELECT CEIL( 100 * COUNT( IF( achievements_points > 4, 1, NULL ) ) / COUNT(*) ) as percentage FROM `ipb__core_members` AS `core_members` WHERE achievements_points > 0                                                      

| 64261500 | database-user | localhost | database-name | Execute |    8 | Sending data | /*database-name::database-user::IPS\core\widgets\_achievements::render:826*/ SELECT * FROM `ipb__core_member_badges` AS `core_member_badges`  LEFT JOIN `ipb__core_badges` AS `core_badges` ON core_member_badges.badge=core_badges.id WHERE ( core_member_badges.member NOT IN(SELECT member_id FROM `ipb__core_members` AS `core_members` WHERE temp_ban != 0 OR (`members_bitoptions` & 1 ) != 0) ) ORDER BY datetime DESC LIMIT 5

| 64261501 | database-user | localhost | database-name | Execute |    8 | Sending data | /*database-name::database-user::IPS\core\widgets\_achievements::render:826*/ SELECT * FROM `ipb__core_member_badges` AS `core_member_badges`  LEFT JOIN `ipb__core_badges` AS `core_badges` ON core_member_badges.badge=core_badges.id WHERE ( core_member_badges.member NOT IN(SELECT member_id FROM `ipb__core_members` AS `core_members` WHERE temp_ban != 0 OR (`members_bitoptions` & 1 ) != 0) ) ORDER BY datetime DESC LIMIT 5

 

i think these tables are just too big as even loading badges & ranks in ACP takes 77 & 90 seconds to load respectively and they increase database usage while they're loading.

thank you!

[Marc]

How many rows do those tables have?

[David..]

4 hours ago, Marc said:

How many rows do those tables have?

ipb__core_member_bages has 4724341. seems like not a lot, but why would it hog server resources so much?

[David..]

@Marc @Stuart Silvester apologies for the annoyance, just informing you that the issue is still present and i would appreciate if someone were to look into it.

[David..]

is there at least a way to disable that query from running entirely?

my community keeps going down every few hours and it's getting stressful.