One of the major problems of administering OpenSim instances is that users tend to upload assets that may get trashed which eventually end up cluttering the OpenSim MySQL database. OpenSim does not perform a deep-purge like Second Life to identify assets that are unreferenced and remove them from the database. In that sense, an OpenSim database is ever-expanding - once an asset is uploaded, it will never be deleted, regardless whether the uploader deletes the texture along with all the primitives that reference that texture. This is a majorly inconvenient design flaw since assets can stack up quickly on a public grid and incur a considerable performance penalty - making MySQL requests slower and generally slowing down the grid. The procedure described here alleviates the problem of accumulating assets by implementing a deep-purge.

• An asset can be either a texture, sound or animation that is uploaded onto a grid and receives an UUID. We also expand the notion of basic asset to (currently):
  • textures
  • sounds
  • clothing
  • scripts
  • body parts
• When an agent uses that asset - for example stamps a texture onto a primitive, that texture becomes referenced. In other words, OpenSim references that uploaded texture by UUID.

Conversely:

• We name a live asset, an asset that has been uploaded onto a grid and then used, for example: a texture stamped on a primitive, an animation used for an AO, some clothes in some avatar's inventory.
• We name an orphaned asset, an asset that has at some point been uploaded by an agent (or created, such as clothes), and trashed without being used in-world. Please note that this excludes uploading a texture, stamping it on a primitive and then trashing the texture - this is still considered a live texture.

And by design, we have that:

• An OAR and an IAR contains assets that are live. If we were to dump the OARs for all the regions and for all avatars, then the whole contents of the OAR and IAR archives will contain all the assets currently "used" on a grid.

Thus:

• Anything that is not present in a full OAR and IAR export of a sever, is considered an orphaned asset¹⁾.

In essence, we are doing a set subtraction of assets:

• The set of orphaned assets is given by the set of all assets in the database minus the set of assets in all the OARs and IARs exported from the console and minus the assets referenced in the scripts in the OARs and IARs exported from the console.

The procedure involves dumping the OARs of all the regions, the IARs of all the avatars to different archives. For example, for an OpenSim server with a region called Merlin and one single avatar named Kira, we would have a directory containing:

Merlin.oar
Kira.iar

Then we add the script from the Code section to the same directory and run:

php wasAssetCleaner.php Merlin.oar Kira.iar

The PHP script will first build a list of all the assets in the database. Then, the PHP script will process the archives (Merlin.oar and Kira.iar) and build a list of assets - these will be considered live assets. The PHP script will also read scripts from the OARs and IARs and look for assets referenced by UUIDs and add them to the list of live assets. After that, the PHP script will delete any assets from the database that are not in the dumped OARs and IARs - effectively wiping out all orphaned assets.

• First, create a separate empty directory on your server and copy the script inside it:

mkdir clean
cd clean

• Then, edit the script and set the following parameters at the top:

// Hostname or IP of your OpenSim MySQL server.
define("MYSQL_HOSTNAME", "localhost");
// Username of the OpenSim MySQL user.
define("MYSQL_USERNAME", "opensim");
// Password of the OpenSim MySQL user.
define("MYSQL_PASSWORD", "opensim");
// Name of the OpenSim database on the MySQL server.
define("MYSQL_DATABASE", "opensim");

• Next, dump the OARs for all your regions and for all your users into the same directory, so that we have the following files, for example:

wasAssetCleaner.php 
Kira.iar
Merlin.oar
Rum.oar

in one single directly. In this example we have two region OARs: Merlin.oar and Rum.oar and a single user IAR: Kira.iar.

• Now run the asset cleaner:

wasAssetCleaner.php Kira.iar Merlin.oar Rum.oar

you can specify as many IARs and OARs as necessary.

This will let you know how many assets will be deleted at each step and ask you to proceed and delete them. After the procedure has completed, restart the server, clear your viewer cache and log-in.

Here is a console dry-run of the tool in action:

