Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007610opensim[GRID] Other Servicepublic2015-06-13 09:262019-02-06 11:50
ReporterGavin Hird 
Assigned Tokcozens 
PrioritynormalSeverityblockReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Versionmaster (dev code) 
Target VersionFixed in Versionmaster (dev code) 
Summary0007610: PGSQL Commit 6f71d5, c1ddb7, 0fa94f for Agenpreferences lacks support for Postgres
DescriptionThe database changes in commits 6f71d5, c1ddb7, 0fa94f lacks support for PGSQL adapter, so this change will not work for those using PostgreSQL backends.

Please make sure that ALL changes that requires database changes are propagated and tested for all supported database adapters.
TagsNo tags attached.
Git Revision or version number
Run ModeStandalone (1 Region) , Standalone (Multiple Regions) , Grid (1 Region per Sim) , Grid (Multiple Regions per Sim)
Physics EngineBasicPhysics, ODE, BulletSim
EnvironmentMono / Linux32, Mono / Linux64, Mono / Windows, Mono / OSX, .NET / Windows32, .NET / Windows64
Mono VersionOther
Viewern/a
Attached Filesdiff file icon AgentPrefsAdapters.diff [^] (8,029 bytes) 2015-06-13 14:51 [Show Content]
? file icon AgentPrefs.migrations [^] (551 bytes) 2015-06-14 10:44
patch file icon 0001-New-fix-for-Mantis-7610-uuid.patch [^] (2,782 bytes) 2016-01-13 10:17 [Show Content]
patch file icon agentprefs_PGSQL.patch [^] (2,196 bytes) 2016-03-17 02:39 [Show Content]

- Relationships
related to 0007157closedDiva llGetAgentLanguage(llDetectedKey(0)) return always en-us 
related to 0007805closedkcozens PGSQL Records are overwritten in Presence table 

-  Notes
(0028679)
cinderblocks (reporter)
2015-06-13 12:37

Lacks support for SQLite and the Null adapters as well.
(0028680)
Gavin Hird (reporter)
2015-06-13 12:39

Yes, correct. Not sure if the title can be changed.
(0028681)
cinderblocks (reporter)
2015-06-13 12:56
edited on: 2015-06-13 13:03

Complete shot in the dark patch, but if this patch could be tested with Postgre and with SQLite it would be wonderful.

(0028682)
Gavin Hird (reporter)
2015-06-13 13:01

It is getting late this side of the pond, but I can get the Postgres tested tomorrow unless something more concrete has emerged overnight. It is usually good to have a clear head when operating on the databases :-)
(0028685)
Diva (administrator)
2015-06-13 14:45

Cinder, I'm getting an error in applying this patch. It says
Patch format detection failed.

Can you generate it again?
(0028686)
cinderblocks (reporter)
2015-06-13 14:52

That first one was a raw diff. This one should work better.
(0028687)
Diva (administrator)
2015-06-13 14:55

Thank you again! <3

[14:54] <cia-opensim> opensim: cinder * r7635138c59af OpenSim/Data (4 files in 4 dirs):
[14:54] <cia-opensim> SQLite and PGSQL adapters for AgentPreferences
[14:54] <cia-opensim>
[14:54] <cia-opensim> Signed-off-by: Diva Canto <diva@metaverseink.com>
(0028691)
Gavin Hird (reporter)
2015-06-13 22:43

I have not tested it yet but quickly looking over the table definitions for PGSQL PrincipalID should be of type uuid and not char(36) as uuid has been used throughout in the other tables.

I will test the code with the field set to uuid and it should work without throwing a casting error.
(0028693)
Gavin Hird (reporter)
2015-06-14 02:13

The main tables have been all set up with UUIDs defined as uuid rather than char(36) in the migrations for PGSQL, but some of the smaller tables still have char(36).

I'll try and go through all the tables and make this consistent with uuid as the field type as this is both safer and faster in the database + consistency leads to less need to typecast for queries and views.
(0028696)
Gavin Hird (reporter)
2015-06-14 07:38

Enclosed new AgentPrefs.migrations that will actually create the AgentPrefs table on Postgres (9.4.4)

Since PrincipalID is now of data type UUID it has by default a constraint to be unique, so that is not required to add to the query creating the table.
(0028699)
Diva (administrator)
2015-06-14 07:56

