Oracle 5.0 Reference Manual Download

Page: 1728 / 3232

Binary Logging of Stored Programs

1708

be unsafe for data recovery or replication. To handle this, measures are implemented to enable
identification of safe routines and to prevent creation of unsafe routines except by users with
sufficient privileges.

• MySQL 5.0.12: For stored functions, when a function invocation that changes data occurs within

a nonlogged statement such as

SELECT

, the server logs a

DO func_name()

statement that

invokes the function so that the function gets executed during data recovery or replication to slave
servers. For stored procedures, the server does not log

CALL

statements. Instead, it logs individual

statements within a procedure that are executed as a result of a

CALL

. This eliminates problems that

may occur when a procedure would follow a different execution path on a slave than on the master.

• MySQL 5.0.16: The procedure logging changes made in 5.0.12 enable the conditions on unsafe

routines to be relaxed for stored procedures. Consequently, the user interface for controlling these
conditions is revised to apply only to functions. Procedure creators are no longer bound by them.

• MySQL 5.0.17: Logging of stored functions as

DO func_name()

statements (per the changes made

in 5.0.12) are logged as

SELECT func_name()

statements instead for better control over error

checking.

Routine logging before MySQL 5.0.6: Statements that create and use stored routines are not written
to the binary log, but statements invoked within stored routines are logged. Suppose that you issue the
following statements:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

CALL mysp();

For this example, only the

INSERT

statement appears in the binary log. The

CREATE PROCEDURE

and

CALL

statements do not appear. The absence of routine-related statements in the binary log means

that stored routines are not replicated correctly. It also means that for a data recovery operation, re-
executing events in the binary log does not recover stored routines.

Routine logging changes in MySQL 5.0.6: To address the absence of logging for stored routine
creation and

CALL

statements (and the consequent replication and data recovery concerns), the

characteristics of binary logging for stored routines were changed as described here. (Some of the
items in the following list point out issues that are dealt with in later versions.)

• The server writes

CREATE PROCEDURE

CREATE FUNCTION

ALTER PROCEDURE

ALTER

FUNCTION

DROP PROCEDURE

, and

DROP FUNCTION

statements to the binary log. Also, the server

logs

CALL

statements, not the statements executed within procedures. Suppose that you issue the

following statements:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

CALL mysp();

For this example, the

CREATE PROCEDURE

and

CALL

statements appear in the binary log, but the

INSERT

statement does not appear. This corrects the problem that occurred before MySQL 5.0.6

such that only the

INSERT

was logged.

• Logging

CALL

statements has a security implication for replication, which arises from two factors:

• Statements executed on a slave are processed by the slave SQL thread which has full privileges.

• It is possible for a procedure to follow different execution paths on master and slave servers.

The implication is that although a user must have the

CREATE ROUTINE

[576]

privilege to create a

routine, the user can write a routine containing a dangerous statement that will execute only on the
slave where it is processed by a thread that has full privileges. For example, if the master and slave
servers have server ID values of 1 and 2, respectively, a user on the master server could create and
invoke an unsafe procedure

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands