Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007350opensim[REGION] OpenSim Corepublic2014-10-10 01:022016-11-24 05:55
Reporteraiaustin 
Assigned To 
PriorityurgentSeveritymajorReproducibilityN/A
StatusnewResolutionopen 
PlatformPCOSWindowsOS Version8.1
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0007350: MySQL Data Base Schemas - Inconsistency in Name and Description Field Lengths
DescriptionProblems occur with assets created in world which allow for longer description(and name() fields than are allowed to be stored in the data base. This means that some scripted items that use the description(and/or name) fields may appear to work until they are persisted, or taken into inventory and reused.

E.g. here is an example of an avatar tracker created in world, in which the Google Docs target form URL is used in the description field. It works since 255 characters are allowed when it is created. But this is truncated to 128 when the asset is persisted... and so a longer description would be truncated to 128 when taken into inventory.

[ASSET DB]: Description 'https://docs.google.com/forms/....' [^] for asset c0943a2d-b63a-474d-a8dc-2f51e34e37ff truncated from 127 to 64 characters on add

Should all these be the same.... note how they differ.. and may be different between MySQL and SQLite too...

name field

assets.name varchar(64)
inventoryitems.inventoryName varchar(128)
prims.Name varchar(255)
prims.description varchar(255)

description field

assets.description varchar(64)
inventoryitems.inventoryDescription varchar(128)
prims.Description varchar(255)
primitems.description varchar(255)

Steps To ReproduceCreate a prim in word. Give it a description that is longer than 64 characters. Wait or trigger backup to ensure prim persists. Note the warning of the description truncation.

Additional InformationThis issue was raised before as Mantis 7288 but also in the context of avatar UUID handling and was marked resolved there since the avatar UUID issue was dealt with. But the inconsistency and field length for Name and Description remains unresolved.

See the database table schemas and migrations for the inconsistent lengths.

Note also the field naming convention varies...

name to Name to inventoryName
description to Description to inventoryDescription
TagsNo tags attached.
Git Revision or version numberr/25337
Run Mode Grid (Multiple Regions per Sim)
Physics EngineBulletSim
Script Engine
Environment.NET / Windows64
Mono VersionNone
ViewerFirestorm 4.6.7 OS 64 Bit
Attached Filespdf file icon fixing the PGSQL adapter.pdf [^] (31,810 bytes) 2015-03-04 01:03
png file icon truncated asset table entry.png [^] (52,634 bytes) 2015-03-07 11:54


png file icon inventory record also truncated to 128.png [^] (98,413 bytes) 2015-03-07 11:55


png file icon asset table field defintion.png [^] (61,179 bytes) 2015-03-07 11:55

- Relationships
related to 0007288closed MySQL Data Base Schemas - Check on name, Description and UUID/Avatar Field Lengths 
related to 0007341new Truncation error storing asset data PGSQL - Robust 
related to 0007476closedBlueWall Prim descirptions truncated to 64 bytes 
related to 0008065new [ASSET DB]: MySQL failure creating asset ... Error: Data too long for column 'description' at row 1 

-  Notes
(0026894)
Gavin Hird (reporter)
2014-10-10 01:04
edited on: 2014-10-10 01:04

I checked to see if the same was the case for the PostgreSQL database table definitions, and it was. So it must be fixed there too.

(0026895)
Gavin Hird (reporter)
2014-10-10 01:09

This is most likely related to 7341, so I added that.
(0026898)
aiaustin (developer)
2014-10-10 08:11

Changed priority to urgent to reflect the serious implications for scripts which use the description field and may fail to operate as anticipated depending on whether they are recently created in memory, persisted or taken to inventory and rezzed afresh.

The data base table fields need a thorough check and need to be made consistent between things stored in world on creation, when persisted to the data base (in all DB types) and when stored in inventory.

There can subtle errors otherwise, as noticed in some scripts that use the name and description fields but get truncated after seemingly running fine on creation (e.g. avatar trackers recording data in Google Docs using a long URL pasted into the description field).
(0027714)
Gavin Hird (reporter)
2015-03-04 01:03
edited on: 2015-03-04 02:46

I have this as part of the crowdfunding pledge to fix PGSQL so hopefully it will be sorted out even faster. Enclosed the pledge description for reference.

(0027717)
aiaustin (developer)
2015-03-04 02:45
edited on: 2015-03-04 02:47

Noted Gavin, when you do the PGSQL version.. it would be good if you can check the MySQL and SQLite schemas and check they are all now consistent and the same. I also worry that some temporary in world versions of objects may allow some fields like description that are longer (255 I think) than what will be stored persistently (was 64 but 127 now from r/24845) These things all ought to be consistent at build time and not truncated silently (to the viewer) on store. This has caused issues with scripts that store data in the description field and stop working after some sim restarts due to losing the URL or whatever is stored in the description field.

(0027718)
Gavin Hird (reporter)
2015-03-04 02:51

As I said checking the MySQL tables are part of the pledge to make sure it is consistent. I suppose we can include the SQLite part too.

I am a bit in a miss over r/24845 because I am sure Oren of Kitely had submitted a contribution that was included in core where all these settings in code were parameterized and it had just to be set one place. If that is the case then r/24845 should be reverted. I'll have to ask him if the can get me back on track on that.
(0027719)
Gavin Hird (reporter)
2015-03-04 03:15

What I meant was that for r/25844 I would expect it to be parameterized and not set in Assetbase.cs.

I suppose for the Assetstore.migrations files in r/24845 it is OK because Posgres can be a bit picky on casting at times, so I suppose it is better to have more control this in the migration files and not rely entirely on parameter settings.
(0027720)
aiaustin (developer)
2015-03-04 04:01
edited on: 2015-03-04 04:10

PLEASE don't revert until the asset record description field is set at 128 length everywhere (versus the previous wrong 64) or we will have more of a mess.

The Data/SQLite/Resources/AssetStore.migrations was not amended when the MySQL and PGSQL ones were... I assume they all need to be consistent? The SQLite one seems to have name and description as 255, whereas name is 64 and description is 128 on MySQL and PGSQL now. A recipe for issues I think?

(0027721)
Gavin Hird (reporter)
2015-03-04 04:08
edited on: 2015-03-04 06:04

I am not going to do that, please rest assure! ;-)

