New Relationships from FK get added then taken away in sync with multiple DB's

Posts   
 
    
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 29-Jan-2018 11:29:29   

We sync with 2 Relational Models: SQL Server with a source of Database and ODP.NET with a source of Mixed. When a FK is added in SQL and both DB's Synced the log shows the corresponding relationship is added then removed.

Steps to repro using Northwind with 2 Relational Models: SQL Server with a source of Database and ODP.NET with a source of Mixed and sync is up-to-date

1. DROP CONSTRAINT [FK_Orders_Shippers] 
2. Sync SQL only
3. Put back CONSTRAINT [FK_Orders_Shippers]
4. Sync Both

The log shows the New relationship 'Order.Shipper - Shipper.Orders (m:1)' added between 'Order' and 'Shipper'. The relationship 'Order.Shipper - Shipper.Orders (m:1)' has been removed because its underlying foreign key constraint wasn't found in the refreshed relational model data.

Sync Relational Model Data Task processing results
Results of running selected sync tasks
Results of performing task 'Sync with a database to sync 'Northwind: dbo' using meta-data obtained from 'SQL Server''
Results of synchronization of relational model elements with database elements from database of type 'SQL Server'
Results of metadata retrieval for database type 'SQL Server'
Actions taken for catalog 'Northwind'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlHierarchyId' from assembly 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlHierarchyId' using type description: 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeometry' from assembly 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeometry' using type description: 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeography' from assembly 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeography' using type description: 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Actions taken for schema 'dbo'
Meta data retrieved
Refresh results
Migration of project elements to new catalogs
Actions performed on Entities for catalog 'Northwind'
Entity 'Category'
Entity migrated to target 'dbo.Categories'.
Entity 'Customer'
Entity migrated to target 'dbo.Customers'.
Entity 'CustomerCustomerDemo'
Entity migrated to target 'dbo.CustomerCustomerDemo'.
Entity 'CustomerDemographic'
Entity migrated to target 'dbo.CustomerDemographics'.
Entity 'Employee'
Entity migrated to target 'dbo.Employees'.
Entity 'EmployeeTerritory'
Entity migrated to target 'dbo.EmployeeTerritories'.
Entity 'Order'
Entity migrated to target 'dbo.Orders'.
Entity 'OrderDetail'
Entity migrated to target 'dbo.Order Details'.
Entity 'Product'
Entity migrated to target 'dbo.Products'.
Entity 'Region'
Entity migrated to target 'dbo.Region'.
Entity 'Shipper'
Entity migrated to target 'dbo.Shippers'.
Entity 'Supplier'
Entity migrated to target 'dbo.Suppliers'.
Entity 'Territory'
Entity migrated to target 'dbo.Territories'.
Actions performed on Typed Views for catalog 'Northwind'
Typed view 'AlphabeticalListOfProduct'
Typed view migrated to target 'dbo.Alphabetical list of products'.
Typed view 'CategorySalesFor1997'
Typed view migrated to target 'dbo.Category Sales for 1997'.
Typed view 'CurrentProductList'
Typed view migrated to target 'dbo.Current Product List'.
Typed view 'CustomerAndSuppliersByCity'
Typed view migrated to target 'dbo.Customer and Suppliers by City'.
Typed view 'Invoice'
Typed view migrated to target 'dbo.Invoices'.
Typed view 'OrderDetailsExtended'
Typed view migrated to target 'dbo.Order Details Extended'.
Typed view 'OrdersQry'
Typed view migrated to target 'dbo.Orders Qry'.
Typed view 'OrderSubtotal'
Typed view migrated to target 'dbo.Order Subtotals'.
Typed view 'ProductsAboveAveragePrice'
Typed view migrated to target 'dbo.Products Above Average Price'.
Typed view 'ProductSalesFor1997'
Typed view migrated to target 'dbo.Product Sales for 1997'.
Typed view 'ProductsByCategory'
Typed view migrated to target 'dbo.Products by Category'.
Typed view 'QuarterlyOrder'
Typed view migrated to target 'dbo.Quarterly Orders'.
Typed view 'SalesByCategory'
Typed view migrated to target 'dbo.Sales by Category'.
Typed view 'SalesTotalsByAmount'
Typed view migrated to target 'dbo.Sales Totals by Amount'.
Typed view 'SummaryOfSalesByQuarter'
Typed view migrated to target 'dbo.Summary of Sales by Quarter'.
Typed view 'SummaryOfSalesByYear'
Typed view migrated to target 'dbo.Summary of Sales by Year'.
Post-migration actions performed on entity model and other project elements
Migration of relationships and unique constraints
New relationship 'Order.Shipper - Shipper.Orders (m:1)' added between 'Order' and 'Shipper'.
Results of automapping unmapped entities
No actions taken
Results of performing task 'Sync with a database to sync '//localhost/xe: TESTUSER' using meta-data obtained from 'Oracle''
Results of synchronization of relational model elements with database elements from database of type 'Oracle'
Results of metadata retrieval for database type 'Oracle'
No actions taken
Refresh results
Post-migration actions performed on entity model and other project elements
Migration of relationships and unique constraints
The relationship 'Order.Shipper - Shipper.Orders (m:1)' has been removed because its underlying foreign key constraint wasn't found in the refreshed relational model data.
Results of automapping unmapped entities
No actions taken
Results of performing task 'Sync with the model to sync 'Oracle (ODP.NET)' using meta-data obtained from 'Entity Model''
Results of synchronization of relational model elements with model elements
Results of automapping unmapped entities
No actions taken
Results of automapping unmapped entity fields
No actions taken
Results of Table Field Type Synchronization
No table fields required synchronization
Results of Meta-data adjustment
Results of syncing PK fields
No actions necessary
Creating FK Constraints for inheritance relationships
No actions necessary
Creating FK Constraints and Unique Constraints for normal relationships
No actions necessary
Creating Unique Constraints for model Unique Constraints
No actions necessary
Setting Identity flags on table fields mapped by sequenced entity fields
No actions necessary
Setting default values on table fields
No actions necessary

