WordPress database error Duplicate entry

Hi @epsiloncool , so I’ve updated to 1.71.238 today and now am seeing lots of these in my PHP error log: [28-Jul-2024 09:30:57 UTC] WordPress database error Table 'wordpress.wpftsi_doctree' doesn't exist for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT trel.q_id, trel.did, tindex.tid, tindex.tsrc, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "128390") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "128390")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [28-Jul-2024 09:30:57 UTC] WordPress database error Table 'wordpress.wpftsi_doctree' doesn't exist for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID, wpftsi_t.relev FROM wp_posts inner join ( select fi.tid, t_end.relev from wpftsi_index fi straight_join ( SELECT tbase.index_id, sum( ( if(tbase.n < 1,0,trel. pow + (trel. res / LOG(tbase.n + 1)) / 1) + if(isnull(t3.id) or (t3.n < 1),0, ( t3. pow + (t3. res / LOG(t3.n + 1)) / 1 ) * (case tbase.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) / (17)) ) * (case tbase.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) / (17) ) relev FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) STRAIGHT_JOIN wpftsi_index tindex ON tindex.id = tbase.index_id LEFT JOIN ( SELECT dt.p_tid, dt.p_tsrc, dt.p_token, t2.id, t2.token AS token2, t2.n AS n2, t2.index_id AS index_id2, t2.pow, t2.res, t2.n FROM wpftsi_doctree AS dt STRAIGHT_JOIN ( SELECT tbase.id, tbase.n, trel.did, trel.res, trel.pow, tindex.tid, tindex.tsrc, tindex.id AS index_id, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs AS tbase ON (trel.did = tbase.id) STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id where 1 and (trel.q_id = "128390") ) AS t2 ON t2.tid = dt.c_tid AND t2.tsrc = dt.c_tsrc AND t2.token = dt.c_token ) AS t3 ON tindex.tid = t3.p_tid AND tindex.tsrc = t3.p_tsrc AND tbase.token = t3.p_token where 1 and (trel.q_id = "128390") group by tbase.index_id ORDER BY NULL ) t_end on t_end.index_id = fi.id order by null ) wpftsi_t on wpftsi_t.tid = wp_posts.ID WHERE 1=1 and (((1))) and (wpftsi_t.relev > 0) AND ((wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled')) OR (wp_po...

…and I’ve just noticed it’s not returning any searches anymore. I’ll try reverting back to the previous version.

Hi @rfischmann Thank you very much for the fast report! It’s actually the strange problem and we didn’t detect it on the testing stage…. 🙁 Could you please make a simple test for me? Please deactivate 1.71.238 and then activate it again. Then open the error_log and tell me whether there is a fresh error message at the moment of activation. The plugin is checking for all the tables and in case some tables not exist, it creates them. In this exact case I see that wpftsi_doctree was not created automatically and I think there was some error why it was not happen. Let me know if you see some error in error_log in the moment of plugin activation. Thanks!

Thanks for the quick reply, @epsiloncool ! So I deactivated the plugin, updated it again to the latest version, and re-activated it. This time, I’m not seeing any error logs and it seems to be working fine. Maybe now it did create the new DB table on activation, but didn’t do that before when I simply updated over the previous version while it was already activated? Cheers!

I’ve just connected to my DB using Sequel Pro and can confirm the wpftsi_doctree table was now created.

Hi @rfischmann Good that the plugin is working now, but it’s bad that the bad is disappear silently. Could you please open your error_log again and find the datetime where you have updated the plugin for the first time? There should be an error message and I still hope to see it. You can try try to search for “create table” or “alter column” or “alter table” sentences… Thank you!

Just looked at it and the first error, right after I updated the plugin, was very similar to the one I’ve pasted above @epsiloncool . Then, the error log started getting flooded by various similar entries like that until I reverted back to the previous version. Would those DB errors really be in that same PHP error.log file or maybe in another one?

Hi @rfischmann There is only one common error_log file for all scripts and plugins. Also in WPFTS we are not recording script/db errors to somewhere else. Okay, Rafael, I got enough information. I suspect that plugin activation while updating comes by different way than on the manual deactivation/activation. And the result is mandatory table was not created. I am going to make more serious tests around this problem. Thank you again!

Sounds good, thanks @epsiloncool ! Glad to help.

Hi @epsiloncool , Just looked at my error logs again and found a few new, different entries: [29-Jul-2024 16:26:01 UTC] WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = '2024-07-30 13:25:11' WHERE option_name = 'wpfts_detector2_message_expdt' made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/fulltext-search/fulltext-search.php'), WPFTS_Core->Init, WPFTS_Core->FeatureDetector, WPFTS_Core->set_option, update_option And: [29-Jul-2024 17:44:18 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT trel.q_id, trel.did, tindex.tid, tindex.tsrc, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "130483") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "130483")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [29-Jul-2024 17:55:04 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT trel.q_id, trel.did, tindex.tid, tindex.tsrc, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "130496") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "130496")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [29-Jul-2024 17:59:03 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT t...

Hi @rfischmann Those MySQL queries looks near to usual, so may be it is not a problem of WPFTS plugin. For example, first query is 100% good and even can be used by other plugins. Do you see any other database-related errors from other plugins or tables? As I know, database errors related to “Lock wait timeout exceeded” may happen when two different processes is trying to access the same database row(s). But it’s normal situation, MySQL server is the thing that is designed to solve such things silently. However, there is a setting in database config that says to MySQL how much lock wait time is “OKAY”. It is called “innodb_lock_wait_timeout” and it is set to 50 seconds by default. If we set it to smaller value, for example, 1 second, MySQL will recognize even small row locks as problems. May I ask you something? Could you try to check which value is set on your MySQL? Make this query in phpMyAdmin or somewhere else with your website database select @@innodb_lock_wait_timeout It should show a value in results, show it to me. Thanks a lot!

I see, thanks @epsiloncool . My “innodb_lock_wait_timeout” is set to 50 seconds, as you said. Here are no other errors in my logs besides those two I’ve pasted above, which are repeating every now and then.

Just so you know, @epsiloncool , these are still showing up after the latest update: [03-Aug-2024 15:47:36 UTC] WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = '2024-08-04 12:46:46' WHERE option_name = 'wpfts_detector2_message_expdt' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/fulltext-search/fulltext-search.php'), WPFTS_Core->Init, WPFTS_Core->FeatureDetector, WPFTS_Core->set_option, update_option [04-Aug-2024 09:12:04 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT trel.q_id, trel.did, tindex.tid, tindex.tsrc, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "140810") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "140810")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query

Hi @rfischmann Thanks, I am actually still looking for a solution. The problem is: I can not repeat the problem on my dev environment nor on other my websites. But I am checking for other developer experience and making tests. This error log message should not make problem on a basic plugin functions. Do you see some problem? Thanks.

No worries, thanks, just keeping you posted on this. 😉 And nope, I’m not seeing actual problems with the plugin’s functions.

Hi @rfischmann Thanks for that, feedback is very important. I will write here as soon as I find the solution.

Just pasting new logs from today that might maybe help identify the issue: [09-Aug-2024 11:17:32 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, 0, 0, NOW() FROM ( SELECT trel.q_id, trel.did, tindex.tid, tindex.tsrc, tbase.token FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "149114") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "149114")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [09-Aug-2024 11:17:35 UTC] WordPress database error Duplicate entry '1028694-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1028694", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by bulk_edit_posts, wp_update_post, wp_insert_post, wp_after_insert_post, do_action('wp_after_insert_post'), WP_Hook->do_action, WP_Hook->apply_filters, wp_save_post_revision_on_insert, wp_save_post_revision, _wp_put_post_revision, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query

Hi @rfischmann Thanks, Rafael, I will take this into an account!

Just a heads-up, @epsiloncool : those errors are still spamming my PHP error log. A few recent entries: [01-Oct-2024 17:24:08 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "241773") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "241773")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [01-Oct-2024 17:24:10 UTC] WordPress database error Duplicate entry '1038201-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1038201", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00"), ("1038200", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Posts_Controller->update_item, wp_after_insert_post, do_action('wp_after_insert_post'), WP_Hook->do_action, WP_Hook->apply_filters, wp_save_post_revision_on_insert, wp_save_post_revision, _wp_put_post_revision, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query [01-Oct-2024 18:15:35 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN...

Here’s another one I just caught in another debug file: [05-Oct-2024 12:13:47 UTC] PHP Deprecated: uksort(): Returning bool from comparison function is deprecated, return an integer less than, equal to, or greater than zero in /home/macmagazine/public_html/wp-content/plugins/fulltext-search/includes/wpfts_tokencollector.php on line 173

Hi @rfischmann Thank you, it’s also very useful observation. Although “deprecated errors” are not dangerous for work, they need to be identified and fixed in a timely manner. Will be fixed in the next release. Thank you!

@epsiloncool You’re welcome! Thanks.

Just a new heads-up, @epsiloncool , these errors are still all over the place in our logs. Just a few examples: [14-Nov-2024 21:47:22 UTC] WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = '2024-11-15 18:46:32' WHERE option_name = 'wpfts_detector2_message_expdt' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/fulltext-search/fulltext-search.php'), WPFTS_Core->Init, WPFTS_Core->FeatureDetector, WPFTS_Core->set_option, update_option [14-Nov-2024 21:47:38 UTC] WordPress database error Duplicate entry '1047382-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1047382", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Autosaves_Controller->create_item, wp_update_post, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query [15-Nov-2024 09:34:48 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "273594") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "273594")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query [15-Nov-2024 09:35:15 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post...

Sending new, updated ones to help you fix the issue @epsiloncool : [28-Nov-2024 17:44:25 UTC] WordPress database error Duplicate entry '1049991-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1049991", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Autosaves_Controller->create_item, wp_update_post, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query [28-Nov-2024 18:16:05 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "95148293") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "95148293")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query

Besides these that continue, @epsiloncool : [26-Dec-2024 21:02:35 UTC] WordPress database error Duplicate entry '1055885-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1055885", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00"), ("1055886", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Posts_Controller->update_item, wp_after_insert_post, do_action('wp_after_insert_post'), WP_Hook->do_action, WP_Hook->apply_filters, wp_save_post_revision_on_insert, wp_save_post_revision, _wp_put_post_revision, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query [26-Dec-2024 21:03:07 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "95267077") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "95267077")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query I’m also noticing lots of these now: [27-Dec-2024 00:11:54 UTC] WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = '2024-12-27 21:11:04', autoload = 'off' WHERE option_name = 'wpfts_detector2_message_expdt' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/fulltext-search/fulltext-search.php'), WPFTS_Core->Init, WPFTS_Core->FeatureDetector, WPFTS_Core->set_option, update_option [27-Dec-2024...

Hi @epsiloncool , So for a few months, every now and then, we observe quick database connection errors to WordPress which usually last just a few seconds — no more than 1-3 minutes. We see an error very similar to this one: https://www.hostpapa.com/knowledgebase/app/uploads/2017/09/1-6.png While that happens, I don’t see anything weird on my server’s monitoring tools. MySQL stays up, CPU and memory don’t spike… so, up until today, I haven’t looked deeply into it. Today, I’ve decided to carefully search through my logs after it happened again and found the culprit: [14-Feb-2025 18:57:42 UTC] WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE wp_options SET option_value = '2025-02-15 15:56:52', autoload = 'off' WHERE option_name = 'wpfts_detector2_message_expdt' made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include_once('/plugins/fulltext-search/fulltext-search.php'), WPFTS_Core->Init, WPFTS_Core->FeatureDetector, WPFTS_Core->set_option, update_option Any idea why is that and how to fix it? Thank you!

Hi @rfischmann Thank you for this report again! Please could you try to execute this MYSQL query on your website and tell me what does it show? SHOW VARIABLES LIKE 'lock_wait_timeout'; Also, since your DB regularly fails to connect clients, I would check the number of allowed simultaneous connections in the my.ini settings. It seems that the DB is working at the limit of its capabilities and therefore periodically “freezes”. It would be good to look at all the DB settings. They can be obtained by the query show variables like '%'; Although there is a large list. It is interesting that this error occurs on your site and no one else’s. Other clients complain about slowdowns with a large number of administrators. We are fixing this. Thanks!

Sorry for the delay, @epsiloncool . As for the first query, it just showed one result: https://d.pr/i/nKcE23 I believe these are our current general configuration: https://d.pr/i/hpGIYU Thanks!

Were those helpful, @epsiloncool ? The database connection errors continue to happen every once in a while.

My PHP error log is still full of these, every single day: [29-May-2025 13:28:58 UTC] WordPress database error Duplicate entry '1084363-wp_posts' for key 'tid_tsrc_unique' for query insert into wpftsi_index ( tid , tsrc , tdt , build_time , update_dt , force_rebuild , locked_dt ) values ("1084363", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00"), ("1084364", "wp_posts", "1970-01-01 00:00:00", 0, "1970-01-01 00:00:00", 1, "1970-01-01 00:00:00") made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, WP_REST_Autosaves_Controller->create_item, WP_REST_Autosaves_Controller->create_post_autosave, _wp_put_post_revision, wp_insert_post, do_action('save_post'), WP_Hook->do_action, WP_Hook->apply_filters, wpfts_save_post_action, wpfts_post_reindex, WPFTS_Core->MakePostsSync, WPFTS_DB->query [29-May-2025 13:31:24 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query insert into wpftsi_tp (q_id, did, pow, res, ts) SELECT t1.q_id, tbase2.id, t1.pow, if(t1.n > 0, t1.res * (case tbase2.token when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) * (-1) / LOG(t1.n + 1), 0), NOW() FROM ( SELECT trel.q_id, trel.did, trel.pow, trel.res, tindex.tid, tindex.tsrc, tbase.token, tbase.n FROM wpftsi_tp trel STRAIGHT_JOIN wpftsi_docs tbase ON (trel.did = tbase.id) AND (trel.q_id = "593391") STRAIGHT_JOIN wpftsi_index AS tindex ON tindex.id = tbase.index_id ) t1 STRAIGHT_JOIN wpftsi_doctree dt ON t1.tid = dt.c_tid AND t1.tsrc = dt.c_tsrc AND t1.token = dt.c_token STRAIGHT_JOIN wpftsi_index tindex2 ON tindex2.tid = dt.p_tid AND tindex2.tsrc = dt.p_tsrc STRAIGHT_JOIN wpftsi_docs AS tbase2 ON tbase2.index_id = tindex2.id AND dt.p_token = tbase2.token where tbase2.id not in ((select did from wpftsi_tp where q_id = "593391")) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array('pre_get_posts'), WP_Hook->do_action, WP_Hook->apply_filters, WPFTS_Search->index_pre_get_posts, WPFTS_Search->sql_parts, WPFTS_DB->query It’s been many months, so it doesn’t seem these will get fixed anytime soon… 😓