Unfortunately, during last few days we found a set of serious bugs in upgrade layer:

Effects of these bugs are:

  • Two different exceptions on generation of upgrade sequence. "Node with name '...' is not found", "Index out of range".
  • Referencial constraint violation on execution of upgrade script, if foreign keys are used, there is actual data (usually - a lot of), and some types get removed.
  • In worst case you might not see this at all: when you delete Cat type and add Dog type, we must delete all the records from "Cat" table, its base type tables (e.g. "Animal" in this case) and cleanup all the references to bases (e.g. Animal type) that actually point to Cat instances. Currently the last part isn't handled properly, and some of such references aren't removed. Worse, if new type (Dog) gets the same TypeId as the removing one (Cat), you'll be able to see instances with completely wrong structure in the database after upgrade: "Animal" table would contain some entries for them (because they were not removed properly), but "Dog" table won't (since it is just created).

We're going to release an update fixing all of them ASAP (a day or two).


Updated at 04.07.2010 8:14:01

Likely, this is the most serious bug we found so far - as you see, it may lead to hidden issues after upgrades.


Updated at 06.07.2010 14:39:32

The case with non-NULL reference columns is rather new for us: earlier we simply denied it, since we must always be able to reset such references on removal. Later, due to legacy mode support, we enabled this opportunity, and finally added IHasNullLEntity interface to handle this case during reference cleanup process.

But as you see, this still doesn't work during upgrades.

Ideal solution we can implement here is support for default values. Until this is done, usage of non-nullable fields is "fully legal" only in legacy mode. As far as I can judge, there is no good workaround. On the other hand, if you don't use legacy mode, it must be acceptable to avoid having non-null constraint.

Btw, the case with NULLs in SQL is really pretty bad:

  • People tend to avoid NULLs, since their language support isn't always good. In .NET world nullable types became first-class citizens only from NET 2.0.

  • Specific of logical operations with NULLs is another reason to hate them for developers. "is NULL" instead of "=NULL" makes crazy almost everyone. Especially, when NULL is parameter value (btw, DO4 handles this properly - see my "Boolean branching" post).

  • On the other hand, this is quite reasonable to have special "no value" value for references. And SQL NULL is the best match here from the point of referential constraints: you can always reference NULL value, even if there is a constraint; additionally, primary keys can't contain NULLs.


Updated at 07.07.2010 13:31:00

They aren't "legacy", I mean currently DO "handles" them properly only in DomainUpgradeMode.ValidateLegacy - i.e. when you update the schema by your own.

DO4 simply doesn't know what to assign to such reference when it needs to remove its target - i.e. there is no any infrastructure for this yet. When target.Remove() is called in "runtime", we already use IHasNullEntity. But we can't use the same interface during upgrade.

So really, it's better to avoid non-nullable references for now. When support of this feature will be fully finished, schema upgrade will work, so you'll be able to unitize this feature by adding just one LOC to each of such fields.

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Jul 04 '10 at 08:08

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

edited Sep 06 '10 at 04:51


One Answer:

No, we've possibly found another.

We've got a table called 'Parameter'. It has a column called 'comment'. Previously we allowed NULL values in this column. After changing this to NOT NULL we got an error during the Build().

SQLException Data: Message = Der Wert NULL kann in die 'Comment'-Spalte, 'KLIBNET2_DO_ReferenceData.dbo.Parameter'-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu.

Which means something like: The column 'Comment', 'KLIBNET2_DO_ReferenceData.dbo.Parameter'-Table does not accept NULL values

In the Script sent to the DB we see the following construct:

EXEC sp_rename '[dbo].[Parameter].[Comment]', 'TempComment', 'COLUMN';
ALTER TABLE [dbo].[Parameter] ADD [Comment] nvarchar(4000) DEFAULT NULL NOT NULL;

A field NOT allowing NULL has a DEFAULT of NULL?

Regards Paul Sinnema Diartis AG


Alex,

Please reread my original entry. I still think there's a problem which is not anwered above.

In the original entry I'm **NOT** talking about 'reference fields' but just 'plain fields'. The 'Comment' field in our table is just a nvarchar(4000) field. We tried modifing it's nullability which caused the funny SQL to be spit out.

Regards Paul Sinnema Diartis AG


Hi Alex,

Found another one:

We temporarily disabled Nullable references in our model. Today tried to restore that. During the Upgrade of the DB a script error caused an exception. Here's the produces script.

