MySql升级到5.7.22及以上报错 at MySql.Data.MySqlClient.MySqlStream.ReadPacket 或 FUNCTION account.AddMinutes does not exist

 

Application Exception
MySql.Data.MySqlClient.MySqlException
FUNCTION account.AddMinutes does not exist
Description: HTTP 500.Error processing request.

Details: Non-web exception. Exception origin (name of application or object): MySql.Data.

Exception stack trace:
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00096] in <861c41359f7d4496a8fdf269ae744290>:0 
  at MySql.Data.MySqlClient.NativeDriver.GetResult (System.Int32& affectedRow, System.Int64& insertedId) [0x00026] in <861c41359f7d4496a8fdf269ae744290>:0 
  at MySql.Data.MySqlClient.Driver.GetResult (System.Int32 statementId, System.Int32& affectedRows, System.Int64& insertedId) [0x00000] in <861c41359f7d4496a8fdf269ae744290>:0 
  at MySql.Data.MySqlClient.Driver.NextResult (System.Int32 statementId, System.Boolean force) [0x00023] in <861c41359f7d4496a8fdf269ae744290>:0 
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00205] in <861c41359f7d4496a8fdf269ae744290>:0 
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00315] in <861c41359f7d4496a8fdf269ae744290>:0 
  at (wrapper remoting-invoke-with-check) MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBehavior)
  at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00000] in <b27518aaf75f49a0a3ab22b953222fb2>:0 
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <cfa990f4664f406f913105998890457d>:0 
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand.ExecuteReader(System.Data.CommandBehavior)
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c (System.Data.Common.DbCommand t, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext`1[TResult] c) [0x00007] in <000f54522ad145bf987b3043022f9799>:0 
  at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1[TInterceptor].Dispatch[TTarget,TInterceptionContext,TResult] (TTarget target, System.Func`3[T1,T2,TResult] operation, TInterceptionContext interceptionContext, System.Action`3[T1,T2,T3] executing, System.Action`3[T1,T2,T3] executed) [0x0000f] in <000f54522ad145bf987b3043022f9799>:0 
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader (System.Data.Common.DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext interceptionContext) [0x00042] in <000f54522ad145bf987b3043022f9799>:0 
  at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) [0x00046] in <000f54522ad145bf987b3043022f9799>:0 
  at System.Data.Common.DbCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00000] in <cfa990f4664f406f913105998890457d>:0 
  at (wrapper remoting-invoke-with-check) System.Data.Common.DbCommand.ExecuteReader(System.Data.CommandBehavior)
  at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands (System.Data.Entity.Core.EntityClient.EntityCommand entityCommand, System.Data.CommandBehavior behavior) [0x0000a] in <000f54522ad145bf987b3043022f9799>:0 
Version Information: 5.8.1.0 (tarball); ASP.NET Version: 4.0.30319.42000

 

 

FUNCTION account.AddMinutes does not exist

 

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

在 /etc/my.cnf 文件里加上如下：
sql_mode='NO_ENGINE_SUBSTITUTION'
然后，重启Mysql服务就可以解决了！

 

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法

是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。


在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;

 

DROP FUNCTION IF EXISTS `AddMinutes`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `AddMinutes`(actualDateTime datetime, minutesToAdd int) RETURNS datetime
BEGIN
    RETURN DATE_ADD(actualDateTime, INTERVAL minutesToAdd MINUTE);
END
;;
DELIMITER ;

 

 

一、问题描述

新安装的MySQL5.7.22 或 8.0.11，在 Navicat 上执行删除操作时，遇到报错：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with

sql_mode=only_full_group_by

 

 

二、问题解决

1、 解决办法： 把 sql_mode中的 only_full_group_by 去掉即可。

2、 办法一 （摘自 这里 只在当前查询页面有效，不能一次性解决问题！）：

• select version(), @@sql_mode;
• SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

 

3、办法二： 修改 my.cnf 文件的 sql_mode （一次性解决问题）

• 查找 my.cnf 文件： find / -name my.cnf 或者 whereis my.cnf
• 获取 sql_mode字段内容： SELECT @@sql_mode;
• 去掉 sql_mode字段中的 only_full_group_by
• 编辑 my.cnf： vim /etc/my.cnf 将上一步骤的 sql_mode 字段内容添加至 my.cnf
• 保存，重启： service mysqld restart
• sql_mode 字段参考：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

问题：The user specified as a definer ('root'@'%') does not exist

解决：

grant all privileges on *.* to root@"%" identified by ".";

flush privileges;