Rufus McDufus

That works perfectly, many thanks!

Ah, that looks good. I shall give it a try. Many thanks!

OK, I’ve found the issue. It was caused by the Wordfence security plugin. It looks like it was blocking calls to /wp-admin/admin-ajax.php. It looks like it had perhaps automatically added a rule in the past to do this(?) as I can’t find any options in Wordfence specifically to block it.

Even disabling and uninstalling the Wordfence plugin wasn’t enough! I had to reinstall and reinitiate all the Wordfence settings. Crazy.

It looks like this is an issue which can affect plugins with user interfaces and typically when hitting update buttons. Judging by my Google searches on this, another frequent cause appears to be caching plugins.

Ah well, it’s not a problem with the User Registration plugin at all and perhaps this might someone else with the same issue in future.

Sorry for the delay in replying – I’m 99% sure the problem here is caused by me. The website is a test website which I haven’t used for some time. It was locked down from external access (which I’ve removed temporarily), and also the SSL certificate is currently out of date which I think may be the cause – I’m in the process of getting this updated.

Running a web inspector, the javascript console is showing this when I hit the “Update” button:

      POST https://sitenamehidden/wp-admin/admin-ajax.php 403 (Forbidden)
send @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:2
ajax @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:2
(anonymous) @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:5
e.<computed> @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:5
ur_save_form @ form-builder.min.js?ver=3.2.1.3:1
(anonymous) @ form-builder.min.js?ver=3.2.1.3:1
dispatch @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:2
v.handle @ load-scripts.php?c=0&load%5Bchunk_0%5D=jquery-core,jquery-migrate,utils,wp-hooks&ver=6.6:2

So clearly some website configuration issue is causing the 403 error.

Thanks for getting back – yes, i am updating the form. I’ll check again today and maybe try disabling some plugins and will also check the error logs.

I tried disabled ad blockers and also tried in 2 different browsers, Chrome & Brave. It’s quite likely I’m doing something stupid though!

I did get the WordPress 6.6 update just before I started testing so hope it’s not related. PHP version is 8.3.something.

Hi – yes (possibly) the same problem here. I understand from earlier correspondence with the plugin author that it’s fixed in 3.0.12 but that version isn’t appearing in the repository yet.

(edit) I was getting a critical error trying to access the admin dashboard.

• This reply was modified 1 year, 3 months ago by Rufus McDufus.
• This reply was modified 1 year, 3 months ago by Rufus McDufus.

Ah, apologies, I see this is normal behaviour. I might try your suggestions here.

https://contactform7.com/loading-javascript-and-stylesheet-only-when-it-is-necessary/

Looking in the access logs and grepping for “contact-form-7” it looks like it’s loading these for every page view. Below are entries for a single page view for the front page which has no contact form on. IP address obfuscated.

xx.xx.xx.xx - - [30/Apr/2023:08:45:34 +0000] "GET /wp-content/plugins/contact-form-7/includes/css/styles.css?ver=5.7.6 HTTP/1.1" 200 2859 "https://biasedbbc.tv/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36"
xx.xx.xx.xx - - [30/Apr/2023:08:45:35 +0000] "GET /wp-content/plugins/contact-form-7/includes/swv/js/index.js?ver=5.7.6 HTTP/1.1" 200 10241 "https://biasedbbc.tv/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36"
xx.xx.xx.xx - - [30/Apr/2023:08:45:35 +0000] "GET /wp-content/plugins/contact-form-7/includes/js/index.js?ver=5.7.6 HTTP/1.1" 200 12943 "https://biasedbbc.tv/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36"
xx.xx.xx.xx - - [30/Apr/2023:08:45:35 +0000] "GET /wp-content/plugins/contact-form-7/modules/recaptcha/index.js?ver=5.7.6 HTTP/1.1" 200 999 "https://biasedbbc.tv/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36"

So I guess the question is – is it normal to load the Contact Form 7 stuff on every page?

• This reply was modified 1 year, 7 months ago by Rufus McDufus.
• This reply was modified 1 year, 7 months ago by Rufus McDufus.

Hi, and thanks for checking.

That’s the thing, as far as I can see everything is working fine. My concern is for the “not verifying reCaptcha tokens” in the Google Recaptcha admin console. I’m wondering maybe if bots are hitting the site and somehow exploiting the API and making malformed Recaptcha requests. The thousands (daily) of “Total requests” mentioned on the Google admin console might support this idea. It only lists high risk and low risk requests, and the bulk are appearing as “low risk”.

I’m not sure there’s anything that can be done. I think I’ll ignore those errors! Thanks again!