When I said reverted I mean the way and where it was defined and not the field length that clearly needs to be 127+1 chars.

(0027722)
cinderblocks (reporter)
2015-03-04 05:59

The name field should *not* be extended to 127+1. Per the sl spec, an asset name is a 63 byte string.
(0027723)
Gavin Hird (reporter)
2015-03-04 06:04

It is the description field that has been extended, not the name.
(0027724)
cinderblocks (reporter)
2015-03-04 06:16

Yes, I know. I contributed the patch for it. :) but this mantis is suggesting that name be extended too: "Lengthening the name and description fields for assets to 128 would reduce the issue and should be backwards compatible. "
(0027725)
cinderblocks (reporter)
2015-03-04 06:17
edited on: 2015-03-04 06:31

I'm not sure why r/24845 would be reverted? MAX_ASSET_DESC is, in fact, Oren's change and does not agree with the sl spec. r/24844 fixes that, r/24845 corrects the migrations file so the db can adhere as well.

As far as making all db fields consistent, I can happily add a patch that shortens the ones that are too long in sqlite and pgsql, but I'd like the opinion of someone of someone with more dba experience than I do on whether there would be a negative impact by doing so.

(0027726)
Gavin Hird (reporter)
2015-03-04 06:28

Question is does the description length have to be as per the SL spec as most viewers seems to support 128 char, and many grids seem to use that, including Kitley that delivers with 128 char descriptions to many grids from the marketplace.

Shortening a field in PGSQL is usually more traumatic than making it longer.

I think the guts of the mantis is to establish consistency. There is no point in having different lengths on fields that essentially contains the same information, like asset description, Inventory description and so on if the viewers can handle a common field length.
(0027727)
cinderblocks (reporter)
2015-03-04 06:41

I'm not sure about firestorm, but singularity allows 255 char strings for description, (I don't know why) Oren's change made it consistent across the board when it stores the string to the database, but it was half the length it should have been for description.

As long as the const MAX_ASSET_DESC and MAX_ASSET_NAME stay in place, it should be consistently /stored/ in the rows and enforced by OpenSim, there is just extra space in the way the column is defined.

The viewer is only ever going to get the 127 chars because the asset service truncates it before it hits the db table regardless of field size.
(0027728)
aiaustin (developer)
2015-03-04 06:42
edited on: 2015-03-04 06:43

I don't think anyone is suggesting altering the name field lengths Cinders... and I have been asking for ages for the description field to be set from the default 64 to 128 (127+1).. so thanks for finally doing that. Many grids already have that length in their schemas but its not what was in MySQL GIT master.

But there are now inconsistencies between MySQL and SQLite I think? SQLite may still have length 255 descriptions?

And in world build (e.g. in Firestorm) seems to allow 255 characters and then truncate (silently as far as viewer is concerned) on store, leading to errors for those who stored longer data in there, e.g. for script use with something like a LONG URL.

(0027729)
cinderblocks (reporter)
2015-03-04 06:50

Yes, sqlite would still be able to store 255 bytes in a description, but you would have to manipulate that in the db by hand. The asset service truncates it to 128 bytes regardless of what the field length in the schema is.
(0027731)
cinderblocks (reporter)
2015-03-04 09:04

XAssetStore was updated in the last two changesets. i've attached a patch here to fix the description length: http://opensimulator.org/mantis/view.php?id=7479 [^]
(0027732)
Gavin Hird (reporter)
2015-03-04 09:46

It did not quite like the PGSQL syntax at the end there:

:VERSION 5

BEGIN;

ALTER TABLE xassetsmeta MODIFY Description varchar(128);

COMMIT;

ERROR: syntax error at or near "MODIFY"

Also Description had been changed to description in version 3 above.
(0027736)
cinderblocks (reporter)
2015-03-04 10:12

Thank you. Patch has been updated.
(0027739)
Gavin Hird (reporter)
2015-03-04 10:49

I also noted that AssetStore.migrations had an ALTER TABLE with MODIFY statement in it, but MODIFY is not a keyword to ALTER TABLE for Postgres.

http://www.postgresql.org/docs/9.4/static/sql-altertable.html [^]
(0027748)
cinderblocks (reporter)
2015-03-05 19:00

Yes, that was fixed in the patch update too.

Looks like it's all been reverted now though.
(0027749)
smxy (reporter)
2015-03-05 19:19

It was reverted because:

1) It's not needed - *nothing* uses the asset's name and description - it's write-only. In an ideal world, both columns would be dropped, along with the code that writes to them.
2) For my grid, with just shy of 210,000 asset table entries, the migration caused 65 minutes of downtime to perform. For much larger grids, it would have potentially caused hours or days of downtime.

There was a large discussion about this in #opensim-dev, yesterday.
(0027750)
cinderblocks (reporter)
2015-03-05 19:49

