This function has none of DETERMINISTIC, NO SQL的解决
来源:互联网 发布:java实现好友列表 编辑:程序博客网 时间:2024/05/16 05:52
In general, the issues described here result from the fact that binary logging occurs at the SQL statement level. A future MySQL release is expected to implement row-level binary logging, which specifies the changes to make to individual rows as a result of executing SQL statements.
Unless noted otherwise, the remarks here assume that you have enabled binary logging by starting the server with the --log-bin
option. (See Section 5.2.3, “The Binary Log”.) If the binary log is not enabled, replication is not possible, nor is the binary log available for data recovery.
The development of stored routine logging in MySQL 5.0 can be summarized as follows:
-
Before MySQL 5.0.6: In the initial implementation of stored routine logging, statements that create stored routines and
CALL
statements are not logged. These omissions can cause problems for replication and data recovery. -
MySQL 5.0.6: Statements that create stored routines and
CALL
statements are logged. Stored function invocations are logged when they occur in statements that update data (because those statements are logged). However, function invocations are not logged when they occur in statements such asSELECT
that do not change data, even if a data change occurs within a function itself; this can cause problems. Under some circumstances, functions and procedures can have different effects if executed at different times or on different (master and slave) machines, and thus can be unsafe for data recovery or replication. To handle this, measures are implemented to allow identification of safe routines and to prevent creation of unsafe routines except by users with sufficient privileges. -
MySQL 5.0.12: For stored functions, when a function invocation that changes data occurs within a non-logged statement such as
SELECT
, the server logs aDO
statement that invokes the function so that the function gets executed during data recovery or replication to slave servers. For stored procedures, the server does not logfunc_name
()CALL
statements. Instead, it logs individual statements within a procedure that are executed as a result of aCALL
. This eliminates problems that may occur when a procedure would follow a different execution path on a slave than on the master. -
MySQL 5.0.16: The procedure logging changes made in 5.0.12 allow the conditions on unsafe routines to be relaxed for stored procedures. Consequently, the user interface for controlling these conditions is revised to apply only to functions. Procedure creators are no longer bound by them.
-
MySQL 5.0.17: Logging of stored functions as
DO
statements (per the changes made in 5.0.12) are logged asfunc_name
()SELECT
statements instead for better control over error checking.func_name
()
As a consequence of the preceding changes, the following conditions currently apply to stored function creation when binary logging is enabled. These conditions do not apply to stored procedure creation.
-
To create or alter a stored function, you must have the
SUPER
privilege, in addition to theCREATE ROUTINE
orALTER ROUTINE
privilege that is normally required. -
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication. Two sets of function characteristics apply here:
-
The
DETERMINISTIC
andNOT DETERMINISTIC
characteristics indicate whether a function always produces the same result for given inputs. The default isNOT DETERMINISTIC
if neither characteristic is given, so you must specifyDETERMINISTIC
explicitly to declare that a function is deterministic.Use of the
NOW()
function (or its synonyms) orRAND()
does not necessarily make a function non-deterministic. ForNOW()
, the binary log includes the timestamp and replicates correctly.RAND()
also replicates correctly as long as it is invoked only once within a function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)SYSDATE()
is not affected by the timestamps in the binary log, so it causes stored routines to be non-deterministic if statement-based logging is used. This does not occur if the server is started with the--sysdate-is-now
option to causeSYSDATE()
to be an alias forNOW()
. -
The
CONTAINS SQL
,NO SQL
,READS SQL DATA
, andMODIFIES SQL DATA
characteristics provide information about whether the function reads or writes data. EitherNO SQL
orREADS SQL DATA
indicates that a function does not change data, but you must specify one of these explicitly because the default isCONTAINS SQL
if no characteristic is given.
By default, for a
CREATE FUNCTION
statement to be accepted,DETERMINISTIC
or one ofNO SQL
andREADS SQL DATA
must be specified explicitly. Otherwise an error occurs:ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log_bin_trust_function_creatorsvariable)
Assessment of the nature of a function is based on the “honesty” of the creator: MySQL does not check that a function declared
DETERMINISTIC
is free of statements that produce non-deterministic results. -
-
To relax the preceding conditions on function creation (that you must have the
SUPER
privilege and that a function must be declared deterministic or to not modify data), set the globallog_bin_trust_function_creators
system variable to 1. By default, this variable has a value of 0, but you can change it like this:mysql>
SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1
option when starting the server.If binary logging is not enabled,
log_bin_trust_function_creators
does not apply andSUPER
is not required for routine creation.
- This function has none of DETERMINISTIC, NO SQL的解决
- MySQL ERROR:This function has none of DETERMINISTIC, NO SQL
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL 错误处理
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL解决办法
- This function has none of DETERMINISTIC, NO SQL, or
- Asp.net 2.0 C#实现压缩/解压功能 (示例代码下载)
- Asp.net 2.0 实现自定义Email格式有效性验证(示例代码下载)
- Asp.net 2.0 制作复合控件示例(二)[示例代码下载]
- Asp.net 2.0 制作最原始的TextBox控件[一](示例代码下载)
- ASP.NET 2.0 读取配置文件[INI](示例代码下载)
- This function has none of DETERMINISTIC, NO SQL的解决
- ASP.NET 2.0 HttpHandler实现对某种文件类型权限保护(示例代码下载)
- ASP.NET 2.0 HttpHandler实现生成图片验证码(示例代码下载)
- 她为了8块5毛钱……[转载]
- 人力资源的统统都来学习一下
- 珍惜自已的爱, 世界上没有後悔药 [转载收藏]
- Asp.net 2.0 自定义控件开发[创建自定义HeaderRow的GridView控件][示例代码下载]
- Asp.net 2.0 自定义控件开发[创建自定义右键PopupMenu控件][示例代码下载]
- [MVP] 热烈祝贺!您已经获得连任 Microsoft MVP 奖励