Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007645opensim[REGION] OpenSim Corepublic2015-07-19 08:562015-07-23 11:55
ReporterVerwijs 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformLlinuxOSOpenSUSEOS Version13.2
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0007645: column "cloud_scroll_x_lock" is of type smallint but expression is of type boolean
DescriptionAPPLICATION EXCEPTION DETECTED: System.UnhandledExceptionEventArgs

Exception: Npgsql.NpgsqlException:
column "cloud_scroll_x_lock" is of type smallint but expression is of type boolean
Severity: ERROR
Code: 42804
Hint: You will need to rewrite or cast the expression.
  at Npgsql.NpgsqlState+<ProcessBackendResponses_Ver_3>d__a.MoveNext () [0x00000] in <filename unknown>:0
  at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] in <filename unknown>:0

Application is terminating: True

[ERROR] FATAL UNHANDLED EXCEPTION: Npgsql.NpgsqlException:
column "cloud_scroll_x_lock" is of type smallint but expression is of type boolean
Severity: ERROR
Code: 42804
Hint: You will need to rewrite or cast the expression.
  at Npgsql.NpgsqlState+<ProcessBackendResponses_Ver_3>d__a.MoveNext () [0x00000] in <filename unknown>:0
  at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] in <filename unknown>:0
Steps To Reproduce..
Additional InformationMono version 3.8.0
Database: PostgreSQL

if i'm correct this is a windlight setting....
Tagsdatabase definition, Linux, PostgreSQL
Git Revision or version numberr/26106
Run ModeStandalone (1 Region)
Physics EngineBulletSim
Script Engine
EnvironmentMono / Linux64
Mono VersionOther
ViewerKokua 3.7.27.35441 x64
Attached Files? file icon RegionStore.migrations [^] (41,568 bytes) 2015-07-20 18:55
log file icon r-26106_OpenSim.log [^] (79,745 bytes) 2015-07-20 19:02
? file icon regionwindlight.sql [^] (5,198 bytes) 2015-07-21 13:26
log file icon r-26112_OpenSim.log [^] (83,575 bytes) 2015-07-22 12:57

- Relationships

-  Notes
(0028925)
Verwijs (reporter)
2015-07-19 11:01

OpenSim/Data/PGSQL/Resources/RegionStore.migrations


:VERSION 40 #-- regionwindlight changed type from smallint to bool

BEGIN TRANSACTION;

ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock DROP DEFAULT;
ALTER TABLE regionwindlight ALTER cloud_scroll_x_lock TYPE bool USING CASE WHEN cloud_scroll_x_lock=0 THEN FALSE ELSE TRUE END;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock SET DEFAULT FALSE;

ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_y_lock DROP DEFAULT;
ALTER TABLE regionwindlight ALTER cloud_scroll_y_lock TYPE bool USING CASE WHEN cloud_scroll_y_lock=0 THEN FALSE ELSE TRUE END;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_y_lock SET DEFAULT FALSE;

ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds DROP DEFAULT;
ALTER TABLE regionwindlight ALTER draw_classic_clouds TYPE bool USING CASE WHEN draw_classic_clouds=0 THEN FALSE ELSE TRUE END;
ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds SET DEFAULT FALSE;

COMMIT;


"bool" should be "boolean", bool doesn't exist as type....
(0028926)
melanie (administrator)
2015-07-19 11:43

Someone who actually uses pgsql needs to fix and test this.
(0028930)
Verwijs (reporter)
2015-07-20 18:54
edited on: 2015-07-20 19:04

OpenSim/Data/PGSQL/Resources/RegionStore.migrations

found it :)

at the end of RegionStore.migrations, ":VERSION 40" should be:
(added comments for reference)

tested several times and works...

--

BEGIN TRANSACTION;

# /* cloud_scroll_x_lock */
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock DROP DEFAULT;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock TYPE boolean
  USING CASE cloud_scroll_x_lock
    WHEN '1' THEN true
    ELSE false END;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock SET DEFAULT false;

# /* cloud_scroll_y_lock */
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_y_lock DROP DEFAULT;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_y_lock TYPE boolean
  USING CASE cloud_scroll_y_lock
    WHEN '1' THEN true
    ELSE false END;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_y_lock SET DEFAULT false;

# /* draw_classic_clouds */
ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds DROP DEFAULT;
ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds TYPE boolean
  USING CASE draw_classic_clouds
    WHEN '1' THEN true
    ELSE false END;
ALTER TABLE regionwindlight ALTER COLUMN draw_classic_clouds SET DEFAULT false;

COMMIT;

--

Other Note:

":VERSION 41" missing ":" character

- added patch as attachment.
- (optional) added opensim.log r/26106

(0028935)
Gavin Hird (reporter)
2015-07-21 13:13

Not sure why this change was needed as migration 40 from Oct 15, 2013 had no problems generating the tables correct. PostgreSQL will accept bool in SQL statements as well as boolean.
(0028937)
Gavin Hird (reporter)
2015-07-21 13:26

