Better database maintenance

[discobot]

not sure about this since i’m not familiar with how DBs work, but garbage in the database as a result of removed plugins, options, etc. has always bothered me

i’m thinking like auto-cleanup, compacting and removing dead options

---

Read-only archive: https://petitions.classicpress.net/posts/218/better-database-maintenance

Author: classyMan

Vote count: 4

Status: open

---

Comments

How would the code know which options are dead, and how would it know which options belonged to which plugin? I think if this was feasible with the current core/plugin architecture it would have been done already.

~ posted by zigpress

i’m not a db wiz so i can’t provide a technical answer, and maybe orphaned options can’t be cleaned, but i’m thinking of various other things like Advanced Database Cleaner - some of this isn’t doable because it would require human review, but much of it may be

~ posted by classyMan

As @zigpress noted, there’s no way for ClassicPress to know what is a dead/orphaned option. Unfortunately, we must rely on developers to clean up after themselves…which they often don’t do, under the excuse of “you just may reinstall the plugin someday.”

In case you’re not aware, ClassicPress does have the internal database optimization/repair tool which may help with at least some cleanup.

See: Editing wp-config.php – WordPress.org Documentation

~ posted by Code Potent

Optimizing the database is not a good idea, as it can easy corrupt the DB. it’s best to leave the garbage (those plugins leave behind) in place.

Even a minor entry that appears harmless, if removed, can have irreversible effects. Based on my own experience and client experience, I would not recommend this.

If CP already has this tool built in, then it is already well thought out and I think would be sufficient.

~ posted by Avrom

This is something I have been thinking about today.

I need to write down the logic I have in mind in order to design the blueprint.

We need a stand-alone table that logs

• what has been installed and when (datetime)
• which files have been added
• which meta data and on which table exactly
• whether it’s currently active or not

These data should be at avail in a unique subpage, for example under Settings so users can navigate in there and check which data are considered orphaned or abandoned.

It’s not an easy task to implement, it’s quite challenging…but we desperately needs this.

~ posted by stefanos82

The WP Optimize plugin provides the ability to clean out a lot of the expired or irrelevant gunk.

~ posted by KTS915

What do you mean, “options”? Actually, everything being asked for in this thread IS doable, first, as a plugin in ClassicPress.

I’m not the guy to program it but I’ve worked a lot with databases, primarily mysql. Everytime a plugin is installed or uninstalled or “options” are modified – or deleted, the database tables themselves are WRITTEN into by standard sql queries. Depending on the change made in the Admin panel different queries are employed to update particular values in dedicated fields in dedicated tables.

Routines could be written when such a plugin (as discussed in this thread) is activated to create a new table that tracks the time when certain queries are initiated as the Administrator "adds options, or activates or deactivates plugins, etc.

Internally, also, mysql triggers could set off a series of queries everytime there is a change made to any of the tables and the query results could be saved in this auxiliary table or tables created by this enhanced, reader-friendly LOG plugin.

Then the data is called up in another view in the ClassicPress Admin dashboard and is seachable for different purposes with export custom searches highlighted.

ClassicPress does not have to “know” anything on its own. Anything the Admin does that involves clicking here or there to Save a setting immediately makes changes in the database that mysql already “knows about” and can be scripted to do things all on its own in the mysql server that hosts your ClassicPress tables.

~ posted by TruthMatters

[anon66243189]

There was a similar request here about theme options Add option to clear all theme options

Both should be closed.

The last suggestion in this thread here, mentioning some tracking and additional logs created for each and every action done and taken in CP involving SQL would probably bring down every website using CP.

There are so many SQL interactions In a Dynamic Website or CMS that the log alone of those actions probably would take more space than the entire database would, even if leaving the dead data in there.

It is the job of plugins to clear up after themselves or offer options to do so.
There are plugins not doing that, for example, Elementor. They do not do that because “the user might later decide to install Elementor again and thus the data would be lost if we delete it on uninstall”.

In other words, this is actually both a good and a bad thing to do, and the right thing to do is giving the user the choice - inside the plugin or theme - to delete said data upon certain action taken (checkbox checked, button pressed, whatever)

I vote for closing this as an unactionable task that should live in the respective software and not within the core CMS