DB Customization Manager

[DawPi]

File Name: DB Customization Manager

File Submitter: DawPi

File Submitted: 23 Oct 2009

File Category: Maintenance

Supported Versions: IP.Board 3.1.x, IP.Board 3.2.x, IP.Board 3.3.x, IP.Board 3.4.x

After years of running a forum and performing version upgrades, converting from another forum software, installing mods, etc., your forum's database can get cluttered with extra unused junk. While this extra data doesn't typically cause any real problems with the functionality of your site, it does take up some extra room and can have a minor impact on performance.

What this application does is looks at every application installed on your forums and determines what it thinks you should have in your database for a variety of objects, then compares it to what you actually have in there, and it gives you a report of what extra stuff you have. You can then add Notes to these items to let yourself or other Admins know whether it should be kept, you can query the database for info about the particular item, and you can quickly delete the items you find. Remember that there is no Undo for deleting items, so only delete stuff you are sure you don't need!

The tool can scan your database and find the following extra types of information:

• BBCodes
• Caches
• Help Files
• Hooks
• Public Language Bits
• Admin Language Bits
• Media tags
• Settings
• Setting Groups
• Skin CSS
• Skin Replacements
• Skin Templates
• SQL Fields
• SQL Tables
• Tasks
  

here to download this file

[Michael]

Woops, I left the Public Title on for this app, but it's an ACP-only app. Re-uploaded a new version of this to fix that. If you mistakenly got one of the first couple versions and you now have a 'Download Manager' tab on your forum, just edit this application from the Manage Applications & Modules page and remove the text in the Public Title box, then save that page.

[Gavrisimo]

Nice Michael! ;)

Im planing to test this tommorow, cant wait! I have around 170 tables atm... :o

[mld11]

My Review :)

Overall, great application. There are only a few bugs that I have noticed:
The rest worked great. I deleted all my help files, and the new ones finally appeared. Man do I feel dumb now... lol

[*] Find extra Setting Groups - Claims the the IP.Shoutbox hook groups are extra groups (Hook: Global Shoutbox and Hook: Active Users) [*] Find extra SQL Tables - This does not allow the end user (me) to drop the extra tables. Upon running the tool again, they are still there. Knowing this, I attempted to manually execute the sql query (DROP TABLE table_name), and was presented with the error: Sorry, those queries are not allowed for your safety. I ran the same query through phpMyAdmin, and it worked just fine.

[Michael]

1. Those actually are extra, there's an issue with those settings from the Shoutbox. Try uninstalling the hooks for the Shoutbox, those settings will still be there. They are safe to remove using this tool. Be sure to re-add your Shoutbox hooks if you removed them.

2. Already caught that issue and is fixed for the next version. It was a bug with me not checking the table prefix.

[Gavrisimo]

I just installed this. Few things:

- "Find extra Language Bits" i get this when i click on it:

Fatal error: Maximum execution time of 30 seconds exceeded

- "Find extra SQL Fields" i get this when i try to delete all 46 fields it found:

An error occurred: undefined

Otherwise it worked great! I deleted all other stuff that i didnt need! ;) Good job!

ps during install i went just like i did for all other applications, with that option "Skip blabla" and it gave me error that it couldnt execute some sql statments, so when it finnished i uninstalled it and started install process again but now i have chosen "Overwrite blabla" and everything went ok.

[Michael]

With the language bits, it is by far the most resource intensive tool there is. I don't know that there's much I can do to deal with that.

With the SQL fields, did it actually delete them? If not, what happens when you try just individual ones? I may have already fixed this as a problem with the SQL prefix like above.

I'll look into the issue with the installation.

[Gavrisimo]

Same thing when i try to delete and individual field.

It does not get deleted, it is still showing in that list.

I upgraded from 2.3.5 where i also used this mod, so i had all tables in database i guess.

For language bits, maybe you could load one file at a time, then start again for next file and so on so you dont get that "Maximum execution time" error. :)

[Michael]

Yeah, it's probably an issue with the table prefixes, wait for the next release it should fix that. I'm just waiting on IPS to respond to a couple bug reports before I release this next version.

For the language files, probably the best I can do is create separate tools for public and admin languages, if I divide them up by file like you suggested I'd need to have you run all of those files separately, that's like 50 or more separate files to run.

[Gavrisimo]

Ok, i will wait. ;)

But cant you make it run automaticly? Like it grab first file, when it finnish it grab another? Ofc i didnt mean to manually click run for all those lang files... :huh:

[Michael]

It's not so easy to carry over stuff that might have been found in one file onto the next page. Once it finishes one file, it needs to retain what, if anything, it found and then send that data to the next item in the loop so that at the end it can display everything it found. I think if I just do the separate admin and public languages that it'll be fine, I'm surprised it's taking over 30 seconds for all of them as it is, it only takes about 3-5 seconds on my site.

