Repeating wds_blog_tabs rows bloating wp_options table

Further, it looks like all 68k rows are identical.

Hi @graham-smith

Please email us at: [email protected]
Subject: ATTN: WPMU DEV support – wp.org

Please send:
– Link back to this thread for reference (https://www.ads-software.com/support/topic/repeating-wds_blog_tabs-rows-bloating-wp_options-table/)

so that we could review this case more for you.

Kind Regards,
Kris

Hi again @graham-smith

I consulted your case with our developers and that option is being added/updated using?update_site_optiononly. So there shouldn’t be any duplicates or 68k rows (unless there are 68k subsites). Is this a multisite installation?

Also, the only way we were able to replicate this so far is by removing the index of the option_name column from the wp_options table:
Without that index, a duplicated wds_blog_tabs record will be created on each request (regardless the WordPress installation is single or multisite). If your site receives a lot of traffic, that could explain the situation. We don’t see any evidence of this being a SmartCrawl bug, so in this case, the first measure is to check the database.

Kind Regards,
Kris

Hello Kris,

Sorry for the delay in responding, do you still need me to email support?

Is this a multisite installation?

No, definitely a single, small site, hosted on a VPS.

Without that index, a duplicated wds_blog_tabs record will be created on each request (regardless the WordPress installation is single or multisite). If your site receives a lot of traffic, that could explain the situation.

I think this is Index-related and is the cause of the issue, as I now have found more examples with option names and values from other plugins, but only 2 with 1000’s of entries, the majority had just 2, the ‘WP Offload Media Lite for Amazon S3, DigitalOcean Spaces, and Google Cloud Storage’ plugin had a similar amount to yours, but otherwise the highest was about 70 duplicate rows as much as I can recall.

I took some screenshot examples at the time to upload here but there seems to be no facility to add them to my posts, but to see exactly what the Indexes previously were, I’ll have to check my backups made before.

Since I removed all of the duplicate rows bar 1 unique row of each, and added the correct unique indexes, the table has stopped growing. I also tried manually adding a duplicate row and of course Mysql complained it wasn’t unique so refused it.

Screenshots of the issue:

https://www.dropbox.com/scl/fi/cqyed6f9u90pq41cqt5yn/IMG_2227.jpg?rlkey=zzrrxtqns63r8xr0u9zbqwwzt&dl=0

https://www.dropbox.com/scl/fi/o6nvj731y857wbqn8crvn/IMG_2228.jpg?rlkey=1hdxlovziqvlqats0jem1uodk&dl=0

My current index structure for the wp_options table, which seems to be working okay:

Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment

PRIMARY	BTREE	Yes	No	option_name	741	A	No
option_id	BTREE	Yes	No	option_id	741	A	No
autoload	BTREE	No	No	autoload	4

FYI: related discussion:

https://www.ads-software.com/support/topic/bug-3-2-4-bloating-wp-options-table-with-duplicate-rows/

• This reply was modified 1 year, 7 months ago by The Old Man.

Hi @graham-smith,

Thanks for providing further info and upon further investigation, it seems that the issue arises only when the index of the option_name column from the wp_options table is removed/missing.

Only in such instances, every request leads to a duplicate wds_blog_tabs record being created, irrespective of whether the WordPress installation is single or multisite.

Website traffic could influence the occurrence of duplicate option names related to SmartCrawl in the table due to a missing unique identifier.

Generally, WordPress runs a database query similar to the one below to update the option:

INSERT INTO wp_options (option_name, option_value, autoload)
VALUES ('wds_blog_tabs', '[DATA]', 'no')
ON DUPLICATE KEY
UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

The ON DUPLICATE KEY phrase prevents a duplicate entry. However, with a missing UNIQUE index or PRIMARY KEY on the option_name column, the query will not function as expected.

It’s also possible you might encounter similar instances with other option names and not just specific to SmartCrawl.

I’m afraid, the only way would be to remove all duplicates first, and then update the table with a unique index.

You can refer to the following SQL as an example:

ALTER TABLE PREFIX_options ADD UNIQUE option_name (option_name) 

This seems to be more of an issue specific to the website’s table structure rather than a bug within SmartCrawl.

Please do let us know if you have any further queries.

Kind Regards,

Nithin

Hi @graham-smith,

Since we haven’t heard from you for a while. I’ll mark this thread as resolved for now. Please feel free to re-open the thread if you need further assistance.

Regards
Nithin