Doing another sync will give the same results - this can be got around by doing a SQL only sync but that's not obvious and gets more thornier when your adding the FK field at the same time - which you usually are.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jan-2018 12:42:09   

We'll look into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jan-2018 13:26:40   

I can't reproduce it, but that's likely caused by the fact you didn't specify the start situation, i.e. you drop an FK in sqlserver, but was there already a relationship present in the model?

If I have two tables, order and orderitem, no FK present, create a model from them, add oracle container, sync forward, no relationship, no FK present. Then I add the FK in sqlserver, sync both, things proceed as normal: relationship is added in model, and fk constraint is added in oracle metadata.

If I start with that model again, so 2 entities, relationship present, FK constraint present in both oracle and sqlserver, and then drop the FK in sqlserver, sync only sqlserver, and then re-add the FK constraint in sqlserver, sync both, it simply adds the relationship again, no fk constraint is removed from oracle...

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 30-Jan-2018 09:00:11   

I get your behaviour when oracle is set as model but when I change it to mixed (and hence get a oracle login dialog) it behaves as I described.

Attachments
Filename File size Added on Approval
NorthwindFK.llblgenproj 21,131 30-Jan-2018 09:00.48 Approved
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Jan-2018 12:01:02   

I can't reproduce it. I use your project, refresh northwind from the sqlserver, then connect to oracle, but as it's set to mixed, the tables aren't pulled from the DB, so I log in but there's no schema with tables there, which is OK, then sync occurs with the model, everything works fine: the FK constraint is added for sqlserver, the one in oracle is still there, the relationship too.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 31-Jan-2018 05:15:12   

I tried it on a different machine and it was OK too - I must have uploaded it in a good state - not very helpful. But when I deleted the FK I got the problem though.

