[Opensim-users] Database clean up

[Jeff Kelley]

At 7:53 PM +0200 4/6/12, Gudule Lapointe wrote:

> how can I clean up the inventory and assets server, without having
> my users loosing their inventory?

Tough topic.

The general answer is : you can't.

The special answer is : on a small, personal grid, where you have extended
knowledge over your assets, you may try to find assets which:

  - are not referenced in inventoryitems (agent inventories)
  - are not referenced in primitems (inworld objects' inventories)
  - does not belong to Library

Maybe you will find assets you *know* have been deleted, for example old
versions of scripts or notecards.

However, you still get assets referenced inside objects inside inventory
(agent or object), recursively. They live inside SceneObjectGroup blobs and
not directly accessible.


Finally, if you had accounts deleted, you may catch useless inventory
folders and items with:

SELECT * FROM inventoryitems   WHERE avatarID NOT IN (SELECT UUID FROM users);
SELECT * FROM inventoryfolders WHERE  agentID NOT IN (SELECT UUID FROM users);

orphaned folders and items:

SELECT * FROM inventoryitems   WHERE parentFolderID NOT IN (SELECT folderID
FROM inventoryfolders);
SELECT * FROM inventoryfolders WHERE parentFolderID NOT IN (SELECT folderID
FROM inventoryfolders);
(the latter will catch inventory roots, children of NULL_KEY)

dangling inventoryitems:

SELECT assetID,assetType,inventoryName FROM inventoryitems WHERE assetID
NOT IN (SELECT id FROM assets);

objects belonging to deleted regions:

SELECT name,UUID FROM prims WHERE RegionUUID NOT IN (SELECT RegionUUID FROM
regionsettings);

Of course, never blindly DELETE with such queries. They are just a quick
way to check db health after many crashes and upgrades. Mine is 3 years old.

More "useful queries" on the wiki and OSGrid forum.