search

Azure / data-api-builder

Data API builder provides modern REST and GraphQL endpoints to your Azure Databases and on-prem stores.
https://aka.ms/dab/docs
MIT License
789 stars 141 forks source link

[Bug]: Builder Not Working For MSSQL Databases With Case Sensitive Collation (Azure.DataApiBuilder.Core.Resolvers.QueryExecutor) #1896

Open gamewizzzz opened 7 months ago

gamewizzzz commented 7 months ago

What happened?

A bug happened!

When starting the data API builder, it goes out to a couple system tables (sys.columns and information_schema.columns) to check for read-only fields. The following syntax is employed by the app:

SELECT ifsc.column_name
from sys.columns as sc
INNER JOIN information_schema.columns as ifsc ON ( sc.is_computed = 1 or ifsc.data_type = 'timestamp' )
                        AND sc.object_id = object_id(@param1+'.'+@param2)
                        and ifsc.table_name = @param1
                        AND ifsc.table_schema = @param2
                        and ifsc.column_name = sc.name;

This works fine for the out-of-the-box database collation. But when the database is set to a case sensitive collation, the query fails with 'invalid object' error. Below is the same query, with the correct case for the field names, so it should work with both case-sensitive and case-insensitive collations:

SELECT ifsc.COLUMN_NAME
from sys.columns as sc
INNER JOIN INFORMATION_SCHEMA.COLUMNS as ifsc ON ( sc.is_computed = 1 or ifsc.DATA_TYPE = 'timestamp' )
                        AND sc.object_id = object_id(@param1+'.'+@param2)
                        AND ifsc.TABLE_NAME = @param1
                        AND ifsc.TABLE_SCHEMA = @param2
                        AND ifsc.COLUMN_NAME = sc.name;

Wanted to create a bug report before attempting to create branches myself. I suspect that MSSQL queries further in the app may have a similar issue since there's no test for this.

Version

0.9.7+e560142426d1c080b9fd7b7fabff51a276f6bf61

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

dab start --config "C:\Users\sample_user\dab-config.json"

Information: Microsoft.DataApiBuilder 0.9.7+e560142426d1c080b9fd7b7fabff51a276f6bf61
Information: Generating default config file: C:\Users\sample_user\dab-config.json
Information: Config file generated.
Information: SUGGESTION: Use 'dab add [entity-name] [options]' to add new entities in your config.
Information: Microsoft.DataApiBuilder 0.9.7+e560142426d1c080b9fd7b7fabff51a276f6bf61
Information: User provided config file: C:\Users\sample_user\dab-config.json
Loading config file from C:\Users\sample_user\dab-config.json.
Information: Added new entity: DUAL with source: dbo.DUAL and permissions: anonymous:*.
Information: SUGGESTION: Use 'dab update [entity-name] [options]' to update any entities in your config.
Information: Microsoft.DataApiBuilder 0.9.7+e560142426d1c080b9fd7b7fabff51a276f6bf61
Information: User provided config file: C:\Users\sample_user\dab-config.json
Loading config file from C:\Users\sample_user\dab-config.json.
Information: Loaded config file: C:\Users\sample_user\dab-config.json
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
Loading config file from C:\Users\sample_user\dab-config.json.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\sample_user\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      [DUAL] REST path: /api/DUAL
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
       Executing query: SELECT STE.type_desc FROM sys.triggers ST inner join sys.trigger_events STE On ST.object_id = STE.object_id AND ST.parent_id = object_id(@param0 + '.' + @param1) WHERE ST.is_disable
d = 0;
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
       Executing query: SELECT ifsc.column_name from sys.columns as sc INNER JOIN information_schema.columns as ifsc ON (sc.is_computed = 1 or ifsc.data_type = 'timestamp') AND sc.object_id = object_id(@pa
ram0+'.'+@param1) and ifsc.table_name = @param1 AND ifsc.table_schema = @param0 and ifsc.column_name = sc.name;
fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
       Query execution error due to:
Invalid object name 'information_schema.columns'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'information_schema.columns'.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpCon
text, List`1 args)
      ClientConnectionId:3f9f4563-3a15-449a-a262-0a8838217224
      Error Number:208,State:1,Class:16
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Invalid object name 'information_schema.columns'.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'information_schema.columns'.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpCon
text, List`1 args)
      ClientConnectionId:3f9f4563-3a15-449a-a262-0a8838217224
      Error Number:208,State:1,Class:16
         --- End of inner exception stack trace ---
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpCon
text, List`1 args)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.<>c__DisplayClass19_0`1.<<ExecuteQueryAsync>b__0>d.MoveNext()
      --- End of stack trace from previous location ---
         at Polly.Retry.AsyncRetryEngine.ImplementationAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicat
es`1 shouldRetryResultPredicates, Func`5 onRetryAsync, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider, Boolean continueOnCapturedContext)
         at Polly.AsyncPolicy.ExecuteAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, Boolean continueOnCapturedContext)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args, String d
ataSourceName)
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateColumnDefinitionsWithReadOnlyFlag(String tableName, String schemaOrDatabaseName, SourceDefinition sourceDefinition)
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateSourceDefinitionAsync(String entityName, String schemaName, String tableName, SourceDefinition sourceDefinition, String[] runtim
eConfigKeyFields)
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities()
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.InitializeAsync()
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)
fail: Azure.DataApiBuilder.Service.Startup[0]
      Could not initialize the engine with the runtime config file: C:\Users\sample_user\dab-config.json
info: Microsoft.Hosting.Lifetime[0]
      Application is shutting down...
dab : Unable to launch the Data API builder engine.
At line:9 char:1
+ dab start --config "C:\Users\sample_user\dab-config.json"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (Unable to launc...builder engine.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

Error: Failed to start the engine.

Code of Conduct

ayush3797 commented 7 months ago

Hey @gamewizzzz, thanks for bringing this up. Feel free to take this up, if you want to. Or let us know and we can take care of this.