diff options
Diffstat (limited to 'mysql-test/suite/engines/rr_trx/include')
5 files changed, 538 insertions, 0 deletions
diff --git a/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback.inc b/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback.inc new file mode 100644 index 00000000..01bc678c --- /dev/null +++ b/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback.inc @@ -0,0 +1,75 @@ +################################################################################ +# +# Checks if an error has occurred and if so rolls back the entire transaction. +# Only source this file when such behavior is needed. +# +# Since this file needs to be sourced _after_ the statement that we want to check +# for error, any unacceptable errors will have already caused the test to fail. +# If we get this far, we know that the error was a valid one. +# +# Typical usage in testcase: +# ------------------------------------------------------------------- +# --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT +# UPDATE t1 SET `int1` = `int1` - 4 WHERE `pk` < 25 LIMIT 1; +# --source suite/stress_tx_rr/include/check_for_error_rollback.inc +# ------------------------------------------------------------------- +# +# Examples of "valid" error types in transactional testing: +# 1205 - ER_LOCK_WAIT_TIMEOUT +# 1213 - ER_LOCK_DEADLOCK +# 1020 - ER_CHECKREAD (Falcon: "Record has changed since last read") +# +# In some situations duplicate key errors etc. are also valid. +# +# We keep an approximate count of the number of errors / rollbacks. +# We don't distinguish between error types, as this would require extra queries, +# reducing concurrency. +# +# We do an explicit rollback to make sure all engines have identical behavior on +# transactional errors (some engines only roll back the last statement in some +# cases). +# We don't show this in the result file because we don't know when it will +# occur and we don't want diffs because of legitimate ROLLBACKs. If, however +# we want to go back and trace ROLLBACKS of this kind, then we need another +# solution. +# +# NOTE: Use check_for_error_rollback_skip.inc instead if subsequent statements +# depend on the statements executed before calling this script, and handling +# varying test output gets too complicated. +# +################################################################################ + +--disable_query_log + +# (Re-) set the error variable in case it has been set to a different value previously. +# This value may be read by the wrapping test script to check if there really +# was an error or not. +let $error= 0; +if ($mysql_errno) +{ + # Last statement sent to the server resulted in an error (0 means no error). + + # Set error variable, because this is used by wrapping tests to determine whether or not + # to continue with other statements in the same transaction. If set, this indicates that + # the last statement executed before calling this script resulted in an error. + + let $error= $mysql_errno; + + ## Old code for determining error type... + #let $deadlock= `SELECT IF($mysql_errno = 1213, 1, 0)`; + #let $timeout= `SELECT IF($mysql_errno = 1205, 1, 0)`; + #if ($deadlock) { ... } (etc.) + + # Do a full rollback of the current transaction. + ROLLBACK; + + # update statistics + # TODO: Only do this every n times (e.g. n = 10 or 100) to reduce contention. + # An idea is to use some MOD expression to determine this (e.g. mod of timestamp or conn_id). + --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD + UPDATE statistics SET tx_errors = tx_errors + 1; + +} + +--enable_query_log + diff --git a/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback_skip.inc b/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback_skip.inc new file mode 100644 index 00000000..35c4cef6 --- /dev/null +++ b/mysql-test/suite/engines/rr_trx/include/check_for_error_rollback_skip.inc @@ -0,0 +1,76 @@ +################################################################################ +# +# Checks if an error has occurred and if so rolls back the entire transaction. +# Only source this file when such behavior is needed. +# +# Since this file needs to be sourced _after_ the statement that we want to check +# for error, any unacceptable errors will have already caused the test to fail. +# If we get this far, we know that the error was a valid one. +# +# Typical usage in testcase: +# ------------------------------------------------------------------- +# --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT +# UPDATE t1 SET `int1` = `int1` - 4 WHERE `pk` < 25 LIMIT 1; +# --source suite/stress_tx_rr/include/check_for_error_rollback.inc +# ------------------------------------------------------------------- +# +# Examples of "valid" error types in transactional testing: +# 1205 - ER_LOCK_WAIT_TIMEOUT +# 1213 - ER_LOCK_DEADLOCK +# 1020 - ER_CHECKREAD (Falcon: "Record has changed since last read") +# +# In some situations duplicate key errors etc. are also valid. +# +# We keep an approximate count of the number of errors / rollbacks. +# We don't distinguish between error types, as this would require extra queries, +# reducing concurrency. +# +# We do an explicit rollback to make sure all engines have identical behavior on +# transactional errors (some engines only roll back the last statement in some +# cases). +# We don't show this in the result file because we don't know when it will +# occur and we don't want diffs because of legitimate ROLLBACKs. If, however +# we want to go back and trace ROLLBACKS of this kind, then we need another +# solution. +# +# At this time we skip the rest of the test to avoid rsult file diff problems +# in error situations vs. non-error situations in later parts of the test, +# e.g. repeatable read checking (requires some output to be useful). +# +################################################################################ + +--disable_query_log + +# (Re-) set the error variable in case it has been set to a different value previously. +# This value may be read by the wrapping test script to check if there really +# was an error or not. +let $error= 0; +if ($mysql_errno) +{ + # Last statement sent to the server resulted in an error (0 means no error). + + # Set error variable, because this is used by wrapping tests to determine whether or not + # to continue with other statements in the same transaction. If set, this indicates that + # the last statement executed before calling this script resulted in an error. + + let $error= $mysql_errno; + + ## Old code for determining error type... + #let $deadlock= `SELECT IF($mysql_errno = 1213, 1, 0)`; + #let $timeout= `SELECT IF($mysql_errno = 1205, 1, 0)`; + #if ($deadlock) { ... } (etc.) + + # Do a full rollback of the current transaction. + ROLLBACK; + + # update statistics + # TODO: Only do this every n times (e.g. n = 10 or 100) to reduce contention. + # An idea is to use some MOD expression to determine this (e.g. mod of timestamp or conn_id). + --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD + UPDATE statistics SET tx_errors = tx_errors + 1; + + --skip Skip rest of the test due to transactional error (deadlock, timeout, etc.) +} + +--enable_query_log + diff --git a/mysql-test/suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc b/mysql-test/suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc new file mode 100644 index 00000000..e8e21a6b --- /dev/null +++ b/mysql-test/suite/engines/rr_trx/include/check_repeatable_read_all_columns.inc @@ -0,0 +1,223 @@ +################################################################################ +# +# Verifies that queries in a REPEATABLE READ transaction is indeed repeatable. +# Wrong results are shown as a result set based on one or more specially crafted +# queries. Normally these result sets should be empty. +# +# We want to verify that reads (SELECTs) are indeed repeatable during a +# REPEATABLE READ transaction. +# +# Generally, queries which should yield the same results at one moment in time +# should also yield the same results later in the same transaction. In some +# configurations, however, phantom reads are allowed (may e.g. depend on +# settings such as falcon_consistent_read). +# +# The check will fail if rows are changed or missing when comparing later +# queries to earlier ones. +# TODO: Phantom reads. +# Note: There is a separate test looking for `is_uncommitted` = 1. +# +# Assumptions: +# - we are in a REPEATABLE READ transaction with autocommit OFF. +# - queries include all columns of table (t1) (we SELECT columns by name) +# +# Requires/using the following variables: +# $query_count - the number of queries to compare. +# Will also be used to deduce the name of the temp table in +# which the query results should be stored (see +# record_query_all_columns.inc). +# +################################################################################ + +# Show results of next queries. Empty results is OK. Non-empty means failure. +--enable_result_log + +# The mysqltest language is unfortunaltely not very flexible, but we try our +# best to compare query results this way: +# - For each query, compare with previous query +# - this requires that at least 2 queries have been stored +# - Number of queries should be stored as $query_count +# - Results should be stored in temp tables with names ending with the query +# number, and with prefix "tmp". +# - E.g. compare "tmp2" with "tmp1", "tmp3" with "tmp2", "tmp4" with "tmp3" etc. +# - Fail the test once we detect changed or missing or invalid extra rows in +# latter query. +# ????? +# - Problem is that if one of the queries deadlocked or timed out, we may not +# have enough result sets to compare, so output will vary depending on this. +# Still we need the output from these checks to see which rows are missing or +# changed. +# So, if we don't have enough queries we fake "correct output" to make mysqltest +# happy. +# +# Unfortunately, we need to utilize SQL and spend client-server roundtrips +# in order to do some computations that the mysqltest language does not handle. +# We try to use mysqltest variables instead where possible, as this should be +# less expensive in terms of CPU usage and time spenditure. + +# +# First, check that we have at least two query results stored. +# We need at least 2 to be able to compare. +# Some results may not have been stored due to locking errors (see record_query_all_columns.inc), so +# we cannot assume that we always have at least 2 query results stored. +# If less than 2 query results are stored, return to calling test/script. +# +if (`SELECT IF($query_count > 1, 1, 0)`) +{ + + --echo *************************************************************************** + --echo * Checking REPEATABLE READ by comparing result sets from same transaction + --echo *************************************************************************** + + --echo *** Query log disabled. See include files used by test for query details. + --disable_query_log + + let $queryA= 1; + let $queryB= 2; + let $more_queries= $query_count; + + # We start out by comparing the first 2 queries, so the while loop should run + # $query_count - 1 times. (If we have 3 queries, compare 1 with 2, 2 and 3). + --dec $more_queries + + while ($more_queries) + { + # We still have one or more queries that have not been compared to the + # previous query. + # Compare queryB ("current query") with queryA ("previous query") + + #--source suite/stress_tx_rr/include/compare_queries_with_pk.inc + + let $tableA= tmp$queryA; + let $tableB= tmp$queryB; + + --echo *** Comparing query $queryA (A) with query $queryB (B): + + # + # In the following queries, 'SELECT * ...' could have been used instead of + # 'SELECT tmp1.pk AS ...' etc., but the latter makes it easier to compare the first + # result set to the second in test/diff output. + + + + ########################### + # Detect extra rows: + # Allow phantoms in some configurations: + # - InnoDB default settings + # + ########################### + # TODO: Execute a query against tmp1 and tmp2 which selects new rows (rows + # present in tmp2 that are not present in tmp1) that are of the uncommitted + # variety (field `is_uncommitted` = 1). + # E.g. something like: + # SELECT ... + # FROM tmp2 LEFT JOIN tmp1 + # ON tmp1.`pk` = tmp2.`pk` + # WHERE tmp1.`int1` IS NULL + # OR tmp1.`int1_key` IS NULL + # OR tmp1.`int1_unique` IS NULL + # OR tmp1.`int2` IS NULL + # OR tmp1.`int2_key` IS NULL + # OR tmp1.`int2_unique` IS NULL + # AND tmp2.`is_uncommitted` = 1; + + + --echo ########################### + --echo # Detect missing rows: + --echo ########################### + + + eval SELECT $tableA.pk AS 'A.pk', + $tableB.pk AS 'B.pk', + $tableA.id AS 'A.id', + $tableB.id AS 'B.id', + $tableA.`int1` AS 'A.int1', + $tableB.`int1` AS 'B.int1', + $tableA.`int1_key` AS 'A.int1_key', + $tableB.`int1_key` AS 'B.int1_key', + $tableA.`int1_unique` AS 'A.int1_unique', + $tableB.`int1_unique` AS 'B.int1_unique', + $tableA.`int2` AS 'A.int2', + $tableB.`int2` AS 'B.int2', + $tableA.`int2_key` AS 'A.int2_key', + $tableB.`int2_key` AS 'B.int2_key', + $tableA.`int2_unique` AS 'A.int2_unique', + $tableB.`int2_unique` AS 'B.int2_unique', + $tableA.`for_update` AS 'A.for_update', + $tableB.`for_update` AS 'B.for_update', + $tableA.timestamp AS 'A.timestamp', + $tableB.timestamp AS 'B.timestamp', + $tableA.`connection_id` AS 'A.connection_id', + $tableB.`connection_id` AS 'B.connection_id', + $tableA.`thread_id` AS 'A.thread_id', + $tableB.`thread_id` AS 'B.thread_id', + $tableA.`is_uncommitted` AS 'A.is_uncommitted', + $tableB.`is_uncommitted` AS 'B.is_uncommitted', + $tableA.`is_consistent` AS 'A.is_consistent', + $tableB.`is_consistent` AS 'B.is_consistent' + FROM $tableA LEFT JOIN $tableB + ON $tableA.`pk` = $tableB.`pk` + WHERE $tableB.`pk` IS NULL; + + # + # OR $tableB.`int1_key` IS NULL + # OR $tableB.`int1_unique` IS NULL + # OR $tableB.`int2` IS NULL + # OR $tableB.`int2_key` IS NULL + # OR $tableB.`int2_unique` IS NULL; + + --echo + --echo ########################### + --echo # Detect changed rows: + --echo ########################### + + eval SELECT $tableA.pk AS 'A.pk', + $tableB.pk AS 'B.pk', + $tableA.id AS 'A.id', + $tableB.id AS 'B.id', + $tableA.`int1` AS 'A.int1', + $tableB.`int1` AS 'B.int1', + $tableA.`int1_key` AS 'A.int1_key', + $tableB.`int1_key` AS 'B.int1_key', + $tableA.`int1_unique` AS 'A.int1_unique', + $tableB.`int1_unique` AS 'B.int1_unique', + $tableA.`int2` AS 'A.int2', + $tableB.`int2` AS 'B.int2', + $tableA.`int2_key` AS 'A.int2_key', + $tableB.`int2_key` AS 'B.int2_key', + $tableA.`int2_unique` AS 'A.int2_unique', + $tableB.`int2_unique` AS 'B.int2_unique', + $tableA.`for_update` AS 'A.for_update', + $tableB.`for_update` AS 'B.for_update', + $tableA.timestamp AS 'A.timestamp', + $tableB.timestamp AS 'B.timestamp', + $tableA.`connection_id` AS 'A.connection_id', + $tableB.`connection_id` AS 'B.connection_id', + $tableA.`thread_id` AS 'A.thread_id', + $tableB.`thread_id` AS 'B.thread_id', + $tableA.`is_uncommitted` AS 'A.is_uncommitted', + $tableB.`is_uncommitted` AS 'B.is_uncommitted', + $tableA.`is_consistent` AS 'A.is_consistent', + $tableB.`is_consistent` AS 'B.is_consistent' + FROM $tableB INNER JOIN $tableA + ON $tableB.`pk` = $tableA.`pk` + WHERE $tableB.`int1` <> $tableA.`int1` + OR $tableB.`int1_key` <> $tableA.`int1_key` + OR $tableB.`int1_unique` <> $tableA.`int1_unique` + OR $tableB.`int2` <> $tableA.`int2` + OR $tableB.`int2_key` <> $tableA.`int2_key` + OR $tableB.`int2_unique` <> $tableA.`int2_unique`; + + --dec $more_queries + --inc $queryA + --inc $queryB + + } + --enable_query_log +} + + +## Cleanup is skipped because temporary tables and prepared statements will +## be cleaned up automatically by the server when this session ends, and we +## want to have as few client-server roundtrips as possible (thus avoid +## unnecessary SQL statement executions). diff --git a/mysql-test/suite/engines/rr_trx/include/record_query_all_columns.inc b/mysql-test/suite/engines/rr_trx/include/record_query_all_columns.inc new file mode 100644 index 00000000..ff2d47ed --- /dev/null +++ b/mysql-test/suite/engines/rr_trx/include/record_query_all_columns.inc @@ -0,0 +1,57 @@ +################################################################################ +# +# Stores results from a given query in a temporary table. +# +# This table can then be used later in the same session for comparing results in +# various stages of a transaction (see check_repeatable_read.inc). +# +# The table name will be: tmp$query_count +# where $query_count is the value of the counter for the number of stored +# queries in this session. Example: "tmp1" +# +# We increment the counter (session scope) for the number of queries so that +# checker scripts may +# a) know how many queries to compare +# b) determine the name of the temp tables storing each query +# +# Assumptions: +# - we may be in the middle of a transaction with autocommit OFF. +# - queries include all columns of table (t1). This is because we want to +# successfully add indexes to columns such as `pk`, `int1_key`, etc. +# +# Requires the following variables to be set: +# $query - the query to be run, which results will be stored in a temp table. +# +# Modifies the following variables: +# $query_count - the number of queries processed by this script so far in this +# session. +# $tmptable - helper variable containing the name of the temp table. +# +# The pattern is "CREATE TEMPORARY TABLE tmpx SELECT ...". This allows us to +# store query results by using SQL without causing implicit commits. +# +################################################################################ + +# increment the query counter +--inc $query_count + +let $tmptable= tmp$query_count; + +# Execute the query and store results in a new temp table. +# Creating indexes now because we cannot do that later withut causing implicit commit. +# Therefore we assume that columns of these names exist in the result set produced by the queries. +--echo *** Disabling query log (we may deadlock and not do this after all) +--disable_query_log +# Also disable warnings, because we get 'Unsafe to binlog' warnings for this with 'newer' server versions. +--disable_warnings +--echo *** Creating temp table with results from query '$query' unless we deadlock or time out. +--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT +--eval CREATE TEMPORARY TABLE $tmptable (PRIMARY KEY (`pk`), KEY (`int1_key`), KEY (`int2_key`), UNIQUE (`int1_unique`), UNIQUE (`int2_unique`)) $query +--enable_warnings + +# We may not have been able to create temp table due to locking constraints. +# In that case, roll back the statement and skip the rest of the test. +--source suite/stress_tx_rr/include/check_for_error_rollback_skip.inc + +--echo *** Enabling query log +--enable_query_log diff --git a/mysql-test/suite/engines/rr_trx/include/rr_init.test b/mysql-test/suite/engines/rr_trx/include/rr_init.test new file mode 100644 index 00000000..7d08c456 --- /dev/null +++ b/mysql-test/suite/engines/rr_trx/include/rr_init.test @@ -0,0 +1,107 @@ +# +# Specify storage engine to use. Variable $engine is set in wrapper test. +# +eval SET @@default_storage_engine = $engine; +if (`SELECT @@default_storage_engine LIKE 'InnoDB' AND @@version LIKE '%6.%'`) +{ + # Need this due to Bug#43447 - Crash when executing SELECT ... LIMIT n FOR UPDATE query + # Hopefully temporary... + # Not applicable to 5.1 server or earlier. + --disable_query_log + SET GLOBAL optimizer_use_mrr='disable'; + SET GLOBAL engine_condition_pushdown=off; + --enable_query_log +} + +# Verify default storage engine. +SHOW VARIABLES LIKE 'default_storage_engine'; + +# Verify default isolation level +SHOW VARIABLES LIKE 'tx_isolation'; + +# +# Create table for keeping track of test metadata/statistics (counters etc.). +# (Need a data structure that will hold across tests, clients, sessions). +# Expand/modify if needeed, but take care of test files using it. +# +# Columns: +# deadlocks - keeps track of the total number of deadlocks so far. +# +# Using default storage engine (see above). +CREATE TABLE statistics ( + tx_errors INTEGER NOT NULL +); + +# Initialize statistics table. +INSERT INTO statistics (tx_errors) VALUES (0); + +# +# Create main test / data table. Some notes: +# * timestamp is automatically DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +# * BOOLEAN is TINYINT(1) +# * `is_consistent` means that the sum of int1* and int2* columns in that row +# is = 0. NOTE: Do not change meaning unless you take care to +# change test cases that rely on it (e.g. rr_id_900). +# Set `is_consistent` = 0 if changing a row's sum to non-zero. +# +# TODO: Get TID (thread_id) from mysql-stress-test.pl somehow. +# + +CREATE TABLE t1 ( + `pk` INTEGER AUTO_INCREMENT NOT NULL, + `id` INTEGER NOT NULL, + `int1` INTEGER, + `int1_key` INTEGER, + `int1_unique` INTEGER, + `int2` INTEGER, + `int2_key` INTEGER, + `int2_unique` INTEGER, + `for_update` BOOLEAN DEFAULT 0, + `timestamp` TIMESTAMP, + `connection_id` INTEGER, + `thread_id` INTEGER DEFAULT 0, + `is_uncommitted` BOOLEAN DEFAULT 0, + `is_consistent` BOOLEAN DEFAULT 0, + KEY (`id`), + KEY (`int1_key`), + KEY (`int2_key`), + UNIQUE (`int1_unique`), + UNIQUE (`int2_unique`), + PRIMARY KEY (`pk`) +); + +# Check that the table was really created with the intended storage engine. +SHOW CREATE TABLE t1; + +## Procedure for inserting the value 1000 into integer fieds, "rows" times. + +--delimiter // + +eval CREATE PROCEDURE insertRows(rows INT) +BEGIN + SET @n = 1; + REPEAT + INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`, + `int2`, `int2_key`, `int2_unique`, + `for_update`, `connection_id`, `thread_id`, + `is_uncommitted`, `is_consistent`) + VALUES (0, 1000, 1000, @n, + -1000, -1000, -@n, + 0, CONNECTION_ID(), 0, + 0, 1); + SET @n = @n + 1; + UNTIL @n > rows + END REPEAT; +END; +// + +--delimiter ; + +## Insert 1000 rows. +CALL insertRows(1000); + +## Check the sum of all int columns +SELECT SUM(`int1` + `int1_key` + `int1_unique` + + `int2` + `int2_key` + `int2_unique`) + AS TotalSum + FROM t1; |