Group Message Attachment Error to Database

[Vinhold]

I had not had any occasion before this to use group messages or send an attachment in any of my worlds or in any others prior to this point. Some in AmG world had been reporting a problem with message attached items simply not being saved. Now I have proof: Group message was sent out to group members and had a picture attached of the time. This is the SQL that failed - Insert into offlines (fromAgentId, fromAgentName, toAgentId, dialogVal, fromGroupVal, offlineMessage, messageId, xPos, yPos, zPos, binaryBucket, parentEstateId, regionId, messageTimestamp, offlineVal) Values ('40e5bbf2-1b75-4195-b304-27a66b054ed6','erdette clarity','947a70df-3b14-4418-a160-c80bb122e03f',32,'true','CUTE EASTER SHORTS |Cute Easter shorts outfit with a chick, egg, pinwheel, flower, or bunny plus one plain outfit you can wear during the summer. Lots of other outfits here for woman, copy this slurl into chat then hop to the store hop://grid.amaryllisgrid.com:8002/Tirenno/95/87/22','62733c4a-bc49-4a40-bf2b-851c80a54254','0','0','0','/wAAADMzZ+J2K02GpTCUDbqdoGgBAEDlu/IbdUGVswQnpmsFTtYgZWFzdGVyIGJ1bm55IGJsdWUudGV4dHVyZQA=',0,'00000000-0000-0000-0000-000000000000',1553780737,1)

Failure was field binaryBucket = '/wAAADMzZ+J2K02GpTCUDbqdoGgBAEDlu/IbdUGVswQnpmsFTtYgZWFzdGVyIGJ1bm55IGJsdWUudGV4dHVyZQA=' which was attempting to stuff 88 characters into a 45 character varchar field. I looked up the table and field in the hDatabase code and it is at varchar (45). So it appears that somewhere else in the attachment process that it looks like it was trying to encode the image and place it in the attachment instead of a reference UUID to it. (my best guess)

[appurist]

There's an in-database server format for the binary_bucket and a viewer-compatible LLSD XML format the server gets it from and returns. At first I thought it sounded like there was a path where the LLSD viewer one goes all the way into the db. But below you'll see that I think we just have the wrong schema definition and the binary bucket field needs to be at least 116 bytes wide.

After converting from LLSD XML, there are two formats for the group notice IM in the db: one that is 19 bytes long for when there is no attachment, and one that is longer when there is an attachment. See lines 588 (no attachment) and 598 (attachment) in FlexiGroups.cs.

In Halcyon the group has an inventory like a user and "inventory items" are created for notice attachments (see line 426, StoreGroupItem, and line 402, FindCreateGroupStorage).

Important: After pulling the fields out of the LLSD XML, it calls FormatBucketForStorage on line 637 which has two very clear formats for without and with an attachment (see line 1226):

This is clearly 52 bytes plus a 00 when there is no attachment, or the bytes representing the attachment name, when there is one. That's 52 bytes + the maximum length of an inventory item name, which I believe is 64. So that would mean the binary bucket could be 52+64=116 bytes long in this case.

I suspect that InWorldz had a larger bucket field and somehow this never made it into the documented database schemas. I checked my current test db and the .sql files in GitHub and it's the smaller 45 everywhere. I think we should change the schema definition to increase the bucket size to at least 128 bytes.

[appurist]

I have checked 3 copies of older databases that I had from InWorldz and Islandz and they are all the same... 45 characters for the binary bucket.

But... I remember a problem in InWorldz that we were unable to track down... that this explains perfectly: users complained that their offlines of group notices with an attached file didn't work, however, if they refetched the notice from the Notices list in the group, they could access the attached file. If I recall correctly, we never tracked that down.

I think that was because it wasn't a "bug" in the code, as much as the field database schema wasn't large enough. Good eye, @Vinhold !

[appurist]

Kudos to @Vinhold for spotting the 45-char thing. I think it answers a long-standing issue that we never solved.

Vin, can you try changing the definition of that field to maybe Varchar(128)? We could set it to 116 but I don't see the point of making it so tight that if we added another small field it would break it, with a table that is routinely emptied. Also, for that reason, we could consider setting it to 255 or 256 just to never deal with it again.

Also, line 380 implies that there may still be a case where we store the LLSD XML and it could be quite a bit longer.

[Vinhold]

Thank you Jim, Setting the field to 128 solved the DB error on too much to put into the binaryBucket, but it has not solved the attachment actually being in the group message. The attached item is showing as being part of the message, but clicking on it results in "Attachment not saved - item not found." message posted to the System tab in Notifications. The binaryBucket field has this in it for an LM attached: /wAAAOdFcKLhnkSkuq0IdPxb4QUBAy/NzRHN3Umci+QpeqXQBCIgTXlTaW4gTWFsZQA=

So now it looks like something down stream when opening the attachment that it cannot be found. I am investigating a selection if items to attach to see just how much content is posted to the field or anything else in the record that does not look correct.

