Oracle 5.0, Reference Manual

Page: 1729 / 3232

Binary Logging of Stored Programs
1709
-> BEGIN
-> IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
-> END;
-> //
mysql> delimiter ;
mysql> CALL unsafe_sp();
The
CREATE PROCEDURE
and
CALL
statements are written to the binary log, so the slave will
execute them. Because the slave SQL thread has full privileges, it will execute the
DROP DATABASE
statement that drops the
accounting
database. Thus, the
CALL
statement has different effects on
the master and slave and is not replication-safe.
The preceding example uses a stored procedure, but similar problems can occur for stored functions
that are invoked within statements that are written to the binary log: Function invocation has different
effects on the master and slave.
To guard against this danger for servers that have binary logging enabled, MySQL 5.0.6 introduces
the requirement that stored procedure and function creators must have the
SUPER
[578] privilege,
in addition to the usual
CREATE ROUTINE
[576] privilege that is required. Similarly, to use
ALTER
PROCEDURE
or
ALTER FUNCTION
, you must have the
SUPER
[578] privilege in addition to the
ALTER
ROUTINE
[576] privilege. Without the
SUPER
[578] privilege, an error will occur:
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)
If you do not want to require routine creators to have the
SUPER
[578] privilege (for example, if
all users with the
CREATE ROUTINE
[576] privilege on your system are experienced application
developers), set the global
log_bin_trust_routine_creators
system variable to 1. You
can also set this variable by using the
--log-bin-trust-routine-creators=1
[1468] option
when starting the server. If binary logging is not enabled,
log_bin_trust_routine_creators
does not apply.
SUPER
[578] is not required for routine creation unless, as described previously, the
DEFINER
value in the routine definition requires it.
• If a routine 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 routine is refused unless you declare the routine to be deterministic or to
not modify data. Two sets of routine characteristics apply here:
• The
DETERMINISTIC
and
NOT DETERMINISTIC
characteristics indicate whether a routine
always produces the same result for given inputs. The default is
NOT DETERMINISTIC
if
neither characteristic is given. To declare that a routine 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 routine reads or writes data. Either
NO SQL
or
READS SQL
DATA
indicates that a routine does not change data, but you must specify one of these explicitly
because the default is
CONTAINS SQL
if no characteristic is given.
By default, for a
CREATE PROCEDURE
or
CREATE FUNCTION
statement to be accepted, at least one
of
DETERMINISTIC
,
NO SQL
, or
READS SQL DATA
must be specified explicitly. Otherwise an error
occurs:
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)