| 1 |
# |
| 2 |
# Specify storage engine to use. Variable $engine is set in wrapper test. |
| 3 |
# |
| 4 |
eval SET @@default_storage_engine = $engine; |
| 5 |
if (`SELECT @@default_storage_engine LIKE 'InnoDB' AND @@version LIKE '%6.%'`) |
| 6 |
{ |
| 7 |
# Need this due to Bug#43447 - Crash when executing SELECT ... LIMIT n FOR UPDATE query |
| 8 |
# Hopefully temporary... |
| 9 |
# Not applicable to 5.1 server or earlier. |
| 10 |
--disable_query_log |
| 11 |
SET GLOBAL optimizer_use_mrr='disable'; |
| 12 |
SET GLOBAL engine_condition_pushdown=off; |
| 13 |
--enable_query_log |
| 14 |
} |
| 15 |
|
| 16 |
if (`SELECT @@default_storage_engine LIKE 'PBXT' AND @@version LIKE '%5.1%'`) |
| 17 |
{ |
| 18 |
--disable_query_log |
| 19 |
SET SESSION binlog_format = 'MIXED'; |
| 20 |
SET GLOBAL binlog_format = 'MIXED'; |
| 21 |
--enable_query_log |
| 22 |
} |
| 23 |
|
| 24 |
# Verify default storage engine. |
| 25 |
SHOW VARIABLES LIKE 'default_storage_engine'; |
| 26 |
|
| 27 |
# Verify default isolation level |
| 28 |
SHOW VARIABLES LIKE 'transaction_isolation'; |
| 29 |
|
| 30 |
# |
| 31 |
# Create table for keeping track of test metadata/statistics (counters etc.). |
| 32 |
# (Need a data structure that will hold across tests, clients, sessions). |
| 33 |
# Expand/modify if needeed, but take care of test files using it. |
| 34 |
# |
| 35 |
# Columns: |
| 36 |
# deadlocks - keeps track of the total number of deadlocks so far. |
| 37 |
# |
| 38 |
# Using default storage engine (see above). |
| 39 |
CREATE TABLE statistics ( |
| 40 |
tx_errors INTEGER NOT NULL |
| 41 |
); |
| 42 |
|
| 43 |
# Initialize statistics table. |
| 44 |
INSERT INTO statistics (tx_errors) VALUES (0); |
| 45 |
|
| 46 |
# |
| 47 |
# Create main test / data table. Some notes: |
| 48 |
# * timestamp is automatically DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
| 49 |
# * BOOLEAN is TINYINT(1) |
| 50 |
# * `is_consistent` means that the sum of int1* and int2* columns in that row |
| 51 |
# is = 0. NOTE: Do not change meaning unless you take care to |
| 52 |
# change test cases that rely on it (e.g. rr_id_900). |
| 53 |
# Set `is_consistent` = 0 if changing a row's sum to non-zero. |
| 54 |
# |
| 55 |
# TODO: Get TID (thread_id) from mysql-stress-test.pl somehow. |
| 56 |
# |
| 57 |
|
| 58 |
CREATE TABLE t1 ( |
| 59 |
`pk` INTEGER AUTO_INCREMENT NOT NULL, |
| 60 |
`id` INTEGER NOT NULL, |
| 61 |
`int1` INTEGER, |
| 62 |
`int1_key` INTEGER, |
| 63 |
`int1_unique` INTEGER, |
| 64 |
`int2` INTEGER, |
| 65 |
`int2_key` INTEGER, |
| 66 |
`int2_unique` INTEGER, |
| 67 |
`for_update` BOOLEAN DEFAULT 0, |
| 68 |
`timestamp` TIMESTAMP, |
| 69 |
`connection_id` INTEGER, |
| 70 |
`thread_id` INTEGER DEFAULT 0, |
| 71 |
`is_uncommitted` BOOLEAN DEFAULT 0, |
| 72 |
`is_consistent` BOOLEAN DEFAULT 0, |
| 73 |
KEY (`id`), |
| 74 |
KEY (`int1_key`), |
| 75 |
KEY (`int2_key`), |
| 76 |
UNIQUE (`int1_unique`), |
| 77 |
UNIQUE (`int2_unique`), |
| 78 |
PRIMARY KEY (`pk`) |
| 79 |
); |
| 80 |
|
| 81 |
# Check that the table was really created with the intended storage engine. |
| 82 |
SHOW CREATE TABLE t1; |
| 83 |
|
| 84 |
## Procedure for inserting the value 1000 into integer fieds, "rows" times. |
| 85 |
|
| 86 |
--delimiter // |
| 87 |
|
| 88 |
eval CREATE PROCEDURE insertRows(rows INT) |
| 89 |
BEGIN |
| 90 |
SET @n = 1; |
| 91 |
REPEAT |
| 92 |
INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`, |
| 93 |
`int2`, `int2_key`, `int2_unique`, |
| 94 |
`for_update`, `connection_id`, `thread_id`, |
| 95 |
`is_uncommitted`, `is_consistent`) |
| 96 |
VALUES (0, 1000, 1000, @n, |
| 97 |
-1000, -1000, -@n, |
| 98 |
0, CONNECTION_ID(), 0, |
| 99 |
0, 1); |
| 100 |
SET @n = @n + 1; |
| 101 |
UNTIL @n > rows |
| 102 |
END REPEAT; |
| 103 |
END; |
| 104 |
// |
| 105 |
|
| 106 |
--delimiter ; |
| 107 |
|
| 108 |
## Insert 1000 rows. |
| 109 |
CALL insertRows(1000); |
| 110 |
|
| 111 |
## Check the sum of all int columns |
| 112 |
SELECT SUM(`int1` + `int1_key` + `int1_unique` |
| 113 |
+ `int2` + `int2_key` + `int2_unique`) |
| 114 |
AS TotalSum |
| 115 |
FROM t1; |