During development of OpenSim version 0.8.1 a bug was introduced in the code where simulators running on Windows would format xml statements wrong so they became unreadable on simulators running on mono with Linux. The result being that content created on a Windows based grid would not rezz when transferred to a Linux based grid. The odd thing being that the same content will rezz when transferred to an OS X based grid with the simulators running on mono.
The fix was in the OpenSim 0.8.2 development branch a few weeks ago to prevent new content being created with the wrong xml formatting, but the damage was already done as much content had both been created and re-uploaded after the OSGrid crash on simulators running Windows. Because of the very nature of Hypergrid content is being moved around, and the corrupted items are being spread between grids contaminating their asset databases.
To combat the spreading a Perl script was developed and released yesterday, and while this worked for MySQL based asset servers, it had to be reworked for PostgreSQL. – Not so much because the script was compatible by just changing a line of code, but because the way assets is stored on the PostgreSQL version of the OpenSim asset server.
The work-around was to take advantage of capabilities in PostgreSQL, and a badass query was developed that would fix the broken xml directly in the database without having to run external scripts. The query can be run directly in the psql console or a front-end like Navicat.
UPDATE assets SET data = DECODE(regexp_replace(ENCODE(data, 'escape'),'((xmlns:){2,})+', '(xmlns:)' , 'g'),'escape') WHERE "assetType" = '6';
There is a lot going on in this query:
It selects the asset records of type 6, sequentially goes through them by first encoding the content from binary format to textual UTF-8 format, then with the regexp_replace function seeks out the wrong formatting replacing it with the correct format. It then proceeds to decode the textual format back to binary, before it updates the record with the new content, and moves on to the next.
On our database server it looped through about 18500 records in 78 seconds which is about 237 records per second.
Be advised that before you try this at home, be sure to have proper backups of your data in case anything goes wrong and do the testing on copies of your data before you let the query loose.
Since native MySQL does not support the regexp_replace function, the above query cannot be used for the majority of OpenSim grid and standalone owners. It is possible to install a user developed function that has regex capabilities.