AdminDatabase error when viewing table data

nukedbit · September 23, 2022, 9:12am

Hi i was trying the AdminDatabase feature with a Customer DB, which is a Sql Server 2014 and i get the following error. It looks like it require an order by which is not included in the query, this table is not mapped in any poco.

under profiling i get the following

And this is the stack trace

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at ServiceStack.OrmLite.OrmLiteCommand.ExecuteReader(CommandBehavior behavior) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteCommand.cs:line 56
at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.ExecReader(IDbCommand dbCmd, String sql, CommandBehavior commandBehavior) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadCommandExtensions.cs:line 54
at ServiceStack.OrmLite.ReadExpressionCommandExtensions.GetSchemaTable(IDbCommand dbCmd, String sql) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/Expressions/ReadExpressionCommandExtensions.cs:line 248
at ServiceStack.OrmLite.ReadExpressionCommandExtensions.GetTableColumns(IDbCommand dbCmd, String sql) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/Expressions/ReadExpressionCommandExtensions.cs:line 256
at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.<>c__DisplayClass70_0.b__0(IDbCommand dbCmd) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadExpressionsApi.cs:line 465
at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteExecFilter.cs:line 66 at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T](IDbConnection dbConn, Func2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadExpressionsApi.cs:line 18
at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.GetTableColumns(IDbConnection dbConn, String sql) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteReadExpressionsApi.cs:line 465
at ServiceStack.AdminDatabaseService.<>c__DisplayClass7_0.b__0(String k) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AdminDatabaseFeature.cs:line 306
at System.Collections.Concurrent.ConcurrentDictionary2.GetOrAdd(TKey key, Func2 valueFactory)
at ServiceStack.AdminDatabaseService.GetColumns(IDbConnection db, String dbName, String table) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AdminDatabaseFeature.cs:line 303
at ServiceStack.AdminDatabaseService.Any(AdminDatabase request) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AdminDatabaseFeature.cs:line 167

mythz · September 23, 2022, 10:11am

This should be resolved from this commit, this change is available from v6.3.1+ that’s now available on MyGet.

Thx for the detailed report!

nukedbit · September 25, 2022, 2:53pm

Hi, with the new profiling feature and logging it has become is easier to pin point issues :).

I still get the same error, i tried to debug with disassembled source code of AdminDatabaseService on rider and i think the error is happening here

var columns = GetColumns(db, request.Db ?? "main", table);

Inside get GetColumns method there is a query that i think is raising the issue.

var columnSchemas = db.GetTableColumns($"SELECT * FROM {table}" + Environment.NewLine + dialect.SqlLimit(0, 1));

mythz · September 26, 2022, 12:45am

ah yes missed that one, should be resolved from this commit, this change is available from v6.3.1+ that’s now available on MyGet.

thx for reporting!

nukedbit · September 26, 2022, 7:11am

You’re welcome.
I think there is also an issue when total is requested, if i skip with the debugger this lines it returns correctly

AdminDatabaseService line 283

    var includes = request.Include?.Split(',') ?? Array.Empty<string>();
    if (includes.Contains("total"))
    {
        var totalSql = $"SELECT COUNT(*) FROM {table}" + sql;
        total = await db.SqlScalarAsync<long>(totalSql, dbParams);
    }

Exception thrown:

Microsoft.Data.SqlClient.SqlException (0x80131904): Column "dbo.Orders.ApplicationID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteExecFilter.cs:line 135
   at ServiceStack.AdminDatabaseService.Any(AdminDatabase request) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AdminDatabaseFeature.cs:line 286
ClientConnectionId:00db8f71-9aef-430a-bef1-dca9fff51167
Error Number:8127,State:1,Class:16

mythz · September 26, 2022, 8:10am

and it shouldn’t be in the aggregate total query fixed in this commit, hopefully all’s good in latest v6.3.1+.