Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007643opensim[GRID] Messaging Servicepublic2015-07-16 11:362015-08-03 07:30
ReporterGavin Hird 
Assigned To 
PlatformOperating SystemOperating System Version
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0007643: Only one record is inserted in the im_offline table - EVER - this record is being constantly overwritten.
DescriptionThis is a rather odd issue, but the im_offline table never has more than one record EVER.

This record is constantly being overwritten by the "last speaker", so rather than generating new records for each offline message stored, the one record is constantly updated.

If the record is deleted, a new record is generated and the ID field is being updated to a new serial number.

I suspect this is an issue with the PGSQL adapter but it would be good to get verification if the same issue exist on MySQL.
Steps To Reproduce1. Configure Offline Instant Messaging V2
2. Send offline message via viewer IM
3. Observe result in the im_offline table
4. Repeat Send offline message via viewer IM
5. Observe m_offline table for either overwritten record or new generated
Additional InformationAs far as I can find out the functionality in the MySQL and PGSQL versions of the code is identical with the exception of what happens in the PGSQLGenericTableHandler.cs which is very messy compared to the MySQL equivalent.
TagsNo tags attached.
Git Revision or version number
Run Mode Grid (Multiple Regions per Sim)
Physics EngineBulletSim
Script Engine
EnvironmentMono / OSX
Mono Version4.0.1
Attached Files

- Relationships
related to 0007654closedorenh PGSQL Presence store out of sync with MySQL version, PGSQLPresenceData.cs missing code 

-  Notes
Gavin Hird (reporter)
2015-07-16 12:33
edited on: 2015-07-16 12:41

If I revoke the update privilege on the im_offiline table an exception is raised in line 115 of OffilineIMServiceRobustConnector.cs

aiaustin (developer)
2015-07-17 08:46
edited on: 2015-07-17 08:49

Gavin, I am using MySQL and also using the inbuilt Offline Instant Messaging V2, so I have checked this between 4 avatars and 8 messages to offline avatars with up to 6 messages queued at one time. All got delivered properly when the recipient avatar logged in. I am on r/26088 as at 23-Jun-2015 on Windows 8 servers at present.

Gavin Hird (reporter)
2015-07-17 13:25

Thanks for confirming my suspicion this was a PGSQL issue. I will see if I can get to the bottom of it. This table should never be updated but only inserted into and deleted from.

I have been able to temporarily alleviate it somewhat by writing a database function and trigger so all messages are kept and delivered to the receiver. There is some weird interaction between the code and the trigger though.
Seth Nygard (reporter)
2015-07-17 14:16

I am also using V2 Messaging and have had good results with the offline IM storage and delivery. There are many instances of users having several entries, all of which get delivered when they login next.

I am however using MySQL, mainly because I know the support for PostgreSQL is incomplete even though it would be my preferred DB engine if actively supported and tested.
Gavin Hird (reporter)
2015-07-18 02:04

@Seth, yeah I know.

I am finding small bugs but have more or less given up submitting the corrections as it seems to be more important to bicker over how to make code submissions than actually getting the corrections into the git repository. 0007606 is a good example where a fix has been sitting there since June 27.
aiaustin (developer)
2015-07-18 02:36

@Gavin.. remember even OpenSim devs take vacations sometimes :-)
Gavin Hird (reporter)
2015-07-18 02:38

They do, and well deserved, no doubt. ;-)
Gavin Hird (reporter)
2015-07-19 07:23

The presence service has exactly the same issue. Only one record is ever written to the database, and is overwritten by the next user logging in.
Gavin Hird (reporter)
2015-07-21 14:06

I have found that the PGSQL version of IM_Store.migrations has not been updated to have the same content as the MySQL version.

Also if you add the missing field from the migration FromID and set it to char(36) as in the MySQL version you will get a casting exception:

22:54:25 - [OFFLINE IM HANDLER]: Exception ERROR: 42883: operator does not exist: character = uuid Npgsql.NpgsqlException:
operator does not exist: character = uuid
Severity: ERROR
Code: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  at Npgsql.NpgsqlState+<ProcessBackendResponses_Ver_3>d__a.MoveNext () [0x00000] in <filename unknown>:0
  at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] in <filename unknown>:0

If the field type is set to uuid the behavior is like before; perpetual update of one record.

I suppose the overlying code assumes the MySQL datatype, but something is going wrong on type conversion in the PGSQL version of the database handler. Going to look closer into this.

As a temporary stopgap, not to loose IMs the following rule has been defined for the table:

DROP RULE IF EXISTS copy_on_update ON im_offline;
CREATE RULE copy_on_update AS
    ON UPDATE TO im_offline
        FROM im_offline
        WHERE im_offline."Message" = NEW."Message")
    INSERT INTO "im_offline"("FromID","Message","PrincipalID","TMStamp")
    VALUES(OLD."FromID",OLD."Message",OLD."PrincipalID", OLD."TMStamp");
Gavin Hird (reporter)
2015-07-26 08:45

Fixing the Presence service (7654) resolved a slew of issues and made teleport invites, group chat, online IM and inventory transfers via drop to chat or profile all work.

In addition it most likely has fixed the obscure error where particularly hypergrid visitors blocked themselves out of visiting neighboring regions running in another sim. It also fixes similar issues with in-grid residents crossing region borders if other agents were logged on.
orenh (administrator)
2015-07-28 00:12

