# Copyright (C) 2009-2010 Sun Microsystems, Inc. All rights reserved. # Use is subject to license terms. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, but # WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU # General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 # USA # From the manual: ################## # Parts of statements which make them unsafe when using statement based replication # In case of binlog format # - MIXED we get an automatic switching from statement-based to row-based replication. # Attention: This test does not contain an explicit check if this switching happens. # - STATEMENT we get a warning that the statement is unsafe. # The "/* QUERY_IS_REPLICATION_SAFE */" which gets added to most generated statements # ensures that RQG aborts in case the statement gets a warning about unsafe actions. # ------------------------------------------------------------------------------------------------------------ # - DML updates an NDBCLUSTER table # FIXME: NOT TESTED # - FOUND_ROWS(), ROW_COUNT(), UUID(), USER(), CURRENT_USER(), LOAD_FILE(), CURRENT_USER, VERSION() , # SYSDATE() are used # --> "value_unsafe_for_sbr" # - LIMIT even if we have a preceding ORDER BY which makes the statement safe # --> "where" --> "unsafe_condition" (no use of ORDER BY) # - 2 or more tables with AUTO_INCREMENT columns are updated. # --> "update","delete" # - any INSERT DELAYED is executed. # --> "low_priority_delayed_high_priority" but this had to be disabled. # - When the body of a view requires row-based replication, the statement creating the view also uses it # — for example, this occurs when the statement creating a view uses the UUID() function. # Observation: When running a statement using a view than the statement will be declared # unsafe in case the SELECT within the VIEW is unsafe. # --> create_view -> where -> optional use of value_unsafe_for_sbr # - Call to a UDF My guess: It is feared that the file might not exist on slave side. # FIXME: NOT IMPLEMENTED # - If a statement is logged by row and the client that executed the statement has any temporary tables, # then logging by row is used for all subsequent statements (except for those accessing temporary tables) # until all temporary tables in use by that client are dropped. # This is true whether or not any temporary tables are actually logged. # Temporary tables cannot be logged using the row-based format; thus, once row-based logging is used, # all subsequent statements using that table are unsafe, and we approximate this condition by treating # all statements made by that client as unsafe until the client no longer holds any temporary tables. # When FOUND_ROWS() or ROW_COUNT() is used. # SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name # WHERE id > 100 LIMIT 10; # SELECT FOUND_ROWS(); # FOUND_ROWS() or ROW_COUNT() are bigint(21) ; # Without SQL_CALC_FOUND_ROWS within the previous SELECT, FOUND_ROWS() = number of rows found by this SELECT. # --> "value_unsafe_for_sbr", but no SQL_CALC_FOUND_ROWS within any statement # - a statement refers to one or more system variables. # Exception. The following system variables, when used with session scope (only), # do not cause the logging format to switch: # * auto_increment_increment # * auto_increment_offset # * character_set_client # * character_set_connection # * character_set_database # * character_set_server # * collation_connection # * collation_database # * collation_server # * foreign_key_checks # * identity # * last_insert_id # --> "value_numeric_int" # * lc_time_names # * pseudo_thread_id # * sql_auto_is_null # * time_zone # * timestamp # --> "shake_clock" affects timestamp # * unique_checks # For information about how replication treats sql_mode, see Section 16.3.1.30, “Replication and Variables”. # When one of the tables involved is a log table in the mysql database. # FIXME: NOT IMPLEMENTED #----------------------------- # When using statement-based replication, the LOAD DATA INFILE statement's CONCURRENT option is not replicated; # that is, LOAD DATA CONCURRENT INFILE is replicated as LOAD DATA INFILE, and LOAD DATA CONCURRENT LOCAL INFILE # is replicated as LOAD DATA LOCAL INFILE. The CONCURRENT option is replicated when using row-based replication. # --> Use of "concurrent_or_empty" in "dml", but there is no explicite check if CONCURRENT is replicated or not. #------------------------------- # If you have databases on the master with character sets that differ from the global character_set_server value, you should # design your CREATE TABLE statements so that tables in those databases do not implicitly rely on the database default character set. # A good workaround is to state the character set and collation explicitly in CREATE TABLE statements. #----------------------------------- # MySQL 5.4.3 and later. # Every # - CREATE DATABASE IF NOT EXISTS # - CREATE TABLE IF NOT EXISTS , this includes CREATE TABLE IF NOT EXISTS ... LIKE # - CREATE EVENT IF NOT EXISTS # statement is replicated, whether or not the object already exists on the master. # However, replication of CREATE TABLE IF NOT EXISTS ... SELECT follows somewhat # different rules; see Section 16.3.1.4, “Replication of CREATE TABLE ... SELECT Statements”, for more information. # #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-differing-tables.html #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-floatvalues.html #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-flush.html #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-slaveerrors.html # FOREIGN KEY, master InnoDB and slave MyISAM # FIXME: NOT TESTED, FOREIGN KEY NOT IMPLEMENTED #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-max-allowed-packet.html # BLOB/TEXT value too big for max-allowed-packet on master or on slave # FIXME: NOT TESTED #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-timeout.html # Slave: Innodb detects deadlock -> slave_transaction_retries to run the action to replicate .... # mleich: Most probably not doable with current RQG. #----------------------------------- # The same system time zone should be set for both master and slave. If not -> problems with NOW() or FROM_UNIXTIME() # CONVERT_TZ(...,...,@@session.time_zone) is properly replicated ... # FIXME: different time_zones NOT TESTED #----------------------------------- # In situations where transactions mix updates to transactional and nontransactional tables, the order of statements # in the binary log is correct, and all needed statements are written to the binary log even in case of a ROLLBACK. # However, when a second connection updates the nontransactional table before the first connection's transaction is # complete, statements can be logged out of order, because the second connection's update is written immediately after # it is performed, regardless of the state of the transaction being performed by the first connection. # # Due to the nontransactional nature of MyISAM tables, it is possible to have a statement that only partially updates # a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating # a key constraint, or if a long update statement is killed after updating some of the rows. # If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do # about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. # # When the storage engine type of the slave is nontransactional, transactions on the master that mix updates of transactional # and nontransactional tables should be avoided because they can cause inconsistency of the data between the master's # transactional table and the slave's nontransactional table. # # RULES FOR THE CURRENT TEST IN CASE OF BINLOG_FORMAT = STATEMENT: # (independend of the use - modify or just query - of the table) # 1. Do not use transactional and non transactional tables within the same statement. # 2. Have only three types of transactions # - use non transactional tables only # - use transactional tables only # - use for the first phase of the transaction only non transactional tables and # use for the last phase of the transaction only transactional tables # --> "$pick_mode" related stuff # 3. SAVEPOINT A followed by some UPDATE on a non transactional table is unsafe # # FROM THE DISCUSSION: # - If you want to change the replication format, do so outside the boundaries of a transaction. (SBR?) # --> "*_binlog_format_sequence" #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/replication-features-triggers.html !!!! #----------------------------------- # TRUNCATE is treated for purposes of logging and replication as DDL rather than DML ... # --> implemented #----------------------------------- # http://dev.mysql.com/doc/refman/5.4/en/mysqlbinlog-hexdump.html # Type Name Meaning # 00 UNKNOWN_EVENT This event should never be present in the log. # 01 START_EVENT_V3 This indicates the start of a log file written by MySQL 4 or earlier. # X 02 QUERY_EVENT The most common type of events. These contain statements executed on the master. # ? 03 STOP_EVENT Indicates that master has stopped. # X 04 ROTATE_EVENT Written when the master switches to a new log file. # --> "rotate_event" # X 05 INTVAR_EVENT Used for AUTO_INCREMENT values or when the LAST_INSERT_ID() function is used in the statement. # --> "value" contains NULL and (nested) LAST_INSERT_ID() # 06 LOAD_EVENT Used for LOAD DATA INFILE in MySQL 3.23. # 07 SLAVE_EVENT Reserved for future use. # 08 CREATE_FILE_EVENT Used for LOAD DATA INFILE statements. This indicates the start of execution of such a statement. A temporary file is created on the slave. Used in MySQL 4 only. # X 09 APPEND_BLOCK_EVENT Contains data for use in a LOAD DATA INFILE statement. The data is stored in the temporary file on the slave. # --> "dml" contains LOAD DATA # 0a EXEC_LOAD_EVENT Used for LOAD DATA INFILE statements. The contents of the temporary file is stored in the table on the slave. Used in MySQL 4 only. # X 0b DELETE_FILE_EVENT Rollback of a LOAD DATA INFILE statement. The temporary file should be deleted on the slave. # --> "dml" contains LOAD DATA # 0c NEW_LOAD_EVENT Used for LOAD DATA INFILE in MySQL 4 and earlier. # X 0d RAND_EVENT Used to send information about random values if the RAND() function is used in the statement. # --> "value_rand" # X 0e USER_VAR_EVENT Used to replicate user variables. # --> "dml" containing SET @aux + "values" containg @aux # X 0f FORMAT_DESCRIPTION_EVENT This indicates the start of a log file written by MySQL 5 or later. # --> ? # X 10 XID_EVENT Event indicating commit of an XA transaction. # X 11 BEGIN_LOAD_QUERY_EVENT Used for LOAD DATA INFILE statements in MySQL 5 and later. # X 12 EXECUTE_LOAD_QUERY_EVENT Used for LOAD DATA INFILE statements in MySQL 5 and later. # --> "dml" contains LOAD DATA # X 13 TABLE_MAP_EVENT Information about a table definition. Used in MySQL 5.1.5 and later. # 14 PRE_GA_WRITE_ROWS_EVENT Row data for a single table that should be created. Used in MySQL 5.1.5 to 5.1.17. # 15 PRE_GA_UPDATE_ROWS_EVENT Row data for a single table that needs to be updated. Used in MySQL 5.1.5 to 5.1.17. # 16 PRE_GA_DELETE_ROWS_EVENT Row data for a single table that should be deleted. Used in MySQL 5.1.5 to 5.1.17. # X 17 WRITE_ROWS_EVENT Row data for a single table that should be created. Used in MySQL 5.1.18 and later. # --> insert # X 18 UPDATE_ROWS_EVENT Row data for a single table that needs to be updated. Used in MySQL 5.1.18 and later. # --> update # X 19 DELETE_ROWS_EVENT Row data for a single table that should be deleted. Used in MySQL 5.1.18 and later. # --> delete # 1a INCIDENT_EVENT Something out of the ordinary happened. Added in MySQL 5.1.18. # My (mleich) markings: # X needs sub test # I most probably already covered (FIXME: Check in hex dump) #------------------------------------------------ # The following restriction applies to statement-based replication only, not to row-based replication. # The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated # without the slave knowing the concurrency context on master. Therefore, these functions should not be used to insert # into a master's table because the content on the slave would differ. # (For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).) #------------------------------------------------ ################################################# ################################################# safety_check: # For debugging the grammar use { return '/*' . $pick_mode . '*/' } /* QUERY_IS_REPLICATION_SAFE */ ; # For faster execution set this grammar element to "empty". # ; query: binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | binlog_format_sequence | set_iso_level | set_iso_level | set_iso_level | set_iso_level | rotate_event | # This runs into a server weakness which finally fools the RQG deadlock detection. # So it must be disabled. # shake_clock | # # We MUST reduce the huge amount of NULL's safety_check UPDATE ignore pick_schema pick_safe_table SET _field[invariant] = col_tinyint WHERE col_tinyint BETWEEN _tinyint[invariant] AND _tinyint[invariant] + _digit AND _field[invariant] IS NULL ; COMMIT | safety_check UPDATE ignore pick_schema pick_safe_table SET _field[invariant] = col_tinyint WHERE col_tinyint BETWEEN _tinyint[invariant] AND _tinyint[invariant] + _digit AND _field[invariant] IS NULL ; COMMIT ; query_init: # We need to know our current SESSION BINLOG_FORMAT. We do this by simply setting the BINLOG_FORMAT. rand_session_binlog_format ; set_iso_level: safety_check SET global_or_session TRANSACTION ISOLATION LEVEL iso_level ; iso_level: { if ( $format == 'STATEMENT' ) { return $prng->arrayElement(['REPEATABLE READ','SERIALIZABLE']) } else { return $prng->arrayElement(['READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ','SERIALIZABLE']) } } ; global_or_session: SESSION | GLOBAL ; shake_clock: safety_check SET SESSION TIMESTAMP = UNIX_TIMESTAMP() plus_minus _digit ; rotate_event: # Cause that the master switches to a new binary log file # RESET MASTER is not useful here because it causes # - master.err: [ERROR] Failed to open log (file '/dev/shm/var_290/log/master-bin.000002', errno 2) # - the RQG test does not terminate in usual way (RQG assumes deadlock) safety_check FLUSH LOGS ; xid_event: # Omit BEGIN because it is only an alias for START TRANSACTION START TRANSACTION | COMMIT | ROLLBACK | savepoint_event | implicit_commit ; savepoint_event: # In SBR after a "SAVEPOINT A" any statement which modifies a nontransactional table is unsafe. # Therefore we enforce here that future statements within the current transaction use # a transactional table. SAVEPOINT A { $pick_mode=3; return undef} | SAVEPOINT A { $pick_mode=3; return undef} | SAVEPOINT A { $pick_mode=3; return undef} | ROLLBACK TO SAVEPOINT A | RELEASE SAVEPOINT A ; implicit_commit: # Attention: Although the name of the grammar item is "implicit_commit", most but not all of the following # statements will do an implicit COMMIT. # Reasons: # 1. Some statements do not COMMIT before execution and only COMMIT after a successful execution. # Due to the randomness of RQG I cannot predict all time if a execution will be successful. # 2. There are some statements which neither COMMIT before or after execution. # But I need a grammar item where I call them. They are partially called here because of # such *technical* reasons. create_schema | create_schema | alter_schema | drop_schema | # create_is_copy | # Experience when running the current test with several sessions: # The content of t1_is_columns_ differs between master and slave. # Bug#29790 information schema returns non-atomic content => replication (binlog) fails # IMHO it is to be expected that INSERT INTO ... SELECT ... FROM information_schema... could lead in SBR mode to # differences between master and slave content because # 1. The current content of the master and the slave rules compared to RBR where only the content of the master rules. # 2. There is some delay till some data modifying activity of a session gets pushed to the slave. # 3. Caused by optimization of binlogging etc. the delay might differ per session. # At least the deactivation of fill_is_copy via $m10,$m11 n case of SBR helped to avoid the content difference. { if ($format=='STATEMENT') { $m10 = '/*' ; $m11 = '*/'} else { $m10 = '' ; $m11 = '' } ; return undef } fill_is_copy | # create_procedure | create_procedure | create_procedure | alter_procedure | drop_procedure | # create_function | create_function | create_function | alter_function | drop_function | # create_trigger | create_trigger | drop_trigger | # # If # Bug#50095 Multi statement including CREATE EVENT causes rotten binlog entry # is fixed please enable the following four lines. # create_event | # create_event | # alter_event | # drop_event | # create_table1 | create_table | create_table | alter_table | truncate_table | # Please enable the next line in case # Bug#50760 RENAME TABLE, Slave stops with HA_ERR_END_OF_FILE # is fixed. # rename_table | drop_table | # create_index | drop_index | # create_view | create_view | alter_view | rename_view | drop_view | # SET AUTOCOMMIT = ON | # OFF -> OFF or wrong value , no implicit COMMIT SET AUTOCOMMIT = OFF | # # Statements that implicitly use or modify tables in the mysql database cause an implicit COMMIT. create_user | create_user | drop_user | rename_user | set_password | # grant | grant | revoke | # table_administration | # create_keycache | cache_index | load_index_to_cache | # flush | # reset | # # LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine # # This causes an implicit COMMIT before execution. LOCK TABLE _table WRITE | # This causes an implicit COMMIT. UNLOCK TABLES ; flush: # No implicit COMMIT. FLUSH local_non_local TABLES | FLUSH local_non_local PRIVILEGES | FLUSH local_non_local QUERY CACHE ; reset: # No implicit COMMIT. RESET QUERY CACHE ; # KEY CACHE is a MyISAM only feature. create_keycache: # 0. There is no SESSION specific KEY CACHE. # 1. This statement does not COMMIT. # 2. 'key_cache_'.$$ gets created if not already known # 3. A KEY CACHE with size = 0 causes that the KEY CACHE is destroyed but. # Nevertheless the name of this KEY CACHE can be used within the corresponding statements and # we do not get error messages. SET GLOBAL { 'key_cache_'.$$ } .key_buffer_size = 128 * 1024 | SET GLOBAL { 'key_cache_'.$$ } .key_buffer_size = 0 ; cache_index: # COMMIT only *after* successful execution. CACHE INDEX pick_schema table_name IN { 'key_cache_'.$$ } | CACHE INDEX pick_schema table_name , pick_schema table_name IN { 'key_cache_'.$$ } | # The next statement will fail. CACHE INDEX pick_schema table_name IN cache_not_exists ; load_index_to_cache: # COMMIT before execution. LOAD INDEX INTO CACHE pick_schema table_name | LOAD INDEX INTO CACHE pick_schema table_name , pick_schema table_name | # The next statement will fail. LOAD INDEX INTO CACHE not_exists ; table_administration: # COMMIT before execution. ANALYZE local_non_local TABLE table_items | OPTIMIZE local_non_local TABLE table_items | REPAIR local_non_local TABLE table_items | CHECK TABLE table_items ; local_non_local: # LOCAL is an alias for NO_WRITE_TO_BINLOG. Therfore we check LOCAL only. | LOCAL ; table_items: pick_schema table_name | pick_schema table_name | pick_schema table_name , pick_schema table_name ; create_user: CREATE USER user_name | CREATE USER user_name | CREATE USER user_name , user_name ; drop_user: DROP USER user_name | DROP USER user_name | DROP USER user_name , user_name ; rename_user: RENAME USER user_name TO user_name | RENAME USER user_name TO user_name | RENAME USER user_name TO user_name , user_name TO user_name ; set_password: # COMMIT before execution. SET PASSWORD FOR user_name = PASSWORD(' _letter '); user_name: { 'Luigi_'.$$.'@localhost' } | { 'Emilio_'.$$.'@localhost' } ; grant: GRANT ALL ON test.* TO user_name | GRANT ALL ON test.* TO user_name | GRANT ALL ON test.* TO user_name , user_name ; revoke: REVOKE ALL ON test.* FROM user_name | REVOKE ALL ON test.* FROM user_name | REVOKE ALL ON test.* FROM user_name , user_name ; create_schema: CREATE SCHEMA IF NOT EXISTS { 'test_'.$$ } CHARACTER SET character_set ; drop_schema: DROP SCHEMA IF EXISTS { 'test_'.$$ } ; alter_schema: # This fails if we have active locked tables or an open transaction which # already modified a table. ALTER SCHEMA { 'test_'.$$ } CHARACTER SET character_set ; # Attention: An open (existing?) temporary table causes that an in case of current # SESSION BINLOG_FORMAT = ROW any SET ... BINLOG_FORMAT fails. create_table: # FIXME Move this out of xid..... CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_myisam_'.$$ } LIKE nontrans_table | CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_innodb_'.$$ } LIKE trans_table | CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_myisam_'.$$ } LIKE nontrans_table | CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_innodb_'.$$ } LIKE trans_table | # FIXME Add later the case that base and temporary table have the same names # Please enable the next two lines if # Bug#49132 Replication failure on temporary table + DDL # is fixed. # CREATE TEMPORARY TABLE IF NOT EXISTS pick_schema { 't1_temp_myisam_'.$$ } LIKE nontrans_table | # CREATE TEMPORARY TABLE IF NOT EXISTS pick_schema { 't1_temp_innodb_'.$$ } LIKE trans_table | # This will fail because mysql.user already exists. CREATE TABLE mysql.user ( f1 BIGINT ) ; create_table1: # We must avoid the generation of statements which are unsafe in SBR. # 1. We get an implicite COMMIT before execution of CREATE ... # 2. In case the table already exists we will get an ugly INSERT ... SELECT . # 3. We pick_mode 1 til 4. # pick_mode | Storage engine type to choose | # 0 | any | # 1 | undef | Set pick_mode = 3 (-> t1_*_innodb_*) # 2 | nontrans | t1_*_myisam_* # 3 | trans | t1_*_innodb_* # 4 | nontrans and later trans | SET pick_mode = 2 (-> t1_*_myisam_*) {if ($format=='STATEMENT') {$pick_mode=2}; return '/*' . $pick_mode . '*/'} vmarker_set CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_myisam_'.$$ } ENGINE = MyISAM AS SELECT _field_list[invariant] FROM table_in_select AS A addition | {if ($format=='STATEMENT') {$pick_mode=3}; return '/*' . $pick_mode . '*/'} vmarker_set CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_innodb_'.$$ } ENGINE = InnoDB AS SELECT _field_list[invariant] FROM table_in_select AS A addition ; drop_table: # FIXME Move this out of xid..... DROP TABLE IF EXISTS pick_schema { 't1_base_myisam_'.$$ } | DROP TABLE IF EXISTS pick_schema { 't1_base_innodb_'.$$ } | # FIXME Add later the case that base and temporary table have the same names # # DROP TEMPORARY TABLE IF EXISTS pick_schema { 't1_temp_myisam_'.$$ } | # DROP TEMPORARY TABLE IF EXISTS pick_schema { 't1_temp_innodb_'.$$ } | # # This will fail because already exist_not_exist. DROP TABLE does_not_exist ; alter_table: ALTER TABLE pick_schema table_name COMMENT ' _letter ' ; truncate_table: TRUNCATE TABLE pick_schema table_name ; table_name: { 't1_base_myisam_'.$$ } | { 't1_base_innodb_'.$$ } | { 't1_base_myisam_'.$$ } | { 't1_base_innodb_'.$$ } | # Please enable the next four lines if # Bug#49132 Replication failure on temporary table + DDL # is fixed. # { 't1_temp_myisam_'.$$ } | # { 't1_temp_innodb_'.$$ } | # { 't1_temp_myisam_'.$$ } | # { 't1_temp_innodb_'.$$ } | does_not_exist ; create_index: CREATE INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } (col_tinyint) | CREATE INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } (col_tinyint) | CREATE INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } (col_tinyint) | CREATE INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } (col_tinyint) | # # Please enable the next four lines if # Bug#49132 Replication failure on temporary table + DDL # is fixed. # CREATE INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } (col_tinyint) | # CREATE INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } (col_tinyint) | # CREATE INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } (col_tinyint) | # CREATE INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } (col_tinyint) | # CREATE INDEX idx_will_fail ON does_not_exist (f1) ; drop_index: DROP INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } | DROP INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } | DROP INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } | DROP INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } | # # Please enable the next four lines if # Bug#49132 Replication failure on temporary table + DDL # is fixed. # DROP INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } | # DROP INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } | # DROP INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } | # DROP INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } | # DROP INDEX idx_will_fail ON does_not_exist ; rename_table: RENAME TABLE test . { 't1_base_myisam_'.$$ } TO test . { 't2_base_myisam_'.$$ } | RENAME TABLE test . { 't2_base_myisam_'.$$ } TO test . { 't1_base_myisam_'.$$ } | RENAME TABLE test . { 't1_base_innodb_'.$$ } TO test . { 't2_base_innodb_'.$$ } | RENAME TABLE test . { 't2_base_innodb_'.$$ } TO test . { 't1_base_innodb_'.$$ } | # RENAME TABLE test . { 't1_base_myisam_'.$$ } TO test . { 't2_base_myisam_'.$$ } , test1 . { 't1_base_myisam_'.$$ } TO test1 . { 't2_base_myisam_'.$$ } | RENAME TABLE test1 . { 't2_base_myisam_'.$$ } TO test1 . { 't1_base_myisam_'.$$ } , test . { 't2_base_myisam_'.$$ } TO test . { 't1_base_myisam_'.$$ } | # # This will fail in case we "move" the table between different schemas and there is a trigger on the table. RENAME TABLE pick_schema { 't1_base_myisam_'.$$ } TO pick_schema { 't1_base_myisam_'.$$ } | # # Please enable the next four lines if # Bug#49132 Replication failure on temporary table + DDL # is fixed. # RENAME TABLE test . { 't1_temp_myisam_'.$$ } TO test . { 't2_temp_myisam_'.$$ } | # RENAME TABLE test . { 't2_temp_myisam_'.$$ } TO test . { 't1_temp_myisam_'.$$ } | # RENAME TABLE test . { 't1_temp_innodb_'.$$ } TO test . { 't2_temp_innodb_'.$$ } | # RENAME TABLE test . { 't2_temp_innodb_'.$$ } TO test . { 't1_temp_innodb_'.$$ } | # # This must fail. RENAME TABLE does_not_exist TO pick_schema { 't1_base_myisam_'.$$ } ; # The server gives a warning in case the current binlog format is STATEMENT and the SELECT # used within the VIEW definition is unsafe in SBR mode. This is IMHO a valueless but # unimportant limitation. create_view: CREATE VIEW trans_view | CREATE VIEW nontrans_view ; trans_view: pick_schema { if ($format=='STATEMENT') {return 'v1_trans_safe_for_sbr_'.$$ } else { return 'v1_trans_unsafe_for_sbr_'.$$ } } AS SELECT _field_list FROM trans_table where ; nontrans_view: pick_schema { if ($format=='STATEMENT') {return 'v1_nontrans_safe_for_sbr_'.$$ } else { return 'v1_nontrans_unsafe_for_sbr_'.$$ } } AS SELECT _field_list FROM nontrans_table where ; drop_view: DROP VIEW IF EXISTS pick_schema { 'v1_trans_safe_for_sbr_'.$$ } | DROP VIEW IF EXISTS pick_schema { 'v1_trans_unsafe_for_sbr_'.$$ } | DROP VIEW IF EXISTS pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } | DROP VIEW IF EXISTS pick_schema { 'v1_nontrans_unsafe_for_sbr_'.$$ } ; alter_view: ALTER VIEW trans_view | ALTER VIEW nontrans_view ; rename_view: RENAME TABLE test . { 'v1_trans_'.$$ } TO test . { 'v2_trans_'.$$ } | RENAME TABLE test . { 'v2_trans_'.$$ } TO test . { 'v1_trans_'.$$ } | RENAME TABLE test . { 'v1_nontrans_'.$$ } TO test . { 'v2_nontrans_'.$$ } | RENAME TABLE test . { 'v2_nontrans_'.$$ } TO test . { 'v1_nontrans_'.$$ } | # # This will fail in case the schemas picked differ. Moving a VIEW from one SCHEMA to another is not supported. RENAME TABLE pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } TO pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } ; vmarker_set: { if ($format=='STATEMENT') { $f0 = ''; $f1 = '/*'; $f2 = '*/' } else { $f0 = '/*'; $f1 = '*/'; $f2 = '' } ; return undef } ; # This procedure and function handling is a bit tricky and I am till now not 100% convinced that the solution is very good. # The base: # 1. CREATE AND DROP PROCEDURE should be replication safe independend of the current session binlog format and # the tables used within preceding statements of the current transaction. # BTW: CREATE/DROP cause an implicite COMMIT before the inner part of the statement itself gets processed. # 2. In case we call a procedure we must ensure that the activity (DML) of the procedure is replication safe. # In short: There is a dependency on the current session binlog format and # the tables used within preceding statements of the current transaction. # Solution: # 1. Procedure names contain depending on their DML activity a part (-> $pick_mode) which tells in which # pick_mode of the current session they can be used. # Of course the DML activity of the procedure has to fit to the pick_mode part of its name. # 2. "The base 1." says that we can create any procedure within the current session. # This would require that we store the current pick_mode, switch to the pick_mode to be used for the DML within # the procedure, create the procedure and restore the old session pick_mode. # But in fact we have only to ensure that # - our procedures are proper defined (pick_mode part of name fits to its DML activity) # - most probably the corresponding procedure exists when we call the procedure # Therefore we only try to create a procedure which fits to the current session pick_mode. # The frequent dynamic switching of the session binlog format causes a calculation of pick_mode. create_procedure: # Activate the next line if # Bug#50423 Crash on second call of a procedure dropping a trigger # is fixed. Not: This crash seems to be fixed in mysql-next-mr and mysql-6.0-codebase-bugfixing. # CREATE PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } () BEGIN dml_list ; END ; CREATE PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } () BEGIN proc_stmt ; END ; proc_stmt: replace | update | delete ; drop_procedure: DROP PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } ; call_procedure: # Enable the next line in case # Bug #50624 crash in check_table_access during call procedure # is fixed or you use # mysql-6.0-codebase-bugfixing # # CALL pick_schema { 'p1_'.$pick_mode.'_'.$$ } () ; ; alter_procedure: ALTER PROCEDURE { 'p1_'.$pick_mode.'_'.$$ } COMMENT ' _letter ' ; create_function: CREATE FUNCTION pick_schema { 'f1_'.$pick_mode.'_'.$$ } () RETURNS TINYINT RETURN ( SELECT MAX( col_tinyint ) FROM pick_schema pick_safe_table where ) ; drop_function: DROP FUNCTION pick_schema { 'f1_'.$pick_mode.'_'.$$ } ; # Note: We use the function within the grammar item "value". alter_function: ALTER FUNCTION { 'f1_'.$pick_mode.'_'.$$ } COMMENT ' _letter ' ; # I am unsure if "$pick_mode" makes here sense. It could be used to tell us what the TRIGGER might be doing but it cannot # be used for deciding if we want to execute the trigger or not. # Therefore "$pick_mode" might be removed in future. # FIXME: # 1. pick_safe_table must point to a base table # 2. trigger and basetable must reside within the same schema # If not we get "ERROR HY000: Trigger in wrong schema". # # I got # Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. # Reason for unsafeness: Statement updates two AUTO_INCREMENT columns. This is unsafe because the generated value cannot be predicted by slave. # in the following situation: # - SBR # - DELETE causes the execution of a trigger which inserts per one statement two rows into a table. # The table where the insert should happen contains an autoincrement primary key but there is no # explicite value for this column within the insert. # Thinkable solutions: # a) Define a stupid trigger which does not modify tables which contain an AUTOINCREMENT column. # b) Define a sophisticated trigger which fits to the situation when the trigger gets used. # This is not so easy. # For example in case: # - the TRIGGER contains a "if @@session.binlog_format = 'ROW' ..." and # - current SESSION BINLOG_FORMAT is 'STATEMENT' # than we get the warning # Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. # Reason for unsafeness: Statement uses a system variable whose value may differ on slave. # c) Ignoring this problem does not work because in most cases the warning about unsafe statement # is right. # Let's try a) first. # create_trigger: CREATE TRIGGER pick_schema { 'tr1_'.$pick_mode.'_'.$$ } trigger_time trigger_event ON pick_schema pick_safe_table FOR EACH ROW BEGIN trigger_action ; END ; trigger_time: BEFORE | AFTER ; trigger_event: INSERT | DELETE ; trigger_action: # insert | replace | delete | update | CALL pick_schema { 'p1_'.$pick_mode.'_'.$$ } () ; SET @aux = 1 ; drop_trigger: DROP TRIGGER IF EXISTS pick_schema { 'tr1_'.$pick_mode.'_'.$$ }; # I am unsure if "$pick_mode" makes here sense. Therefore this might be removed in future. create_event: CREATE EVENT IF NOT EXISTS pick_schema { 'e1_'.$pick_mode.'_'.$$ } ON SCHEDULE EVERY 10 SECOND STARTS NOW() ENDS NOW() + INTERVAL 21 SECOND completion_handling DO insert ; completion_handling: ON COMPLETION not_or_empty PRESERVE ; drop_event: DROP EVENT IF EXISTS pick_schema { 'e1_'.$pick_mode.'_'.$$ } ; not_or_empty: NOT | ; alter_event: ALTER EVENT pick_schema { 'e1_'.$pick_mode.'_'.$$ } ON SCHEDULE EVERY 10 SECOND STARTS NOW() ENDS NOW() + INTERVAL 21 SECOND completion_handling DO insert ; # Some INFORMATION_SCHEMA related tests #-------------------------------------- # Please note the following: # - There is no drop table grammar item. # - The copies of the current information_schema tables do contain only a subset of columns. # All columns where I guessed that they are probably unsafe in replication are omitted. # - The tests around information_schema are intentionally simpler than other tests. # - Bug#29790 information schema returns non-atomic content => replication (binlog) fails create_is_copy: CREATE TABLE IF NOT EXISTS test . { 't1_is_schemata_'.$$ } AS schemata_part WHERE 1 = 0 | # Experience: The value of tables.AUTO_INCREMENT can differ between master and slave. CREATE TABLE IF NOT EXISTS test . { 't1_is_tables_'.$$ } AS tables_part WHERE 1 = 0 | CREATE TABLE IF NOT EXISTS test . { 't1_is_columns_'.$$ } AS columns_part WHERE 1 = 0 | CREATE TABLE IF NOT EXISTS test . { 't1_is_routines_'.$$ } AS routines_part WHERE 1 = 0 ; fill_is_copy: TRUNCATE test . { 't1_is_schemata_'.$$ } ; safety_check { return $m10 } INSERT INTO test . { 't1_is_schemata_'.$$ } schemata_part WHERE SCHEMA_NAME LIKE 'test%' ORDER BY 1 { return $m11 } ; safety_check COMMIT | TRUNCATE test . { 't1_is_tables_'.$$ } ; safety_check { return $m10 } INSERT INTO test . { 't1_is_tables_'.$$ } tables_part WHERE TABLE_SCHEMA LIKE 'test%' ORDER BY 1,2 { return $m11 } ; safety_check COMMIT | TRUNCATE test . { 't1_is_columns_'.$$ } ; safety_check { return $m10 } INSERT INTO test . { 't1_is_columns_'.$$ } columns_part WHERE TABLE_SCHEMA LIKE 'test%' ORDER BY 1,2,3 { return $m11 } ; safety_check COMMIT | TRUNCATE test . { 't1_is_routines_'.$$ } ; safety_check { return $m10 } INSERT INTO test . { 't1_is_routines_'.$$ } routines_part WHERE ROUTINE_SCHEMA LIKE 'test%' ORDER BY 1,2 { return $m11 } ; safety_check COMMIT ; schemata_part: SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME FROM information_schema.schemata ; tables_part: SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,TABLE_COLLATION,TABLE_COMMENT FROM information_schema.tables ; columns_part: SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,PRIVILEGES,COLUMN_COMMENT FROM information_schema.columns ; routines_part: SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,IS_DETERMINISTIC,SECURITY_TYPE,SQL_MODE,DEFINER,CHARACTER_SET_CLIENT,COLLATION_CONNECTION,DATABASE_COLLATION FROM information_schema.routines ; # Guarantee that the transaction has ended before we switch the binlog format binlog_format_sequence: COMMIT ; safety_check binlog_format_set ; dml_list ; safety_check xid_event ; dml_list: safety_check dml | safety_check dml nontrans_trans_shift ; dml_list ; nontrans_trans_shift: # This is needed for the generation of the following scenario. # m statements of an transaction use non transactional tables followed by # n statements which use transactional tables. { if ( ($prng->int(1,4) == 4) && ($pick_mode == 4) ) { $pickmode = 3 } ; return undef } ; binlog_format_set: # 1. SESSION BINLOG_FORMAT --> How the actions of our current session will be bin logged. # 2. GLOBAL BINLOG_FORMAT --> How actions with DELAYED will be bin logged. # --> Initial SESSION BINLOG_FORMAT of session started in future. # This means any SET GLOBAL BINLOG_FORMAT ... executed by any session has no impact on any # already existing session (except 2.). # # In case we # - are running in the moment with BINLOG_FORMAT = ROW and # - have an open (existing?) temporary table # any SET SESSION/GLOBAL gets # Query: ... SET SESSION BINLOG_FORMAT = ROW failed: # 1559 Cannot switch out of the row-based binary log format when the session has open temporary tables # Although this means we do not get the intended BINLOG_FORMAT there will be no additional # problems like we run unsafe statements etc. Our fortune is that we are already running # with binary log format row which is "compatible" with any pickmode. rand_global_binlog_format | rand_session_binlog_format | rand_session_binlog_format | rand_session_binlog_format ; rand_global_binlog_format: SET GLOBAL BINLOG_FORMAT = STATEMENT | SET GLOBAL BINLOG_FORMAT = MIXED | SET GLOBAL BINLOG_FORMAT = ROW ; rand_session_binlog_format: SET SESSION BINLOG_FORMAT = { $format = 'STATEMENT' ; $pick_mode = $prng->int(1,4) ; return $format } vmarker_set | SET SESSION BINLOG_FORMAT = { $format = 'MIXED' ; $pick_mode = 0 ; return $format } vmarker_set | SET SESSION BINLOG_FORMAT = { $format = 'ROW' ; $pick_mode = 0 ; return $format } vmarker_set ; dml: # Enable the next line if # Bug#49628 corrupt table after legal SQL, LONGTEXT column # is fixed. # generate_outfile ; safety_check LOAD DATA concurrent_or_empty INFILE _tmpnam REPLACE INTO TABLE pick_schema pick_safe_table | update | delete | insert | call_procedure | # LOAD DATA INFILE ... is not supported in prepared statement mode. PREPARE st1 FROM " update " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 | PREPARE st1 FROM " delete " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 | PREPARE st1 FROM " insert " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 | # We need the next statement for other statements which should use a user variable. SET @aux = value | # We need the next statements for other statements which should be affected by switching the database. USE `test` | USE `test1` | select_for_update | xid_event ; generate_outfile: SELECT * FROM pick_schema pick_safe_table ORDER BY _field INTO OUTFILE _tmpnam ; concurrent_or_empty: | CONCURRENT ; pick_schema: | test . | test1 . ; delete: # Delete in one table, search in one table # Unsafe in statement based replication except we add ORDER BY DELETE low_priority quick ignore FROM pick_schema pick_safe_table where | # Delete in two tables, search in two tables # Note: The next grammar line leads unfortunately to frequent failing statements (Unknown table A or B). # The reason is that in case both tables are located in different SCHEMA's than the # the schema_name must be written before the table alias. # Example: DELETE test.A, test1.B FROM test.t1 AS A NATURAL JOIN test1.t7 AS B .... # DELETE low_priority quick ignore A , B FROM pick_schema pick_safe_table AS A join where | DELETE low_priority quick ignore A , B FROM pick_safe_table AS A NATURAL JOIN pick_safe_table B where | DELETE low_priority quick ignore test1.A , test.B FROM test1 . pick_safe_table AS A NATURAL JOIN test . pick_safe_table B where ; join: # 1. Do not place a where condition here. # 2. join is also use when modifying two tables in one statement. # Therefore we must use "pick_safe_table" here. NATURAL JOIN pick_schema pick_safe_table B ; subquery: correlated | non_correlated ; subquery_part1: AND A. _field[invariant] IN ( SELECT _field[invariant] FROM pick_schema pick_safe_table AS B ; correlated: subquery_part1 WHERE B.col_tinyint = A.col_tinyint ) ; non_correlated: subquery_part1 ) ; where: # Note abaout "AND ( _field[invariant] IS NULL OR _field[invariant] <> value_unsafe_for_sbr )" # 1. This statement piece is unsafe (we also get a warning) when using SESSION BINLOG_FORMAT = STATEMENT. # 2. We add this piece whenever SESSION BINLOG_FORMAT <> STATEMENT. # 3. It should be very unlikely that it gives FALSE. WHERE col_tinyint BETWEEN _tinyint[invariant] AND _tinyint[invariant] + 2 { return $f0 . $f1 } unsafe_condition { return $f2 } ; unsafe_condition: AND ( _field[invariant] IS NULL OR _field[invariant] <> value_unsafe_for_sbr ) | LIMIT 2 ; insert: # Insert into one table, search in no other table INSERT low_priority_delayed_high_priority ignore INTO pick_schema pick_safe_table ( _field , col_tinyint ) VALUES values_list on_duplicate_key_update | # Insert into one table, search in >= 1 tables INSERT low_priority_delayed_high_priority ignore INTO pick_schema pick_safe_table ( _field_list[invariant] ) SELECT _field_list[invariant] FROM table_in_select AS A addition ; values_list: ( value , _tinyint ) | ( value , _tinyint ) , ( value , _tinyint ) ; on_duplicate_key_update: # Only 10 % | | | | | | | | | # Enable the next line in case # Bug#50619 assert in handler::update_auto_increment # is fixed. # ON DUPLICATE KEY UPDATE _field = value ; ON DUPLICATE KEY UPDATE _field = ABS( value ) ; table_in_select: pick_schema pick_safe_table | ( SELECT _field_list[invariant] FROM pick_schema pick_safe_table ) ; addition: where | where subquery | join where | where union where ; union: UNION SELECT _field_list[invariant] FROM table_in_select AS B ; replace: # HIGH_PRIORITY is not allowed REPLACE low_priority_delayed INTO pick_schema pick_safe_table ( _field , col_tinyint ) VALUES values_list on_duplicate_key_update | REPLACE low_priority_delayed INTO pick_schema pick_safe_table ( _field_list[invariant] ) SELECT _field_list[invariant] FROM table_in_select AS A addition ; update: # mleich: Search within another table etc. should be already sufficient covered by "delete" and "insert". # Update one table UPDATE ignore pick_schema pick_safe_table SET _field = value where | # Update two tables UPDATE ignore pick_schema pick_safe_table AS A join SET A. _field = value , B. _field = value where ; select_for_update: # SELECT does not get replicated, but we want its sideeffects on the transaction. SELECT col_tinyint, _field FROM pick_safe_table where FOR UPDATE; value: value_numeric | # Enable the next line in case # Bug#50511 Sometimes wrong handling of user variables containing NULL # is fixed. # value_rand | value_string_converted | value_string | value_temporal | @aux | NULL | pick_schema { 'f1_'.$pick_mode.'_'.$$ } () | { if ($format=='STATEMENT') {return '/*'} } value_unsafe_for_sbr { if ($format=='STATEMENT') {return '*/ 17 '} }; value_unsafe_for_sbr: # Functions which are unsafe when bin log format = 'STATEMENT' # + we get a warning : "Statement may not be safe to log in statement format" # bigint(21) FOUND_ROWS() | ROW_COUNT() | # varchar(36) CHARACTER SET utf8 UUID() | # bigint(21) unsigned UUID_SHORT() | # varchar(77) CHARACTER SET utf8 CURRENT_USER | CURRENT_USER() | USER() | VERSION() | SYSDATE() | # _data gets replace by LOAD_FILE( ) which is unsafe for SBR. # mleich: I assume this refers to the risk that an input file # might exist on the master but probably not on the slave. # This is irrelevant for the usual RQG test configuration # where master and slave run on the same box. _data ; value_numeric: # We have 'bit' -> bit(1),'bit(4)','bit(64)','tinyint','smallint','mediumint','int','bigint', # 'float','double', # 'decimal' -> decimal(10,0),'decimal(35)' # FIXME 1. We do not need all of these values. # 2. But a smart distribution of values is required so that we do not hit all time # outside of the allowed value ranges value_numeric_int | # Enable the next line in case # Bug#50511 Sometimes wrong handling of user variables containing NULL # is fixed. # value_numeric_double | -1.1 | +1.1 ; value_numeric_int: - _digit | _digit | _bit(1) | _bit(4) | _tinyint | _tinyint_unsigned | _smallint | _smallint_unsigned | _mediumint | _mediumint_unsigned | _int | _int_unsigned | _bigint | _bigint_unsigned | _bigint | _bigint_unsigned | # int(10) CONNECTION_ID() | # Value of the AUTOINCREMENT (per manual only applicable to integer and floating-point types) # column for the last INSERT. LAST_INSERT_ID() ; value_numeric_double: -2.0E-1 | +2.0E-1 | -2.0E+1 | +2.0E+1 | -2.0E-10 | +2.0E-10 | -2.0E+10 | +2.0E+10 | -2.0E-100 | +2.0E-100 | -2.0E+100 | +2.0E+100 ; value_rand: # The ( _digit ) makes thread = 1 tests deterministic. RAND( _digit ) ; value_string: # We have 'char' -> char(1),'char(10)', # 'varchar' - varchar(1),'varchar(10)','varchar(257)', # 'tinytext','text','mediumtext','longtext', # 'enum', 'set' # mleich: I fear values > 16 MB are risky, so I omit them. _char(1) | _char(10) | _varchar(1) | _varchar(10) | _varchar(257) | _text(255) | _text(65535) | _text(16777215) | DATABASE() | _set ; value_string_converted: CONVERT( value_string USING character_set ); character_set: UTF8 | UCS2 | LATIN1 | BINARY ; value_temporal: # We have 'datetime', 'date', 'timestamp', 'time','year' # _datetime - a date+time value in the ISO format 2000-01-01 00:00:00 # _date - a valid date in the range from 2000 to 2010 # _timestamp - a date+time value in the MySQL format 20000101000000 # _time - a time in the range from 00:00:00 to 29:59:59 # _year - a year in the range 2000 to 2010 _datetime | _date | _time | _datetime | _timestamp | _year | NOW() ; any_table: undef_table | nontrans_table | trans_table ; undef_table: # table0 | table0_int | table0_int_autoinc | # table1 | table1_int | table1_int_autoinc | # table10 | table10_int | table10_int_autoinc ; nontrans_table: { 't1_base_myisam_'.$$ } | { 't2_base_myisam_'.$$ } | { 't1_temp_myisam_'.$$ } | { 't2_temp_myisam_'.$$ } | # A VIEW used in SBR mode must not be based on a SELECT which is unsafe in SBR mode. { if ($format=='STATEMENT') { return 'v1_nontrans_safe_for_sbr_'.$$ } else { return 'v1_nontrans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } | { if ($format=='STATEMENT') { return 'v2_nontrans_safe_for_sbr_'.$$ } else { return 'v2_nontrans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } | # table0_myisam | table0_myisam_int | table0_myisam_int_autoinc | # table1_myisam | table1_myisam_int | table1_myisam_int_autoinc | # table10_myisam | table10_myisam_int | table10_myisam_int_autoinc ; trans_table: { 't1_base_innodb_'.$$ } | { 't2_base_innodb_'.$$ } | { 't1_temp_innodb_'.$$ } | { 't2_temp_innodb_'.$$ } | # A VIEW used in SBR mode must not be based on a SELECT which is unsafe in SBR mode. { if ($format=='STATEMENT') { return 'v1_trans_safe_for_sbr_'.$$ } else { return 'v1_trans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } | { if ($format=='STATEMENT') { return 'v2_trans_safe_for_sbr_'.$$ } else { return 'v2_trans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } | # table0_innodb | table0_innodb_int | table0_innodb_int_autoinc | # table1_innodb | table1_innodb_int | table1_innodb_int_autoinc | # table10_innodb | table10_innodb_int | table10_innodb_int_autoinc ; pick_safe_table: # pick_mode | table type to choose | setting # 0 | any any_table /* undef_table nontrans_table trans_table */ # 1 | undef /* any_table */ undef_table /* nontrans_table trans_table */ # 2 | nontrans /* any_table undef_table */ nontrans_table /* trans_table */ # 3 | trans /* any_table undef_table nontrans_table */ trans_table # 4 | nontrans /* any_table undef_table */ nontrans_table /* trans_table */ tmarker_init tmarker_set { return $m0 } any_table { return $m1 } undef_table { return $m2 } nontrans_table { return $m3 } trans_table { return $m4 } ; tmarker_init: { $m0 = ''; $m1 = ''; $m2 = ''; $m3 = ''; $m4 = ''; return undef } ; tmarker_set: { if ($pick_mode==0) {$m1='/*';$m4='*/'} elsif ($pick_mode==1) {$m0='/*';$m1='*/';$m2='/*';$m4='*/'} elsif ($pick_mode==2) {$m0='/*';$m2='*/';$m3='/*';$m4='*/'} elsif ($pick_mode==3) {$m0='/*';$m3='*/'} elsif ($pick_mode==4) {$m0='/*';$m2='*/';$m3='/*';$m4='*/'} ; return undef }; #### Basic constructs which are used at various places delayed: # "DELAYED" is declared to be unsafe whenever the GLOBAL binlog_format is 'statement'. # --> Either # - set GLOBAL binlog_format during query_init, don't switch it later and adjust usage of delayed ? # or # - do not use DELAYED (my choice, mleich) # DELAYED | ; high_priority: | HIGH_PRIORITY ; ignore: # Only 10 % | | | | | | | | | # mleich temporary disabled IGNORE ; ; low_priority: | | | LOW_PRIORITY ; low_priority_delayed_high_priority: # All MyISAM only features. | | low_priority | delayed | high_priority ; low_priority_delayed: | | low_priority | delayed ; plus_minus: + | - ; quick: # Only 10 % | | | | | | | | | QUICK ;