EXEC sp_rename '[dbo].[Address].[AddressTypeId]', 'TempAddressTypeId', 'COLUMN';
ALTER TABLE [dbo].[Address] ADD [AddressTypeId] integer DEFAULT 0 NOT NULL;
EXEC sp_rename '[dbo].[Address].[ContactId]', 'TempContactId', 'COLUMN';
ALTER TABLE [dbo].[Address] ADD [ContactId] integer DEFAULT 0 NOT NULL;
CREATE INDEX [Address.FK_Z_AddressType] ON [dbo].[Address] ([AddressTypeId] ASC)  INCLUDE ([TypeId]);
CREATE INDEX [Address.FK_Z_Contact] ON [dbo].[Address] ([ContactId] ASC)  INCLUDE ([TypeId]);
EXEC sp_rename '[dbo].[ContactAdditionalInformation].[ContactId]', 'TempContactId', 'COLUMN';

Caution: If you change parts of a Objektnamens, scripts and stored procedures may be inoperative. Caution: If you change parts of a Objektnamens, scripts and stored procedures may be inoperative. Msg 1913, Level 16, State 1, Line 5 errors in the process, because an Index or a statistics with the name 'Address.FK_Z_AddressType' for 'dbo.Address' (table) already exists.

I checked the DB and he's right (as always) the index is already in the DB. I think you need to drop the index before creating a new one.

Regards Paul


My mistake. I see there are 2 scripts being sent to the DB. The first one drops the index. Now the error is different.

Msg 547, Level 16, State 0, the Line 166 ALTER TABLE statement is in conflict with the FOREIGN KEY restriction 'FK_Address-Z_AddressType-AddressType_Z_AddressType'. The conflict occurred in the 'KLIBNET2_DO_ReferenceData'-Database, Table 'dbo.AddressType', column 'Id' on

This is at line 166

ALTER TABLE [dbo].[Address] ADD CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType] FOREIGN KEY ([AddressTypeId]) REFERENCES [dbo].[AddressType] ([Id]);

This is the Table create script from the DB

USE [KLIBNET2_DO_ReferenceData]
GO