• This reply was modified 1 year, 7 months ago by Rufus McDufus.

Got a chance to test out the queries (on 970k comments). Results were the same for all 3 queries. I’m just using the Ubuntu/Linux “time” utility here in front of the query from the command line.

Time to execute the troublesome original query:
real 0m36.527s
user 0m0.005s
sys 0m0.007s

Time to execute first proposed query solution in your post above:
real 0m0.014s
user 0m0.002s
sys 0m0.010s

Time to execute second proposed query:
real 0m0.011s
user 0m0.003s
sys 0m0.005s

That’s a big difference!

• This reply was modified 2 years ago by Rufus McDufus.

Given this a go. I’m still getting a full table scan with the new index, but it’s taking 6 seconds as opposed to 30+ before so looks like it’s using it (yes I can see from the “explain” that it probably is).

These were the indexes on wp_posts before creating the new one. This site has been around a long time so there’s a lot of historical fluff that has built up. including indexes by the looks of it!

mysql> show indexes from wp_posts;
+----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                   | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY                    |            1 | ID                | A         |       17065 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date           |            1 | post_type         | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date           |            2 | post_status       | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date           |            3 | post_date         | A         |       17065 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date           |            4 | ID                | A         |       17065 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent                |            1 | post_parent       | A         |         948 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author                |            1 | post_author       | A         |          36 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name                  |            1 | post_name         | A         |       17065 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified       |            1 | post_type         | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified       |            2 | post_status       | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified       |            3 | post_modified_gmt | A         |       17065 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified_no_id |            1 | post_type         | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified_no_id |            2 | post_status       | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_modified_no_id |            3 | post_date_gmt     | A         |       17065 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_status                |            1 | post_status       | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
15 rows in set (0.00 sec)

This is the query before the index was created:

mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ;
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table       | partitions | type   | possible_keys                                          | key                       | key_len | ref                                       | rows   | filtered | Extra                                              |
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | wp_comments | NULL       | ref    | comment_post_ID,comment_approved_date_gmt,comment_type | comment_approved_date_gmt | 82      | const                                     | 969865 |   100.00 | Using index condition; Using where; Using filesort |
|  1 | SIMPLE      | wp_posts    | NULL       | eq_ref | PRIMARY,post_status                                    | PRIMARY                   | 8       | biasedbb_live.wp_comments.comment_post_ID |      1 |    80.12 | Using where                                        |
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

And after:

mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ;
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table       | partitions | type   | possible_keys                                          | key                       | key_len | ref                                       | rows   | filtered | Extra                                              |
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | wp_comments | NULL       | ref    | comment_post_ID,comment_approved_date_gmt,comment_type | comment_approved_date_gmt | 82      | const                                     | 969867 |   100.00 | Using index condition; Using where; Using filesort |
|  1 | SIMPLE      | wp_posts    | NULL       | eq_ref | PRIMARY,post_status,test_post_status                   | PRIMARY                   | 8       | biasedbb_live.wp_comments.comment_post_ID |      1 |    83.64 | Using where                                        |
+----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

6 seconds is still too long though. I’m not sure why it’s fine with Decent Comments 0.1.10 and not 0.1.11 & 0.1.12 though. Could it be something else?
Thanks again for your help.

• This reply was modified 2 years ago by Rufus McDufus.
• This reply was modified 2 years ago by Rufus McDufus.

Many thanks for you research on this. I can see in hindsight it looks like a core WP query anyway. I’ll give this index a go and see what happens…

It’s worth pointing out that I suspect I may not be seeing these issues if I were using MariaDB (or maybe MySQL 8.x?) as it seems better at forming an optimal query execution plan.

• This reply was modified 2 years ago by Rufus McDufus.

Hi – thanks for the reply. Definitely no DB rollback and I ran a mysqlcheck on the DB just to make sure there were no corrupted tables.

There are new subscribers and the subscriptions appear to be persisting now.
What was happening was that some regular post subscribers were subscribing to new posts but the subscriptions just seem to disappear after a short period of time – maybe a few minutes, maybe hours, I’m not sure. No other issues with the site.

I can’t think what else it could have been – perhaps something to do with stale object cache, no idea. So far so good though and it appears to be OK. I’ll keep any eye on it.

• This reply was modified 2 years, 3 months ago by Rufus McDufus.

I don’t think it’s comments being deleted which is the major issue here. I’ve monitored it over a day or so and. no comments were deleted in that time.

Subscriptions seem to stick around for a few hours then just get disappear.
Subscriptions from Aug 15 and before are still listed though.