ONLY_FULL_GROUP_BY disabled but still getting error 1055

Question

I'm testing mysql server 5.7.17 and I have defined sql-mode as

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Running each of these gives me the sql-mode defined:

SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

But using mysql .net connector 6.9.8 with EF6 on my vs2012 desktop app i always get the error

InnerException: MySql.Data.MySqlClient.MySqlException
       HResult=-2147467259
       Message=Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'punto_dia.Id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
       Source=MySql.Data
       ErrorCode=-2147467259
       Number=1055
       StackTrace:
            en MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            en MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
            en MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
            en MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
            en MySql.Data.MySqlClient.MySqlDataReader.NextResult()
            en MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            en MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            en System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            en System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
            en System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
            en System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
            en System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            en System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            en System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

I know that fixing the query to use GROUP BY properly i solve the error, but if want to use the query as i used with mysql server 5.6.35 why i can't ?

The sql-mode not define the only_full_group_by, so i don't understand why get this error.

Any ideas ? Thanks

Have you restarted the server after making this change in the config? — Shadow, May 04 '17 at 20:25
before the SELECT, you should also SET GLOBAL and SESSION, like indicated here - https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html — kasparg, May 04 '17 at 20:28
Yes, i restarted the server and the error is still appearing — Alexander Criollo, May 05 '17 at 07:24
i'm using a view, so inside i can't set global or set session. If I use a client mysql like Heidi SQL for example there is no error, the error only appear when use ef6 with mysql net connnector. — Alexander Criollo, May 05 '17 at 07:59
I have the exact same issue - did you ever get anywhere with it? the only thing i can do to get around it is wrap fields with ANY_VALUE() but obviously this isnt practical! — JazziJeff, Dec 06 '17 at 09:25
In case using drupal 8 check this out https://stackoverflow.com/a/65503455/7995302 — Naser Nikzad, Dec 30 '20 at 07:38

ONLY_FULL_GROUP_BY disabled but still getting error 1055

0 Answers0