opensim@opensim.local:~$ php wasAssetCleaner.php Merlin.oar Kira.iar 
-------------------- REMOVE -----------------
The following assets will be removed : 
bricks 00000000-0000-1111-9999-000000000001
rocks 00000000-0000-1111-9999-000000000003
...
From IAR f6773b16-2484-4c9e-ae95-359fb67c85d1
Cypress 1 fb2ae204-3fd1-df33-594f-c9f882830e66
---------------------------------------------
172 textures will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
-------------------- REMOVE -----------------
The following assets will be removed : 
UISndPieMenuSliceHighlight3 09b2184e-8601-44e2-afbb-ce37434b8ba1
UISndObjectDelete 0cb7b00a-4c10-6948-84de-a93c09af2ba9
...
UISndObjectCreate f4a0660f-5446-dea2-80b7-6482a082803c
UISndPieMenuSliceHighlight1 f6ba9816-dcaf-f755-7b67-51b31b6233e5
83 sounds will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
-------------------- REMOVE -----------------
The following assets will be removed : 
Shirt 00000000-38f9-1111-024e-222222111110
Pants 00000000-38f9-1111-024e-222222111120
...
From IAR c98b0dd9-cbd3-4cc4-9298-0068efc69d27
From IAR df333523-1e39-488c-a91c-75137029400b
---------------------------------------------
11 clothings will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
-------------------- REMOVE -----------------
The following assets will be removed : 
llBase64ToString 1d4c71d9-b428-11dc-8314-0800200c9a66
llSetRot 220baef9-b376-11dc-8314-0800200c9a66
From IAR f0b9884b-b0fa-4836-8d2e-f20716a73963
From IAR f3903b7c-ad4a-4738-9f5a-24258ab469fb
---------------------------------------------
47 scripts will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
-------------------- REMOVE -----------------
The following assets will be removed : 
From IAR 263cad4e-7d5b-4e07-93da-4334c27e3a75
From IAR 2c5589a8-cd3c-c31f-d24b-305963c7dc16
...
From IAR e91a4824-d0b6-4784-9544-ff68db67ea37
From IAR e9803423-fbe5-42f6-88ec-f988f32be70d
---------------------------------------------
19 bodyparts will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
-------------------- REMOVE -----------------
The following assets will be removed : 
tpose2 01645eeb-8c51-60dc-58cc-b9512f05afc0
...
Handshake ddc2400f-ecdb-b00e-aee7-442ff99d5fb7
From IAR dfa5aca7-b579-4f3b-80fe-54a9100543fd
---------------------------------------------
14 animations will be removed from the database. Are you sure? (y/n) : n
Bailing out. Nothing was deleted.
 
All operations completed!

And after answering yes to all questions:

opensim@opensim.local:~$ php wasAssetCleaner.php Merlin.oar Kira.iar 
Congratulations! Your OpenSim database contains no unreferenced textures.
Congratulations! Your OpenSim database contains no unreferenced sounds.
Congratulations! Your OpenSim database contains no unreferenced clothings.
Congratulations! Your OpenSim database contains no unreferenced scripts.
Congratulations! Your OpenSim database contains no unreferenced bodyparts.
Congratulations! Your OpenSim database contains no unreferenced animations.
 
All operations completed!

Note that the OpenSim injects into the database some default assets - such as the assets you will find in the OpenSim library. It is safe to delete them because they get re-injected when the server is restarted.

Note that the problem is not that an avatar uploaded two different identical assets - the problem is that an avatar uploaded one asset and then deleted it. In this case, all of the following do not seem to work at all:

• An external asset server SRAS is written in ruby for OpenSim that apparently handled de-duplication, compression and others. However it is not exactly clear whether it deletes orphaned assets (recall, orphaned assets are assets that have been uploaded into OpenSim and then the user trashed them). Eliminating duplicates sounds awesome but it still does not solve the issue of orphaned assets.
• Hashing. A very simple concept. Hash two identical slices of data and get the same hash. Very useful to eliminate duplicate assets but still does not solve the asset stacking problem.
• Recording access times of assets - this is one possible avenue we have considered before writing the script, however what if a user uploads a texture and never uses it? Instead, the avatar keeps the texture around in inventory - or worse, in a primitive. This would never work.

