AutoQuery error on AWS MySQL

ServiceStack.OrmLite MySql
1

Version: MySQL 5.6.34 on AWS RS
Latest version of ServiceStack.

Is AutoQuery compatible with MySQL 5.6.*, or is this an AWS issue? I don’t have this issue on a local MySQL instance running 5.7.
This only occurred once I deployed to test the api on AWS RDS …

Here’s the error:

{
  "offset": 0,
  "total": 0,
  "responseStatus": {
    "errorCode": "MySqlException",
    "message": "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'",
    "stackTrace": "[QueryAccountMemberDaos: 2/2/2017 10:36:39 PM]:\n[REQUEST: {}]\nMySql.Data.MySqlClient.MySqlException (0x80004005): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'\r\n   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)\r\n   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)\r\n   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()\r\n   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n   at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ConvertToList(IDbCommand dbCmd, Type refType, String sql)\r\n   at ServiceStack.OrmLite.Support.LoadListSync`2.SetRefField(FieldDefinition fieldDef, Type refType)\r\n   at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.LoadListWithReferences[Into,From](IDbCommand dbCmd, SqlExpression`1 expr, IEnumerable`1 include)\r\n   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)\r\n   at ServiceStack.TypedQuery`2.Execute[Into](IDbConnection db, ISqlExpression query)",
    "errors": []
  }
}

Here are the types involved in this sample query (slightly abridged). No failures on other types that do not have foreign keys. Happens with other types as well that have foreign keys.

AccountMemberDao {
Id (long),
UserAuthId (int),
AccountId (long),
Account (AccountDao, optional)
}
AccountDao {
Id (long),
Uid (string),
Name (string, optional),
TenantId (long),
Tenant (TenantDao, optional),
Disabled (boolean)
}
2

I just tried it with another AWS RDS instance, with MySQL 5.7.16, and I get the same error …
So looks like an incompatibility with AWS RDS MySQL ?

3

You’ve hit a limitation with MySql which isn’t fully SQL compatible and throws runtime exceptions like this when sending a valid SQL that it doesn’t support, in this case:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Without seeing the query I’m assuming its your combination of Load References with LIMIT and a query filter.

If you’ve registered a MaxLimit, you can try removing it, i.e:

Plugins.Add(new AutoQueryFeature { MaxLimit = null }); //default

Otherwise you may need to remove your [Reference] to remove the subquery.

4

Ok. I’ll just switch to postgres for now. Seems like an annoying compatibility issue though between AutoQuery and MySQL (at least on RDS). After reading a bit about it online, seems like the consensus from the MySQL community is that the queries can mostly be rewritten using joins, instead of using the in/all and limit syntax in subqueries, but then that defeats my purpose of using AutoQuery if i have to re-write the queries.