I have enclosed a SQL file that will generate the table as it were from the original migration 40 of 2013.
(0028944)
Verwijs (reporter)
2015-07-22 12:57

regionwindlight.sql works. this could be added/updated to
OpenSim/Data/PGSQL/Resources/RegionStore.migrations (:VERSION 25)

:VERSION 40 could be removed since cloud_scroll_x_lock, cloud_scroll_y_lock,
draw_classic_clouds are bool then..

:VERSION 41 becomes :VERSION 40


other note:

ALTER TABLE land ALTER LandFlags TYPE bigint;

deeds to be:

ALTER TABLE land ALTER "LandFlags" TYPE bigint;

or else you get:

column "landflags" of relation "land" does not exist


this would solve this bug...

- added r-26112_OpenSim.log (with regionwindlight table) for view
(0028945)
Gavin Hird (reporter)
2015-07-22 13:00

You can't remove the previous versions but only roll the table forward. Otherwise you get all kinds of issues with existing installations.
(0028946)
melanie (administrator)
2015-07-22 16:26

Also, these values are booleans, so the code should actually be made to send booleans to the engine and expect them, instead of converting booleans to int. MySQL lacks a system boolean type but PGSQL has it, why not use it?
(0028947)
Gavin Hird (reporter)
2015-07-23 00:35

@Melanie. Agreed. The PGSQL code needs a general brush-up and this should be done as part of such an effort.

It can also store JSON datatypes directly and operate on them. That could possibly also be put into action.
(0028950)
Verwijs (reporter)
2015-07-23 03:31

A "brush-up" would be very welcome... FriendsStore, UserAccount, AuthStore, InventoryStore also have miner quirks.... (see opensim.log) but first lets implement a solution for this bug...
(0028951)
Gavin Hird (reporter)
2015-07-23 03:49
edited on: 2015-07-23 03:57

@Verwijs

1. What version of Postgres are you on?
Out of curiosity it also looks like you have some pretty nonstandard logging messages during startup, so where did you source your standalone code from?

2. Go to your pgsql console or another tool of your choice and issue the following SQL statements:

ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock TYPE bool;
ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock TYPE boolean;

Both should return the response ALTER TABLE.

The LandFlag issue was fixed in R/26113 last night and the migration executes correct and migrates regionstore to version 41 in the Migrations table in addition to changing the Land.LandFlags field to bigint. I have run the migration on 4 server instances this morning without a hitch.

(0028952)
Gavin Hird (reporter)
2015-07-23 03:56

> FriendsStore, UserAccount, AuthStore, InventoryStore also have miner quirks.... (see opensim.log)

Actually they don't because associated with them in your log file is the following messages:

"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."

This always happens when you first initialize the tables.

Once you get the base updated all the tables you mention works. There are other tables that have issues related to IM and Group chat. The rest of the outstanding issues for the PGSQL support is in the code and most likely in the PGSQLGenericTableHandler But I am sure we can fix that :-)
(0028954)
Verwijs (reporter)
2015-07-23 04:22

PostgreSQL Version 9.3.6-40.1
OS: OpenSUSE 13.2 (Linux 3.16.7-21-desktop)
KDE: 4.14.9
Mono version 3.8.0-1.4

for configuration its pretty basic, yes i use Autobackup for region backup
and "Severe" for OSFunction(s) but only for standalone.


and for the other "quirks", i understand. if there's no data yet, pgsql can't find anything....
(0028956)
Gavin Hird (reporter)
2015-07-23 04:28

Do you use PGAdmin from http://www.pgadmin.org [^] ?

They link to a very good (probably best) source for the PostgreSQL executables. http://www.enterprisedb.com/products-services-training/pgdownload [^]

Navicat is an excellent tool and although this is a commercial product they have 30 day trials http://www.navicat.com/products/navicat-for-postgresql [^]
(0028958)
aiaustin (developer)
2015-07-23 07:39
edited on: 2015-07-23 07:43

Melanie noted that PostgreSQL needs quotes round column/field names in a recent commit... I see that VERSION 25 (after first two entries which are in double quote) and VERSION 40 migrations in file

     OpenSim/Data/PGSQL/Resources/RegionStore.migrations

do not seem to have quotes round the column/field names? Can someone who understands PostgreSQL check the whole file is okay?

Maybe they are all fine because they are all lower case, but for uniformity wouldn't it make more sense always to enclose field names in quotes to avoid future errors if names have mixed case?

:VERSION 25

