Can't create simple MySQL function without super privilege

Question

I've been creating stored procedures without problems but when trying to create a simple function which returns the result of a COUNT() function on a table, I get:

ERROR 1418: 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)

Since it's using COUNT() I change it to be READS SQL DATA but then get the error:

ERROR 1419: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

I don't understand why I can create procedures but not functions. The logging is not something I really have access to, even if I understood what the problem was in that area... a little Googling suggests it's to do with DB replication or something but this is a simple DB only.

Answer 1

The binlog is also used when rolling forward a database restored from backup. It's built in as standard.

If you execute your function at different times it will return different values depending on how many rows it has counted. And the point of binlogging is to get your system from one known state to another known state. When your statement is replayed will affect the outcome. The solution is to switch off binlogging if your not relying on it, or to allow the DBMS to write the data changes to the binlog instead of the statements used to change the data.

It's all in the manual.