The conclusion is that the only reliable way of guaranteeing that all the assets are considered is to dump the OARs of all regions and the IARs of all inventories. That gives us a true image of what is contained on a grid.

• The database query performs a lookup on texture assets - since they occupy the most space, however it is possible to expand the script to include all types of assets (TODO). Done.
• A fork of this script could effectively delete an user from OpenSim, along with all the assets by querying the CreatorID and wiping all the assets of that avatar - provided that they are not live.
• It is further possible to automate this script to make it automatically dump IARs and OARs.
• The problem with dumping IARs is that they require the user's password to be dumped - this is very inconvenient for public grids. There is a patch available that addresses this issue and disables IAR passwords.

wasAssetCleaner.php

#!/usr/bin/php
<?php
 
/////////////////////////////////////////////////////////////
// Wizardry and Steamworks (c) grimore.org - 2013, License: MIT //      
//                                                         //
// Permission is hereby granted, free of charge, to any    //
// person obtaining a copy of this software and associated //
// documentation files (the "Software"), to deal in the    //
// Software without restriction, //including without       //
// limitation the rights to use, copy, modify, merge,      //
// publish, distribute, sublicense, and/or sell copies of  //
// the Software, and to permit persons to whom the         //
// Software is furnished to do so, subject to the          //
// following conditions:                                   //
//                                                         //
// The above copyright notice and this permission notice   //
// shall be included in all copies or substantial portions //
// of the Software.                                        //
//                                                         //
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF   //
// ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT         //
// LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS   //
// FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO     //
// EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE  //
// FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER      //
// IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING    //
// FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR      //
// THE USE OR OTHER DEALINGS IN THE SOFTWARE.              //
/////////////////////////////////////////////////////////////
 
/////////////////////////////////////////////////////////////
//                    CONFIGURATION                        //
/////////////////////////////////////////////////////////////
 
// Hostname or IP of your OpenSim MySQL server.
define("MYSQL_HOSTNAME", "localhost");
// Username of the OpenSim MySQL user.
define("MYSQL_USERNAME", "opensim");
// Password of the OpenSim MySQL user.
define("MYSQL_PASSWORD", "opensim");
// Name of the OpenSim database on the MySQL server.
define("MYSQL_DATABASE", "opensim");
 
/////////////////////////////////////////////////////////////
//                     INTERNALS                           //
/////////////////////////////////////////////////////////////
 
if(!defined('STDIN')) {
    print 'This script is meant to be run on the command line.'."\n";
    return 1;
}
if($argc < 2) {
    print 'ERROR: Please specify OARs and IARs to filter on the command line.'."\n";
    print 'Syntax: php '.$argv[0]. ' <OAR|IAR|...>'."\n";
    return 1;
}
 
/*
* Connect to the database and grab all the texture UUIDs
* and store them in an array for later filtering.
*/
$connection_ok = mysql_connect(MYSQL_HOSTNAME, MYSQL_USERNAME, MYSQL_PASSWORD);
if(!$connection_ok) {
    print 'Could not connect to the OpenSim database. Please edit the script and make sure the credentials are correct.'."\n";
    return 1;
}
$db_selected = mysql_select_db(MYSQL_DATABASE);
if(!$db_selected) {
    print 'Could not select the opensim database. Please edit this script and make sure the credentials are correct.'."\n";
    return 1;
}
// Now we can get rid of the script name.
array_shift($argv);
 
/* 
 * First thing to do is search all scripts for references to assets. This will ensure that assets 
 * that are referenced inside scripts and that are deleted from an avatar's inventory or not 
 * displayed in-world will not be deleted from the database.
 */ 
$scriptReferences = array();
foreach($argv as $arg) {
    if(!file_exists($arg)) {
        print 'Archive: '.$arg.' does not exist.'."\n";
        return 1;
    }
    $files = array();
    exec('tar -tzf '.$arg.' 2>/dev/null', $files, $ret);
    foreach($files as $file) {
        if(!preg_match('/_script/i', $file)) continue;
        $data = array();
        exec('tar -zf '.$arg.' -xO '.$file.' 2>/dev/null', $data, $ret);
        if($ret != 0) {
            print 'Could not process script: '.$file."\n";
            return 1;
        }
        preg_match_all("/([a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12})/i", implode($data), $referencesInScripts);
        foreach($referencesInScripts[1] as $uuid) {
            array_push($scriptReferences, $uuid);
        }
    }
}
 
/*
 * Now start cleaning assets.
 */
$assets = array( "texture" => 0, "sound" => 1, "clothing" => 5, "script" => 10, "bodypart" => 13, "animation" => 20 );
foreach($assets as $assetName => $assetValue) {
    $query = 'SELECT * FROM assets WHERE assetType='.$assetValue;
    $result = mysql_query($query);
 
    $allAssets = array();
    while($row = mysql_fetch_array($result)) {
        array_push($allAssets, $row['id']);
    }
 
    /*
    * Now grab all the texture UUIDs from the OARs and IARs
    * supplied on the command line for an exclusion list.
    */
    $liveAssets = array();
    foreach($argv as $arg) {
        if(!file_exists($arg)) {
            print 'Archive: '.$arg.' does not exist.'."\n";
            return 1;
        }
        $files = array();
        exec('tar -tzf '.$arg.' 2>/dev/null', $files, $ret);
        if($ret != 0) {
            print 'Could not process archive: '.$arg."\n";
            return 1;
        }
        preg_match_all("/([a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12})_$assetName/i", implode($files), $dumpAssets);
        foreach($dumpAssets[1] as $uuid) {
            array_push($liveAssets, $uuid);
        }
    }
    // Now add the script references to the pool.
    array_push($liveAssets, $scriptReferences);
 
    /*
    * We consider all the assets in the OARs and IARs 
    * supplied on the command line to be live assets.
    * Everything else is considered unreferenced and 
    * orphaned.
    *
    * ORPHANED_ASSETS = (ALL_ASSETS \ LIVE_ASSETS) \ SCRIPT_REFERENCES_TO_ASSETS
    *
    * The same applies to all other asset types.
    */
    $orphanedAssets = array_values(array_diff($allAssets, $liveAssets));
    if(sizeof($orphanedAssets) == 0) {
        print 'Congratulations! Your OpenSim database contains no unreferenced '.$assetName."s.\n";
        continue;
    }
 
    /*
     * List assets to be deleted.
     */
    print '-------------------- REMOVE -----------------'."\n";
    foreach($orphanedAssets as $asset) {
        $query = 'SELECT * FROM assets WHERE assetType='.$assetValue.' AND id=\''.$asset.'\'';
        $result = mysql_query($query);
        while($row = mysql_fetch_array($result)) {
            print $row['name'].' '.$asset."\n";
        }
    }
    print '---------------------------------------------'."\n";
 
    /*
    * Perform some minor liability dumping. Last chance to quit.
    */
    ASK:   
    print sizeof($orphanedAssets).' '.$assetName.'s will be removed from the database. Are you sure? (y/n) : ';
    $in=trim(fgets(STDIN));
    if($in == 'y') goto GO;
    if($in == 'n') {
        print 'Bailing out. Nothing was deleted.'."\n";
        continue;
    }
    print 'Please type either y to proceed or n to quit without deleting anything.'."\n";
    goto ASK;
 
    /*
    * Start to delete unreferenced assets from the database.
    */
    GO:
    foreach($orphanedAssets as $uuid) {
        $query = 'DELETE FROM assets WHERE assetType='.$assetValue.' AND id=\''.$uuid.'\'';
        $result = mysql_query($query);
        if($result) {
            print '-';
            continue;
        }
        print 'e';
    }
 
    print "\n";
    print 'Finished deleting '.$assetName."s.\n";
}
 
print "\n";
print 'All operations completed!'."\n";
 
?>