1 ################################################################################
5 # INSERT and commit new rows, using the constants "40" for most values.
6 # For each new transaction, the constant is increased by 1.
8 # This test runs a number of consecutive transactions to generate high
12 # - multi-statemement insert, inserting first positive then negative number (0-sum).
15 # - insert multiple rows using a single statement.
18 # - INSERT IGNORE using both known duplicate values and non-duplicates.
20 # Net effect: 6 more rows
22 # In this test we need some kind of valid unique integer value for the columns
23 # with unique indexes.
26 # - Set unique value as 0 and rollback if ERR_DUP_KEY (see
27 # check_error_rollback.inc), then make sure to UPDATE where unique value is 0
29 # - OR: insert NULL (requires special handling when calculating row sums in
31 # - OR: skip unique indexes entirely (except `pk`) (remove from t1 in init).
33 # Using CONNECTION_ID (swithcing sign and doing +/- 3) as unique value, meaning
34 # that some of the INSERTs will fail with duplicate key until this is high
35 # enough (should not take long with a relatively high number of threads and some
36 # duration, given that the number of initial rows is relatively low, ~1000).
37 # Let's just say this is a warm-up period.
39 # Alternatively, add some random integer to the value or use UNIX_TIMESTAMP()
40 # (the latter requires that some care is taken in subsequent updates etc. For
41 # example, simply doubling the value will cause overflow/truncation).
43 # No need to ROLLBACK if all statements in a transaction by themselves are
47 ################################################################################
51 --echo *** multi-statemement insert, inserting first positive then negative number:
54 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY
55 INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`,
56 `int2`, `int2_key`, `int2_unique`,
57 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
58 VALUES (40, 40, 40, CONNECTION_ID(),
59 -40, -40, -CONNECTION_ID(),
60 0, CONNECTION_ID(), 0, 0, 1);
63 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY
64 INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`,
65 `int2`, `int2_key`, `int2_unique`,
66 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
67 VALUES (-40, -40, -40, -CONNECTION_ID(),
68 40, 40, CONNECTION_ID(),
69 0, CONNECTION_ID(), 0, 0, 1);
75 --echo *** insert multiple rows using a single statement:
78 # First row is by itself consistent (sum = 0). Row 3 zero-sums row 2, so the
79 # statement itself is consistent.
80 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY
81 INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`,
82 `int2`, `int2_key`, `int2_unique`,
83 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
84 VALUES (41, 41, 41, CONNECTION_ID()+1,
85 -41, -41, -(CONNECTION_ID()+1),
86 0, CONNECTION_ID(), 0, 0, 1),
87 (41, 41, 41, CONNECTION_ID()+2,
88 41, 41, CONNECTION_ID()+2,
89 0, CONNECTION_ID(), 0, 0, 0),
90 (41, -41, -41, -(CONNECTION_ID()+2),
91 -41, -41, -(CONNECTION_ID()+2),
92 0, CONNECTION_ID(), 0, 0, 0);
98 --echo *** INSERT IGNORE using both known duplicate values and non-duplicates:
101 # This MAY be discarded (duplicate entry in UNIQUE index) - should succeed if CONNECTION_ID is high enough (int*_unique).
102 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY
103 INSERT IGNORE INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`,
104 `int2`, `int2_key`, `int2_unique`,
105 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
106 VALUES (42, 42, 42, CONNECTION_ID()+3,
107 -42, -42, -(CONNECTION_ID()+3),
108 0, CONNECTION_ID(), 0, 0, 1);
110 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT
111 # This WILL (SHOULD) be discarded (duplicate entry as primary key).
112 # pk's that are 1000 or less AND divisible by 5 should all be present (i.e. never deleted), so we pick pk 5.
113 # Note that we insert an inconsistent row, so it will show up as a sum anomaly if it succeeds.
114 INSERT IGNORE INTO t1 (`pk`, `id`, `int1`, `int1_key`, `int1_unique`,
115 `int2`, `int2_key`, `int2_unique`,
116 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
117 VALUES (5, 43, 42, 42, CONNECTION_ID(),
118 -42, -42, CONNECTION_ID(),
119 0, CONNECTION_ID(), 0, 0, 0);
121 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT
122 # This MAY be discarded (duplicate entry in UNIQUE index).
123 INSERT IGNORE INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`,
124 `int2`, `int2_key`, `int2_unique`,
125 `for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`)
126 VALUES (44, 42, 42, (CONNECTION_ID() + 1000) MOD 5000,
127 -42, -42, -((CONNECTION_ID() + 1000) MOD 5000),
128 0, CONNECTION_ID(), 0, 0, 1);
130 --source suite/engines/rr_trx/include/check_for_error_rollback.inc