I'm well aware of the time it takes to migrate as I migrated 630,000+ rows two nights ago. :) It doesn't change the fact that the code is, once again, not to spec. Anyway, not going to argue the decision. Nie mój cyrk, nie moje ma?py. Just trying to be helpful.
(0027751)
BlueWall (administrator)
2015-03-05 20:52

Hi,

Yesterday, we were starting to get alerts that migrations were taking a long time for the assets, and then it was noted that we are not using those fields. In the interest of preventing many people from doing the migrations we decide to allow the migration version to bump, but do a no-op instead of the actual migration. I reverted everything that touched the database, including setting the MAX_ASSET_DESC back to the wrong value until the details can be worked out.

This needs be worked out in a way that avoids hitting the assets database, as those fields are no longer used.
(0027752)
aiaustin (developer)
2015-03-06 01:24
edited on: 2015-03-06 01:27

I am aware that the migration took a while as my grid migrated to the assets description field being varchar(128) with the recent commits. They have not been altered back by the latest commit.. Really that should have been a separate version to keep everyone in step. I assume that some earlier changes up and down like this is why we have some grids using varchar(64) and others using varchar(128).

I don't understand why you think assets are not being truncated though... I previously have seen many times messages on my grids when they store incoming assets that were longer than 64.

And we know that some odd LSL script errors are due to URLs being stored in the description field and then in a new environment on a different grid they stop working die to the URL getting its end chopped off a sit worked on a varchar(128) grid but then started giving failures on a varchar(64) grid.

See http://opensimulator.org/mantis/view.php?id=7350 [^]

Unless the description is actually not stored in assets at all but in prims, prim items, inventory items, etc.. but even then some code somewhere is imposing a 64 character limit.

(0027753)
aiaustin (developer)
2015-03-06 01:25
edited on: 2015-03-06 04:19

Meanwhile, I have a revision 9 asset store that has assets description varchar(128) which is NOT what the current master code base says! That can lead to all sorts of issues when reporting OpenSim errors and trying to track down bugs.. one of the primary uses of one of my grids. Can those of us that want to keep in step with the code base execute this MySQL: command safely to stay in step?

ALTER TABLE assets MODIFY description varchar(64);

(0027762)
smxy (reporter)
2015-03-06 17:00

Melanie told us that the Name and Description fields, in the asset table, are not read by anything. They are written to, but never read from. Therefore, it makes no difference what size they are or whether any value written to them gets truncated or not. Thus, it was not appropriate to cause significant downtime to perform an unnecessary migration. She said that, ideally, both columns should be dropped, along with the code that writes to them, but that the dropping of them would be expensive too, so a better solution would be to simply drop the code that writes to them.

It's the prims and/or inventory name and description that needs to be the proper length, and fixed, if need be, but not the asset.
(0027763)
BlueWall (administrator)
2015-03-06 17:49

default
{
    state_entry()
    {
        llSay(0, "Script running");
    }
    
    touch_start(integer det)
    {
        llSetObjectDesc("This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long");
        
        llSetTimerEvent(3.0);
    }
     
    timer()
     {
        llSay(0,llGetObjectDesc());
        llSetTimerEvent(0);
    }
}
(0027764)
BlueWall (administrator)
2015-03-06 18:16

rf6168ded4773 removes the last of the code to actually use that variable. It was only used in the dynamic texture module to truncate the description for the local asset (dynamic textures are not uploaded to the asset database and are only available locally). The database will remain the same for now until some further cleaning can be done. Just thinking that removing references to the unused fields can make it possible to drop the fields.
(0027765)
Gavin Hird (reporter)
2015-03-06 21:54

By not writing the Name and Description fields to the asset table you essentially makes the table human unreadable.

For a person inspecting the data these two fields can convey a lot of information that quickly makes it possible to get a handle of the content without writing views to obtain the information elsewhere. It is also valuable in debugging and for completeness on exports, backups and restores.


Without this information a view showing the assets an Inventory or Prim item consists of would only show a bunch of UUIDs and other binary data. Not very useful really.

More important is that since the data are only written on asset creation, and many content developers use the description field for copyright information, it essentially puts a creator stamp on the asset that will follow it, unlike the Inventory item description that can and often is edited – also for the unskilled to "claim" the item as their own. Retaining the original asset information is important for full perm items to track the originator. It is therefore also important it is not truncated.

By writing the original name and description on creation you make it a constant that can be used over time for tracking, By leaving it to the Inventory item description you make it a fluid, which is much harder to be used for tracking over time.

If the size of these fields are set wisely, database migrations on the asset table should only happen once in a blue moon.

That people's systems choke on this migration tells more about their system management practices than anything else, pulling code directly from latest master to production. This is actually no excuse at all to make necessary changes. People must test and plan. Release notes must tell there are migrations so people are informed.
(0027766)
smxy (reporter)
2015-03-06 22:37

I believe Justin said that the fields get overwritten during OAR and IAR load. Something to the effect of "Created from and OAR [IAR] import." So the fields are not necessarily going to maintain whatever was put in them at asset creation time, so it is not a constant, as you suggest, Gavin.
(0027767)
Gavin Hird (reporter)
2015-03-06 23:12

OK, but the essence is that regardless the Name and Description field is there to make the table useful for human consumption. :-)

Not sure that OAR or IAR restore overwriting an existing UUID is a good thing as this is one of the methods claiming ownership of content you never created. Of course for pure backup and restore it is totally valid, but it can be misused.