Test 1 - Land Mark: /wAAAN7PgRqYiUdypp5kjQ3v0ggBA362CXNTh0P7glMIyzpwJAIgVGVzdE5XAA== Test 2 - Picture: /wAAAB0tuolJh0d+lYIOPg4HFrsBAH62CXNTh0P7glMIyzpwJAIgVmlzaXRvcnMgdG8gVGFsbHkgSG8gMy0xNy0xOQA= Test 3 - Shirt: /wAAAInUSVY3MkfEvbzPjT5xZXABBX62CXNTh0P7glMIyzpwJAIgU2hpcnQA Test 4 - Prim Item: /wAAAO93Q3LxqE/ZhMM1FNLiWT4BBn62CXNTh0P7glMIyzpwJAIgTGF3biBDaGFpcgA=

All failed to be found, these are the binaryBucket field contents. The rest of the record fields look to be correct with intended content. So this must be related to the delivery processing now.

[appurist]

This is probably all connected to the concept of groups having inventories. If you're using the newer Halcyon code and MySQL inventory, it might be that the code to handle MySQL inventories doesn't handle the possibility of a group, e.g. if it starts by looking up the corresponding user (it won't find them). My guess is that increasing the limit bypassed the first problem but there is a larger issue that will require a code change. I'd need to trace it through and see if it's trying to look up the inventory owner only as a user. It might need to do that for reporting, but really this code shouldn't care about who owns the inventory when it fetches the attachment. So the fix would be to either remove the lookup of a user and just search for an inventory by that owner, or to add code to look up either a user or a group by that ID.

[sonjamichelle]

Just where and how is the work around implemented? It isn't fully clear from discussions.

[appurist]

@sonjamichelle This is from 2.5 years ago but I think I remember what this was about. If I follow, the workaround was confirmed when Vin increased the size of the binarybucket field (column) in the offlines table in the MySQL database so that there was sufficient room to store it. Varchar(128) should handle it, but I mentioned in one of the comments that there may be some other cases where it needed to be bigger. It might be safer to make it Varchar(256) or more.

[sonjamichelle]

@appurist Ok, I'll give that a shot. I did set it to 128 with no luck. I'll try the 256. I have an error message from the previous attempt

' 15:25:16 - [LLCV]: Avatar database update (4) committing for user 54d1ce8d-d14f-4f32-a4fe-cca409901e9e 15:25:32 - System.NullReferenceException: Object reference not set to an instance of an object. at Halcyon.Data.Inventory.MySQL.MysqlStorageImpl.getUserRootFolder(UUID user) in C:\Users\sonja\OneDrive\Desktop\halcyon-0.9.45\Halcyon\Halcyon.Data.Inventory.MySQL\MysqlStorageImpl.cs:line 317 15:25:32 - System.NullReferenceException: Object reference not set to an instance of an object. at Halcyon.Data.Inventory.MySQL.MysqlStorageImpl.getUserRootFolder(UUID user) in C:\Users\sonja\OneDrive\Desktop\halcyon-0.9.45\Halcyon\Halcyon.Data.Inventory.MySQL\MysqlStorageImpl.cs:line 317 15:25:32 - [CLIENT]: Sonja Galileo: Job threw an exception: System.NullReferenceException: Object reference not set to an instance of an object. 15:30:19 - [SCENE]: Bulk storing 1 objects 15:30:19 - [SCENE]: Bulk storage completed 15:31:08 - System.NullReferenceException: Object reference not set to an instance of an object. at Halcyon.Data.Inventory.MySQL.MysqlStorageImpl.getUserRootFolder(UUID user) in C:\Users\sonja\OneDrive\Desktop\halcyon-0.9.45\Halcyon\Halcyon.Data.Inventory.MySQL\MysqlStorageImpl.cs:line 317 15:31:08 - System.NullReferenceException: Object reference not set to an instance of an object. at Halcyon.Data.Inventory.MySQL.MysqlStorageImpl.getUserRootFolder(UUID user) in C:\Users\sonja\OneDrive\Desktop\halcyon-0.9.45\Halcyon\Halcyon.Data.Inventory.MySQL\MysqlStorageImpl.cs:line 317 15:31:08 - [CLIENT]: Sonja Galileo: Job threw an exception: System.NullReferenceException: Object reference not set to an instance of an object. 15:32:44 - [AGENT INVENTORY]: Received request to create inventory item Grid Library in folder a3cf7ded-1714-1d54-dba3-60ea6f22a7a4 15:32:44 - [FRIEND]: Got approve friendship from Sonja Galileo 54d1ce8d-d14f-4f32-a4fe-cca409901e9e, agentID 54d1ce8d-d14f-4f32-a4fe-cca409901e9e, tid a6aa6dff-82e9-4051-9389-0d4a23cbe910 15:32:44 - [FRIEND]: Remote agent detected. 15:32:44 - [FRIEND]: sending IM success = True Region (Main Test Region) # '

IT has been a while, once I make the change in the DB do I need to restart the entire grid to apply it? Or just the region/regons? Or just the Grid Services?

[Vinhold]

This is an unresolved issue as of 11/27/2022. Increasing the size of the DB field for the binarybitbucket had not resolved the problem as I noted above when I last was testing this problem. The code that places the data into the field and the code that reads that data back out do not agree with each other - so the encoding section or the decoding is where the problem with attachments is happening. What winds up in the binarybitbucket never really appears to change all that much. End result - attachments do not work and fails reading the data.