MySQL Err 1418 的原因分析及解決方法

MySQL的有個(gè)參數(shù)log_bin_trust_function_creators，官方文檔對(duì)這個(gè)參數(shù)的介紹、解釋如下所示：

This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 23.7, “Binary Logging of Stored Programs”.

簡(jiǎn)單介紹一下，當(dāng)二進(jìn)制日志啟用后，這個(gè)變量就會(huì)啟用。它控制是否可以信任存儲(chǔ)函數(shù)創(chuàng)建者，不會(huì)創(chuàng)建寫入二進(jìn)制日志引起不安全事件的存儲(chǔ)函數(shù)。如果設(shè)置為0（默認(rèn)值），用戶不得創(chuàng)建或修改存儲(chǔ)函數(shù)，除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權(quán)之外的SUPER權(quán)限。 設(shè)置為0還強(qiáng)制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性聲明函數(shù)的限制。 如果變量設(shè)置為1，MySQL不會(huì)對(duì)創(chuàng)建存儲(chǔ)函數(shù)實(shí)施這些限制。 此變量也適用于觸發(fā)器的創(chuàng)建。 請(qǐng)參見第23.7節(jié)“Binary Logging of Stored Programs”。

下面我們測(cè)試一下，當(dāng)開啟二進(jìn)制日志后，如果變量log_bin_trust_function_creators為OFF，那么創(chuàng)建或修改存儲(chǔ)函數(shù)就會(huì)報(bào)“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_creators variable)”這樣的錯(cuò)誤，如下所示：

在調(diào)用存儲(chǔ)函數(shù)時(shí)，也會(huì)遇到這個(gè)錯(cuò)誤,如下測(cè)試所示：

那么為什么MySQL有這樣的限制呢？ 因?yàn)槎M(jìn)制日志的一個(gè)重要功能是用于主從復(fù)制，而存儲(chǔ)函數(shù)有可能導(dǎo)致主從的數(shù)據(jù)不一致。所以當(dāng)開啟二進(jìn)制日志后，參數(shù)log_bin_trust_function_creators就會(huì)生效，限制存儲(chǔ)函數(shù)的創(chuàng)建、修改、調(diào)用。那么此時(shí)如何解決這個(gè)問題呢？官方文檔介紹如下，具體可以參考23.7 Binary Logging of Stored Programs

If you do not want to require function creators to have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system variable to 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. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.
If a function that performs updates is nondeterministic, it is not repeatable. This can have two undesirable effects:

· It will make a slave different from the master.

· Restored data will be different from the original data.

To deal with these problems, MySQL enforces the following requirement: On a master server, creation and alteration of a function is refused unless you declare the function to be deterministic or to not modify data. Two sets of function characteristics apply here:

· The DETERMINISTIC and NOT DETERMINISTIC characteristics indicate whether a function always produces the same result for given inputs. The default is NOT DETERMINISTIC if neither characteristic is given. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.

· The CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA characteristics provide information about whether the function reads or writes data. Either NO SQL or READS SQL DATA indicates that a function does not change data, but you must specify one of these explicitly because the default is CONTAINS SQL if no characteristic is given.

1： 如果數(shù)據(jù)庫(kù)沒有使用主從復(fù)制，那么就可以將參數(shù)log_bin_trust_function_creators設(shè)置為1。

mysql> set global log_bin_trust_function_creators=1;

這個(gè)動(dòng)態(tài)設(shè)置的方式會(huì)在服務(wù)重啟后失效，所以我們還必須在my.cnf中設(shè)置，加上log_bin_trust_function_creators=1，這樣就會(huì)永久生效。

2：明確指明函數(shù)的類型，如果我們開啟了二進(jìn)制日志， 那么我們就必須為我們的function指定一個(gè)參數(shù)。其中下面幾種參數(shù)類型里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。這樣一來相當(dāng)于明確的告知MySQL服務(wù)器這個(gè)函數(shù)不會(huì)修改數(shù)據(jù)。

1 DETERMINISTIC 不確定的

2 NO SQL 沒有SQl語句，當(dāng)然也不會(huì)修改數(shù)據(jù)

3 READS SQL DATA 只是讀取數(shù)據(jù)，當(dāng)然也不會(huì)修改數(shù)據(jù)

4 MODIFIES SQL DATA 要修改數(shù)據(jù)

5 CONTAINS SQL 包含了SQL語句