[Michael]

OK, the next version should have separate tools for admin & public language packs. There was a surprising number of changes needed to make that work. But the upgrade will make all of the changes necessary to make this happen, including retaining any existing languages notes that you may have saved.

The error regarding issues when installing where you chose the 'skip' option should be fixed in the next version too, I wasn't accounting for folks who still had the tables for this mod in their databases.

[Michael]

OK, I released an update to this to version 1.0.0. Changed in this version are the following:

• The check for extra language strings has been split into 2 separate checks, one for public and one for admin strings, to reduce the resources usage on this intensive task
• Fixed bugs with not being able to drop SQL columns and/or tables due to table prefixes
• Added an overall count of the number of found and noted entries to the overview page
• Added text showing which tool was run on the results page
• Since the SQL fields tool only works with MySQL, it's now hidden if you are using a different driver
• Hard-coded a few caches as being needed by IP.Board/IP.Blog even though they don't show up in the designated place for such required caches
• Added code to deal with the unusual file structure of IP.Subscriptions installation file, so that it's tables do not show up as extra
• Fixed hard-coded references to MySQL installation files
• Fixed issue with table prefixes and columns/tables added by hooks

Upgrading is the same as any other IP.Board app: upload the new files, visit the Manage Applications & Modules page, and click the link to upgrade.

I theory, anything this finds at this point should really be extra. I know there are some issues with settings on IP.Shoutbox hooks, it will probably find some extra settings for that even if you are up to date. My advice is to uninstall the 2 IP.Shoutbox hooks, run this tool and clean out anything related to those hooks, and then re-import the hooks. If you have any questions about anything else this tool might find, post it here and I'll do my best to help. :)

[Gavrisimo]

Hmmm i upgraded and tested evrything. "Find extra SQL Tables" didnt work in last version, i just ran that option and i again found all talbes, which i again deleted and they are not deleted. :)

"Find extra SQL Fields" is still buggy. I ran that and had 46 fields. I selected them all to be deleted. "Loading..." element poped out and i went to wc. Few minutes after it was still "Loading..." and then i clicked on "View Cleanup Tools" on left side of the screen and again i got that "An error occurred: undefined".

Then when i clicked again on "Find extra SQL Fields" it was printing some error, i didnt copy that text, it was echoing it before html so text was all weird like all big and stuff, i just clicked again on "View Cleanup Tools".

I gave it a few minutes, then clicked again on "Find extra SQL Fields" nad it found 9 fields. I tried to delete them but again infinitly loading which i canceled with click on "View Cleanup Tools" after which i was served with "An error occurred: undefined".

I did that again, but now it showed me that there are 11 fields... :huh: Again same everything as before.

I run it again, now showing 9 fields... :huh:

I run it again, this time 8 fields...

So everything except "Find extra SQL Fields" is working for me... :)

[Michael]

Is anyone else having problems with that tool? It works fine for me at this point.

[Gavrisimo]

Ehhh i just saw this deleted my IP.Content database... :)

[Michael]

From the mod's description:

Remember that there is no Undo for deleting items, so only delete stuff you are sure you don't need!

[Gavrisimo]

I know, it was only a testing database, so not a problem... :)

[Michael]

It's worth noting that along those same lines, this mod will also find anything like custom profile fields as being extra too, as they are changes to the SQL table structure, so again, exercise caution when choosing to delete stuff.

[dyelton]

I just installed this on my board that has the application iContact Form. Unfortunately I get this error when running your program:

System Error
Could not find Setup directory for the 'iContact Form' application.

Is there anyway we can bypass and use it even though it can't find the setup directory for an installed application?

[Michael]

Create a /setup/versions/install/sql/ folder in that application's folder.

[dyelton]

Create a /setup/versions/install/sql/ folder in that application's folder.

That worked! Thanks!

[MindTooth]

Not running any IP.Board forums atm., but this looks like a must for everyone. Even this should be build in. So that every user if IP.Board could clean their database after some time.

Birger :)

[MindTooth]

Found an old backup I have now upgraded to IP.Board 3.0. And I wanted to try this. However, I could find a way to uninstall this, or at least, I don't know how to. Or if this will leave behind some trash.

Please provide a portion of your documentation on how to remove your addon. Since I guess users want to clean their board, and no install another addon which is hard to remove.

Thanks for a great addon though ;)

Birger :)

[Michael]

You would remove it just like you'd remove any other IP.Board 3.x addon: by uninstalling it from the Manage Applications & Modules page and then deleting the files you uploaded. I don't think it's necessary to include that, especially since there's no real reason to uninstall this after you use it.