MySql升级到5.7.22及以上报错 at MySq
原创
©著作权归作者所有:来自51CTO博客作者wx62c572815b406的原创作品,请联系作者获取转载授权,否则将追究法律责任
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
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;