[Bug]: Cannot start DAB when a table has a non-supported data type, even if the column has been added to the exclude list

[yorek]

What happened?

My table is the following:

create table vehicle_position_data
(
    id varchar(20) not null primary key clustered,
    trip_id int not null,
    direction_id int not null,
    vehicle_id varchar(20) not null,
    route_id int not null,
    latitude decimal (9,6) not null,
    longitude decimal (9,6) not null,        
    [position] as geography::Point(latitude, longitude, 4326) persisted,
    [timestamp] int not null,
    stop_info nvarchar(max) not null check(isjson(stop_info)=1)
) 
go

The geography data type is not yet supported, so I added the position column to the excluded columns:

"entities": {
    "Positions": {
      "source": "dbo.vehicle_position_data",
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*",
              "fields": {
                "exclude": [ "position" ]
              }
            }
          ]
        }
      ],
      "rest": {
        "path": "/positions"
      }
    }
  }

But I get the error:

Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Positions with underlying database object source: dbo.vehicle_position_data due to: DataReader.GetFieldType(7) returned null.
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.GetTableWithSchemaFromDataSetAsync(String entityName, String schemaName, String tableName)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateSourceDefinitionAsync(String entityName, String schemaName, String tableName, SourceDefinition sourceDefinition, String[] runtimeConfigKeyFields)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities()
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.InitializeAsync()
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)

Which is not expected as the column has been excluded.

Version

0.7.5

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST

Relevant log output

Information: Microsoft.DataApiBuilder 0.7.5+2f938a6dfe4b90c26d9e24352b38280cb6e66f15
Found config file: dab-config.development.json.
Found config file: dab-config.json.
Information: Using merged config file based on environment:dab-config.development.merged.json.
Information: User provided config file: dab-config.development.merged.json
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Using file dab-config.development.merged.json to configure the runtime.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Positions is Positions
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime configuration has been successfully loaded.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL path: /graphql
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      StaticWebApps
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime config loaded from file.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\damauri\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Positions path: /api/Positions
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Positions with underlying database object source: dbo.vehicle_position_data due to: DataReader.GetFieldType(7) returned null.
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.GetTableWithSchemaFromDataSetAsync(String entityName, String schemaName, String tableName)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateSourceDefinitionAsync(String entityName, String schemaName, String tableName, SourceDefinition sourceDefinition, String[] runtimeConfigKeyFields)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities()
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.InitializeAsync()
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)
info: Azure.DataApiBuilder.Service.Startup[0]
      Loading config file: dab-config.development.merged.json
fail: Azure.DataApiBuilder.Service.Startup[0]
      Exiting the runtime engine...
crit: Microsoft.AspNetCore.Hosting.Diagnostics[6]
      Application startup exception
      System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.development.merged.json
         at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
         at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
         at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
         at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
Unable to launch the runtime due to: System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.development.merged.json
   at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at Azure.DataApiBuilder.Service.Program.StartEngine(String[] args)
Error: Failed to start the engine.

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[seantleonard]

Tracking support for geography data type: https://github.com/Azure/data-api-builder/issues/1397

[Aniruddh25]

@yorek, The exclude list says the field is NOT authorized for access by anonymous role. It does NOT mean we exclude it from being available in the schema.

In a different config, its possible that you might want to exclude it from anonymous role but include it for some other role. Hence, we always gather the metadata in the schema. We don't analyze the entire config to determine if its not needed at all by any role - that would be a perf hit at startup.

Adding support for the data type should be prioritized over this issue since that will solve this problem.

[seantleonard]

todo: in schema generation, don't validate/resolve unsupported type if field is excluded.

May be more involved depending on the order of db metadata resolving versus permissions processing. db metadata process may take place before permissions.

[seantleonard]

in the interim, since supported geo/spatial types is more involved (extra .DLL which only works on Windows?) , the action here would be to improve the returned error message. Instead of multiple errors/stack traces , a single error should be printed:

Entity: "Positions" | Database Object Source: "dbo.vehicle_position_data" | Column: "position" | Unsupported column type.

While unsupported column type is geography::Point, that value doesn't seem to be returned by the schema fetcher mechanism. We may not be able to output the type in the error message. When this issue is picked up, please check in the debugger for any usable values.

[valentyn-tulub]

As a temporary solution, I created a view with all the columns from the target table except for the non-supported. As an option, you can also convert your field in the view into smth supported. E.g. you can convert geometry to WKT. Hope it helps someone, I wasted a few hours figuring out a viable solution.