WordPress database error Duplicate entry

(@rfischmann)

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_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled')) OR (wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled')))
					 
					 ORDER BY  (wpftsi_t.relev) DESC, wp_posts.post_date DESC
					 LIMIT 0, 20 made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, apply_filters_ref_array('posts_pre_query'), WP_Hook->apply_filters, WPFTS_Search->index_posts_pre_query, WPFTS_DB->get_results
[28-Jul-2024 09:30:57 UTC] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home/macmagazine/public_html/wp-content/themes/caards-child/functions/itunes_live_tile.php:294) in /home/macmagazine/public_html/wp-includes/rest-api/class-wp-rest-server.php on line 1831
[28-Jul-2024 09:30:59 UTC] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home/macmagazine/public_html/wp-content/themes/caards-child/functions/itunes_live_tile.php:393) in /home/macmagazine/public_html/wp-includes/rest-api/class-wp-rest-server.php on line 1831
[28-Jul-2024 09:31:01 UTC] PHP Warning:  Cannot modify header information - headers already sent by (output started at /home/macmagazine/public_html/wp-content/themes/caards-child/functions/itunes_live_tile.php:294) in /home/macmagazine/public_html/wp-includes/rest-api/class-wp-rest-server.php on line 1831
[28-Jul-2024 09:31:06 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 = "128391")
    					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 = "128391"))
				 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:31:06 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)) / 1305.5032653379349) 
						+ if(isnull(t3.id) or (t3.n < 1),0, (
									t3.pow + (t3.res / LOG(t3.n + 1)) / 1305.5032653379349
								) *  (case tbase.token  when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) / (5))
						) *  (case tbase.token  when "post_title" then 0.9 when "post_content" then 0.4 when "post_excerpt" then 0.2 else 1 end) / (5)
					) 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 = "128391")
				) 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 = "128391")
				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_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled')) OR (wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled')))
					 
					 ORDER BY  (wpftsi_t.relev) DESC, wp_posts.post_date DESC
					 LIMIT 0, 20 made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, apply_filters_ref_array('posts_pre_query'), WP_Hook->apply_filters, WPFTS_Search->index_posts_pre_query, WPFTS_DB->get_results

What should I do, please?

Thread Starter

(@rfischmann)

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

Plugin Author

(@epsiloncool)

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!

Thread Starter

(@rfischmann)

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!

Thread Starter

(@rfischmann)

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

Plugin Author

(@epsiloncool)

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!

Thread Starter

(@rfischmann)

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?

Plugin Author

(@epsiloncool)

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!

Thread Starter

(@rfischmann)

Sounds good, thanks @epsiloncool! Glad to help.

Thread Starter

(@rfischmann)

3 months, 4 weeks ago

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
							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 = "130500")
    					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 = "130500"))
				 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

There are a few of these.

Plugin Author

(@epsiloncool)

3 months, 4 weeks ago

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!

Thread Starter

(@rfischmann)

3 months, 4 weeks ago

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.

Thread Starter

(@rfischmann)

3 months, 3 weeks ago

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

Plugin Author

(@epsiloncool)

3 months, 3 weeks ago

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.

Thread Starter