Sync Relational Model Data Task processing results
Results of running selected sync tasks
Results of performing task 'Sync with a database to sync 'Northwind: dbo' using meta-data obtained from 'SQL Server''
Results of synchronization of relational model elements with database elements from database of type 'SQL Server'
Results of metadata retrieval for database type 'SQL Server'
Actions taken for catalog 'Northwind'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlHierarchyId' from assembly 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlHierarchyId' using type description: 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeometry' from assembly 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeometry' using type description: 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Error: 'Exception caught while instantiating UDT type 'Microsoft.SqlServer.Types.SqlGeography' from assembly 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91': Couldn't find type: 'Microsoft.SqlServer.Types.SqlGeography' using type description: 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. Type skipped.'
Actions taken for schema 'dbo'
Meta data retrieved
Refresh results
Migration of project elements to new catalogs
Actions performed on Entities for catalog 'Northwind'
Entity 'Order'
Entity migrated to target 'dbo.Orders'.
Entity 'OrderDetail'
Entity migrated to target 'dbo.Order Details'.
Post-migration actions performed on entity model and other project elements
Migration of relationships and unique constraints
The relationship 'OrderDetail.Order - Order.OrderDetails (m:1)' has been removed because its underlying foreign key constraint wasn't found in the refreshed relational model data.
Results of automapping unmapped entities
No actions taken
Results of performing task 'Sync with a database to sync 'Default: Default' using meta-data obtained from 'Oracle''
Results of synchronization of relational model elements with database elements from database of type 'Oracle'
Results of metadata retrieval for database type 'Oracle'
No actions taken
Refresh results
Post-migration actions performed on entity model and other project elements
Migration of relationships and unique constraints
New relationship 'OrderDetail.Order - Order.OrderDetails (m:1)' added between 'OrderDetail' and 'Order'.
Results of automapping unmapped entities
No actions taken
Results of performing task 'Sync with the model to sync 'Oracle (ODP.NET)' using meta-data obtained from 'Entity Model''
Results of synchronization of relational model elements with model elements
Results of automapping unmapped entities
No actions taken
Results of automapping unmapped entity fields
No actions taken
Results of Table Field Type Synchronization
No table fields required synchronization
Results of Meta-data adjustment
Results of syncing PK fields
No actions necessary
Creating FK Constraints for inheritance relationships
No actions necessary
Creating FK Constraints and Unique Constraints for normal relationships
No actions necessary
Creating Unique Constraints for model Unique Constraints
No actions necessary
Setting Identity flags on table fields mapped by sequenced entity fields
No actions necessary
Setting default values on table fields
No actions necessary

and this warning

Message type: Warning
Message text: DZ0025: For the database with driver 'SQL Server Driver (SqlClient)', the relationship 'OrderDetail.Order - Order.OrderDetails (m:1)' has no backing foreign key constraint defined on the target FK side table 'Northwind.dbo.Order Details'. This conflicts with the setting 'RelationshipsFollowDBForeignKeyConstraints' which is set to true and the containing schema is synced with an external database.
Source: OrderDetail mapping
Created on: Wednesday, 31 January 2018 5:13 PM
Corrections/suggestions
    Choice 1: Mark relationship 'OrderDetail.Order - Order.OrderDetails (m:1)' as 'Model Only'.
    Choice 2: Remove 'OrderDetail.Order - Order.OrderDetails (m:1)' from the project


Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Jan-2018 12:56:59   

Indeed. It looks like the refresher performs post-migration fixups for elements regardless of whether they're synced with model or database.

We'll look into changing this.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Jan-2018 14:17:29   

Fixed in 5.3.3 hotfix. Uploaded now.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 31-Jan-2018 22:19:17   

Otis wrote:

Fixed in 5.3.3 hotfix. Uploaded now.

Yep that sorted it - thank you! smile We're still on 5.2 any chance you can fix it there as well?

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Feb-2018 10:47:12   

Done simple_smile

Frans Bouma | Lead developer LLBLGen Pro