It would be interesting to know when these fields are overwritten during the normal operation of the server?
(0027768)
smxy (reporter)
2015-03-06 23:41

Well, this was the discussion of it (pasted here, so you don't think I imagined it), but I can't find any evidence of this in my assets table, and I know I've loaded OARs and IARs:

[18:51] <justincc> actually, the OARs and IARs ignore the asset name and description entirely....
[18:51] <ftcodes> they change it to "Imported by IAR" or "Imported by OAR"
[18:51] <justincc> yeah, which is kind of dumb on reflection
[18:51] <justincc> would be better blanking probably - I'm not sure that's useful
[18:52] <justincc> sometimes it can be useful for debugging, perhaps if it actualy put more info
[18:52] <justincc> I dunno
[18:52] <ftcodes> a few serialization set the asset name and description still relating to the inventory name and description

Since I'm just parroting what others have said, I think I'll bow out of this and let you take it up further with them, directly. :) It'll be interesting to see what the end result is.
(0027769)
Gavin Hird (reporter)
2015-03-06 23:53

Ha! On moving my inventory from OSGrid the day it came up to XMIR via IAR, it has indeed set the Name field to "From IAR" and blanked the Description field for those records in the Assets table. How stupid!
(0027770)
smxy (reporter)
2015-03-07 00:15

Ah, ok. I was searching in the Description field, not the Name field. At least you've confirmed the behavior, anyway.
(0027771)
Gavin Hird (reporter)
2015-03-07 00:37

Thinking about it, this is even more than stupid. – If you restored a backup IAR or OAR possibly it would wipe the info for From IAR and From IAO with blanked descriptions...

Is it possible to parse the IAR and get the name and description + UUID out so the database can be updated?
(0027772)
aiaustin (developer)
2015-03-07 01:32
edited on: 2015-03-07 01:36

Okay... understood that the message about chopped down descriptions may need to be fixed for prims, primitems and inventoryitems... but it does chop the description length before it is stored/persisted and if its moved from grids with longer descriptions to the current (wrong) default of 64*** characters... and it does cause issues... see 7350.. and it is reported by the [ASSETDB] module...

E.g. here is an example of an avatar tracker created in world, in which the Google Docs target form URL is used in the description field.

[ASSET DB]: Description 'https://docs.google.com/forms/....' [^] [^] for asset c0943a2d-b63a-474d-a8dc-2f51e34e37ff truncated from 127 to 64 characters on add

The OAR/IAR name/description changes not show if asssets already are on the grid of course as load OAR/IAR does not recre ate assets that ar already on the grid wiithe the same UUID. But the change does seem odd and I see a lot of those as I tend to do builds on temporary grids or simonastick setups and then move them when ready onto our main grid. Maybe that should be changed?

*** I still think there is a separate issue that descriptions of length 255 are temporarrliy allowed on creating the asset in world and it being chopped off on store to the max asset length... and feel ALL these lengths should be identical to avoid scripts that use the dscription (and name) fields running in some contexts and nit in others or after persist or transfer between grids. Its a mess at the moment and will lead to all sorts of hard to track doen erors.

(0027773)
aiaustin (developer)
2015-03-07 06:27
edited on: 2015-03-07 06:30

Thinking about this more... surely SOMETHING in the [ASSET DB] area is storing assets when they are first created or brought onto a grid via build, import OAR/IAR or via HG transfer.. or even via marketplaces like Kiteky. Its clear that along the line some of these assets have 128 length descriptions and on store on a grid using the current OpenSim defaults that gets truncated.. causing errors in scripts that had stored a description longer than 64 characters (e.g for URls which ar typically longer than that) and that worked fine until brought onto a grid with 64 length descriptions.

So even if the current code base is not using the ASSET_MAX_LENGTH that was/is something in [ASSET DB] is doing teh truncation to 64 characters and outlutting the truncation warning.

BlueWall, the objects on which I have seen some of these messages are NOT related to dynamic textures at all.. they are, e.g., avatar counters sensing the area and reporting via an extenal system via the URl stored in the description. But typically items where longer URL or parametric information is maintained in the description.

(0027774)
BlueWall (administrator)
2015-03-07 06:58

default
{
    state_entry()
    {
        llSay(0, "Script running");
    }
    
    touch_start(integer det)
    {
        llSetObjectDesc("This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long * This is a long string of text to see if we can trigger the error message about description text being too long");
        
        llSetTimerEvent(3.0);
    }
     
    timer()
     {
        llSay(0,llGetObjectDesc());
        llSetTimerEvent(0);
    }
    
    on_rez(integer param)
    {
        llSay(0,llGetObjectDesc());
    }
}
(0027775)
BlueWall (administrator)
2015-03-07 07:04

If you touch it, you will see that the length is unchanged in-world. If you take it into inventory then rez it from inventory, you will see that the desc has been truncated to 128. I guess you could go further and see if the asset cache truncates it as well.

So far, I cannot find anything using the assets for the description or applying that assetbase variable for the cutoff size anymore. I can imagine a third party module might unknowingly use it, but that is beyond our ability to check those.
(0027776)
BlueWall (administrator)
2015-03-07 07:05

I wonder if the case of url in the description is passing the 128 byte length? I can see in landmarks for hypergrid locations we could easily fill that space.
(0027777)
Gavin Hird (reporter)
2015-03-07 07:12

64 byte is short any which way you see it these days. Apart from the database migration time, are there any issues identified lengthening it to 128?
(0027779)
BlueWall (administrator)
2015-03-07 07:25

It should be 128 throughout the system now, as of yesterday - unless 3rd party modules are using it. I cannot find any place that we are using 64 bytes for the description now. So, all the discussion here needs to move on to code that is f6168ded and beyond.
(0027780)
Gavin Hird (reporter)
2015-03-07 07:28

Good! We can live with that ;-)
(0027781)
BlueWall (administrator)
2015-03-07 07:36

After looking into this, I am inclined to make the description field larger to accommodate Hypergrid landmarks. But, such a change will involve discussion and will be a team decision.

It would also be good to clean all of this out of the assets area and place constants in the inventory area to avoid confusion in the future.
(0027782)
Gavin Hird (reporter)
2015-03-07 07:42

Are we sure it always involves Inventory?

I see there are lots of asset transfers going on that not always ends up as an entry in someone's Inventory, such as when a Hypergrid teleport is incoming assets in their My Suitcase folder goes to the asset server, but it never shows in anyone's local inventory unless the HG traveler gives something to someone on the local grid.
(0027783)
Gavin Hird (reporter)
2015-03-07 07:44

Such as here:

2015-03-07 01:33:33,479 DEBUG - OpenSim.Region.Framework.Scenes.HGUuidGatherer [HGUUIDGatherer]: Copied asset 5a3feea2-6966-4ebd-b63c-18aaa73393ed from http://assets.osgrid.org:80/ [^] to local asset server
2015-03-07 01:33:33,759 DEBUG - OpenSim.Region.Framework.Scenes.HGUuidGatherer [HGUUIDGatherer]: Copied asset a9b72da2-bb45-4307-9383-8e1af6d51f91 from http://assets.osgrid.org:80/ [^] to local asset server
(0027784)
BlueWall (administrator)
2015-03-07 07:49

In the case of the Hg visitor, the description will be in their inventory and will be duplicated and copied in the inventory to the new owner if inventory is given. Or, if the object is rezzed, the description will be part of the SceneObjectPart.
(0027785)
Gavin Hird (reporter)
2015-03-07 08:02

Right, but the My Suitcase inventory structure of a visitor is never duplicated as inventory entries to the local server? Are not just the assets for the wearables copied to the local asset server initially so the simulator can bake the textures and serve up the wearables, AO animations and scripts to the viewer and scene for the visiting avatar?

To me it looks like (from observing the console), it is only when a visitor rezz something or give something the assets for this are copied across. – And only when there is a transfer to a local user account an inventory entry is created on the local server? – Or are there shadow inventories for visitor that local accounts never will see?
(0027786)
BlueWall (administrator)
2015-03-07 08:18

These are just like a local user, except the Hypergrid visitor is running off their own assets/inventory via the Hypergrid services. the inventory travels in the viewer and will negotiate through their own Hypergrid services to transfer an item to anpother, ot rez in-world. At that point their current simulator will negotiate through their Hypergrid services for the asset and inventory data.

When they arrive in the remote sim, they are rezzing any assets that comprise their avatar into the simulator.
(0027787)
BlueWall (administrator)
2015-03-07 08:19

And in all regards, the Description will ride, either in the SOP or Inventory. Not the asset.
(0027788)
Gavin Hird (reporter)
2015-03-07 08:29

Yes, ok ... I think I understand.

But then there will be a lot of entries in the local asset table there never is a local inventory entry for, so zapping the Name and Description field as was suggested further up the thread, would make it very hard to navigate the asset table for a human if there was a need. (debugging, audit, DMCA takedowns etc.)

Also importing assets with name set to From IAR and blank description is not optimal at any rate for the same reasons.
(0027789)
BlueWall (administrator)
2015-03-07 09:27

The authoritative source is Inventory. Each inventory item points to an asset via id. Searching inventory for matches on any criteria, description or otherwise, will yield ids that point to the asset records.
(0027792)
aiaustin (developer)
2015-03-07 11:33
edited on: 2015-03-07 11:41

So where does the [ASSET DB] truncation message come from.., it must be in the code somewhere and I assumed it was the ASSET DB module.. a core module I think?

And I think we do see such truncations for worn assets that HG visitors are wearing when they come onto a grid.

Can you totally remove the ASSET_MAX_ASSET_DESC variable so we will find out when compiling ifts use din any addon module. Except for core modules, some optional like Profiles v2, the only add on I use is WiFi.

Anyway, Openvue and SIland are now runnimg on r/25875 but do have the MySQL asset db table description length set at 128 since I went through the migration to revision 9 before it was set back to a no-op and therefore for any DB that skipped that update they will still have asset descriptions of length 64! I would like to make my DB be at the default... Can I safely run a MySQL command..

ALTER TABLE assets MODIFY description varchar(64);

or would that mess things up even more.

(0027793)
BlueWall (administrator)
2015-03-07 11:39

aiaustin, can you paste a line of that debug message back here please? And if you are able to run in DEBUG mode to produce line numbers, that would be great as well. I want to leave the ASSET_MAX_ASSET_DESC for the time being as it is still present in the database adapters themselves. If code is still writing to those to save the description, then we need to locate it so we can make it do the right thing. After we have done that,then we can look at removing things to clean up.
(0027794)
aiaustin (developer)
2015-03-07 11:43
edited on: 2015-03-07 11:46

This is a pre-upgrade message sample...

[ASSET DB]: Description 'https://docs.google.com/forms/....' [^] [^] [^] for asset c0943a2d-b63a-474d-a8dc-2f51e34e37ff truncated from 127 to 64 characters on add