I don't use pgsql. I note, however, that all tables in that DB connector use varchar(36) for uuids. If you're changing it in one, we should change it everywhere. This warrants a message to opensim-dev asking why varchar(36 were used in that connector in the first place, and if there are any bad consequences in changing them to uuid.
(0028702)
Gavin Hird (reporter)
2015-06-14 08:08

In the existing table definitions for PGSQL most of the tables have been defined with UUID rather than varchar(36) - (not done by me) both because it automatically is unique and fast.

On MySQL there has up to recently been a performance issue using the UUID type, but I believe this has been sorted out, so for newer versions of MySQL this should not be an issue any more.

It would probably be better to change it from varchar(36) to uuid across the board. There are a few tables where there are UUID types field that have been made longer to store the HG address of the identity. One example is inventoryitems.creatorID that is a varchar(255)
(0028703)
cinderblocks (reporter)
2015-06-14 08:28

UUIDs are always generated in code not by the database, so uniqueness isn't really a factor here, and as far as speed, I'd really like to see some in context performance testing done to prove that. Index scans, for instance, are slower using UUID fields than char(36), and there's really no way to tell how it's going to affect performance until you actually test it. The only real benefit to this would be a savings of 24 bytes per row, which does add up, but if storage were the reason for changing it, it would be far more advantageous to use bigint for uuids and not store uuids in hex format at all.
(0028704)
Gavin Hird (reporter)
2015-06-14 08:39

> Index scans, for instance, are slower using UUID fields than char(36)

Performance using UUID is very good on Postgres, and using it doesn't set any pre-determined limit on how many servers/databases may be in a cluster., so it is good when you need to grow your environment.
(0028705)
Diva (administrator)
2015-06-14 09:32

Each DB technology has their own types, that's fine. In the pg connector, I see that the originally created tables had varchar(36) but subsequently were changed to uuid. I've changed the agent preferences table in the pg connector to use uuid too. This is untested.

[09:31] <cia-opensim> opensim: diva * rbe68f4852ce0 OpenSim/Data/PGSQL/Resources (AgentPrefs.migrations):
[09:31] <cia-opensim> Mantis 0007610. Changed AgentPrefs table in PGSQL to use uuid instead of varchar(36), to be consistent with the otehr pgsql tables. Since the first commit on this was yesterday, there's no migration; instead, the version starts at 2. If anyone created the table yesterday, it will be dropped and recreated.
[09:31] <cia-opensim> WARNING: untested. (I don't use pg)
(0028706)
Gavin Hird (reporter)
2015-06-14 09:41

I'll build it and test it, but I am uncertain how to test it in-world properly? Presumably I need FS as the other viewer don't support hover-height?

I only tested the table got created without errors, robust started properly, the sims started and that everything worked as normal.
(0028709)
cinderblocks (reporter)
2015-06-14 10:13

Hover Height is not supported yet, and in fact, even if enabled in OpenSim, it would only work in Singularity Alpha builds as Firestorm has disabled it across the board in OpenSim.

To test, change language in the viewer (any viewer) to Japanese and relog, rez a prim with the following script:

default{
    touch_start( integer num_detected ){
        llOwnerSay(llGetAgentLanguage(llDetectedKey(0)));
    }
}

The object should respond with 'ja' when touched.

A record should also be created for your avatar in the AgentPrefs table upon login even without changing language.
(0028713)
Gavin Hird (reporter)
2015-06-14 10:43

Getting a shitstorm of error messages :-)

I eliminated some of them by renaming the table as Postgres is case sensitive, so with a table called AgentPrefs and the SQL calling agentprefs it did not work. All the other tables are all lowercase so that works ok. I'll upload a revised version.


This happens after you have changed the language and then log in.


19:38:08 - [AGENT PREFERENCES HANDLER]: Exception System.InvalidCastException: Cannot cast from source type to destination type.
  at NpgsqlTypes.BasicNativeToBackendTypeConverter.ToSingleDouble (NpgsqlTypes.NpgsqlNativeTypeInfo TypeInfo, System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery (System.Object NativeData) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend (System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.PassParam (System.Text.StringBuilder query, Npgsql.NpgsqlParameter p) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetClearCommandText () [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetCommandText () [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) Npgsql.NpgsqlCommand:GetCommandText ()
  at Npgsql.NpgsqlQuery.WriteToStream (System.IO.Stream outputStream) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlReadyState.QueryEnum (Npgsql.NpgsqlConnector context, Npgsql.NpgsqlCommand command) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlConnector.QueryEnum (Npgsql.NpgsqlCommand queryCommand) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetReader (CommandBehavior cb) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
  at OpenSim.Data.PGSQL.PGSQLGenericTableHandler`1[OpenSim.Data.AgentPreferencesData].Store (OpenSim.Data.AgentPreferencesData row) [0x00000] in <filename unknown>:0
  at OpenSim.Services.UserAccountService.AgentPreferencesService.StoreAgentPreferences (OpenSim.Services.Interfaces.AgentPrefs data) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.SetAgentPrefs (System.Collections.Generic.Dictionary`2 request) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.ProcessRequest (System.String path, System.IO.Stream requestData, IOSHttpRequest httpRequest, IOSHttpResponse httpResponse) [0x00000] in <filename unknown>:0
19:38:08 - [PRESENCE SERVICE]: ReportAgent: session af8c8f82-d886-4256-bee9-cf2ffffd853f, user 97d02b42-9119-4708-804e-4c7d699162ae, region d6ab91df-345c-46ef-881a-db85efa5cd2c. Previously: region 00000000-0000-0000-0000-000000000000
19:38:08 - [AGENT PREFERENCES HANDLER]: Exception System.InvalidCastException: Cannot cast from source type to destination type.
  at NpgsqlTypes.BasicNativeToBackendTypeConverter.ToSingleDouble (NpgsqlTypes.NpgsqlNativeTypeInfo TypeInfo, System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery (System.Object NativeData) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend (System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.PassParam (System.Text.StringBuilder query, Npgsql.NpgsqlParameter p) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetClearCommandText () [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetCommandText () [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) Npgsql.NpgsqlCommand:GetCommandText ()
  at Npgsql.NpgsqlQuery.WriteToStream (System.IO.Stream outputStream) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlReadyState.QueryEnum (Npgsql.NpgsqlConnector context, Npgsql.NpgsqlCommand command) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlConnector.QueryEnum (Npgsql.NpgsqlCommand queryCommand) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetReader (CommandBehavior cb) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
  at OpenSim.Data.PGSQL.PGSQLGenericTableHandler`1[OpenSim.Data.AgentPreferencesData].Store (OpenSim.Data.AgentPreferencesData row) [0x00000] in <filename unknown>:0
  at OpenSim.Services.UserAccountService.AgentPreferencesService.StoreAgentPreferences (OpenSim.Services.Interfaces.AgentPrefs data) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.SetAgentPrefs (System.Collections.Generic.Dictionary`2 request) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.ProcessRequest (System.String path, System.IO.Stream requestData, IOSHttpRequest httpRequest, IOSHttpResponse httpResponse) [0x00000] in <filename unknown>:0
19:38:09 - [AGENT PREFERENCES HANDLER]: Exception System.InvalidCastException: Cannot cast from source type to destination type.
  at NpgsqlTypes.BasicNativeToBackendTypeConverter.ToSingleDouble (NpgsqlTypes.NpgsqlNativeTypeInfo TypeInfo, System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery (System.Object NativeData) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend (System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.PassParam (System.Text.StringBuilder query, Npgsql.NpgsqlParameter p) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetClearCommandText () [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetCommandText () [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) Npgsql.NpgsqlCommand:GetCommandText ()
  at Npgsql.NpgsqlQuery.WriteToStream (System.IO.Stream outputStream) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlReadyState.QueryEnum (Npgsql.NpgsqlConnector context, Npgsql.NpgsqlCommand command) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlConnector.QueryEnum (Npgsql.NpgsqlCommand queryCommand) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetReader (CommandBehavior cb) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
  at OpenSim.Data.PGSQL.PGSQLGenericTableHandler`1[OpenSim.Data.AgentPreferencesData].Store (OpenSim.Data.AgentPreferencesData row) [0x00000] in <filename unknown>:0
  at OpenSim.Services.UserAccountService.AgentPreferencesService.StoreAgentPreferences (OpenSim.Services.Interfaces.AgentPrefs data) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.SetAgentPrefs (System.Collections.Generic.Dictionary`2 request) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.ProcessRequest (System.String path, System.IO.Stream requestData, IOSHttpRequest httpRequest, IOSHttpResponse httpResponse) [0x00000] in <filename unknown>:0
19:38:14 - [AGENT PREFERENCES HANDLER]: Exception System.InvalidCastException: Cannot cast from source type to destination type.
  at NpgsqlTypes.BasicNativeToBackendTypeConverter.ToSingleDouble (NpgsqlTypes.NpgsqlNativeTypeInfo TypeInfo, System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery (System.Object NativeData) [0x00000] in <filename unknown>:0
  at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend (System.Object NativeData, Boolean ForExtendedQuery) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.PassParam (System.Text.StringBuilder query, Npgsql.NpgsqlParameter p) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetClearCommandText () [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetCommandText () [0x00000] in <filename unknown>:0
  at (wrapper remoting-invoke-with-check) Npgsql.NpgsqlCommand:GetCommandText ()
  at Npgsql.NpgsqlQuery.WriteToStream (System.IO.Stream outputStream) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlReadyState.QueryEnum (Npgsql.NpgsqlConnector context, Npgsql.NpgsqlCommand command) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlConnector.QueryEnum (Npgsql.NpgsqlCommand queryCommand) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.GetReader (CommandBehavior cb) [0x00000] in <filename unknown>:0
  at Npgsql.NpgsqlCommand.ExecuteNonQuery () [0x00000] in <filename unknown>:0
  at OpenSim.Data.PGSQL.PGSQLGenericTableHandler`1[OpenSim.Data.AgentPreferencesData].Store (OpenSim.Data.AgentPreferencesData row) [0x00000] in <filename unknown>:0
  at OpenSim.Services.UserAccountService.AgentPreferencesService.StoreAgentPreferences (OpenSim.Services.Interfaces.AgentPrefs data) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.SetAgentPrefs (System.Collections.Generic.Dictionary`2 request) [0x00000] in <filename unknown>:0
  at OpenSim.Server.Handlers.AgentPreferences.AgentPreferencesServerPostHandler.ProcessRequest (System.String path, System.IO.Stream requestData, IOSHttpRequest httpRequest, IOSHttpResponse httpResponse) [0x00000] in <filename unknown>:0
(0028714)
Diva (administrator)
2015-06-14 10:46
edited on: 2015-06-14 10:46

I'm afraid you're going to have to solve this, Gavin, since you're the one who sent the PGSQL table creation code. I can't test any of this. The best I can do is to revert.

Make sure the code you send actually works.

(0028715)
Gavin Hird (reporter)
2015-06-14 10:47

It never worked anyway, this is actually progress. It is very common to have casing errors like this.
(0028716)
Gavin Hird (reporter)
2015-06-14 10:57

@cinder, can you give me the queries it is supposed to build so I can test the right syntax with Postgres.

Are the queries only built in PGSQLAgentPreferences.cs or are they formed elsewhere?

thx
(0028717)
aiaustin (developer)
2015-06-14 11:05
edited on: 2015-06-14 11:05

Cinder, will you raise then JIRA on Firestorm to have Hover Height enabled ready for the connection to the new changes in OpenSim?

(0028718)
cinderblocks (reporter)
2015-06-14 11:08

Looks like their jira is down for maintenance at the moment. I'll raise the issue when the implementation is a little closer. It will likely need another module to tie AgentPrefs in with sending the udp hover packet
(0028719)
cinderblocks (reporter)
2015-06-14 11:14

Gavin, the data model for the table can be found in IAgentPreferencesService.cs:

        UUID PrincipalID;
        string AccessPrefs;
        double HoverHeight;
        string Language;
        bool LanguageIsPublic;
        int PermEveryone;
        int PermGroup;
        int PermNextOwner;

The queries are generic Get and Set of those parameters; nothing fancy.
(0028720)
Gavin Hird (reporter)
2015-06-14 11:20

I think it perhaps does not like the uppercase UUID. Maybe the Get statement in line 47 needs to be

Get("PrincipalID::uuid",
(0028721)
Gavin Hird (reporter)
2015-06-14 11:29
edited on: 2015-06-14 11:31

If you look in the other PGSQL...Data.cs files they explicitly build the queries there, so I think it will have to be done the same way. Maybe ask Bluewall as he did some work on the PGSQL adapter last fall.

(0028722)
Diva (administrator)
2015-06-14 11:37

Is this preventing your sims from starting and running well? Or are these just exceptions printed on the console that don't have any further consequences?
(0028723)
cinderblocks (reporter)
2015-06-14 11:39

PGSQLGenericTableHandler should be building the queries itself. If it can't do that, the problem resides there, I would think.
(0028724)
Gavin Hird (reporter)
2015-06-14 11:40

Everything is running ok, so there is no functional change as far as I can see.
It is also only invoked on logging back in after changing language - which "never" happens unless you have a very good reason - such as testing this.
(0028725)
Gavin Hird (reporter)
2015-06-14 11:43

@cinder there is nothing that points to it doing that for the other tables.

It could very well be a fundamental problem with PGSQLGenericTableHandler, but that is possibly a longer wash to fix.
(0028726)
Gavin Hird (reporter)
2015-06-14 12:04

If I execute the query that is built on line 106 in PGSQLGenericTableHandler.cs for the table in question:

SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'agentprefs';

You get the following result.

PrincipalID uuid
AccessPrefs character
HoverHeight double precision
Language character
LanguageIsPublic boolean
PermEveryone integer
PermGroup integer
PermNextOwner integer

So unless you use these types in the queries, you will get casting errors.
(0028728)
Diva (administrator)
2015-06-14 14:51

[14:49] <cia-opensim> opensim: diva * r4a516fa1569e OpenSim/Data/PGSQL (2 files in 2 dirs):
[14:49] <cia-opensim> Removed the pgsql table for AgentPrefs, because it is not working. For now the Agent Preferences feature will not work with PGSQL installations -- someone who uses PGSQL needs to send a table that works.

This has nothing to do with the code that's in opensim; it's the table definition that is broken -- meaning incompatible with the code. The code is generic and should not change; the table needs to adjust. According to the only error reported, there is a problem, at least, with the double field.
(0028729)
Gavin Hird (reporter)
2015-06-14 23:39

The original table definition did not work at all. There is a number of reasons for that:

1. Postgres use standard ANSI SQL " quoting and not ' like MySQL does
2. The DOUBLE(30, 27) definition is a MYSQL only notation that does not work in any other databases
3. The notation for UNIQUE KEY and PRIMARY KEY in the create statement is not used by Postgres the way it is formed in the original AgentPrefs.migrations file.


The log output of the original statements are below.


2015-06-14 15:56:58,837 INFO - OpenSim.Server.OpenSimServer [SERVER]: Loading AgentPreferencesServiceConnector on port 8003
2015-06-14 15:56:58,842 INFO - OpenSim.Data.Migration [MIGRATIONS]: Upgrading AgentPrefs to latest revision 1.
2015-06-14 15:56:58,842 INFO - OpenSim.Data.Migration [MIGRATIONS]: NOTE - this may take a while, don't interrupt this process!
2015-06-14 15:56:58,845 DEBUG - OpenSim.Data.Migration [MIGRATIONS]: Cmd was ERROR: 42601: syntax error at or near "`" in SQL: BEGIN; CREATE TABLE `AgentPrefs` ( `PrincipalID` UUID NOT NULL, `AccessPrefs` CHAR(2) NOT NULL DEFAULT 'M', `HoverHeight` DOUBLE(30, 27) NOT NULL DEFAULT 0, `Language` CHAR(5) NOT NULL DEFAULT 'en-us', `LanguageIsPublic` BOOLEAN NOT NULL DEFAULT 1, `PermEveryone` INT(6) NOT NULL DEFAULT 0, `PermGroup` INT(6) NOT NULL DEFAULT 0, `PermNextOwner` INT(6) NOT NULL DEFAULT 532480, UNIQUE KEY `PrincipalID` (`PrincipalID`), PRIMARY KEY(`PrincipalID`)); COMMIT;
2015-06-14 15:56:58,845 DEBUG - OpenSim.Data.Migration [MIGRATIONS]: An error has occurred in the migration. If you're running OpenSim for the first time then you can probably safely ignore this, since certain migration commands attempt to fetch data out of old tables. However, if you're using an existing database and you see database related errors while running OpenSim then you will need to fix these problems manually. Continuing.
2015-06-14 15:56:58,846 INFO - OpenSim.Data.Migration [MIGRATIONS]: Creating AgentPrefs at version 1
2015-06-14 15:56:58,873 DEBUG - OpenSim.Services.UserAccountService.AgentPreferencesService [AGENT PREFERENCES SERVICE]: Starting agent preferences service
2015-06-14 15:56:58,873 INFO - OpenSim.Server.OpenSimServer [SERVER]: AgentPreferencesServiceConnector loaded successfully
(0028730)
Gavin Hird (reporter)
2015-06-15 00:28

The Double Precision datatype has been used in multiple fields for both the definition of the "prims" and "regionwindlight" tables in the RegionStore.migrations.

where for the regionwindlight table they have built explicit queries in the PGSQLSimulationData.cs file starting around line 739

Who wrote the PGSQL adapter originally? – So we could seek help there and at the same time fix some other issues with it.
(0028735)
Gavin Hird (reporter)
2015-06-18 03:10

The patch has probably resolved the issue for SQLite but not for PGSQL, for which the patch did/could not work (see previous comments.)

Could anyone please verify if this now works for SQLite?
(0029946)
TomTheDragon (reporter)
2016-01-12 05:38

I was able to use the DOUBLE PRECISION datatype in PGSQL with the following modifications to the PGSQLManager.cs:

--- a/OpenSim/Data/PGSQL/PGSQLManager.cs
+++ b/OpenSim/Data/PGSQL/PGSQLManager.cs
@@ -251,7 +251,7 @@ namespace OpenSim.Data.PGSQL
             }
             if (PGFieldType == "double precision")
             {
- return (Double)value;
+ return Convert.ToDouble(value);
             }
             return CreateParameterValue(value);
         }

It is quite possible that this is not valid or is inefficient, however this seems to avoid a casting problem when using DOUBLE PRECISION in general.
(0029947)
Gavin Hird (reporter)
2016-01-12 07:42

@Tom,

I'll check it out and see how it works. In this case I don't think efficiency is much of a concern as the code is only called for this particular functionality (that is the only time I have seen errors for it) and then very infrequently.

I found there is a general problem with the generic table handler where the MySQL version and the code in general depends on an "upsert" statement that only will be supported in PostgreSQL 9.5 (that is now in release candidate 1 status). Even that code is not called very frequently but prevents classifieds, offline IM and a couple other functions from working properly in the current codebase.

Rather than try to fix the current handler, I thought I'd wait it out till 9.5 is released and then clean it up.

That is also probably the time to switch from the NPSQL included in mono, but no longer maintained by the project, to the version that is actively maintained.
(0029948)
TomTheDragon (reporter)
2016-01-12 07:50

If you are going to test this, here is the table format I've used for this one. There may still be a few issues, the LanguageIsPublic is false no matter what I select in the UI.

#---------
:VERSION 1

BEGIN TRANSACTION;

CREATE TABLE AgentPrefs (
    "PrincipalID" CHAR(36) NOT NULL PRIMARY KEY,
    "AccessPrefs" CHAR(2) NOT NULL DEFAULT 'M',
    "HoverHeight" DOUBLE PRECISION NOT NULL DEFAULT 0,
    "Language" CHAR(5) NOT NULL DEFAULT 'en-us',
    "LanguageIsPublic" BOOLEAN NOT NULL DEFAULT TRUE,
    "PermEveryone" INTEGER NOT NULL DEFAULT 0,
    "PermGroup" INTEGER NOT NULL DEFAULT 0,
    "PermNextOwner" INTEGER NOT NULL DEFAULT 532480
);

COMMIT;
(0029949)
TomTheDragon (reporter)
2016-01-12 07:53

Some printf debugging shows that this issue is outside of the database code altogether. Upon login,

10:53:05 - [CompleteMovement]: openChildAgents: 412ms
10:53:05 - [CompleteMovement]: SendInitialDataToMe: 412ms
10:53:05 - [CompleteMovement]: friendsModule: 415ms
10:53:05 - [CompleteMovement]: end: 415ms
10:53:05 - [AgentPrefs]: UpdateAgentPreferences for 5b8005ec-1b25-4180-a4b6-1ca28890f49c
10:53:05 - [AGENTPREFS]: Language is public? False
(0029950)
Gavin Hird (reporter)
2016-01-12 07:54

Which is probably because of the generic table hander that does not update fields, but just overwrites one record all the time.
(0029951)
TomTheDragon (reporter)
2016-01-12 07:55

However, it does indeed get set on login:
            if (req.ContainsKey("language_is_public"))
            {
                data.LanguageIsPublic = req["language_is_public"].AsBoolean();
                m_log.DebugFormat("[AGENTPREFS] Language is public? {0}", data.LanguageIsPublic);
            }
(0029952)
Gavin Hird (reporter)
2016-01-12 07:59

The acid test is what happens with the record when the next avatar does the same. The one record that is ever created will be overwritten.

I'll check it out in the course of the evening – there is a small test script a few posts further up that you might want to use.
(0029953)
TomTheDragon (reporter)
2016-01-12 10:17

Certainly getting more than one record.

ostest=# select "PrincipalID","Language","PermEveryone" from agentprefs;
             PrincipalID | Language | PermEveryone
--------------------------------------+----------+--------------
 5b8005ec-1b25-4180-a4b6-1ca28890f49c | de | 32768
 0f4e6e37-6933-438c-bcde-0acf49f46d33 | en-us | 32768
(0029954)
Gavin Hird (reporter)
2016-01-12 11:35

That's good to hear.

I had completely disabled that portion of the code so when I re-enabled the test script worked properly, but no record was written to the DB. I have added quite a bit of the http handling code from the 0.9.dev to my servers, and not everything is working fully yet, so this is another item to be sorted. I'll figure it out :-)

I have the same table structure as you, but with an index agentprefs_pkey with SQL
CREATE UNIQUE INDEX agentprefs_pkey ON agentprefs USING btree ("PrincipalID")
(0029957)
TomTheDragon (reporter)
2016-01-12 20:07

I have attached a patch which should properly implement the agent preferences, and eliminate the problems of casting errors.
(0029958)
Gavin Hird (reporter)
2016-01-12 23:58

Could you please change the table definition for the patch to use uuid rather than char36() for PrincipalID?

Thanks,
(0029959)
Gavin Hird (reporter)
2016-01-13 01:26

It is working now, writing records to the DB. I have only tested it with the 3.8.7 and 4.0.1 builds of Kokua.

When it comes to LanguageIsPublic that is only a setting in the advanced debug settings in Kokua, and despite the default being set to True, which is also the default for the database field, it is set to false somewhere in the code. Will have to investigate that.

The same is the case for the Profile floater "Show in search" that also is impossible to set to true via the UI. The last time I looked at it, I concluded it was the generic table handler that was involved, so I had put that on the back burner to be fixed after the release of PostgreSQL 9.5.
(0029960)
TomTheDragon (reporter)
2016-01-13 10:18

Attached is a new patch, only difference is it uses the UUID type. I did not have to do anything special anywhere else for this, and still has the same behavior.
(0029961)
Gavin Hird (reporter)
2016-01-13 11:11

With the patch included the issue should largely be resolved with one minor issue to be fixed when the generic table handler is updated to handle new functionality in PostgreSQL 9.5.
(0029962)
Gavin Hird (reporter)
2016-01-13 11:14

Thanks Tom for the update.

UUID gives a small performance improvement server side over the generic charnnn() data type. There are still a few tables that does not have char36() replaced by UUID.
(0030082)
Gavin Hird (reporter)
2016-03-17 02:41

Added a new patch without the table migration as the table should be OK as is.
(0030220)
kcozens (administrator)
2016-04-24 20:55
edited on: 2016-04-25 09:16

commit b4180339151bf98e86e6f420ae6fe7582ecebd89
Author: Geir Nøklebye <geir.noklebye@dayturn.com>
Date: Thu Mar 17 10:37:27 2016 +0100

    Updated the code to handle Agent Preferences. Thanks to TomDataworks for providing a final solution.
    
    Signed-off-by: Kevin Cozens <kevin@ve3syb.ca> (mantis 0007610)

(0034558)
BillBlight (developer)
2019-02-06 11:50

Marked as Resolved but never closed, can be reopened if needed.

- Issue History
Date Modified Username Field Change
2015-06-13 09:26 Gavin Hird New Issue
2015-06-13 12:37 cinderblocks Note Added: 0028679
2015-06-13 12:39 Gavin Hird Note Added: 0028680
2015-06-13 12:56 cinderblocks File Added: MoreSQL.diff
2015-06-13 12:56 cinderblocks Note Added: 0028681
2015-06-13 13:01 Gavin Hird Note Added: 0028682
2015-06-13 13:03 cinderblocks Note Edited: 0028681 View Revisions
2015-06-13 13:04 cinderblocks Status new => patch included
2015-06-13 14:45 Diva Note Added: 0028685
2015-06-13 14:50 cinderblocks File Deleted: MoreSQL.diff
2015-06-13 14:51 cinderblocks File Added: AgentPrefsAdapters.diff
2015-06-13 14:52 cinderblocks Note Added: 0028686
2015-06-13 14:55 Diva Note Added: 0028687
2015-06-13 22:31 Diva Relationship added related to 0007157
2015-06-13 22:43 Gavin Hird Note Added: 0028691
2015-06-14 01:59 Gavin Hird Note Added: 0028692
2015-06-14 01:59 Gavin Hird File Added: os_groups_Store.migrations
2015-06-14 01:59 Gavin Hird File Added: AgentPrefs.migrations
2015-06-14 02:01 Gavin Hird Note Edited: 0028692 View Revisions
2015-06-14 02:13 Gavin Hird Note Added: 0028693
2015-06-14 07:35 Gavin Hird File Deleted: AgentPrefs.migrations
2015-06-14 07:35 Gavin Hird File Added: AgentPrefs.migrations
2015-06-14 07:38 Gavin Hird Note Added: 0028696
2015-06-14 07:54 Diva Note Deleted: 0028692
2015-06-14 07:54 Diva File Deleted: os_groups_Store.migrations
2015-06-14 07:56 Diva Note Added: 0028699
2015-06-14 08:08 Gavin Hird Note Added: 0028702
2015-06-14 08:28 cinderblocks Note Added: 0028703
2015-06-14 08:39 Gavin Hird Note Added: 0028704
2015-06-14 09:32 Diva Note Added: 0028705
2015-06-14 09:41 Gavin Hird Note Added: 0028706
2015-06-14 10:13 cinderblocks Note Added: 0028709
2015-06-14 10:43 Gavin Hird Note Added: 0028713
2015-06-14 10:44 Gavin Hird File Deleted: AgentPrefs.migrations
2015-06-14 10:44 Gavin Hird File Added: AgentPrefs.migrations
2015-06-14 10:46 Diva Note Added: 0028714
2015-06-14 10:46 Diva Note Edited: 0028714 View Revisions
2015-06-14 10:47 Gavin Hird Note Added: 0028715
2015-06-14 10:57 Gavin Hird Note Added: 0028716
2015-06-14 11:05 aiaustin Note Added: 0028717
2015-06-14 11:05 aiaustin Note Edited: 0028717 View Revisions
2015-06-14 11:08 cinderblocks Note Added: 0028718
2015-06-14 11:14 cinderblocks Note Added: 0028719
2015-06-14 11:20 Gavin Hird Note Added: 0028720
2015-06-14 11:29 Gavin Hird Note Added: 0028721
2015-06-14 11:31 Gavin Hird Note Edited: 0028721 View Revisions
2015-06-14 11:37 Diva Note Added: 0028722
2015-06-14 11:39 cinderblocks Note Added: 0028723
2015-06-14 11:40 Gavin Hird Note Added: 0028724
2015-06-14 11:43 Gavin Hird Note Added: 0028725
2015-06-14 12:04 Gavin Hird Note Added: 0028726
2015-06-14 14:51 Diva Note Added: 0028728
2015-06-14 23:39 Gavin Hird Note Added: 0028729
2015-06-15 00:28 Gavin Hird Note Added: 0028730
2015-06-18 03:10 Gavin Hird Note Added: 0028735
2015-06-18 03:10 Gavin Hird Status patch included => patch feedback
2016-01-12 05:38 TomTheDragon Note Added: 0029946
2016-01-12 07:42 Gavin Hird Note Added: 0029947
2016-01-12 07:50 TomTheDragon Note Added: 0029948
2016-01-12 07:53 TomTheDragon Note Added: 0029949
2016-01-12 07:54 Gavin Hird Note Added: 0029950
2016-01-12 07:55 TomTheDragon Note Added: 0029951
2016-01-12 07:59 Gavin Hird Note Added: 0029952
2016-01-12 10:17 TomTheDragon Note Added: 0029953
2016-01-12 10:51 TomTheDragon Relationship added related to 0007805
2016-01-12 11:35 Gavin Hird Note Added: 0029954
2016-01-12 20:05 TomTheDragon File Added: 0001-PostgreSQL-changes-to-fix-Mantis-7610-Agent-Preferen.patch
2016-01-12 20:07 TomTheDragon Note Added: 0029957
2016-01-12 23:58 Gavin Hird Note Added: 0029958
2016-01-13 01:26 Gavin Hird Note Added: 0029959
2016-01-13 10:17 TomTheDragon File Deleted: 0001-PostgreSQL-changes-to-fix-Mantis-7610-Agent-Preferen.patch
2016-01-13 10:17 TomTheDragon File Added: 0001-New-fix-for-Mantis-7610-uuid.patch
2016-01-13 10:18 TomTheDragon Note Added: 0029960
2016-01-13 11:11 Gavin Hird Note Added: 0029961
2016-01-13 11:11 Gavin Hird Status patch feedback => patch included
2016-01-13 11:14 Gavin Hird Note Added: 0029962
2016-03-17 02:39 Gavin Hird File Added: agentprefs_PGSQL.patch
2016-03-17 02:41 Gavin Hird Note Added: 0030082
2016-03-17 02:43 Gavin Hird Summary Commit 6f71d5, c1ddb7, 0fa94f lacks support for PGSQL adapter => PGSQL Commit 6f71d5, c1ddb7, 0fa94f for Agenpreferences lacks support for Postgres
2016-04-24 20:55 kcozens Note Added: 0030220
2016-04-24 20:55 kcozens Status patch included => resolved
2016-04-24 20:55 kcozens Fixed in Version => master (dev code)
2016-04-24 20:55 kcozens Resolution open => fixed
2016-04-24 20:55 kcozens Assigned To => kcozens
2016-04-25 09:16 kcozens Note Edited: 0030220 View Revisions
2019-02-06 11:50 BillBlight Note Added: 0034558
2019-02-06 11:50 BillBlight Status resolved => closed


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker