Table of Contents

Introduction

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.

Terminology

Conversely:

And by design, we have that:

Thus:

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

The Procedure, Briefly

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.

Step-by-Step Guide

mkdir clean
cd clean
// 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");
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.

Make sure that you have dumped all your regions to OARs and all your users to IARs. Any asset that is not in the OARs or IARs will be deleted from the database.

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.

Example Run

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.

Contrast to Alternate Methods of Asset Handling

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:

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.

Further Work

Code

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";
 
?>
1)
However, it is possible that an asset is referenced inside the script, so we must also search scripts within OARs and IARs for references to assets and not delete them.