BEGIN TRANSACTION;
CREATE TABLE regionwindlight (
  "region_id" varchar(36) NOT NULL DEFAULT '000000-0000-0000-0000-000000000000' PRIMARY KEY,
  "water_color_r" double precision NOT NULL DEFAULT '4.000000',
  water_color_g double precision NOT NULL DEFAULT '38.000000',
  ...

:VERSION 40 #-- regionwindlight changed type from smallint to bool

BEGIN TRANSACTION;

ALTER TABLE regionwindlight ALTER COLUMN cloud_scroll_x_lock DROP DEFAULT;
...

(0028960)
Gavin Hird (reporter)
2015-07-23 07:48
edited on: 2015-07-23 07:57

The whole file is OK. It works!

Postgres does not always need quotes and how it is to be quoted is rather convoluted compared to MySQL. The quoting is much closer to standard SQL / Oracle / SQL server quoting, but still convoluted.

You can check the statements yourself on the PGSQL console.

Here is an example of a query that has the variety of quoting you often will have to use in a single statement.

SELECT assets.name,
    assets.description,
    '1970-01-01 00:00:00'::timestamp without time zone + assets.access_time::double precision * '00:00:01'::interval AS "Last Access"
   FROM assets
  WHERE NOT (assets.id IN ( SELECT inventoryitems."assetID"::uuid FROM inventoryitems))
          AND NOT (assets.id IN ( SELECT primitems."assetID" FROM primitems))
          AND NOT (assets.id IN ( SELECT regions."parcelMapTexture" FROM regions));

(0028962)
Verwijs (reporter)
2015-07-23 11:07

I found information here about quotes:
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html [^]

"4.1.1. Identifiers and Key Words"

or here:
http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x1428_003.htm [^]

"Quoted identifiers"
(0028963)
Gavin Hird (reporter)
2015-07-23 11:55

After the LandFlags field was updated it now looks like landtool.php is working again – at least being called, but the OMC module does not handle it correct for zero cost sales. – Which is a different story altogether.

Don't think this issue ever ended up in Mantis, but in the OSG forums http://forums.osgrid.org/viewtopic.php?f=14&t=5472 [^]

- Issue History
Date Modified Username Field Change
2015-07-19 08:56 Verwijs New Issue
2015-07-19 08:56 Verwijs File Added: OpenSim.log.txt
2015-07-19 10:31 Verwijs Note Added: 0028924
2015-07-19 10:58 Verwijs Note Deleted: 0028924
2015-07-19 11:01 Verwijs Note Added: 0028925
2015-07-19 11:43 melanie Note Added: 0028926
2015-07-20 18:54 Verwijs Note Added: 0028930
2015-07-20 18:55 Verwijs File Added: RegionStore.migrations
2015-07-20 18:55 Verwijs File Deleted: OpenSim.log.txt
2015-07-20 18:57 Verwijs Note Edited: 0028930 View Revisions
2015-07-20 19:01 Verwijs Note Edited: 0028930 View Revisions
2015-07-20 19:02 Verwijs File Added: r-26106_OpenSim.log
2015-07-20 19:04 Verwijs Note Edited: 0028930 View Revisions
2015-07-20 19:07 Verwijs Tag Attached: database definition
2015-07-20 19:07 Verwijs Tag Attached: Linux
2015-07-20 19:07 Verwijs Tag Attached: PostgreSQL
2015-07-21 13:13 Gavin Hird Note Added: 0028935
2015-07-21 13:26 Gavin Hird Note Added: 0028937
2015-07-21 13:26 Gavin Hird File Added: regionwindlight.sql
2015-07-22 12:57 Verwijs File Added: r-26112_OpenSim.log
2015-07-22 12:57 Verwijs Note Added: 0028944
2015-07-22 13:00 Gavin Hird Note Added: 0028945
2015-07-22 16:26 melanie Note Added: 0028946
2015-07-23 00:35 Gavin Hird Note Added: 0028947
2015-07-23 03:31 Verwijs Note Added: 0028950
2015-07-23 03:49 Gavin Hird Note Added: 0028951
2015-07-23 03:56 Gavin Hird Note Added: 0028952
2015-07-23 03:57 Gavin Hird Note Edited: 0028951 View Revisions
2015-07-23 04:22 Verwijs Note Added: 0028954
2015-07-23 04:28 Gavin Hird Note Added: 0028956
2015-07-23 07:39 aiaustin Note Added: 0028958
2015-07-23 07:40 aiaustin Note Edited: 0028958 View Revisions
2015-07-23 07:42 aiaustin Note Edited: 0028958 View Revisions
2015-07-23 07:42 aiaustin Note Edited: 0028958 View Revisions
2015-07-23 07:43 aiaustin Note Edited: 0028958 View Revisions
2015-07-23 07:48 Gavin Hird Note Added: 0028960
2015-07-23 07:54 Gavin Hird Note Edited: 0028960 View Revisions
2015-07-23 07:55 Gavin Hird Note Edited: 0028960 View Revisions
2015-07-23 07:57 Gavin Hird Note Edited: 0028960 View Revisions
2015-07-23 11:07 Verwijs Note Added: 0028962
2015-07-23 11:55 Gavin Hird Note Added: 0028963


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker