Query to run in phpmyadmin

G’day jamesja,

Here’s the basic query from the plugin that deletes all expired transients and their timeout pairs. You’ll need to substitute the name of your options table and the timeout threshold in seconds that you want.

delete from t1, t2
using $table t1
join $table t2 on t2.option_name = replace(t1.option_name, '_timeout', '')
where (t1.option_name like '\_transient\_timeout\_%' or t1.option_name like '\_site\_transient\_timeout\_%')
and t1.option_value < '$threshold'

You can see it in the source code here, and also find other queries that might be useful.

cheers,
Ross

Hi Ross, thank you so much for your reply.

I get this error message in PMA:

Static analysis:

3 errors were found during analysis.

Unrecognized keyword. (near “using” at position 20)
Unexpected token. (near “wp_options” at position 26)
Unexpected token. (near “t1″ at position 37)

SQL query:

delete from t1, t2 using wp_options t1 join wp_options t2 on t2.option_name = replace(t1.option_name, ‘_timeout’, ”) where (t1.option_name like ‘\_transient\_timeout\_%’ or t1.option_name like ‘\_site\_transient\_timeout\_%’) and t1.option_value < ‘1509330263’

MySQL said: Documentation
#1213 – Deadlock found when trying to get lock; try restarting transaction

Any idea what’s going wrong?

Regards

The 3 errors in the static analysis bit probably just means the phpMyAdmin doesn’t understand MySQL delete syntax very well; ignore those.

The actual problem reported is that you’re getting a deadlock. Quite likely, there are so many rows to search for and delete that it’s taking too long and hitting lock conditions with other processes.

If your system can withstand a little downtime, you can try locking the options table during the delete operation, like this:

set autocommit=0;
lock tables wp_options as t1 write, wp_options as t2 write;
delete from t1, t2 
	using wp_options t1 
	join wp_options t2 on t2.option_name = replace(t1.option_name, '_timeout', '') 
	where (t1.option_name like '\_transient\_timeout\_%' or t1.option_name like '\_site\_transient\_timeout\_%') 
	and t1.option_value < '1509330263';
commit;
unlock tables;

cheers,
Ross

Thank you very much.

Just wanted to update that I couldn’t delete from wp-admin due to conflicting settings in W3 Total Cache. I had checked this option ‘Store transients in database’ under Object Cache. After unchecking, I am able to delete from wp-admin properly.

Thanks for the update. I’ve heard of that option in W3TC stuffing up other things too. Essentially it seems to break transient caching. Given that transients are stored in the database unless you have memcached / redis / apt etc. (which are better), I don’t understand what W3TC is trying to achieve there.

Well done on figuring it all out!

cheers,
Ross