[Opensim-users] Database cleanup

[Karen Palen]

That did the trick, - thank you!

However, a closer look reveals that the inactive regions are only a small part of the problem :-( Only about 2Mb of the 600Mb total!

500Mb is devoted to assets - 9500 of them! Over half textures and a great many duplicates (to judge from the descriptions anyway).

I don't see any easy fix by inspection, since the assets depend on other tables to identify if they are used.

Karen

--- On Wed, 12/23/09, Michael <mikeeab at gmail.com> wrote:

> From: Michael <mikeeab at gmail.com>
> Subject: Re: [Opensim-users] Database cleanup
> To: opensim-users at lists.berlios.de
> Date: Wednesday, December 23, 2009, 12:21 AM
> Inactive regions are pretty easy to fix.
> But, as always, a backup is recommended before poking that
> thar bear with a stick. :)
> Ya just need to take your favorite database
> tool, and connect to your OpenSim db. First you'll need
> the UUIDs of any inactive regions. If you no longer have a
> record of this somewhere (say, in an old XML or INI region
> file) you can compare the UUIDs from your active regions to
> the results of the following query, and take note of the
> ones that are dead:
> 
> SELECT RegionID FROM estate_map;
> Looks to me like there's only really two
> tables to worry about here named "prims" and
> "primshapes". We'll start with safe queries
> that don't actually change anything, so you can get a
> feel for whether this is worthwhile for you. We'll also
> only do one stale region for the sake of simplicity.
> Let's start by seeing just how many rows you might be
> able to remove. Run this simple query:
> 
> SELECT COUNT(*) FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> Replace <OldRegionUUID> with the inactive
> region's UUID. That query will return a number
>  equaling the number of stale prims sitting in that
> table.
> 
> That's the prims table, which is easiest
> since each prim has a RegionUUID column. The primshapes
> table doesn't, so we'll have to use the prims table
> to figure out which shapes are stale. The following query
> will do this for you:
> 
> SELECT COUNT(*)    FROM
> primshapes    WHERE EXISTS  
>  (SELECT * FROM prims WHERE  
>  prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
> 
> This *should* return the exact same number for
> primshapes that that you got from the query to the prims
> table. If you do not, stop right here, and don't make
> any changes to your database. If both counts are the same
> though, you now know how many rows you can remove by
> combining the two counts. So now we'll remove those
> rows. Make sure you have that backup before the next
> steps.
> 
> Now we'll remove the stale prims from the
> primshapes table. It's important to do this with
> primshapes before modifying the prims table. The following
> query will do so:
> 
> DELETE FROM primshapes    WHERE
> EXISTS    (SELECT * FROM prims
> WHERE    prims.RegionUUID =
> '<OldRegionUUID>'    AND
> prims.UUID = primshapes.UUID);
> 
> Finally, the query to remove them from the prims
> table:
> DELETE FROM prims WHERE RegionUUID =
> '<OldRegionUUID>';
> And there you have it. These queries removed
> over 9000 rows from my little OpenSim database. You can also
> remove those stale region UUIDs from the estate_* tables,
> but that should be pretty easy to do via a GUI of your
> choice since these tables tend to be very small in
> comparison to the prim tables. I just left them out of sheer
> laziness... they're a single row, so the space they take
> up is insignificant.
> 
> Marcus Llewellyn
> 
> On Tue, Dec 22, 2009 at 6:27 PM,
> Karen Palen <karen_palen at yahoo.com>
> wrote:
> 
> I
> definitely have the remove/replace region problem. I suspect
> that I have more than that too though :-)
> 
> 
> 
> Karen
> 
> 
> 
> -----Inline Attachment Follows-----
> 
> _______________________________________________
> Opensim-users mailing list
> Opensim-users at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-users
>