I tend to throw away logs of previous builds when I have upgraded, but I am alert to this now and will try to get more detailed log info if I see this again now I am on r/25875 and beyond.

(0027795)
Gavin Hird (reporter)
2015-03-07 11:43

If I create a landmark on my landing parcel for the grid, it spits out this message:

20:41:30 - [ASSET DB]: Description 'Welcome to the xmir sims where you will find from coastal to mountainous river valleys and lakes. Home of Bootes and xmir Mesh - Enjoy your stay! @ http://grid.xmir.org:8002' [^] for asset 6c1076fb-12f3-4cb8-b40d-1f47604693d0 truncated from 173 to 64 characters on add
(0027796)
Gavin Hird (reporter)
2015-03-07 11:53

The record in the Asset Table for the Landmark has the description truncated to 64 chars despite the field is 128 char. (see screenshot)

The corresponding inventory record has 128 chars preserved of the description that originally was 173 chars.
(0027797)
Gavin Hird (reporter)
2015-03-07 12:05

I assume this is the origin of the message. This is from the code as of r/25875

I the file MySQLAssetData.cs the following lines occur from line 170:

                    if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
                    {
                        assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
                        m_log.WarnFormat(
                            "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
                            asset.Name, asset.ID, asset.Name.Length, assetName.Length);
                    }

                    string assetDescription = asset.Description;
                    if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
                    {
                        assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
                        m_log.WarnFormat(
                            "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
                            asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
                    }


The same for the PGSQLAssetData.cs from line 169:


            if (asset.Name.Length > AssetBase.MAX_ASSET_NAME)
            {
                assetName = asset.Name.Substring(0, AssetBase.MAX_ASSET_NAME);
                m_log.WarnFormat(
                    "[ASSET DB]: Name '{0}' for asset {1} truncated from {2} to {3} characters on add",
                    asset.Name, asset.ID, asset.Name.Length, assetName.Length);
            }
            
            string assetDescription = asset.Description;
            if (asset.Description.Length > AssetBase.MAX_ASSET_DESC)
            {
                assetDescription = asset.Description.Substring(0, AssetBase.MAX_ASSET_DESC);
                m_log.WarnFormat(
                    "[ASSET DB]: Description '{0}' for asset {1} truncated from {2} to {3} characters on add",
                    asset.Description, asset.ID, asset.Description.Length, assetDescription.Length);
            }
(0027798)
aiaustin (developer)
2015-03-07 12:09
edited on: 2015-03-07 12:19

Just a thought... would it not be better to make everything be limited by the MAX_ASSET_DESC or just a new global MAX_DESC constant and NOT hardwire 128 all over the place... then when we find the rogue 64 bit truncation position (I see Gavin identified it above.. and there may be more places it is used) that can be corrected. Better for the future so errors are gradually removed and further hardwired constraints are not built up in the code to bite in future.

E.g., revert the commit at r/25875

(0027804)
aiaustin (developer)
2015-03-08 09:09
edited on: 2015-03-08 09:15

I find that the MySQL workbench SQL editor can no longer retrieve the assets on one of my grids (the one where the asset description field was amended to varchar(128)) even though I only have about 32,000 assets. It is now showing

Error code:2008 MySQL client ran out of memory

Whereas a larger and more established grid that was left at varchar(64) works fine with many more assets.

I did manage to retrieve the assets by selecting only specific fields...

SELECT name,assetType,creatorID FROM opensim.assets;

(0027805)
Gavin Hird (reporter)
2015-03-08 09:15

I use Navicat from http://www.navicat.com [^] and have no problems retrieving 67000 asset records with description at 128 chars. This is on Postgres, but you can download the trial Navicat Pro or MySQL and see how it does.
(0027806)
smxy (reporter)
2015-03-08 09:20

My dbForge Studio for MySQL (Professional Version), by devart, doesn't seem to have problems fetching the assets for my grid, and I ran the same migration. I have ~210K rows in my table. However, it does seem to only retrieve 1000 rows at a time. Perhaps you need to adjust a settting.
(0027808)
aiaustin (developer)
2015-03-08 12:07

I was retrieving the whole table rather than the (default) 1000 at a rime.. but I was making the point that the change to varchar (128) for the asset descriptions seems to have made retrieval even for small data bases needing a lot more memory.

Anyway, I managed to get things by only retrieving certain fields as I noted above.

I am still not sure if I should manually change the asset drscription length back to 64 to stay in step with git master?
(0027810)
Gavin Hird (reporter)
2015-03-08 12:09

I would not worry about it. Keep it as is.
(0027811)
Gavin Hird (reporter)
2015-03-08 14:03

It can't be right that Inventory is the authoritative for assets.

I've been sitting watching two hypergrid guests using the Sandbox sim and the assets for items they have worn (AO animations) and items they have rezzed are in the asset table as entries, but there is no trace of them in Inventory Items or Prim Items tables respectively.
(0027812)
FreakyTech (reporter)
2015-03-08 14:25
edited on: 2015-03-08 14:27

No HG visitor is replicating his inventory item data on any other grid.
This is accessed via the communicated SRV_InventoryURI.

The only data that gets replicated into the visited grid are assets. This is necessary when it is rezzed on a sim for an unattached object when it comes to its object inventory, textures and so on.

Attachments are not stored in primitems tables. Those attachments are only existing as long as that avatar exists on the sim. They get removed on logout.

The only authoritative source for attachments is hold in memory as long as the sim runs and that avatar is on it wearing it.

(0027813)
Gavin Hird (reporter)
2015-03-08 14:31

Yes, but the assets remain in the asset table and for this reason both the Name and Description field must be preserved and properly populated both for debugging, audit purposes and to assist in DMCA takedowns or for other reasons when you need to identify assets.

Without these fields (as have been suggested above) you only have a table of UUIDs and other binary data (mostly) and it becomes infinitely harder to navigate the data.

On the same ground it is hopeless that imports for IAR and OAR have written "From IAR" or "From OAR" as names and the description field blanked.
(0027814)
FreakyTech (reporter)
2015-03-08 14:35

But do you want to argue with large grids having Terrabytes of data why they have to take a down time of one month and twice the disk size they have?


It is already hard to navigate that data. Ever tried to access a table with more than one million entries without any kind of index created for that field?

And by the way, you cannot guarantee that you have the right asset at all.

The string is not authoritative due to user influence. It is merely a hint.
(0027815)
Gavin Hird (reporter)
2015-03-08 14:46

Actually there is quite a difference between the database systems how they would handle this. Postgres would possibly be much more storage efficient as the data would automatically be compressed in the database.

The difference between storing 128 + 64 bytes for the description and name fields and not storing it is negligible compared to the binary information stored for textures, sculpted items, sounds and mesh items for instance.

If you offloaded storage for these data types outside the database, the database would even with millions of records be lightweight in terms of disk space, and indexing the fields would be a minor overhead.
(0027816)
Gavin Hird (reporter)
2015-03-08 15:07

The other thing about databases is that they don't store data byte by byte, they store data by a minimum multiple of a disk block (often 512 byte) so the minimum disk space for a record is typically 4 or 8 Kb regardless of how little data is in there. The binary data will allocate additional storage above that.

So as long you can pack all the data of a record into a database block, you're good. The result on the platter is exactly the same.
(0027824)
aiaustin (developer)
2015-03-09 02:37

I can see why our data bases grow to be enormous.. when assets needed for visitor avatar appearance are left permanently in the data base tables. I know its tricky to judge what can and cannot be safely erased. It makes me wonder whether "temporary" assets only in the server instance like the attachments and so on would be possible? But I am a bit off the point for this issue perhaps.
(0027825)
Gavin Hird (reporter)
2015-03-09 02:47

I suppose you could flag the records on create time that they were created for HG visitor appearance and attachments, and use the flag to purge such records over a certain age either manually or by a set parameter.
(0027827)
FreakyTech (reporter)
2015-03-09 11:07

If you like to apply an optimization to Robust's asset table, try this in MySQL:

ALTER TABLE assets ROW_FORMAT=DYNAMIC;

That essentially switches to off-table storage in InnoDB.

Be prepared to have to wait for a long time depending on the size of the table.

- Issue History
Date Modified Username Field Change
2014-10-10 01:02 aiaustin New Issue
2014-10-10 01:03 aiaustin Git Revision or version number => r/25337
2014-10-10 01:04 aiaustin Relationship added related to 0007288
2014-10-10 01:04 Gavin Hird Note Added: 0026894
2014-10-10 01:04 Gavin Hird Note Edited: 0026894 View Revisions
2014-10-10 01:08 Gavin Hird Relationship added related to 0007341
2014-10-10 01:09 Gavin Hird Note Added: 0026895
2014-10-10 08:09 aiaustin Priority normal => urgent
2014-10-10 08:11 aiaustin Note Added: 0026898
2015-03-04 00:59 aiaustin Relationship added related to 0007476
2015-03-04 01:03 Gavin Hird Note Added: 0027714
2015-03-04 01:03 Gavin Hird File Added: fixing the PGSQL adapter.pdf
2015-03-04 01:46 aiaustin Note Edited: 0027714 View Revisions
2015-03-04 02:45 aiaustin Note Added: 0027717
2015-03-04 02:46 aiaustin Note Edited: 0027717 View Revisions
2015-03-04 02:46 aiaustin Note Edited: 0027714 View Revisions
2015-03-04 02:47 aiaustin Note Edited: 0027717 View Revisions
2015-03-04 02:51 Gavin Hird Note Added: 0027718
2015-03-04 03:15 Gavin Hird Note Added: 0027719
2015-03-04 04:01 aiaustin Note Added: 0027720
2015-03-04 04:02 aiaustin Note Edited: 0027720 View Revisions
2015-03-04 04:04 aiaustin Note Edited: 0027720 View Revisions
2015-03-04 04:08 Gavin Hird Note Added: 0027721
2015-03-04 04:10 aiaustin Note Edited: 0027720 View Revisions
2015-03-04 04:17 Gavin Hird Note Edited: 0027721 View Revisions
2015-03-04 05:59 cinderblocks Note Added: 0027722
2015-03-04 06:04 Gavin Hird Note Added: 0027723
2015-03-04 06:04 Gavin Hird Note Edited: 0027721 View Revisions
2015-03-04 06:16 cinderblocks Note Added: 0027724
2015-03-04 06:17 cinderblocks Note Added: 0027725
2015-03-04 06:28 Gavin Hird Note Added: 0027726
2015-03-04 06:31 cinderblocks Note Edited: 0027725 View Revisions
2015-03-04 06:35 aiaustin Description Updated View Revisions
2015-03-04 06:35 aiaustin Additional Information Updated View Revisions
2015-03-04 06:41 cinderblocks Note Added: 0027727
2015-03-04 06:42 aiaustin Note Added: 0027728
2015-03-04 06:43 aiaustin Note Edited: 0027728 View Revisions
2015-03-04 06:50 cinderblocks Note Added: 0027729
2015-03-04 09:04 cinderblocks Note Added: 0027731
2015-03-04 09:46 Gavin Hird Note Added: 0027732
2015-03-04 10:12 cinderblocks Note Added: 0027736
2015-03-04 10:49 Gavin Hird Note Added: 0027739
2015-03-05 19:00 cinderblocks Note Added: 0027748
2015-03-05 19:19 smxy Note Added: 0027749
2015-03-05 19:49 cinderblocks Note Added: 0027750
2015-03-05 20:52 BlueWall Note Added: 0027751
2015-03-06 01:24 aiaustin Note Added: 0027752
2015-03-06 01:25 aiaustin Note Added: 0027753
2015-03-06 01:27 aiaustin Note Edited: 0027752 View Revisions
2015-03-06 04:19 aiaustin Note Edited: 0027753 View Revisions
2015-03-06 17:00 smxy Note Added: 0027762
2015-03-06 17:49 BlueWall Note Added: 0027763
2015-03-06 18:16 BlueWall Note Added: 0027764
2015-03-06 21:54 Gavin Hird Note Added: 0027765
2015-03-06 22:37 smxy Note Added: 0027766
2015-03-06 23:12 Gavin Hird Note Added: 0027767
2015-03-06 23:41 smxy Note Added: 0027768
2015-03-06 23:53 Gavin Hird Note Added: 0027769
2015-03-07 00:15 smxy Note Added: 0027770
2015-03-07 00:37 Gavin Hird Note Added: 0027771
2015-03-07 01:32 aiaustin Note Added: 0027772
2015-03-07 01:36 aiaustin Note Edited: 0027772 View Revisions
2015-03-07 06:27 aiaustin Note Added: 0027773
2015-03-07 06:29 aiaustin Note Edited: 0027773 View Revisions
2015-03-07 06:30 aiaustin Note Edited: 0027773 View Revisions
2015-03-07 06:58 BlueWall Note Added: 0027774
2015-03-07 07:04 BlueWall Note Added: 0027775
2015-03-07 07:05 BlueWall Note Added: 0027776
2015-03-07 07:12 Gavin Hird Note Added: 0027777
2015-03-07 07:25 BlueWall Note Added: 0027779
2015-03-07 07:28 Gavin Hird Note Added: 0027780
2015-03-07 07:36 BlueWall Note Added: 0027781
2015-03-07 07:42 Gavin Hird Note Added: 0027782
2015-03-07 07:44 Gavin Hird Note Added: 0027783
2015-03-07 07:49 BlueWall Note Added: 0027784
2015-03-07 08:02 Gavin Hird Note Added: 0027785
2015-03-07 08:18 BlueWall Note Added: 0027786
2015-03-07 08:19 BlueWall Note Added: 0027787
2015-03-07 08:29 Gavin Hird Note Added: 0027788
2015-03-07 09:27 BlueWall Note Added: 0027789
2015-03-07 11:33 aiaustin Note Added: 0027792
2015-03-07 11:39 aiaustin Note Edited: 0027792 View Revisions
2015-03-07 11:39 BlueWall Note Added: 0027793
2015-03-07 11:40 aiaustin Note Edited: 0027792 View Revisions
2015-03-07 11:41 aiaustin Note Edited: 0027792 View Revisions
2015-03-07 11:43 aiaustin Note Added: 0027794
2015-03-07 11:43 Gavin Hird Note Added: 0027795
2015-03-07 11:44 aiaustin Note Edited: 0027794 View Revisions
2015-03-07 11:46 aiaustin Note Edited: 0027794 View Revisions
2015-03-07 11:53 Gavin Hird Note Added: 0027796
2015-03-07 11:54 Gavin Hird File Added: truncated asset table entry.png
2015-03-07 11:55 Gavin Hird File Added: inventory record also truncated to 128.png
2015-03-07 11:55 Gavin Hird File Added: asset table field defintion.png
2015-03-07 12:05 Gavin Hird Note Added: 0027797
2015-03-07 12:09 aiaustin Note Added: 0027798
2015-03-07 12:10 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:11 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:15 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:16 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:17 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:18 aiaustin Note Edited: 0027798 View Revisions
2015-03-07 12:19 aiaustin Note Edited: 0027798 View Revisions
2015-03-08 09:09 aiaustin Note Added: 0027804
2015-03-08 09:10 aiaustin Note Edited: 0027804 View Revisions
2015-03-08 09:15 Gavin Hird Note Added: 0027805
2015-03-08 09:15 aiaustin Note Edited: 0027804 View Revisions
2015-03-08 09:20 smxy Note Added: 0027806
2015-03-08 12:07 aiaustin Note Added: 0027808
2015-03-08 12:09 Gavin Hird Note Added: 0027810
2015-03-08 14:03 Gavin Hird Note Added: 0027811
2015-03-08 14:25 FreakyTech Note Added: 0027812
2015-03-08 14:27 FreakyTech Note Edited: 0027812 View Revisions
2015-03-08 14:31 Gavin Hird Note Added: 0027813
2015-03-08 14:35 FreakyTech Note Added: 0027814
2015-03-08 14:46 Gavin Hird Note Added: 0027815
2015-03-08 15:07 Gavin Hird Note Added: 0027816
2015-03-09 02:37 aiaustin Note Added: 0027824
2015-03-09 02:47 Gavin Hird Note Added: 0027825
2015-03-09 11:07 FreakyTech Note Added: 0027827
2016-11-24 05:55 aiaustin Relationship added related to 0008065


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker