Mantis Bug Tracker

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0008200opensim[REGION] OpenSim Corepublic2017-06-27 20:252017-11-12 01:19
Reportertestdev627 
Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Statuspatch includedResolutionopen 
PlatformOSOS Version
Product Versionmaster (dev code) 
Target VersionFixed in Version 
Summary0008200: PGSQL EstateStore.migrations does not create estate_settings_id SEQUENCE and will prevent OpenSim from completing first run
DescriptionOn fresh install the PGSQL EstateStore.migrations do not complete because an exception is thrown on

CREATE TABLE public.estate_settings
(
  "EstateID" integer NOT NULL DEFAULT nextval('estate_settings_id'::regclass)....

The OpenSim.exe will fail to complete a first run. It works if you add this to the top of the migrations file

-- ----------------------------
-- SEQUENCE estate_settings_id
-- ----------------------------
CREATE SEQUENCE IF NOT EXISTS "public"."estate_settings_id"
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;
Steps To ReproduceFresh install using Postgresql with GridHypergrid Architecture

http://opensimulator.org/mantis/view.php?id=8163 [^] was a partial fix to the EstateStore.migrations problem but did not include this sequence.
TagsNo tags attached.
Git Revision or version number
Run Mode Grid (Multiple Regions per Sim)
Physics EngineBulletSim
EnvironmentUnknown
Mono VersionNone
Viewer
Attached Filespatch file icon 0001-PGSQL-EstateStore-create-estate_settings_id-SEQUENCE.patch [^] (1,151 bytes) 2017-11-11 16:47 [Show Content]

- Relationships
related to 0008163closedUbitUmarov PGSQL EstateStore.migrations leaves database without estate tables on first run 

-  Notes
(0032155)
Gavin Hird (reporter)
2017-07-17 00:02

What version of Postgres do you test on?

This is the full SQL that is needed to create the estate_settings table:

-- ----------------------------
-- Table structure for estate_settings
-- ----------------------------
DROP TABLE IF EXISTS "estate_settings";
CREATE TABLE "estate_settings" (
  "EstateID" int4 NOT NULL DEFAULT nextval('estate_settings_id'::regclass),
  "EstateName" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "AbuseEmailToEstateOwner" bool NOT NULL DEFAULT NULL,
  "DenyAnonymous" bool NOT NULL DEFAULT NULL,
  "ResetHomeOnTeleport" bool NOT NULL DEFAULT NULL,
  "FixedSun" bool NOT NULL DEFAULT NULL,
  "DenyTransacted" bool NOT NULL DEFAULT NULL,
  "BlockDwell" bool NOT NULL DEFAULT NULL,
  "DenyIdentified" bool NOT NULL DEFAULT NULL,
  "AllowVoice" bool NOT NULL DEFAULT NULL,
  "UseGlobalTime" bool NOT NULL DEFAULT NULL,
  "PricePerMeter" int4 NOT NULL DEFAULT NULL,
  "TaxFree" bool NOT NULL DEFAULT NULL,
  "AllowDirectTeleport" bool NOT NULL DEFAULT NULL,
  "RedirectGridX" int4 NOT NULL DEFAULT NULL,
  "RedirectGridY" int4 NOT NULL DEFAULT NULL,
  "ParentEstateID" int4 NOT NULL DEFAULT NULL,
  "SunPosition" float8 NOT NULL DEFAULT NULL,
  "EstateSkipScripts" bool NOT NULL DEFAULT NULL,
  "BillableFactor" float8 NOT NULL DEFAULT NULL,
  "PublicAccess" bool NOT NULL DEFAULT NULL,
  "AbuseEmail" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL,
  "EstateOwner" uuid NOT NULL DEFAULT NULL,
  "DenyMinors" bool NOT NULL DEFAULT NULL,
  "AllowLandmark" bool NOT NULL DEFAULT true,
  "AllowParcelChanges" bool NOT NULL DEFAULT true,
  "AllowSetHome" bool NOT NULL DEFAULT true
)
;
ALTER TABLE "estate_settings" OWNER TO "tableowner";

-- ----------------------------
-- Primary Key structure for table estate_settings
-- ----------------------------
ALTER TABLE "estate_settings" ADD CONSTRAINT "estate_settings_pkey" PRIMARY KEY ("EstateID");
(0032410)
TomTheDragon (reporter)
2017-11-11 15:32

I can confirm this issue.
Running PostgreSQL 10.1, opensim master.

I believe the issue is that the sequence is not created in the migration and it's expected it already exists.. should probably be added to the migration so it is smooth either way.
(0032411)
TomTheDragon (reporter)
2017-11-11 19:26

I have included patch listed in description which I have tested. YMMV.
(0032413)
UbitUmarov (administrator)
2017-11-11 19:47

ok thx, waiting a bit for more feedback to improve "mileage" :)
(0032414)
Gavin Hird (reporter)
2017-11-12 00:58

It looks like the statement

create Sequence estate_settings_id increment by 100 start with 100;

got dropped in commit 03a38a8 presumably because Navicat Postgres does not include sequences required for the table in the table definition SQL export. (I'll report that as a bug to Navicat).

The patch can be added, but please increment the version number of Estatestore.migrations to version 13. meaning there must be a new transaction block:

:VERSION 13

BEGIN TRASACTION;

-- ----------------------------
-- SEQUENCE estate_settings_id
-- ----------------------------
CREATE SEQUENCE IF NOT EXISTS "public"."estate_settings_id"
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100
  CACHE 1;

COMMIT;
(0032415)
UbitUmarov (administrator)
2017-11-12 01:19

ok on master as version 13

- Issue History
Date Modified Username Field Change
2017-06-27 20:25 testdev627 New Issue
2017-07-05 10:49 testdev627 Relationship added related to 0008163
2017-07-05 10:52 testdev627 Steps to Reproduce Updated View Revisions
2017-07-17 00:02 Gavin Hird Note Added: 0032155
2017-11-11 15:32 TomTheDragon Note Added: 0032410
2017-11-11 16:47 TomTheDragon File Added: 0001-PGSQL-EstateStore-create-estate_settings_id-SEQUENCE.patch
2017-11-11 19:26 TomTheDragon Note Added: 0032411
2017-11-11 19:26 TomTheDragon Status new => patch included
2017-11-11 19:47 UbitUmarov Note Added: 0032413
2017-11-12 00:58 Gavin Hird Note Added: 0032414
2017-11-12 01:19 UbitUmarov Note Added: 0032415


Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker