MySQL Administrator's Bible- P7

MySQL Administrators Bible- P7: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...
Nội dung trích xuất từ tài liệu:
MySQL Administrators Bible- P7 Stored Routines, Triggers, and Events 7continuedWhether or not a stored routine is deterministic is important due to binary logging. A deterministicstored routine will replicate without a problem; however, a stored routine that is not deterministicmay have problems replicating. If binary logging is set as statement-based (see Chapter 16), thebinary log contains the statements that change data, so they can be replayed during an incrementalrestore or during replication. A stored routine that is not deterministic may have a different outputgiven the same input, which means that an incremental restore or slave will not have data thatmatches the original data.Be very careful when using a stored routine that is not deterministic, because statement-basedbinary logging may not be adequate to store the data changes. If data integrity is a concern, userow-based binary logging or a deterministic stored routine instead.Unfortunately, at the time of this writing, the DETERMINISTIC and NOT DETERMINISTIC optionsserve only as comments, and are not verified by mysqld. There are no warnings or errors if anon-deterministic routine is set as DETERMINISTIC.MySQL will throw an error and refuse to create a stored function if binary logging of that functionmay be unsafe:ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, orREADS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log_bin_trust_function_creators variable)The best way to fix this problem is to declare the function to be DETERMINISTIC, NO SQL, orREADS DATA. The next best way is to have a user with the SUPER privilege define the function.Turning binary logging off is another way to fix this issue, though it may not be possible. Settinglog_bin_trust_function_creators to 1 will also fix this issue, though it is less safe becauseyou may end up attempting to replicate a function that is not safe for replication. SQL usage The remaining option is what SQL statements the stored routine uses. These values are for informational purposes only, and do not affect the way mysqld handles the stored routine. The possible values are: ■ MODIFIES SQL DATA — The stored routine may update data (with a DELETE, INSERT, or UPDATE command, for instance). ■ READS SQL DATA — The stored routine does not contain SQL to write data (as in MODI- FIES SQL DATA) but does contain SQL that reads data (that is, SELECT statements). The store_offerings stored procedure is an example of a stored routine that qualifies as READS SQL DATA. ■ CONTAINS SQL — The stored routine does not read or write data in the database. The curr_time and increment_counter stored procedures are examples of stored routines that qualify as CONTAINS SQL. ■ NO SQL — The stored routine has no SQL statements in it. 267Part II Developing with MySQL The default is CONTAINS SQL. Any changes to the default are done manually by the user; MySQL does not check whether or not the option is correct. For example, the store_offerings stored procedure is created with the default of CONTAINS SQL even though READS SQL DATA is the correct option. Because this is for informational purposes only, there is no harm in having this option be incorrect. However, if you intend to use the information to guide decisions, make sure you set the appropriate option. Full CREATE PROCEDURE syntax The full CREATE PROCEDURE syntax is: CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE p_name ([parameter[, . . . ]]) [SQL SECURITY {DEFINER | INVOKER}] [option . . . ] {statement} option is one or more of: SQL SECURITY {DEFINER | INVOKER} COMMENT ’comment string’ LANGUAGE SQL [NOT] DETERMINISTIC {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} parameter is: [IN|OUT|INOUT] name data_type Note that the CREATE ROUTINE privilege is needed in order to create a stored procedure. See Chapter 14 for more information about managing privileges. Creating a basic stored function A stored function outputs only one scalar value, so there is no such thing as an OUT parame- ter in a stored function. Therefore, the requirements for arguments to a stored function are that each input variable is named and the type defined. You must also ...