WP site using too much data and got suspended by host

First, you spelled “memorabilia” wrong on the home page!

Second, there are plugins like https://www.ads-software.com/plugins/wp-optimize/ that will remove transients from your wp-options table, either manually or on a schedule and compress the table after.

It’s sort of a tough word to spell. So installing that plugin sounds like it’ll be a good idea. But, right now I can’t get to the dashboard. When I go to https://dukescollector.com/wp-admin/ it gives me the Database Update Required and when I press the update button it takes a while then gives me the ‘Sorry, that didn’t work.
Please try again or come back later.
503 Error. Service Unavailable.’
found at this address:
https://dukescollector.com/wp-admin/upgrade.php?step=1&backto=%2Fwp-admin%2F
What are my options?

• This reply was modified 2 years, 1 month ago by boduke.

Try disabling all plugins, then try updating the DB.

I don’t know how to do anything because I can’t get past that update page. All I can do is log in and then I’m stuck on the error page.

It will be difficult, if not impossible, to perform a database update with such a huge wp_options table. Disabling plugins will probably not resolve this issue, though it should be attempted — using FTP, rename all subdirectories in the wp-content/plugins directory, and that will deactivate all your plugins.

Since your hosting company will not remove data from the table unless you provide the queries (no doubt for liability reasons), it will be up to you to identify the data should be deleted. To do that, you will need access to something called phpMyAdmin, which should be available through your hosting control panel. It will allow you to view the contents of the wp_options table and determine what should be deleted, and hopefully determine which plugin (or plugins) caused this problem.

If you cannot do this yourself, you’ll probably need to hire someone to do it.

Additional note: the 503 message you get is a timeout of the server service. If you have SSH access and wp cli is installed there, you can also use it to make changes to settings. Advantage: you have no timeout there (unless your hoster has one built in as security for SSH connections) To update the database there is a command there:
https://developer.www.ads-software.com/cli/commands/core/update-db/

Also via SSH you can make a backup of the database without the options table:
mysqldump -u username -p dbname--ignore-table=dbname.wp_options > dump.sql
See manual: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_ignore-table

You have to adjust the values in the command based on the information in your wp-config.php. Using phpmyadmin or another graphical database tool provided by your hoster would of course be possible as well.

The already mentioned check of the contents of the wp_option via phpmyadmin would be what I would also recommend as the most obvious.

If you need support that your hosting support can’t give you, you can surely find someone here: https://jobs.wordpress.net/

Hello. So the question is about the deleting transients in the database.

First off, if you have 327 gigabytes of transients, then, yeah, you have a problem.

Second off, transients are meant to be temporary in theory. In theory, you should be able to just delete them all. Your host sounds willing to do this, or help you with this. So just tell them delete all the transients. Refer them here if you need to.

Host: If you read this, delete every row with an option name that contains _transient. It’s really that simple.

If you prefer: DELETE FROM wp_options WHERE option_name LIKE '%\_transient\_%';

Make sure they get backups, of course, in case anything was wrong. But yes, delete all the transients. WordPress will rebuild the ones that it needs.

That’ll do for short term, but for long term, you need to find out what’s creating these transients in the first place. Most likely, it’s a plugin. Most likely it’s a bad or otherwise broken plugin. So figure out which one’s causing it, then remove it.

Samuel Wood, your response seems to be the easiest solution. Thank you. I sent your message and this is the response I got. Should I have them delete without a backup?

Thank you for contacting Hostgator support.

I apologize for any problems you experienced which led to you to contact live support, however I will be happy to assist you. Please note that due to the size of the database “327 GB” we would not be able to make a backup of it prior to performing the requested action of permanently removing all rows containing “transient” data from the database wadjaq0a_WzUdgwiMX3lOOSSG. Once we remove the data it would not be recoverable, please confirm if you still want us to proceed with this request with the knowledge that we will not be able to make a backup of the database.

If you have any questions or concerns, please don’t hesitate to contact support via online chat or calling 1-866-96-GATOR or 713-574-5287. We are more than happy to assist you at anytime. Our associates are available 24 hours a day 7 days a week.

Should I have them delete without a backup?

It’s a risk. Without a backup, if something goes wrong when they run that query, you lose ALL the website settings and text content. Do you have any recent database backups (i.e. .sql files less than a few GB in size) you could rely on in an emergency?

I don’t. I’m more or less learning as I go. But I think it’s my only option.

One more thought on this:
You could also export the options table via console WITHOUT the transients. I think in the simplest case a CSV comes out, but better than nothing.

mysql -u dbuser -p dbname -e 'SELECT * FROM wp_options WHERE option_name NOT LIKE '%\_transient\_%'' > dump.csv

I would also recommend to do it at the console because of the table size and a possible timeout if you do it via web.

After that you could empty the whole table (possible in MySQL via TRUNCATE).
And then import the exported CSV with the contents back into the table.

This sounds like a good option, but I have no idea what it means.

Ask the hoster (as already mentioned above) or look for someone who can help you with this, e.g. here: https://jobs.wordpress.net

So Hostgator was able to remove all the transient files and get the site back up and running. I am able to log it and it looks like things are good. I’m looking into installing https://www.ads-software.com/plugins/wp-optimize/ to automatically delete the transient files as Steven suggested and I will be correcting the spelling of memorabilia. Is there a way to see which plugin is causing the file size issue? This is the latest message I got from hostgator customer support:

I have completed importing the repaired wp_options table into your database with the excluded "transient_data" entries and verified your site is loading as well as the admin page. 

After viewing your site It appears a plugin is creating these large _transient_feed_ which is referencing all 446 posts in each of them. 
I suspect it is related to one of your fee plugins listed below which you may either want to disable/delete or ensure settings are properly configured. 
#9   [  Active] RSS Feed Widget                  Version 2.9.1        (File: rss-feed-widget/index.php)
#10  [  Active] Smash Balloon Instagram Feed     Version 6.1          (File: instagram-feed/instagram-feed.php)
#14  [  Active] WPZOOM Instagram Widget & Block  Version 2.0.12       (File: instagram-widget-by-wpzoom/instagram-widget-by-wpzoom.php)

Since importing your wp-options table back into your database, the table has already grown to 55MB while I was browsing the site, this is exclusively due to _transient_feed_  entries. 

Since we did not have sufficient space to backup your original wp-options table, I have it temporarily saved on the server where myql is unable to read it, but in the event we need to revert the changes I made to the table we could perform this entire process again. To allow you time to review your site, I am setting this case to reopen in 7 days in order to remove the 330GB backup of your original wp-options table from the server.

Great news that you are making progress. To identify which plugin is creating those transients, you should either a) use the wp-optimize plugin to scan the database and identify the prefix of the transient name (e.g. sbif_transient_feed could be associated with the Smash Balloon Instagram Feed), or b) scan the database, note the number of transients, disable 2 of those 3 plugins, browse some of the pages and posts, and check to see if that enabled plugin is the one that is causing the transients to increase in size.