CREATE FUNCTIONでエラー

log-binをオフにすると、なぜかエラー
なぜ？？？

root@localhost[test]>create function hello1(input char(20))
returns char(50) return concat('Hello, ', input, '!');
ERROR 1418 (HY000): This function has none of DETERMINISTIC,
 NO SQL, or READS SQL DATA in its declaration and binary log
ging is enabled (you *might* want to use the less safe log_b
in_trust_function_creators variable)

log-binをオフにすると、エラーなし


答え：

ファンクション作成時には、明示的にDETERMINISTIC, NO SQL, READS SQL DATAなどを指定して、「そのままバイナリログに出力しても問題ないよ」と教えてやる必要がある。

または、上記のオプション無しでもエラーを返さないように、まとめて設定してしまう。

log_bin_trust_function_creators = 1;

なんでStored Functionでだけ？

メモ

As you know stored procedures are invoked with:

CALL proc(...);

MySQL does not log the CALL statement into binary log. If procedure contains
"writing" statements (INSERT, UPDATE etc.) MySQL logs these statements in binary
log.

When it comes to Replication for example if you call such procedure on the
Master the Slave server will get and apply changes even if it does not have such
procedure at all. In other words CALL will not be replicated, but the underlying
statements will be replicated.


Functions at the other hand are always invoked within regular statement like:

SELECT x, my_stored_func() FROM my_table;
UPDATE my_table SET y = my_stored_func();


Now when my_stored_func() modifies table data MySQL must log each statement
where this function is called. Even if it is SELECT like above it will go to the
binary log because it modifies some data implicitly.

But the actual statements executed within the body of my_stored_func() will not
be logged. If both the function calling statement *and* the underlying
statements are logged same change will be applied twice.


This implies that upon replaying the binary log the server must have stored
functions defined unlike with stored procedures.

As you see this is fundamental difference between logging stored procedures and
functions.

To repeat it in brief:
* Procedure invocation is not logged, underlying statements are logged.
* Function invocation is logged, underlying statements are not.