mirror of
https://github.com/MariaDB/server.git
synced 2025-07-08 04:18:14 +02:00
228b7e4db5
This imports and adapts a number of MySQL 5.7 test cases that are applicable to MariaDB. Some tests for old bug fixes are not that relevant because the code has been refactored since then (especially starting with MariaDB Server 10.6), and the tests would not reproduce the original bug if the fix was reverted. In the test innodb_fts.opt, there are many duplicate MATCH ranks, which would make the results nondeterministic. The test was stabilized by changing some LIMIT clauses or by adding sorted_result in those cases where the purpose of a test was to show that no sorting took place in the server. In the test innodb_fts.phrase, MySQL 5.7 would generate FTS_DOC_ID that are 1 larger than in MariaDB. In innodb_fts.index_table the difference is 2. This is because in MariaDB, fts_get_next_doc_id() post-increments cache->next_doc_id, while MySQL 5.7 pre-increments it. Reviewed by: Thirunarayanan Balathandayuthapani
130 lines
4.7 KiB
Text
130 lines
4.7 KiB
Text
SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
|
|
SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;
|
|
SET @saved_include_delete_marked = @@GLOBAL.innodb_stats_include_delete_marked;
|
|
SET GLOBAL innodb_stats_include_delete_marked = ON;
|
|
SET @saved_traditional = @@GLOBAL.innodb_stats_traditional;
|
|
SET GLOBAL innodb_stats_traditional=false;
|
|
SET @saved_modified_counter = @@GLOBAL.innodb_stats_modified_counter;
|
|
SET GLOBAL innodb_stats_modified_counter=1;
|
|
CREATE TABLE t1 (id SERIAL, val INT UNSIGNED NOT NULL, KEY(val))
|
|
ENGINE=INNODB STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
|
|
CREATE TABLE t2 LIKE t1;
|
|
INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
|
|
SET STATEMENT use_stat_tables=never FOR
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
connect con1, localhost, root,,;
|
|
START TRANSACTION;
|
|
DELETE FROM t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
connection default;
|
|
# With innodb_stats_include_delete_marked=ON,
|
|
# DELETE must not affect statistics before COMMIT.
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 1 Using index
|
|
connection con1;
|
|
COUNT(*)
|
|
0
|
|
ROLLBACK;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
16
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 16 Using index
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
0
|
|
connection default;
|
|
BEGIN;
|
|
INSERT INTO t2 (val) SELECT 4 FROM seq_1_to_16;
|
|
# The INSERT will show up before COMMIT.
|
|
EXPLAIN SELECT * FROM t2 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref val val 4 const 16 Using index
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
16
|
|
# The ROLLBACK of the INSERT must affect the statistics.
|
|
ROLLBACK;
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
0
|
|
connection con1;
|
|
EXPLAIN SELECT * FROM t2 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref val val 4 const 1 Using index
|
|
InnoDB 0 transactions not purged
|
|
# After COMMIT and purge, the DELETE must show up.
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 1 Using index
|
|
SET GLOBAL innodb_stats_include_delete_marked = OFF;
|
|
BEGIN;
|
|
INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 16 Using index
|
|
ROLLBACK;
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 1 Using index
|
|
BEGIN;
|
|
INSERT INTO t1 (val) SELECT 4 FROM seq_1_to_16;
|
|
COMMIT;
|
|
EXPLAIN SELECT * FROM t1 WHERE val=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref val val 4 const 16 Using index
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
0
|
|
# With innodb_stats_include_delete_marked=OFF,
|
|
# DELETE must affect statistics even before COMMIT.
|
|
# However, if there was a WHERE condition,
|
|
# ha_innobase::records_in_range() would count the delete-marked records.
|
|
EXPLAIN SELECT * FROM t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL val 4 NULL 1 Using index
|
|
ROLLBACK;
|
|
EXPLAIN SELECT * FROM t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL val 4 NULL 16 Using index
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
16
|
|
disconnect con1;
|
|
connection default;
|
|
DROP TABLE t1,t2;
|
|
SET GLOBAL innodb_stats_include_delete_marked = @saved_include_delete_marked;
|
|
SET GLOBAL innodb_stats_traditional = @saved_traditional;
|
|
SET GLOBAL innodb_stats_modified_counter = @saved_modified_counter;
|
|
SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
|
|
CREATE TABLE bug12429573 (i INTEGER PRIMARY KEY, j INTEGER, KEY(j))
|
|
ENGINE=INNODB STATS_PERSISTENT=1;
|
|
SET STATEMENT use_stat_tables=never FOR
|
|
ANALYZE TABLE bug12429573;
|
|
Table Op Msg_type Msg_text
|
|
test.bug12429573 analyze status OK
|
|
SELECT last_update INTO @last FROM mysql.innodb_table_stats
|
|
WHERE table_name = 'bug12429573';
|
|
SELECT * FROM mysql.innodb_index_stats
|
|
WHERE table_name = 'bug12429573' AND last_update!=@last;
|
|
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
|
|
SET STATEMENT use_stat_tables=never FOR
|
|
ANALYZE TABLE bug12429573;
|
|
Table Op Msg_type Msg_text
|
|
test.bug12429573 analyze status OK
|
|
SELECT * FROM mysql.innodb_table_stats
|
|
WHERE table_name = 'bug12429573' AND last_update=@last;
|
|
database_name table_name last_update n_rows clustered_index_size sum_of_other_index_sizes
|
|
SELECT * FROM mysql.innodb_index_stats
|
|
WHERE table_name = 'bug12429573' AND last_update=@last;
|
|
database_name table_name index_name last_update stat_name stat_value sample_size stat_description
|
|
DROP TABLE bug12429573;
|