rss_admin_db_manager: Database management in Textpattern

Bloke · 2020-07-10 23:56:10

Can’t find the original plugin thread so I’ll post this here since I’ve taken over this plugin and overhauled it for Textpattern 4.8.

Latest release (for Textpattern <= 4.8.5)
Diagnostics panel version (for Textpattern 4.8.6 and higher ONLY).

As always, raise an issue if you spot anything going sideways. Hope this update proves useful.

Last edited by Bloke (2021-04-20 12:51:08)

michaelkpate · 2020-07-11 02:09:23

[ORPHAN] rss_admin_db_manager – database backups and more

I searched for it and then went to the last page of the search results.

colak · 2020-07-11 05:23:23

Looks good and I like it that for tables which can not be repaired the repair link is missing. I nevertheless had a minor issue first time I used it when I clicked the backup on such a table. I’m wondering if it would be easy for those tables to have the word repaired but unlinked.

Bloke · 2020-07-11 08:18:28

Ha, thanks Michael. I searched in the Plugin Support thread section, which is why I didn’t find it and forgot about the archive in my 1am stupour. Should we:

a) Move that thread out of the archive and bring it back here, then merge this topic with it.
b) Rebrand this as smd_db_manager and rename this thread then leave rss_admin_db_manager behind?

I don’t mind either way.

colak wrote #324422:

I nevertheless had a minor issue first time I used [the repair link] when I clicked the backup on such a table. I’m wondering if it would be easy for those tables to have the word repaired but unlinked.

Here’s the thing. I tried that set of ‘actions’ with pipes between them. Looked great until you go to mobile, then it shows this:

Repair |
Backup |
Optimize
| Drop

which is ugly. So I went without, but don’t care for the fact the links are closely spaced now. I could go back to 4 individual table cells with redundant column headings.

Either way, I don’t think ‘repaired’ is the right message. Because for an InnoDB table, it might well be corrupt. Two things we could do here:

Only link the Repair button if the table has errors/overhead. So you can’t repair a table that is showing green/0B. AND show the Repair text (similarly unlinked) for InnoDB tables all the time.
Try and enable InnoDB tables to be repaired manually (because MySQL doesn’t support REPAIR TABLE for InnoDB).

The latter is very labour intensive as it involves (at minimum) cloning the table structure to a temporary table, cloning the data into it, truncating the original table and then moving the data back and deleting the clone. And it’s recommended that the MySQL service be stopped while this happens, which of course we can’t do in a live Txp environment or the admin side will stop working!

Lots of things could also go wrong there, and there’s still no guarantee it’ll fix it. Mind you, there’s no guarantee a repair operation will work on a MyISAM table either if it’s very corrupt. There are so many things that can go wrong with InnoDB tables and repair is usually incredibly labour intensive at the command line.

So I dunno really.

colak · 2020-07-11 09:20:31

Admittedly I have no experience with InnoDB.

Is there a way to detect and serve different options or will it be too convoluted?
In my setup at least, I like option 1:)

Bloke · 2020-07-11 09:39:49

colak wrote #324425:

Is there a way to detect and serve different options or will it be too convoluted?

It does that now. If engine = InnoDB, then omit Repair link.

I’m not sure if adding an unlinked ‘Repair’ text to InnoDB rows implies that it might be repairable, like the other tables are. That’s misleading as the text will never become a link in this case. It does, however, keep everything lined up nicely.

The other option is to entirely omit the Repair link unless the table has overhead/errors. That means repairable tables stick out a bit more by having one extra action. Is that enough to reduce the clutter and make it less likely for you to mistake the word ‘backup’ for ‘repair’? :)

colak · 2020-07-11 16:13:47

Bloke wrote #324426:

The other option is to entirely omit the Repair link unless the table has overhead/errors. That means repairable tables stick out a bit more by having one extra action. Is that enough to reduce the clutter and make it less likely for you to mistake the word ‘backup’ for ‘repair’? :)

I do not think that I’ll make the mistake again:) The issue was with an unrepairable table with an overhead and my conditioning. I have to admit that I am a bit of a db_manager addict. I visit it once a day and repair all tables that need and can be repaired.

I do a backup once every 15 days which I alternate with an SSL one. These backups become more frequent when I add content or change the presentation.

I very seldom need to use the drop table and I only do so to delete remnants of uninstalled plugins or updated plugins that build new tables after they back-up the old ones.

I do not remember ever optimising a table.

Omitting the repair link for InnoDB and all other tables that do not need repairing may be a good idea.

Another option could be to text-align:right the particular tds … for desktops.

colak · 2020-08-16 17:18:05

I just noticed a little display glitch when after clicking on the txp_prefs from within the plugin. The page that loads is much wider than the browser width. Similar glitches appear on all the links.

phiw13 · 2020-08-17 00:32:17

yeah, any of those page have display “glitches” to some extend. for some it is less visible because the db table has only a few columns. other db tables, textpattern for example, have many columns and longish content with result in a huuuge HTML table. I see the computed width for that table being a small 5500px.

looking at the HTML code, there is a set of nested tables, none of which are admin theme friendly.

Bloke · 2020-08-17 00:50:42

The markup’s ancient on the SQL results page. If anyone would like to suggest some more admin-theme friendly containers, please do so and I’ll implement it.

phiw13 · 2020-08-17 01:33:10

Bloke wrote #325402:

The markup’s ancient on the SQL results page.

Yeah, I thought so… and I didn’t’t imply otherwise

If anyone would like to suggest some more admin-theme friendly containers, please do so and I’ll implement it.

First pass

look for the  comment where the important stuff is.

that is based on the txp_user SQL table (was wide enough, enough columns and easy to clean up out of my local playground). most inline CSS is removed, with the exception of that red and blue text under the textarea. we don’t have core classes that matches exactly that meaning I think.

PS – the HTML structure is based on the Plugins panel.

Last edited by phiw13 (2020-08-17 01:35:55)

Bloke · 2020-08-17 08:48:04

Thank you so much. I’ve grabbed the code and will fold the wrappers into the plugin.

Things do still go wonky when viewing some table content because they have Textile/HTML markup in them which gets rendered or matching style rules get executed. Not a lot we can do about that unfortunately. But hopefully constraining the content to a scrollable table will at least help.

Last edited by Bloke (2020-08-17 08:50:01)

Textpattern CMS

Textpattern CMS support forum

#1 2020-07-10 23:56:10