Is this bug still present, or has it been fixed by the recent changes to PGSQL (e.g., 0007606)?
Gavin Hird (reporter)
2015-07-28 00:14

It is still unresolved, but getting closer to a fix.
Gavin Hird (reporter)
2015-07-28 00:44
edited on: 2015-07-28 00:44

This table is a bit special in that it should only be possible to insert and delete records, but the code does updates too.

If you remove the ability to update records directly on the table in the database, the only section of the code the throws an error is OfflineIMServiceRobustConnector.cs so I have not been able to pinpoint exactly where the update statement is issued. Deletions works as they should when messages are delivered to the receiver.

I'll give it another go, but presence had to be fixed first as it prevented even online IM between two parties. One of the parties would get messages, the other would be told the receiver was offline, so it was a one-way channel :-))

Gavin Hird (reporter)
2015-07-28 07:55

Oren, there is a patch in 0007398 that needs to be applied that also have table migrations for the im_offline table. This is a prerequisite to fix the remaining issue.

orenh (administrator)
2015-08-02 00:22

As 0007398 is now resolved, will there be a patch for *this* issue, or should it be closed?
Gavin Hird (reporter)
2015-08-02 11:24

@Oren, no it can't be closed as there needs to be a code change to PGSQLGenericTableHandler. I have identified the code section where bug occurs, but I am not sure if Im_offline is the only table affected or if there are others.
orenh (administrator)
2015-08-02 22:51

Gavin, please see 0007666 as well.
Gavin Hird (reporter)
2015-08-03 06:50
edited on: 2015-08-03 06:51

The issue is that the code leans on the MySQL "replace into" SQL extension which automagically figures out if to do an update or insert into the table (by analyzing primary key and unique indexes).

This function does not exist (yet) in PostgreSQL so additional code has been added to the PGSQLGenericTableHandler that fails to correctly detect if it shall do an insert or an update.

This code probably need to be rewritten both for clarity and to make it work properly.

PostgreSQL 9.5 which is now in alpha has a new UPSERT (short for INSERT...ON DUPLICATE KEY UPDATE) SQL statement that will mimic "replace into", which would simplify the code.

We cannot assume that all users will be on 9.5 and most certainly not 9.5 alpha, so the existing code needs to be made work reliable.

Gavin Hird (reporter)
2015-08-03 07:30

The use of "replace into" has lead to an overly complicated key on the im_offline table to make the function work, while in reality you only need a key on the ID (automatic sequenced) field, and perhaps an index on the PrincipalID field in a grid that stores a large number of messages.

The reason why you can simplify this is that im_offline is nothing more than a sequential log that is written to or deleted from. Nothing needs to be updated in this table ever.

1. A new message that needs to be stored for the user is simply stored with the next ID in the sequence as key, and the rest of the table fields populated.

2. When an agent comes online, the messages for the agent identified by the PrincipalID is retrieved and if successfully delivered to the agent, deleted.

That is all there is to it.

- Issue History
Date Modified Username Field Change
2015-07-16 11:36 Gavin Hird New Issue
2015-07-16 12:33 Gavin Hird Note Added: 0028909
2015-07-16 12:41 Gavin Hird Note Edited: 0028909 View Revisions
2015-07-17 08:46 aiaustin Note Added: 0028912
2015-07-17 08:48 aiaustin Note Edited: 0028912 View Revisions
2015-07-17 08:49 aiaustin Note Edited: 0028912 View Revisions
2015-07-17 08:49 aiaustin Note Edited: 0028912 View Revisions
2015-07-17 13:25 Gavin Hird Note Added: 0028914
2015-07-17 14:16 Seth Nygard Note Added: 0028916
2015-07-18 02:04 Gavin Hird Note Added: 0028919
2015-07-18 02:36 aiaustin Note Added: 0028920
2015-07-18 02:38 Gavin Hird Note Added: 0028921
2015-07-19 07:23 Gavin Hird Note Added: 0028923
2015-07-21 14:06 Gavin Hird Note Added: 0028938
2015-07-24 11:49 Gavin Hird Relationship added related to 0007654
2015-07-26 08:45 Gavin Hird Note Added: 0028991
2015-07-28 00:12 orenh Note Added: 0029000
2015-07-28 00:14 Gavin Hird Note Added: 0029001
2015-07-28 00:44 Gavin Hird Note Added: 0029002
2015-07-28 00:44 Gavin Hird Note Edited: 0029002 View Revisions
2015-07-28 07:55 Gavin Hird Note Added: 0029004
2015-07-28 21:34 orenh Assigned To => orenh
2015-07-28 21:34 orenh Status new => assigned
2015-08-02 00:22 orenh Note Added: 0029064
2015-08-02 11:24 Gavin Hird Note Added: 0029070
2015-08-02 22:51 orenh Note Added: 0029072
2015-08-03 05:30 orenh Assigned To orenh =>
2015-08-03 05:31 orenh Assigned To => orenh
2015-08-03 05:31 orenh Status assigned => new
2015-08-03 05:31 orenh Assigned To orenh =>
2015-08-03 06:50 Gavin Hird Note Added: 0029082
2015-08-03 06:51 Gavin Hird Note Edited: 0029082 View Revisions
2015-08-03 07:30 Gavin Hird Note Added: 0029083

Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker