1
# Copyright (C) 2009-2010 Sun Microsystems, Inc. All rights reserved.
2
# Use is subject to license terms.
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; version 2 of the License.
8
# This program is distributed in the hope that it will be useful, but
9
# WITHOUT ANY WARRANTY; without even the implied warranty of
10
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11
# General Public License for more details.
13
# You should have received a copy of the GNU General Public License
14
# along with this program; if not, write to the Free Software
15
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
20
# Statements which are unsafe when using statement based replication
21
# In case of binlog format
22
# - MIXED we get an automatic switching from statement-based to row-based replication.
23
# Attention: This test does not contain an explicit check if this switching happens.
24
# - STATEMENT we get a warning that the statement is unsafe.
25
# The "/* QUERY_IS_REPLICATION_SAFE */" which gets added to most generated statements
26
# ensures that RQG aborts in case the statement gets a warning about unsafe actions.
27
# ------------------------------------------------------------------------------------------------------------
28
# - DML updates an NDBCLUSTER table
30
# - FOUND_ROWS(), ROW_COUNT(), UUID(), USER(), CURRENT_USER(), LOAD_FILE(), CURRENT_USER, VERSION() ,
31
# SYSDATE(), RAND() are used
32
# --> "value_unsafe_for_sbr"
33
# - LIMIT even if we have a preceding ORDER BY which makes the statement safe
34
# --> "where" --> "unsafe_condition" (no use of ORDER BY)
35
# - 2 or more tables with AUTO_INCREMENT columns are updated.
36
# --> "update","delete"
37
# - any INSERT DELAYED is executed.
38
# --> "low_priority_delayed_high_priority" but this had to be disabled.
39
# - When the body of a view requires row-based replication, the statement creating the view also uses it
40
# — for example, this occurs when the statement creating a view uses the UUID() function.
41
# Observation: When running a statement using a view than the statement will be declared
42
# unsafe in case the SELECT within the VIEW is unsafe.
43
# --> create_view -> where -> optional use of value_unsafe_for_sbr
44
# - Call to a UDF My guess: It is feared that the file might not exist on slave side.
45
# FIXME: NOT IMPLEMENTED
46
# - If a statement is logged by row and the client that executed the statement has any temporary tables,
47
# then logging by row is used for all subsequent statements (except for those accessing temporary tables)
48
# until all temporary tables in use by that client are dropped.
49
# This is true whether or not any temporary tables are actually logged.
50
# Temporary tables cannot be logged using the row-based format; thus, once row-based logging is used,
51
# all subsequent statements using that table are unsafe, and we approximate this condition by treating
52
# all statements made by that client as unsafe until the client no longer holds any temporary tables.
53
# When FOUND_ROWS() or ROW_COUNT() is used.
54
# SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
55
# WHERE id > 100 LIMIT 10;
56
# SELECT FOUND_ROWS();
57
# FOUND_ROWS() or ROW_COUNT() are bigint(21) ;
58
# Without SQL_CALC_FOUND_ROWS within the previous SELECT, FOUND_ROWS() = number of rows found by this SELECT.
59
# --> "value_unsafe_for_sbr", but no SQL_CALC_FOUND_ROWS within any statement
60
# - a statement refers to one or more system variables.
61
# Exception. The following system variables, when used with session scope (only),
62
# do not cause the logging format to switch:
63
# * auto_increment_increment
64
# * auto_increment_offset
65
# * character_set_client
66
# * character_set_connection
67
# * character_set_database
68
# * character_set_server
69
# * collation_connection
70
# * collation_database
72
# * foreign_key_checks
75
# --> "value_numeric_int"
81
# --> "shake_clock" affects timestamp
83
# For information about how replication treats sql_mode, see Section 16.3.1.30, “Replication and Variables”.
84
# When one of the tables involved is a log table in the mysql database.
85
# FIXME: NOT IMPLEMENTED
86
#-----------------------------
87
# When using statement-based replication, the LOAD DATA INFILE statement's CONCURRENT option is not replicated;
88
# that is, LOAD DATA CONCURRENT INFILE is replicated as LOAD DATA INFILE, and LOAD DATA CONCURRENT LOCAL INFILE
89
# is replicated as LOAD DATA LOCAL INFILE. The CONCURRENT option is replicated when using row-based replication.
90
# --> Use of "concurrent_or_empty" in "dml", but there is no explicite check if CONCURRENT is replicated or not.
91
#-------------------------------
92
# If you have databases on the master with character sets that differ from the global character_set_server value, you should
93
# design your CREATE TABLE statements so that tables in those databases do not implicitly rely on the database default character set.
94
# A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.
95
#-----------------------------------
96
# MySQL 5.4.3 and later.
98
# - CREATE DATABASE IF NOT EXISTS
99
# - CREATE TABLE IF NOT EXISTS , this includes CREATE TABLE IF NOT EXISTS ... LIKE
100
# - CREATE EVENT IF NOT EXISTS
101
# statement is replicated, whether or not the object already exists on the master.
102
# However, replication of CREATE TABLE IF NOT EXISTS ... SELECT follows somewhat
103
# different rules; see Section 16.3.1.4, “Replication of CREATE TABLE ... SELECT Statements”, for more information.
105
#-----------------------------------
106
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-differing-tables.html
107
#-----------------------------------
108
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-floatvalues.html
109
#-----------------------------------
110
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-flush.html
111
#-----------------------------------
112
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-slaveerrors.html
113
# FOREIGN KEY, master InnoDB and slave MyISAM
114
# FIXME: NOT TESTED, FOREIGN KEY NOT IMPLEMENTED
115
#-----------------------------------
116
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-max-allowed-packet.html
117
# BLOB/TEXT value too big for max-allowed-packet on master or on slave
119
#-----------------------------------
120
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-timeout.html
121
# Slave: Innodb detects deadlock -> slave_transaction_retries to run the action to replicate ....
122
# mleich: Most probably not doable with current RQG.
123
#-----------------------------------
124
# The same system time zone should be set for both master and slave. If not -> problems with NOW() or FROM_UNIXTIME()
125
# CONVERT_TZ(...,...,@@session.time_zone) is properly replicated ...
126
# FIXME: different time_zones NOT TESTED
127
#-----------------------------------
128
# In situations where transactions mix updates to transactional and nontransactional tables, the order of statements
129
# in the binary log is correct, and all needed statements are written to the binary log even in case of a ROLLBACK.
130
# However, when a second connection updates the nontransactional table before the first connection's transaction is
131
# complete, statements can be logged out of order, because the second connection's update is written immediately after
132
# it is performed, regardless of the state of the transaction being performed by the first connection.
134
# Due to the nontransactional nature of MyISAM tables, it is possible to have a statement that only partially updates
135
# a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating
136
# a key constraint, or if a long update statement is killed after updating some of the rows.
137
# If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do
138
# about it unless the error code is legitimate and execution of the statement results in the same error code on the slave.
140
# When the storage engine type of the slave is nontransactional, transactions on the master that mix updates of transactional
141
# and nontransactional tables should be avoided because they can cause inconsistency of the data between the master's
142
# transactional table and the slave's nontransactional table.
144
# RULES FOR THE CURRENT TEST IN CASE OF BINLOG_FORMAT = STATEMENT:
145
# (independend of the use - modify or just query - of the table)
146
# 1. Do not use transactional and non transactional tables within the same statement.
147
# 2. Have only three types of transactions
148
# - use non transactional tables only
149
# - use transactional tables only
150
# - use for the first phase of the transaction only non transactional tables and
151
# use for the last phase of the transaction only transactional tables
152
# --> "$pick_mode" related stuff
153
# 3. SAVEPOINT A followed by some UPDATE on a non transactional table is unsafe
155
# FROM THE DISCUSSION:
156
# If you want to change the replication format, do so outside the boundaries of a transaction. (SBR?)
157
# --> "*_binlog_format_sequence"
158
#-----------------------------------
159
# http://dev.mysql.com/doc/refman/5.4/en/replication-features-triggers.html !!!!
160
#-----------------------------------
161
# TRUNCATE is treated for purposes of logging and replication as DDL rather than DML ...
163
#-----------------------------------
164
# http://dev.mysql.com/doc/refman/5.4/en/mysqlbinlog-hexdump.html
166
# 00 UNKNOWN_EVENT This event should never be present in the log.
167
# 01 START_EVENT_V3 This indicates the start of a log file written by MySQL 4 or earlier.
168
# X 02 QUERY_EVENT The most common type of events. These contain statements executed on the master.
169
# ? 03 STOP_EVENT Indicates that master has stopped.
170
# X 04 ROTATE_EVENT Written when the master switches to a new log file.
172
# X 05 INTVAR_EVENT Used for AUTO_INCREMENT values or when the LAST_INSERT_ID() function is used in the statement.
173
# --> "value" contains NULL and (nested) LAST_INSERT_ID()
174
# 06 LOAD_EVENT Used for LOAD DATA INFILE in MySQL 3.23.
175
# 07 SLAVE_EVENT Reserved for future use.
176
# 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.
177
# 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.
178
# --> "dml" contains LOAD DATA
179
# 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.
180
# X 0b DELETE_FILE_EVENT Rollback of a LOAD DATA INFILE statement. The temporary file should be deleted on the slave.
181
# --> "dml" contains LOAD DATA
182
# 0c NEW_LOAD_EVENT Used for LOAD DATA INFILE in MySQL 4 and earlier.
183
# X 0d RAND_EVENT Used to send information about random values if the RAND() function is used in the statement.
184
# --> "value_unsafe_for_sbr"
185
# X 0e USER_VAR_EVENT Used to replicate user variables.
186
# --> "dml" containing SET @aux + "values" containg @aux
187
# X 0f FORMAT_DESCRIPTION_EVENT This indicates the start of a log file written by MySQL 5 or later.
189
# X 10 XID_EVENT Event indicating commit of an XA transaction.
190
# X 11 BEGIN_LOAD_QUERY_EVENT Used for LOAD DATA INFILE statements in MySQL 5 and later.
191
# X 12 EXECUTE_LOAD_QUERY_EVENT Used for LOAD DATA INFILE statements in MySQL 5 and later.
192
# --> "dml" contains LOAD DATA
193
# X 13 TABLE_MAP_EVENT Information about a table definition. Used in MySQL 5.1.5 and later.
194
# 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.
195
# 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.
196
# 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.
197
# X 17 WRITE_ROWS_EVENT Row data for a single table that should be created. Used in MySQL 5.1.18 and later.
199
# X 18 UPDATE_ROWS_EVENT Row data for a single table that needs to be updated. Used in MySQL 5.1.18 and later.
201
# X 19 DELETE_ROWS_EVENT Row data for a single table that should be deleted. Used in MySQL 5.1.18 and later.
203
# 1a INCIDENT_EVENT Something out of the ordinary happened. Added in MySQL 5.1.18.
204
# My (mleich) markings:
206
# I most probably already covered (FIXME: Check in hex dump)
207
#------------------------------------------------
208
# The following restriction applies to statement-based replication only, not to row-based replication.
209
# The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated
210
# without the slave knowing the concurrency context on master. Therefore, these functions should not be used to insert
211
# into a master's table because the content on the slave would differ.
212
# (For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).)
213
#------------------------------------------------
215
#################################################
218
# For debugging the grammar use
219
{ return '/*' . $pick_mode . '*/' } /* QUERY_IS_REPLICATION_SAFE */ ;
220
# For faster execution set this grammar element to "empty".
224
binlog_format_sequence |
225
binlog_format_sequence |
226
binlog_format_sequence |
227
binlog_format_sequence |
228
binlog_format_sequence |
229
binlog_format_sequence |
230
binlog_format_sequence |
231
binlog_format_sequence |
232
binlog_format_sequence |
233
binlog_format_sequence |
234
binlog_format_sequence |
235
binlog_format_sequence |
241
# This runs into a server weakness which finally fools the RQG deadlock detection.
242
# So it must be disabled.
245
# We MUST reduce the huge amount of NULL's
246
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 |
247
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 ;
250
# We need to know our current SESSION BINLOG_FORMAT. We do this by simply setting the BINLOG_FORMAT.
251
rand_session_binlog_format ;
254
safety_check SET global_or_session TRANSACTION ISOLATION LEVEL iso_level ;
256
{ if ( $format eq 'STATEMENT' ) { return $prng->arrayElement(['REPEATABLE READ','SERIALIZABLE']) } else { return $prng->arrayElement(['READ UNCOMMITTED','READ COMMITTED','REPEATABLE READ','SERIALIZABLE']) } } ;
259
# There seems to be only a minor impact of GLOBAL on the test. Therefore it should be less likely.
260
SESSION | SESSION | GLOBAL ;
263
safety_check SET SESSION TIMESTAMP = UNIX_TIMESTAMP() plus_minus _digit ;
266
# Cause that the master switches to a new binary log file
267
# RESET MASTER is not useful here because it causes
268
# - master.err: [ERROR] Failed to open log (file '/dev/shm/var_290/log/master-bin.000002', errno 2)
269
# - the RQG test does not terminate in usual way (RQG assumes deadlock)
270
safety_check FLUSH LOGS ;
273
# Omit BEGIN because it is only an alias for START TRANSACTION
281
# In SBR after a "SAVEPOINT A" any statement which modifies a nontransactional table is unsafe.
282
# Therefore we enforce here that future statements within the current transaction use
283
# a transactional table.
284
SAVEPOINT A { $pick_mode=3; return undef} |
285
SAVEPOINT A { $pick_mode=3; return undef} |
286
SAVEPOINT A { $pick_mode=3; return undef} |
287
ROLLBACK TO SAVEPOINT A |
288
RELEASE SAVEPOINT A ;
291
# Attention: Although the name of the grammar item is "implicit_commit", most but not all of the following
292
# statements will do an implicit COMMIT.
294
# 1. Some statements do not COMMIT before execution and only COMMIT after a successful execution.
295
# Due to the randomness of RQG I cannot predict all time if a execution will be successful.
296
# 2. There are some statements which neither COMMIT before or after execution.
297
# But I need a grammar item where I call them. They are partially called here because of
298
# such *technical* reasons.
305
# Experience when running the current test with several sessions:
306
# The content of t1_is_columns_<pid> differs between master and slave.
307
# Bug#29790 information schema returns non-atomic content => replication (binlog) fails
308
# IMHO it is to be expected that INSERT INTO ... SELECT ... FROM information_schema... could lead in SBR mode to
309
# differences between master and slave content because
310
# 1. The current content of the master and the slave rules compared to RBR where only the content of the master rules.
311
# 2. There is some delay till some data modifying activity of a session gets pushed to the slave.
312
# 3. Caused by optimization of binlogging etc. the delay might differ per session.
313
# At least the deactivation of fill_is_copy via $m10,$m11 n case of SBR helped to avoid the content difference.
314
{ if ($format eq 'STATEMENT') { $m10 = '/*' ; $m11 = '*/'} else { $m10 = '' ; $m11 = '' } ; return undef } fill_is_copy |
333
# Bug#50095 Multi statement including CREATE EVENT causes rotten binlog entry
334
# is fixed please enable the following four lines.
345
# Please enable the next line in case
346
# Bug#50760 RENAME TABLE, Slave stops with HA_ERR_END_OF_FILE
360
SET AUTOCOMMIT = ON |
361
# OFF -> OFF or wrong value , no implicit COMMIT
362
SET AUTOCOMMIT = OFF |
364
# Statements that implicitly use or modify tables in the mysql database cause an implicit COMMIT.
375
table_administration |
379
load_index_to_cache |
384
# Bug#51336 Assert in reload_acl_and_cache during RESET QUERY CACHE
385
# is fixed please enable the next line.
388
# LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine
390
# This causes an implicit COMMIT before execution.
391
LOCK TABLE _table WRITE |
392
# This causes an implicit COMMIT.
396
# No implicit COMMIT.
397
FLUSH local_non_local TABLES |
398
FLUSH local_non_local PRIVILEGES |
399
FLUSH local_non_local QUERY CACHE ;
402
# No implicit COMMIT.
405
# KEY CACHE is a MyISAM only feature.
407
# 0. There is no SESSION specific KEY CACHE.
408
# 1. This statement does not COMMIT.
409
# 2. 'key_cache_'.$$ gets created if not already known
410
# 3. A KEY CACHE with size = 0 causes that the KEY CACHE is destroyed but.
411
# Nevertheless the name of this KEY CACHE can be used within the corresponding statements and
412
# we do not get error messages.
413
SET GLOBAL { 'key_cache_'.$$ } .key_buffer_size = 128 * 1024 |
414
SET GLOBAL { 'key_cache_'.$$ } .key_buffer_size = 0 ;
416
# COMMIT only *after* successful execution.
417
CACHE INDEX pick_schema table_name IN { 'key_cache_'.$$ } |
418
CACHE INDEX pick_schema table_name , pick_schema table_name IN { 'key_cache_'.$$ } |
419
# The next statement will fail.
420
CACHE INDEX pick_schema table_name IN cache_not_exists ;
422
# COMMIT before execution.
423
LOAD INDEX INTO CACHE pick_schema table_name |
424
LOAD INDEX INTO CACHE pick_schema table_name , pick_schema table_name |
425
# The next statement will fail.
426
LOAD INDEX INTO CACHE not_exists ;
428
table_administration:
429
# COMMIT before execution.
430
ANALYZE local_non_local TABLE table_items |
431
OPTIMIZE local_non_local TABLE table_items |
432
REPAIR local_non_local TABLE table_items |
433
CHECK TABLE table_items ;
435
# LOCAL is an alias for NO_WRITE_TO_BINLOG. Therfore we check LOCAL only.
438
pick_schema table_name |
439
pick_schema table_name |
440
pick_schema table_name , pick_schema table_name ;
443
CREATE USER user_name |
444
CREATE USER user_name |
445
CREATE USER user_name , user_name ;
447
DROP USER user_name |
448
DROP USER user_name |
449
DROP USER user_name , user_name ;
451
RENAME USER user_name TO user_name |
452
RENAME USER user_name TO user_name |
453
RENAME USER user_name TO user_name , user_name TO user_name ;
455
# COMMIT before execution.
456
SET PASSWORD FOR user_name = PASSWORD(' _letter ');
458
{ 'Luigi_'.$$.'@localhost' } |
459
{ 'Emilio_'.$$.'@localhost' } ;
462
GRANT ALL ON test.* TO user_name |
463
GRANT ALL ON test.* TO user_name |
464
GRANT ALL ON test.* TO user_name , user_name ;
466
REVOKE ALL ON test.* FROM user_name |
467
REVOKE ALL ON test.* FROM user_name |
468
REVOKE ALL ON test.* FROM user_name , user_name ;
471
CREATE SCHEMA IF NOT EXISTS { 'test_'.$$ } CHARACTER SET character_set ;
473
DROP SCHEMA IF EXISTS { 'test_'.$$ } ;
475
# This fails if we have active locked tables or an open transaction which
476
# already modified a table.
477
ALTER SCHEMA { 'test_'.$$ } CHARACTER SET character_set ;
479
# Attention: An open (existing?) temporary table causes that an in case of current
480
# SESSION BINLOG_FORMAT = ROW any SET ... BINLOG_FORMAT fails.
482
# FIXME Move this out of xid.....
483
CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_myisam_'.$$ } LIKE nontrans_table |
484
CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_innodb_'.$$ } LIKE trans_table |
485
CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_myisam_'.$$ } LIKE nontrans_table |
486
CREATE TABLE IF NOT EXISTS pick_schema { 't1_base_innodb_'.$$ } LIKE trans_table |
487
# FIXME Add later the case that base and temporary table have the same names
488
# Please enable the next two lines if
489
# Bug#49132 Replication failure on temporary table + DDL
491
# CREATE TEMPORARY TABLE IF NOT EXISTS pick_schema { 't1_temp_myisam_'.$$ } LIKE nontrans_table |
492
# CREATE TEMPORARY TABLE IF NOT EXISTS pick_schema { 't1_temp_innodb_'.$$ } LIKE trans_table |
493
# This will fail because mysql.user already exists.
494
CREATE TABLE mysql.user ( f1 BIGINT ) ;
496
# We must avoid the generation of statements which are unsafe in SBR.
497
# 1. We get an implicite COMMIT before execution of CREATE ...
498
# 2. In case the table already exists we will get an ugly INSERT ... SELECT .
499
# 3. We pick_mode 1 til 4.
500
# pick_mode | Storage engine type to choose |
502
# 1 | undef | Set pick_mode = 3 (-> t1_*_innodb_*)
503
# 2 | nontrans | t1_*_myisam_*
504
# 3 | trans | t1_*_innodb_*
505
# 4 | nontrans and later trans | SET pick_mode = 2 (-> t1_*_myisam_*)
506
{if ($format eq '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 |
507
{if ($format eq '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 ;
509
# FIXME Move this out of xid.....
510
DROP TABLE IF EXISTS pick_schema { 't1_base_myisam_'.$$ } |
511
DROP TABLE IF EXISTS pick_schema { 't1_base_innodb_'.$$ } |
512
# FIXME Add later the case that base and temporary table have the same names
514
# DROP TEMPORARY TABLE IF EXISTS pick_schema { 't1_temp_myisam_'.$$ } |
515
# DROP TEMPORARY TABLE IF EXISTS pick_schema { 't1_temp_innodb_'.$$ } |
517
# This will fail because already exist_not_exist.
518
DROP TABLE does_not_exist ;
520
ALTER TABLE pick_schema table_name COMMENT ' _letter ' ;
522
TRUNCATE TABLE pick_schema table_name ;
524
{ 't1_base_myisam_'.$$ } |
525
{ 't1_base_innodb_'.$$ } |
526
{ 't1_base_myisam_'.$$ } |
527
{ 't1_base_innodb_'.$$ } |
528
# Please enable the next four lines if
529
# Bug#49132 Replication failure on temporary table + DDL
531
# { 't1_temp_myisam_'.$$ } |
532
# { 't1_temp_innodb_'.$$ } |
533
# { 't1_temp_myisam_'.$$ } |
534
# { 't1_temp_innodb_'.$$ } |
538
CREATE INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } (col_tinyint) |
539
CREATE INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } (col_tinyint) |
540
CREATE INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } (col_tinyint) |
541
CREATE INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } (col_tinyint) |
543
# Please enable the next four lines if
544
# Bug#49132 Replication failure on temporary table + DDL
546
# CREATE INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } (col_tinyint) |
547
# CREATE INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } (col_tinyint) |
548
# CREATE INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } (col_tinyint) |
549
# CREATE INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } (col_tinyint) |
551
CREATE INDEX idx_will_fail ON does_not_exist (f1) ;
553
DROP INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } |
554
DROP INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } |
555
DROP INDEX { 'idx_base_myisam_'.$$ } ON { 't1_base_myisam_'.$$ } |
556
DROP INDEX { 'idx_base_innodb_'.$$ } ON { 't1_base_innodb_'.$$ } |
558
# Please enable the next four lines if
559
# Bug#49132 Replication failure on temporary table + DDL
561
# DROP INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } |
562
# DROP INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } |
563
# DROP INDEX { 'idx_temp_myisam_'.$$ } ON { 't1_temp_myisam_'.$$ } |
564
# DROP INDEX { 'idx_temp_innodb_'.$$ } ON { 't1_temp_innodb_'.$$ } |
566
DROP INDEX idx_will_fail ON does_not_exist ;
569
RENAME TABLE test . { 't1_base_myisam_'.$$ } TO test . { 't2_base_myisam_'.$$ } |
570
RENAME TABLE test . { 't2_base_myisam_'.$$ } TO test . { 't1_base_myisam_'.$$ } |
571
RENAME TABLE test . { 't1_base_innodb_'.$$ } TO test . { 't2_base_innodb_'.$$ } |
572
RENAME TABLE test . { 't2_base_innodb_'.$$ } TO test . { 't1_base_innodb_'.$$ } |
574
RENAME TABLE test . { 't1_base_myisam_'.$$ } TO test . { 't2_base_myisam_'.$$ } , test1 . { 't1_base_myisam_'.$$ } TO test1 . { 't2_base_myisam_'.$$ } |
575
RENAME TABLE test1 . { 't2_base_myisam_'.$$ } TO test1 . { 't1_base_myisam_'.$$ } , test . { 't2_base_myisam_'.$$ } TO test . { 't1_base_myisam_'.$$ } |
577
# This will fail in case we "move" the table between different schemas and there is a trigger on the table.
578
RENAME TABLE pick_schema { 't1_base_myisam_'.$$ } TO pick_schema { 't1_base_myisam_'.$$ } |
580
# Please enable the next four lines if
581
# Bug#49132 Replication failure on temporary table + DDL
583
# RENAME TABLE test . { 't1_temp_myisam_'.$$ } TO test . { 't2_temp_myisam_'.$$ } |
584
# RENAME TABLE test . { 't2_temp_myisam_'.$$ } TO test . { 't1_temp_myisam_'.$$ } |
585
# RENAME TABLE test . { 't1_temp_innodb_'.$$ } TO test . { 't2_temp_innodb_'.$$ } |
586
# RENAME TABLE test . { 't2_temp_innodb_'.$$ } TO test . { 't1_temp_innodb_'.$$ } |
589
RENAME TABLE does_not_exist TO pick_schema { 't1_base_myisam_'.$$ } ;
591
# The server gives a warning in case the current binlog format is STATEMENT and the SELECT
592
# used within the VIEW definition is unsafe in SBR mode. This is IMHO a valueless but
593
# unimportant limitation.
595
CREATE VIEW trans_view |
596
CREATE VIEW nontrans_view ;
598
pick_schema { if ($format eq 'STATEMENT') {return 'v1_trans_safe_for_sbr_'.$$ } else { return 'v1_trans_unsafe_for_sbr_'.$$ } } AS SELECT _field_list FROM trans_table where ;
600
pick_schema { if ($format eq 'STATEMENT') {return 'v1_nontrans_safe_for_sbr_'.$$ } else { return 'v1_nontrans_unsafe_for_sbr_'.$$ } } AS SELECT _field_list FROM nontrans_table where ;
602
DROP VIEW IF EXISTS pick_schema { 'v1_trans_safe_for_sbr_'.$$ } |
603
DROP VIEW IF EXISTS pick_schema { 'v1_trans_unsafe_for_sbr_'.$$ } |
604
DROP VIEW IF EXISTS pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } |
605
DROP VIEW IF EXISTS pick_schema { 'v1_nontrans_unsafe_for_sbr_'.$$ } ;
607
ALTER VIEW trans_view |
608
ALTER VIEW nontrans_view ;
610
RENAME TABLE test . { 'v1_trans_'.$$ } TO test . { 'v2_trans_'.$$ } |
611
RENAME TABLE test . { 'v2_trans_'.$$ } TO test . { 'v1_trans_'.$$ } |
612
RENAME TABLE test . { 'v1_nontrans_'.$$ } TO test . { 'v2_nontrans_'.$$ } |
613
RENAME TABLE test . { 'v2_nontrans_'.$$ } TO test . { 'v1_nontrans_'.$$ } |
615
# This will fail in case the schemas picked differ. Moving a VIEW from one SCHEMA to another is not supported.
616
RENAME TABLE pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } TO pick_schema { 'v1_nontrans_safe_for_sbr_'.$$ } ;
619
{ if ($format eq 'STATEMENT') { $f0 = ''; $f1 = '/*'; $f2 = '*/' } else { $f0 = '/*'; $f1 = '*/'; $f2 = '' } ; return undef } ;
621
# This procedure and function handling is a bit tricky and I am till now not 100% convinced that the solution is very good.
623
# 1. CREATE AND DROP PROCEDURE should be replication safe independend of the current session binlog format and
624
# the tables used within preceding statements of the current transaction.
625
# BTW: CREATE/DROP cause an implicite COMMIT before the inner part of the statement itself gets processed.
626
# 2. In case we call a procedure we must ensure that the activity (DML) of the procedure is replication safe.
627
# In short: There is a dependency on the current session binlog format and
628
# the tables used within preceding statements of the current transaction.
630
# 1. Procedure names contain depending on their DML activity a part (-> $pick_mode) which tells in which
631
# pick_mode of the current session they can be used.
632
# Of course the DML activity of the procedure has to fit to the pick_mode part of its name.
633
# 2. "The base 1." says that we can create any procedure within the current session.
634
# This would require that we store the current pick_mode, switch to the pick_mode to be used for the DML within
635
# the procedure, create the procedure and restore the old session pick_mode.
636
# But in fact we have only to ensure that
637
# - our procedures are proper defined (pick_mode part of name fits to its DML activity)
638
# - most probably the corresponding procedure exists when we call the procedure
639
# Therefore we only try to create a procedure which fits to the current session pick_mode.
640
# The frequent dynamic switching of the session binlog format causes a calculation of pick_mode.
642
# Activate the next line if
643
# Bug#50423 Crash on second call of a procedure dropping a trigger
644
# is fixed. Not: This crash seems to be fixed in mysql-next-mr and mysql-6.0-codebase-bugfixing.
645
# CREATE PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } () BEGIN dml_list ; END ;
646
CREATE PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } () BEGIN proc_stmt ; END ;
648
replace | update | delete ;
650
DROP PROCEDURE pick_schema { 'p1_'.$pick_mode.'_'.$$ } ;
652
# Enable the next line in case
653
# Bug #50624 crash in check_table_access during call procedure
654
# is fixed or you use
655
# mysql-6.0-codebase-bugfixing
657
# CALL pick_schema { 'p1_'.$pick_mode.'_'.$$ } () ;
660
ALTER PROCEDURE { 'p1_'.$pick_mode.'_'.$$ } COMMENT ' _letter ' ;
663
CREATE FUNCTION pick_schema { 'f1_'.$pick_mode.'_'.$$ } () RETURNS TINYINT RETURN ( SELECT MAX( col_tinyint ) FROM pick_schema pick_safe_table where ) ;
665
DROP FUNCTION pick_schema { 'f1_'.$pick_mode.'_'.$$ } ;
666
# Note: We use the function within the grammar item "value".
668
ALTER FUNCTION { 'f1_'.$pick_mode.'_'.$$ } COMMENT ' _letter ' ;
670
# 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
671
# be used for deciding if we want to execute the trigger or not.
672
# Therefore "$pick_mode" might be removed in future.
674
# 1. pick_safe_table must point to a base table
675
# 2. trigger and basetable must reside within the same schema
676
# If not we get "ERROR HY000: Trigger in wrong schema".
679
# Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT.
680
# Reason for unsafeness: Statement updates two AUTO_INCREMENT columns. This is unsafe because the generated value cannot be predicted by slave.
681
# in the following situation:
683
# - DELETE causes the execution of a trigger which inserts per one statement two rows into a table.
684
# The table where the insert should happen contains an autoincrement primary key but there is no
685
# explicite value for this column within the insert.
686
# Thinkable solutions:
687
# a) Define a stupid trigger which does not modify tables which contain an AUTOINCREMENT column.
688
# b) Define a sophisticated trigger which fits to the situation when the trigger gets used.
689
# This is not so easy.
690
# For example in case:
691
# - the TRIGGER contains a "if @@session.binlog_format = 'ROW' ..." and
692
# - current SESSION BINLOG_FORMAT is 'STATEMENT'
693
# than we get the warning
694
# Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT.
695
# Reason for unsafeness: Statement uses a system variable whose value may differ on slave.
696
# c) Ignoring this problem does not work because in most cases the warning about unsafe statement
698
# Let's try a) first.
701
CREATE TRIGGER pick_schema { 'tr1_'.$pick_mode.'_'.$$ } trigger_time trigger_event ON pick_schema pick_safe_table FOR EACH ROW BEGIN trigger_action ; END ;
707
# insert | replace | delete | update | CALL pick_schema { 'p1_'.$pick_mode.'_'.$$ } () ;
710
DROP TRIGGER IF EXISTS pick_schema { 'tr1_'.$pick_mode.'_'.$$ };
712
# I am unsure if "$pick_mode" makes here sense. Therefore this might be removed in future.
714
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 ;
716
ON COMPLETION not_or_empty PRESERVE ;
718
DROP EVENT IF EXISTS pick_schema { 'e1_'.$pick_mode.'_'.$$ } ;
723
ALTER EVENT pick_schema { 'e1_'.$pick_mode.'_'.$$ } ON SCHEDULE EVERY 10 SECOND STARTS NOW() ENDS NOW() + INTERVAL 21 SECOND completion_handling DO insert ;
725
# Some INFORMATION_SCHEMA related tests
726
#--------------------------------------
727
# Please note the following:
728
# - There is no drop table grammar item.
729
# - The copies of the current information_schema tables do contain only a subset of columns.
730
# All columns where I guessed that they are probably unsafe in replication are omitted.
731
# - The tests around information_schema are intentionally simpler than other tests.
732
# - Bug#29790 information schema returns non-atomic content => replication (binlog) fails
734
CREATE TABLE IF NOT EXISTS test . { 't1_is_schemata_'.$$ } AS schemata_part WHERE 1 = 0 |
735
# Experience: The value of tables.AUTO_INCREMENT can differ between master and slave.
736
CREATE TABLE IF NOT EXISTS test . { 't1_is_tables_'.$$ } AS tables_part WHERE 1 = 0 |
737
CREATE TABLE IF NOT EXISTS test . { 't1_is_columns_'.$$ } AS columns_part WHERE 1 = 0 |
738
CREATE TABLE IF NOT EXISTS test . { 't1_is_routines_'.$$ } AS routines_part WHERE 1 = 0 ;
740
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 |
741
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 |
742
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 |
743
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 ;
745
SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME FROM information_schema.schemata ;
747
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,TABLE_COLLATION,TABLE_COMMENT FROM information_schema.tables ;
749
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 ;
751
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 ;
753
# Guarantee that the transaction has ended before we switch the binlog format
754
binlog_format_sequence:
755
COMMIT ; safety_check binlog_format_set ; dml_list ; safety_check xid_event ;
758
safety_check dml nontrans_trans_shift ; dml_list ;
760
nontrans_trans_shift:
761
# This is needed for the generation of the following scenario.
762
# m statements of an transaction use non transactional tables followed by
763
# n statements which use transactional tables.
764
{ if ( ($prng->int(1,4) == 4) && ($pick_mode == 4) ) { $pick_mode = 3 } ; return undef } ;
767
# 1. SESSION BINLOG_FORMAT --> How the actions of our current session will be bin logged.
768
# 2. GLOBAL BINLOG_FORMAT --> How actions with DELAYED will be bin logged.
769
# --> Initial SESSION BINLOG_FORMAT of session started in future.
770
# This means any SET GLOBAL BINLOG_FORMAT ... executed by any session has no impact on any
771
# already existing session (except 2.).
774
# - are running in the moment with BINLOG_FORMAT = ROW and
775
# - have an open (existing?) temporary table
776
# any SET SESSION/GLOBAL gets
777
# Query: ... SET SESSION BINLOG_FORMAT = ROW failed:
778
# 1559 Cannot switch out of the row-based binary log format when the session has open temporary tables
779
# Although this means we do not get the intended BINLOG_FORMAT there will be no additional
780
# problems like we run unsafe statements etc. Our fortune is that we are already running
781
# with binary log format row which is "compatible" with any pick_mode.
782
rand_global_binlog_format |
783
rand_session_binlog_format |
784
rand_session_binlog_format |
785
rand_session_binlog_format ;
786
rand_global_binlog_format:
787
SET GLOBAL BINLOG_FORMAT = STATEMENT |
788
SET GLOBAL BINLOG_FORMAT = MIXED |
789
SET GLOBAL BINLOG_FORMAT = ROW ;
790
rand_session_binlog_format:
791
SET SESSION BINLOG_FORMAT = { $format = 'STATEMENT' ; $pick_mode = $prng->int(1,4) ; return $format } vmarker_set |
792
SET SESSION BINLOG_FORMAT = { $format = 'MIXED' ; $pick_mode = 0 ; return $format } vmarker_set |
793
SET SESSION BINLOG_FORMAT = { $format = 'ROW' ; $pick_mode = 0 ; return $format } vmarker_set ;
796
# Enable the next line if
797
# Bug#49628 corrupt table after legal SQL, LONGTEXT column
799
# generate_outfile ; safety_check LOAD DATA concurrent_or_empty INFILE _tmpnam REPLACE INTO TABLE pick_schema pick_safe_table |
805
# LOAD DATA INFILE ... is not supported in prepared statement mode.
806
PREPARE st1 FROM " update " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 |
807
PREPARE st1 FROM " delete " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 |
808
PREPARE st1 FROM " insert " ; safety_check EXECUTE st1 ; DEALLOCATE PREPARE st1 |
809
# We need the next statement for other statements which should use a user variable.
811
# We need the next statements for other statements which should be affected by switching the database.
812
USE `test` | USE `test1` |
817
SELECT * FROM pick_schema pick_safe_table ORDER BY _field INTO OUTFILE _tmpnam ;
827
# Delete in one table, search in one table
828
# Unsafe in statement based replication except we add ORDER BY
829
DELETE low_priority quick ignore FROM pick_schema pick_safe_table where |
830
# Delete in two tables, search in two tables
831
# Note: The next grammar line leads unfortunately to frequent failing statements (Unknown table A or B).
832
# The reason is that in case both tables are located in different SCHEMA's than the
833
# the schema_name must be written before the table alias.
834
# Example: DELETE test.A, test1.B FROM test.t1 AS A NATURAL JOIN test1.t7 AS B ....
835
# DELETE low_priority quick ignore A , B FROM pick_schema pick_safe_table AS A join where |
836
DELETE low_priority quick ignore A , B FROM pick_safe_table AS A NATURAL JOIN pick_safe_table B where |
837
DELETE low_priority quick ignore test1.A , test.B FROM test1 . pick_safe_table AS A NATURAL JOIN test . pick_safe_table B where ;
840
# 1. Do not place a where condition here.
841
# 2. join is also use when modifying two tables in one statement.
842
# Therefore we must use "pick_safe_table" here.
843
NATURAL JOIN pick_schema pick_safe_table B ;
845
correlated | non_correlated ;
847
AND A. _field[invariant] IN ( SELECT _field[invariant] FROM pick_schema pick_safe_table AS B ;
849
subquery_part1 WHERE B.col_tinyint = A.col_tinyint ) ;
853
# Note about "AND ( _field[invariant] IS NULL OR _field[invariant] <> value_unsafe_for_sbr )"
854
# 1. This statement piece is unsafe (we also get a warning) when using SESSION BINLOG_FORMAT = STATEMENT.
855
# 2. We add this piece whenever SESSION BINLOG_FORMAT <> STATEMENT.
856
# 3. It should be very unlikely that it gives FALSE.
857
WHERE col_tinyint BETWEEN _tinyint[invariant] AND _tinyint[invariant] + 2 { return $f0 . $f1 } unsafe_condition { return $f2 } ;
859
AND ( _field[invariant] IS NULL OR _field[invariant] <> value_unsafe_for_sbr ) ;
860
# FIXME: Syntax error in multi table delete LIMIT 2 ;
863
# Insert into one table, search in no other table
864
INSERT low_priority_delayed_high_priority ignore INTO pick_schema pick_safe_table ( _field , col_tinyint ) VALUES values_list on_duplicate_key_update |
865
# Insert into one table, search in >= 1 tables
866
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 ;
869
( value , _tinyint ) |
870
( value , _tinyint ) , ( value , _tinyint ) ;
872
on_duplicate_key_update:
875
# Enable the next line in case
876
# Bug#50619 assert in handler::update_auto_increment
878
# ON DUPLICATE KEY UPDATE _field = value ;
879
ON DUPLICATE KEY UPDATE _field = ABS( value ) ;
882
pick_schema pick_safe_table |
883
( SELECT _field_list[invariant] FROM pick_schema pick_safe_table ) ;
886
where | where subquery | join where | where union where ;
889
UNION SELECT _field_list[invariant] FROM table_in_select AS B ;
892
# HIGH_PRIORITY and on_duplicate_key_update are not allowed
893
REPLACE low_priority_delayed INTO pick_schema pick_safe_table ( _field , col_tinyint ) VALUES values_list |
894
REPLACE low_priority_delayed INTO pick_schema pick_safe_table ( _field_list[invariant] ) SELECT _field_list[invariant] FROM table_in_select AS A addition ;
897
# mleich: Search within another table etc. should be already sufficient covered by "delete" and "insert".
899
UPDATE ignore pick_schema pick_safe_table SET _field = value where |
901
UPDATE ignore pick_schema pick_safe_table AS A join SET A. _field = value , B. _field = value where ;
904
# SELECT does not get replicated, but we want its sideeffects on the transaction.
905
SELECT col_tinyint, _field FROM pick_safe_table where FOR UPDATE;
909
value_string_converted |
913
# Enable the next line in case
914
# Bug#50511 Sometimes wrong handling of user variables containing NULL
917
pick_schema { 'f1_'.$pick_mode.'_'.$$ } () |
918
{ if ($format eq 'STATEMENT') {return '/*'} } value_unsafe_for_sbr { if ($format eq 'STATEMENT') {return '*/ 17 '} };
920
value_unsafe_for_sbr:
921
# Functions which are unsafe when bin log format = 'STATEMENT'
922
# + we get a warning : "Statement may not be safe to log in statement format"
926
# varchar(36) CHARACTER SET utf8
928
# bigint(21) unsigned
930
# varchar(77) CHARACTER SET utf8
936
# The ( _digit ) makes thread = 1 tests deterministic.
938
# _data gets replace by LOAD_FILE( <some path> ) which is unsafe for SBR.
939
# mleich: I assume this refers to the risk that an input file
940
# might exist on the master but probably not on the slave.
941
# This is irrelevant for the usual RQG test configuration
942
# where master and slave run on the same box.
946
# We have 'bit' -> bit(1),'bit(4)','bit(64)','tinyint','smallint','mediumint','int','bigint',
948
# 'decimal' -> decimal(10,0),'decimal(35)'
949
# FIXME 1. We do not need all of these values.
950
# 2. But a smart distribution of values is required so that we do not hit all time
951
# outside of the allowed value ranges
953
value_numeric_double |
958
_tinyint | _tinyint_unsigned |
959
_smallint | _smallint_unsigned |
960
_mediumint | _mediumint_unsigned |
961
_int | _int_unsigned |
962
_bigint | _bigint_unsigned |
963
_bigint | _bigint_unsigned |
966
# Value of the AUTOINCREMENT (per manual only applicable to integer and floating-point types)
967
# column for the last INSERT.
969
value_numeric_double:
972
-2.0E-10 | +2.0E-10 |
973
-2.0E+10 | +2.0E+10 |
974
-2.0E-100 | +2.0E-100 |
975
-2.0E+100 | +2.0E+100 ;
978
# We have 'char' -> char(1),'char(10)',
979
# 'varchar' - varchar(1),'varchar(10)','varchar(257)',
980
# 'tinytext','text','mediumtext','longtext',
982
# mleich: I fear values > 16 MB are risky, so I omit them.
983
_char(1) | _char(10) |
984
_varchar(1) | _varchar(10) | _varchar(257) |
985
_text(255) | _text(65535) | _text(16777215) |
989
value_string_converted:
990
CONVERT( value_string USING character_set );
993
UTF8 | UCS2 | LATIN1 | BINARY ;
996
# We have 'datetime', 'date', 'timestamp', 'time','year'
997
# _datetime - a date+time value in the ISO format 2000-01-01 00:00:00
998
# _date - a valid date in the range from 2000 to 2010
999
# _timestamp - a date+time value in the MySQL format 20000101000000
1000
# _time - a time in the range from 00:00:00 to 29:59:59
1001
# _year - a year in the range 2000 to 2010
1002
_datetime | _date | _time | _datetime | _timestamp | _year |
1013
table0_int_autoinc |
1016
table1_int_autoinc |
1019
table10_int_autoinc ;
1022
{ 't1_base_myisam_'.$$ } |
1023
{ 't2_base_myisam_'.$$ } |
1024
{ 't1_temp_myisam_'.$$ } |
1025
{ 't2_temp_myisam_'.$$ } |
1026
# A VIEW used in SBR mode must not be based on a SELECT which is unsafe in SBR mode.
1027
{ if ($format eq 'STATEMENT') { return 'v1_nontrans_safe_for_sbr_'.$$ } else { return 'v1_nontrans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } |
1028
{ if ($format eq 'STATEMENT') { return 'v2_nontrans_safe_for_sbr_'.$$ } else { return 'v2_nontrans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } |
1031
table0_myisam_int_autoinc |
1034
table1_myisam_int_autoinc |
1036
table10_myisam_int |
1037
table10_myisam_int_autoinc ;
1040
{ 't1_base_innodb_'.$$ } |
1041
{ 't2_base_innodb_'.$$ } |
1042
{ 't1_temp_innodb_'.$$ } |
1043
{ 't2_temp_innodb_'.$$ } |
1044
# A VIEW used in SBR mode must not be based on a SELECT which is unsafe in SBR mode.
1045
{ if ($format eq 'STATEMENT') { return 'v1_trans_safe_for_sbr_'.$$ } else { return 'v1_trans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } |
1046
{ if ($format eq 'STATEMENT') { return 'v2_trans_safe_for_sbr_'.$$ } else { return 'v2_trans_'.$prng->arrayElement(['safe_for_sbr_','unsafe_for_sbr_']).$$ } } |
1049
table0_innodb_int_autoinc |
1052
table1_innodb_int_autoinc |
1054
table10_innodb_int |
1055
table10_innodb_int_autoinc ;
1058
# pick_mode | table type to choose | setting
1059
# 0 | any any_table /* undef_table nontrans_table trans_table */
1060
# 1 | undef /* any_table */ undef_table /* nontrans_table trans_table */
1061
# 2 | nontrans /* any_table undef_table */ nontrans_table /* trans_table */
1062
# 3 | trans /* any_table undef_table nontrans_table */ trans_table
1063
# 4 | nontrans /* any_table undef_table */ nontrans_table /* trans_table */
1064
tmarker_init tmarker_set { return $m0 } any_table { return $m1 } undef_table { return $m2 } nontrans_table { return $m3 } trans_table { return $m4 } ;
1067
{ $m0 = ''; $m1 = ''; $m2 = ''; $m3 = ''; $m4 = ''; return undef } ;
1070
{ 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 };
1074
#### Basic constructs which are used at various places
1077
# "DELAYED" is declared to be unsafe whenever the GLOBAL binlog_format is 'statement'.
1079
# - set GLOBAL binlog_format during query_init, don't switch it later and adjust usage of delayed ?
1081
# - do not use DELAYED (my choice, mleich)
1092
# mleich temporary disabled IGNORE ;
1099
low_priority_delayed_high_priority:
1100
# All MyISAM only features.
1106
low_priority_delayed:
added
removed
tests/randgen/conf/replication/replication-ddl_sql.yy
