Edit Question - KnowledgeHub

Title Body

Our database has a function to generate an order number. It reads a value from a Settings table, increments it, then returns the new value. For example: CREATE FUNCTION NextOrderNumber() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC BEGIN DECLARE number INTEGER UNSIGNED; UPDATE Settings SET IntegerValue=LAST_INSERT_ID(IntegerValue+1) WHERE KeyName='NextOrderNumber'; SET number=LAST_INSERT_ID(); return number; END Note: Don't critique this function I know it has flaws it's just for illustration. We use this function as follows: INSERT INTO Orders(OrderNumber, ...) SELECT NextOrderNumber(), ... When binary logging is enabled, CREATE FUNCTION gives this error: 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) Regardless of what binlog_format is set, is there really a problem with the above function? According to my reading of the relevant MySQL page I can't see any reason why this function would be incompatible with replication, with either ROW or STATEMENT level binary logging. If the function is safe, setting the global log_bin_trust_function_creators=1 makes me uneasy. I don't want to disable this check for all functions, just this one. Could I instead just flag the function as NO SQL to suppress the warning? I tried it and it worked. Will this cause any problem?

Tags (comma-separated) Cancel