ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA...，hy000deterministic

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA...，hy000deterministic

今天建立function

CREATE FUNCTION `func_get_split_string_total`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)BEGIN  return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));END

報錯資訊：

    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)

查了一些資料，解決辦法是把log_bin_trust_function_creators = 1

但是log_bin_trust_function_creators這個參數是做什麼的呢？開啟後會有哪些影響呢？

接下來開始介紹一下相關的作用及影響：

    對儲存子程式語句的二進位日誌功能的特徵在下面列表中描述。一些條目指出你應該注意到的問題。

    CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,和ALTER FUNCTION 語句被寫進二進位日誌，CALL, DROP PROCEDURE, 和DROP FUNCTION 也一樣。

    儘管如此，對複製有一個安全暗示：要建立一個子程式，使用者必須有CREATE ROUTINE許可權,但有這個許可權的使用者不能寫一個 子程式在從伺服器上執行任何操作。因為在從伺服器上的SQL線程用完全許可權來運行。例如，如果主伺服器和從伺服器分別有伺服器ID值1和2，在主伺服器上的使用者可能建立並調用如下一個 程式：

mysql> delimiter //mysql> CREATE PROCEDURE mysp ()-> BEGIN-> IF @@server_id=2 THEN DROP DATABASE accounting; END IF;-> END;-> //mysql> delimiter ;mysql> CALL mysp();

    CREATE PROCEDURE和CALL語句將被寫進二進位日誌，所以從伺服器將執行它們。因為從SQL線程有完全許可權，它將移除accounting資料庫。

    要使允許二進位日誌功能的伺服器避免這個危險，MySQL 5.1已經要求 儲存程式和函數的建立者除了通常需要的CREATE ROUTINE的許可權外，還必須有SUPER 許可權。類似地，要使用ALTER PROCEDURE或ALTER FUNCTION，除了ALTER ROUTINE許可權外你必須有SUPER許可權。沒有SUPER許可權，將會發生一個錯誤：
    ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)

    你可能不想強制要求子程式建立者必須有SUPER許可權。例如，你系統上所有有CREATE ROUTINE許可權的使用者可能是有經驗的應用程式開發人員。要禁止掉對SUPER許可權的要求，設定log_bin_trust_routine_creators 全域系統變數為1。預設地，這個變數值為0。

    如果二進位日誌功能不被允許，log_bin_trust_routine_creators 沒有被用上，子程式建立需要SUPER許可權。
i.  一個執行更新的非確定子程式是不可重複的，它能有兩個不如意的影響：
ii. 它會使得從伺服器不同於主伺服器。
iii.恢複的資料與未經處理資料不同。

    要解決這些問題，MySQL強製做下面要求：在主伺服器上，除非子程式被聲明為確定性或者不更改資料，否則建立或者替換子程式將被拒絕。這意味著當你建立一個子程式的時候，你必須要麼聲明它是確定性，要麼它不改變資料。兩套子程式特徵在這裡適用：
i.  DETERMINISTIC和NOT DETERMINISTIC指出一個子程式是否對給定的輸入總是產生同樣的結果。如果沒有給定任一特徵，預設是NOTDETERMINISTIC，所以你必須明確指定DETERMINISTIC來 聲明一個 子程式是確定性。

ii. 使用NOW() 函數（或它的同義）或者RAND() 函數不是必要地使也一個子程式非確定性。對NOW()而言，二進位日誌包括時間戳記並正確複製。RAND()只要在一個 子程式內被調用一次也可以正確複製。（你可以認為子程式執行時間戳記和隨機數種子作為毫無疑問地輸入，它們在主伺服器和從伺服器上是一樣的。）
iii.CONTAINS SQL, NO SQL, READS SQL DATA, 和 MODIFIES SQL資料提供子程式是讀還是寫資料的資訊。無論NO SQL 還是READS SQL DATA 都指出，子程式沒有改變資料，但你必須明白地指明這些中的一個，因為如果任何這些特徵沒有被給出， 預設的特徵是CONTAINS SQL。

    當你開啟binlog，要一個 CREATE PROCEDURE 或 CREATE FUNCTION 語句被接受，DETERMINISTIC 或 NO SQL與READS SQL DATA 中的一個必須明白地指定，否則會產生如下錯誤：
    ERROR 1418 (HY000): This routine 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_routine_creators variable)。

    注意，子程式本性的評估是基於建立者的“誠實度” ：MySQL不檢查聲明為確定性子程式是否不含產生非確定性結果的語句。

    如果子程式返回無錯，CALL語句被寫進二進位日誌，否則就不寫。當一個子程式修改資料失敗了，你會得到這樣的警告：
    ERROR 1417 (HY000): A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes.

    這個記日誌行為潛在地導致問題.如果一個子程式部分地修改一個非互動表（比如一個MyISAM表able）並且返回一個錯誤，二進位日誌將反映這些變化。要防止這種情況，你應該在 子程式中使用互動表並且在互動動作內修改表。

    在一個子程式內，如果你在INSERT, DELETE, 或者UPDATE裡使用IGNORE關鍵詞來忽略錯誤，可能發生一個部分更新，但沒有錯誤產生。這樣的語句被記錄日誌，且正常複製。

    如果一個儲存函數在一個如SELECT這樣不修改資料的語句內被調用，即使函數本身更改資料，函數的執行也將不被寫進二進位日誌裡。這個記錄日誌的行為潛在地導致問題。假設函數myfunc()如下定義：

CREATE FUNCTION myfunc () RETURNS INTBEGININSERT INTO t (i) VALUES(1);RETURN 0;END;

按照上面定義，下面的語句修改表t，因為myfunc()修改表t, 但是語句不被寫進二進位日誌，因為它是一個SELECT語句：
    SELECT myfunc();

在一個子程式內執行的語句不被寫進二進位日誌。假如你發布下列語句：
    CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
    CALL mysp;

    對於這個例子來說，CREATE PROCEDURE 和CALL語句出現在二進位日誌裡，但INSERT語句並未出現。在從伺服器上，當決定複製哪個來自主伺服器的事件時，下列限制被應用：--replicate-*-table規則不適用於CALL語句或子程式內的語句：在這些情況下，總是返回“複製！”

    觸發程式類似於儲存函數，所以前述的評論也適用於觸發程式，除了下列情況： CREATE TRIGGER沒有可選的DETERMINISTIC特徵，所以觸發程式被假定為總是確定性。然而，這個假設在一些情況下是非法的。比如，UUID()函數是非確定性（不能複製）。你應該小心在 觸發程式中使用這個函數。

    觸發程式目前不能更新表，但是在將來會支援。因為這個原因，如果你沒有SUPER許可權且log_bin_trust_routine_creators 被設為0，得到的錯誤資訊類似於儲存子程式與CREATE TRIGGER產生的錯誤資訊。

    到這就介紹完了，最後來總結一下，log_bin_trust_function_creators的作用就是定義create procedure或者create function的時候，屏蔽super許可權和去除必須明白地指定 DETERMINISTIC 或 NO SQL與READS SQL DATA 的要求（DETERMINISTIC 或 NO SQL與READS SQL DATA只是聲明的作用，而非約束）。

    在同步中，如果啟用log_bin_trust_function_creators，建立修改表資料的function，可能會導致主從同步不一致。而在用binlog做資料恢複的時候，可能會導致恢複資料不完整而丟失，原因上述已闡明。

附：官方相關介紹http://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html