/****** Object:  Table [dbo].[Address]    Script Date: 07/15/2010 11:57:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Address](
    [Id] [int] NOT NULL,
    [TypeId] [int] NOT NULL,
    [Supplement1] [nvarchar](4000) NULL,
    [Supplement2] [nvarchar](4000) NULL,
    [Street] [nvarchar](4000) NULL,
    [BuildingNumber] [nvarchar](4000) NULL,
    [PostOfficeBox] [nvarchar](4000) NULL,
    [PostOfficeBoxNumber] [nvarchar](4000) NULL,
    [Comment] [nvarchar](4000) NULL,
    [IsPostal] [bit] NOT NULL,
    [PostalCodeId] [int] NULL,
    [AddressTypeId] [int] NULL,
    [ContactId] [int] NULL,
 CONSTRAINT [PK_Address.AbstractEntity] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_AbstractHistory] FOREIGN KEY([Id])
REFERENCES [dbo].[AbstractHistory] ([Id])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_AbstractHistory]
GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType] FOREIGN KEY([AddressTypeId])
REFERENCES [dbo].[AddressType] ([Id])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_AddressType-AddressType_Z_AddressType]
GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address-Z_Contact-Contact_Z_Contact] FOREIGN KEY([ContactId])
REFERENCES [dbo].[Contact] ([Id])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_Contact-Contact_Z_Contact]
GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address-Z_PostalCode-PostalCode_Z_PostalCode] FOREIGN KEY([PostalCodeId])
REFERENCES [dbo].[PostalCode] ([Id])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address-Z_PostalCode-PostalCode_Z_PostalCode]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [Supplement1]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [Supplement2]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [Street]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [BuildingNumber]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [PostOfficeBox]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [PostOfficeBoxNumber]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [Comment]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (CONVERT([bit],(0),0)) FOR [IsPostal]
GO

ALTER TABLE [dbo].[Address] ADD  DEFAULT (NULL) FOR [PostalCodeId]
GO

Here's the DO4 Address Class

public  partial class Address : AbstractIntersectingHistory , IComparable
    { // START partial class
        #region DataObjectsFields

        [Field(Nullable = true), FieldMapping("Supplement1")]
        public string Z_Supplement1 { get;  set; }

        [Field(Nullable = true), FieldMapping("Supplement2")]
        public string Z_Supplement2 { get;  set; }

        [Field(Nullable = true), FieldMapping("Street")]
        public string Z_Street { get;  set; }

        [Field(Nullable = true), FieldMapping("BuildingNumber")]
        public string Z_BuildingNumber { get;  set; }

        [Field(Nullable = true), FieldMapping("PostOfficeBox")]
        public string Z_PostOfficeBox { get;  set; }

        [Field(Nullable = true), FieldMapping("PostOfficeBoxNumber")]
        public string Z_PostOfficeBoxNumber { get;  set; }

        [Field(Nullable = true), FieldMapping("Comment")]
        public string Z_Comment { get;  set; }

        [Field(Nullable = false), FieldMapping("IsPostal")]
        public bool Z_IsPostal { get;  set; }

        [Field(Nullable = false), FieldMapping("AddressTypeId")]
        [Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
        public AddressType Z_AddressType { get;  set; }

        [Field(Nullable = false), FieldMapping("ContactId")]
        [Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
        public Contact Z_Contact { get;  set; }

        [Field(Nullable = true), FieldMapping("PostalCodeId")]
        [Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear)]
        public PostalCode Z_PostalCode { get;  set; }
}

Here's the DO4 AddressType Class

public  partial class AddressType : AbstractEntity , IComparable
    { // START partial class
        #region DataObjectsFields

        [Field(Nullable = true), FieldMapping("TypeLanguage1")]
        public string Z_TypeLanguage1 { get;  set; }

        [Field(Nullable = true), FieldMapping("TypeLanguage2")]
        public string Z_TypeLanguage2 { get;  set; }

        [Field(Nullable = true), FieldMapping("TypeLanguage3")]
        public string Z_TypeLanguage3 { get;  set; }

        [Field(Nullable = true), FieldMapping("TypeLanguage4")]
        public string Z_TypeLanguage4 { get;  set; }

        [Field(Nullable = false), FieldMapping("CanBePerson")]
        public bool Z_CanBePerson { get;  set; }

        [Field(Nullable = false), FieldMapping("CanBeOrganization")]
        public bool Z_CanBeOrganization { get;  set; }

        [Field(Nullable = false), FieldMapping("IsRepresentative")]
        public bool Z_IsRepresentative { get;  set; }

        [Field()]
        [Association(OnOwnerRemove = OnRemoveAction.Clear, OnTargetRemove = OnRemoveAction.Clear, PairTo = "Z_AddressType")]
        public EntitySet<Address> Z_AddressList { get;  set; }

}

Here's the Data in Address

Id          TypeId                         Supplement1                    Supplement2                    Street                         BuildingNumber                 PostOfficeBox                  PostOfficeBoxNumber            Comment                        IsPostal                       PostalCodeId AddressTypeId ContactId

----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------ ------------- -----------
2177        105                                                                                                                                                                                                                                                     0                              NULL         346           1025

(1 row(s) affected)

Here's the Data in AddressType

Id          TypeId      TypeLanguage1                  TypeLanguage2                  TypeLanguage3                  TypeLanguage4                  CanBePerson CanBeOrganization IsRepresentative

----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------- ----------------- ----------------
344         106         Home address2                  Wohnadresse2                   Wohnadresse2                   Wohnadresse2                   1           0                 0
345         106         Organisation Address           Firmensitz                     Firmensitz                     Firmensitz                     0           1                 0
346         106         Home address                   Wohnadresse                    Wohnadresse                    Wohnadresse                    1           0                 0
347         106         Work address                   Arbeitsadresse                 Arbeitsadresse                 Arbeitsadresse                 1           1                 0

(4 row(s) affected)

answered Jul 06 '10 at 12:21

Paul%20Sinnema's gravatar image

Paul Sinnema
261848896

Is it possible to get .exe (we don't need source, we need just something we can run to reproduce the error) + .bak (database backup) allowing to reproduce this via e-mail? It will be much simpler to find out what's wrong in this case.

(Jul 06 '10 at 12:21) Alex Yakunin Alex%20Yakunin's gravatar image

Hi Alex,

You can download app and DB from here: http://www.sinnema.ch/Diartis/DB%20Upgrade%20Problem.zip

Regards Paul

(Jul 06 '10 at 12:21) Paul Sinnema Paul%20Sinnema's gravatar image

Done - you can remove the file.

(Jul 06 '10 at 12:21) Alex Yakunin Alex%20Yakunin's gravatar image

What is the status for this Bug?

(Jul 06 '10 at 12:21) Paul Sinnema Paul%20Sinnema's gravatar image

It's in our "must fix ASAP" list in internal issue tracker, but for now nothing is done here yet.

(Jul 06 '10 at 12:21) Alex Yakunin Alex%20Yakunin's gravatar image

Alex,

So the short answer to this is: Don't use non-nullable (reference) fields in other modes than 'Legacy'?

Regards Paul Sinnema Diartis AG.

(Jul 06 '10 at 12:21) Paul Sinnema Paul%20Sinnema's gravatar image

Not nullable reference fields are Legacy? Are you kidding?

(Jul 06 '10 at 12:21) xumix xumix's gravatar image

Ups, true. Sorry, I really missed this.

We'll fix this ASAP.

(Jul 06 '10 at 12:21) Alex Yakunin Alex%20Yakunin's gravatar image

Hopefully, I fixed all of them. See http://code.google.com/p/dataobjectsdotnet/issues/detail?id=743 and two other issues listed there.

(Aug 28 '10 at 21:33) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA