[Opensim-users] Database cleanup

[Michael]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-users/attachments/20091223/b30c6900/attachment.html>