Are there any shortcuts can be taken by manual table deletes

Ready_Eddie · Last edited by Ready_Eddie on Wed Jan 06, 2010 4:39 am; edited 1 time in total

A bit ago I decided to delete one of my workgroups and start over with it.

So I did a remove workgroup. That was eight hours ago, and my drives are still clacking away. I see a "delete wgparts16.* from wgparts16" with a "left join on wgheaders", etc, in progress on a connection. wgparts16 is 14Gig big, which is why I'm trashing it and starting over.

Is there a way one can blow away the wgparts table files and have NMP repair the wgheaders, etc, more simply? I seem to recall you could substitute empty wgparts tables and then have wgheaders cleanup via repair.

The larger general question is are there any shortcuts users can take by manually deleting/manipulating tables?

administrator · Posted: Tue Jan 05, 2010 1:25 pm Post subject:

Yes, but you'll need to issue SQL queries in order to do it. Since the segments table is named "wgparts16", that tells us that you want to remove Workgroup ID 16. To do this, issue the following queries:

Ready_Eddie · Posted: Wed Jan 06, 2010 3:34 am Post subject:

Excellent! My Slow Query Log says the "delete wgparts16.*" took 74573 seconds, or 20 hours 43 minutes. Looks like a couple queries later NMP gave up the ghost, at least it was gone when I came home. The workgroup, headers and parts table file are still there, but all the message parts are missing in the properties, as expected.

I don't want to do that again, I'll try your queries.

What does a database sweep actually do?

I often use MySQL Administrator to do table repair and optimize. Does the NMP repair and optimize do the same (for all the tables), then does the sweep address inter-table issues?

By the way, I had to dig to find how to do a database sweep. Searching the wiki shows that the word "sweep" is never used.

administrator · Posted: Wed Jan 06, 2010 11:27 am Post subject:

I suspect MySQL ran out of memory trying to run that query. Since you say that the parts have been removed, then that's the worst of it.

Removing a large Workgroup is problematic using NMP unless you delete and purge all the messages first. I'll make a note to review this process to see if there might be ways to improve the process.

The database sweep checks for orphaned data between tables.

NMP's repair facilities are a subset of what is available using MySQL Administrator. For example, NMP cannot repair a corrupted table header while MySQL Administrator gives you that option. For optimizing tables, NMP uses the same commands that MySQL Administrator uses.

Regards