[MDEV-13625] Merge InnoDB test cases from MySQL 5.6 Created: 2017-08-23  Updated: 2023-10-31  Resolved: 2023-10-31

Status: Closed
Project: MariaDB Server
Component/s: Tests
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Won't Fix Votes: 0
Labels: compat56

Issue Links:
Blocks
blocks MDEV-13626 Merge InnoDB test cases from MySQL 5.7 Closed
is blocked by MDEV-22379 Cherry-pick mysql-5.6:mysql-test/suit... Open
PartOf
includes MDEV-13647 Mysql Bug#11815557 60269: reject atte... Closed
includes MDEV-13668 InnoDB unnecessarily rebuilds table w... Closed
includes MDEV-18618 Merge innodb_zip.8k, innodb_zip.4k, i... Confirmed
is part of MDEV-4784 merge test cases from 5.6 Stalled
Relates
relates to MDEV-13654 Various crashes due to DB_TRX_ID mism... Closed
relates to MDEV-13671 InnoDB should use case-insensitive co... Closed
relates to MDEV-13680 InnoDB may crash when btr_page_alloc(... Closed
relates to MDEV-13795 ALTER TABLE…DROP PRIMARY KEY, ADD PRI... Closed
relates to MDEV-20094 MariaDB Server 10.2 grows .ibd files ... Stalled
relates to MDEV-13359 Enable online ALTER TABLE for compres... Closed
relates to MDEV-13613 Dropping column from table that is pa... Closed
relates to MDEV-13640 ALTER TABLE CHANGE and ADD INDEX on a... Closed
relates to MDEV-13693 innodb.innodb-index-online-fk fails w... Closed
relates to MDEV-14140 IMPORT TABLESPACE must not go beyond ... Closed

 Description   

Until now, the MariaDB process for merging changes from MySQL 5.6 is ignoring additions to tests (new test cases or test files), even though Oracle has been publishing tests for other than security bug fixes for quite some time now.

Several InnoDB tests are missing as a result of this. I have added some of them. Especially some tests for ALTER TABLE…ALGORITHM=INPLACE are missing.

To make the task more challenging, some tests may have been renamed or refactored in MySQL or MariaDB. For example, the innodb_zip suite was refactored from tests in the InnoDB suite.

Also, MariaDB supports slightly different semantics and SQL syntax than MySQL. So, some tests will necessarily be different or not applicable.

That said, we must ensure that all applicable test cases from the following MySQL 5.6 tests are present in MariaDB 10.0:

  • main.alter_table (missing stuff like "Test for ALTER column DROP DEFAULT")
  • innodb.*
  • innodb_zip.*
  • innodb_fts.*

All the affected tests should be documented in this ticket in detail (especially those that are already covered by other MariaDB tests).

 Comments   
Comment by Marko Mäkelä [ 2017-08-29 ]

Because I need some MySQL 5.6 tests for testing MDEV-13654, I imported and adjusted the first batch of tests.

Comment by Alice Sherepa [ 2017-10-05 ]

Branch mdev-13625. My state for merging test cases from mysql 5.6.37 with 10.0.

--------------innodb_fts suite
new tests:

fts_compatibility.test
 
fts_compatibility_win.test
 
innodb_fts_index_table.test
 
innodb_fts_opt.test
 
innodb_fts_savepoint.test
 
phrase.test
 
subexpr.test
 
sync.test
 
sync_block.test

Failing test(s): innodb_fts.fts_compatibility
skipped test - innodb_fts.fts_compatibility_win --not tested yet

CURRENT_TEST: innodb_fts.fts_compatibility
 
mysqltest: At line 110: query 'SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2')' succeeded - should have failed with errno 1764...
 
 
 
The result from queries just before the failure was:
 
< snip >
 
FTS_0	test/FTS_AUX_BEING_DELETED
 
FTS_0	test/FTS_AUX_BEING_DELETED_CACHE
 
FTS_0	test/FTS_AUX_CONFIG
 
FTS_0	test/FTS_AUX_DELETED
 
FTS_0	test/FTS_AUX_DELETED_CACHE
 
DROP TABLE t1;
 
# Non windows aux table flag failure and rebuild the corrupted index.
 
call mtr.add_suppression("\\[Warning\\] InnoDB: Parent table of FTS auxiliary table .* not found.");
 
call mtr.add_suppression("\\[ERROR] InnoDB: Flagged corruption of .* in table .* in DROP ORPHANED TABLE");
 
call mtr.add_suppression("\\[Warning\\] InnoDB: Setting aux table .* to hex format failed.");
 
SET GLOBAL debug_dbug='+d,innodb_test_wrong_fts_aux_table_name';
 
CREATE TABLE t1(a TEXT, b TEXT, c TEXT, FULLTEXT `AB` (a, b), FULLTEXT `C1` (c));
 
INSERT INTO t1 VALUES('TEST1', 'TEST2', 'TEXT3');
 
INSERT INTO t1 VALUES('TEXT1', 'TEXT2', 'TEXT5');
 
SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2');
 
a	b	c
 
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('TEXT5');
 
a	b	c
 
SET GLOBAL debug_dbug='-d,innodb_test_wrong_fts_sys_table_name';
 
SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ('TEST2');

---------------innodb_zip suite:
new tests:

innodb-restart.test
 
innodb_16k.test
 
innodb_4k.test
 
innodb_8k.test
 
innodb_cmp_per_index.test
 
innodb_index_large_prefix_4k.test
 
innodb_index_large_prefix_8k.test
 
innodb_wl6347_comp_indx_stat.test

Failing test(s): innodb_zip.innodb_4k innodb_zip.innodb_cmp_per_index

innodb_zip.innodb_cmp_per_index 'innodb_plugin' [ pass ]   3639
 
innodb_zip.innodb_cmp_per_index 'xtradb' [ fail ]
 
        Test ended at 2017-10-04 15:50:46
 
 
 
CURRENT_TEST: innodb_zip.innodb_cmp_per_index
 
mysqltest: At line 77: query 'SELECT * FROM information_schema.innodb_cmp_per_index' succeeded - should have failed with errno 1227...
 
 
 
The result from queries just before the failure was:
 
< snip >
 
compress_ops_ok	1
 
uncompress_ops	0
 
database_name	test
 
table_name	t
 
index_name	c
 
compress_ops	1
 
compress_ops_ok	1
 
uncompress_ops	0
 
database_name	test
 
table_name	t
 
index_name	PRIMARY
 
compress_ops	1
 
compress_ops_ok	1
 
uncompress_ops	0
 
BEGIN;
 
COMMIT;
 
ALTER TABLE t DROP INDEX c;
 
GRANT USAGE ON *.* TO 'tuser01'@'localhost' IDENTIFIED BY 'cDJvI9s_Uq';
 
FLUSH PRIVILEGES;
 
SELECT * FROM information_schema.innodb_cmp_per_index;

--------------------------------------------------------------------------------

innodb_zip.innodb_4k 'innodb_plugin'     w3 [ pass ]    832
 
innodb_zip.innodb_4k 'xtradb'            w2 [ fail ]
 
        Test ended at 2017-10-04 15:17:48
 
 
 
CURRENT_TEST: innodb_zip.innodb_4k
 
--- /home/alice/aliska/10.0/mysql-test/suite/innodb_zip/r/innodb_4k.result	2017-10-04 13:54:49.515546820 +0200
 
+++ /home/alice/aliska/10.0/mysql-test/suite/innodb_zip/r/innodb_4k.reject	2017-10-04 15:17:47.980007024 +0200
 
@@ -10,7 +10,7 @@
 
 SELECT variable_value FROM information_schema.global_status
 
 WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
 
 variable_value
 
-2048
 
+2047
 
 # Test 3) Query some information_shema tables that are dependent upon
 
 #         the page size.
 
 INDEX_ID	NAME	TABLE_ID	TYPE	N_FIELDS	PAGE_NO	SPACE
 
 
 
mysqltest: Result content mismatch

Comment by Alice Sherepa [ 2017-10-05 ]

---innodb suite
new tests:

add_foreign_key.test
 
analyze_table.test
 
blob_redo.test
 
checksum.test
 
create_isl_with_direct.test
 
end_range_check.test
 
flush-hang.test
 
foreign_key.test
 
ibuf_not_empty.test
 
import.test
 
import_update_stats.test
 
innodb-2byte-collation.test
 
innodb-ac-non-locking-select.test
 
innodb-bug12552164.test
 
innodb-bug14219515.test
 
innodb-double-write.test
 
innodb-lock-inherit-read_commited.test
 
innodb-log-file-size-1.test
 
innodb-log-file-size.test
 
innodb-multiple-tablespaces.test
 
innodb-read-view.test
 
innodb-status-output.test
 
innodb-system-table-view.test
 
innodb-tablespace.test
 
innodb-use-sys-malloc.test
 
innodb-wl5980-debug.test
 
innodb-wl5980-discard.test
 
innodb-wl5980-linux.test
 
innodb-wl5980-windows.test
 
innodb-wl6445-1.test
 
innodb-wl6445-2.test
 
innodb-wl6445.test
 
innodb_autoinc_reset.test
 
innodb_buffer_pool_load.test
 
innodb_bug-13628249.test
 
innodb_bug11766634.test
 
innodb_bug11789106.test
 
innodb_bug11933790.test
 
innodb_bug12429573.test
 
innodb_bug13635833.test
 
innodb_bug13867871.test
 
innodb_bug14006907.test
 
innodb_bug14007109.test
 
innodb_bug14169459.test
 
innodb_bug59307.test
 
innodb_bug70867.test
 
innodb_copy_col_in_partition.test
 
innodb_deadlock_with_autoinc.test
 
innodb_file_limit_check.test
 
innodb_force_recovery.test
 
innodb_i_s_innodb_locks.test
 
innodb_i_s_innodb_trx.test
 
innodb_io_pf.test
 
innodb_misc1.test
 
innodb_page_size_func.test
 
innodb_replace.test
 
innodb_stats_auto_recalc.test
 
innodb_stats_auto_recalc_ddl.test
 
innodb_stats_auto_recalc_lots.test
 
innodb_stats_auto_recalc_on_nonexistent.test
 
innodb_stats_del_mark.test
 
innodb_stats_external_pages.test
 
innodb_stats_flag_global_off.test
 
innodb_stats_flag_global_on.test
 
innodb_stats_sample_pages.test
 
innodb_stats_table_flag_auto_recalc.test
 
innodb_stats_table_flag_sample_pages.test
 
innodb_upd_stats_if_needed_not_inited.test
 
innodb_ut_format_name.test
 
monitor.test
 
monitor_debug.test
 
portability_wl5980_linux.zip
 
portability_wl5980_windows.zip
 
strict_checksum.test
 
undo_space_id.test

-------------modified 

innodb/t/create-index.test
 
innodb/t/innodb-autoinc.test
 
innodb/t/innodb-lock.test
 
innodb/t/innodb.test
 
innodb/t/innodb_bug12661768.test
 
t/alter_table.test

Failing test(s): innodb.innodb-autoinc innodb.innodb_file_limit_check innodb.innodb-wl6445-1 innodb.innodb_stats_auto_recalc innodb.innodb-log-file-size innodb.innodb-system-table-view innodb.innodb_io_pf innodb.innodb_i_s_innodb_trx innodb.innodb-wl5980-linux innodb.innodb_page_size_func innodb.innodb_stats_fetch innodb.innodb_stats_fetch_corrupted innodb.innodb_stats_auto_recalc_ddl
Errors/warnings were found in logfiles during server shutdown after running the
following sequence(s) of tests:
innodb.innodb-log-file-size
innodb.innodb-wl5980-linux
4 tests were skipped, 4 by the test itself.

Comment by Marko Mäkelä [ 2017-10-06 ]

alice, at least the test innodb.innodb-log-file-size is already present as innodb.log_file_size, added by me in MDEV-11814. It also contains the test cases from innodb.innodb-log-file-size-1.
The test innodb.innodb-double-write is present in 10.1 as innodb.doublewrite, with some MariaDB specific modifications by me. I think that we should rather port that test to 10.0, or simply not merge innodb-double-write to 10.0.
The test innodb_zip.innodb_4k is present as innodb_zip.4k in 10.2. The innodb_zip tests could be easily backported from 10.2 to 10.0.

Please try harder to find tests that already exist in MariaDB, either 10.0 or 10.2. Also, try to enumerate the Oracle modifications that the MariaDB tests are missing.

Note that MDEV-12042 will affect the tests that use innodb_page_size. We might not want the innodb_page_size_func test at all.

Comment by Alice Sherepa [ 2017-10-26 ]

Marko Mäkelä, please take a look on tests from innodb_zip suite – wl6501_*
https://github.com/MariaDB/server/tree/mdev-13625/mysql-test/suite/innodb_zip/t

1) in 10.0 query 'truncate table t' succeeded - should have failed with errno 2013... -what is expected result there?
(the same in all related tests)
2) tests, that have --source include/have_innodb_32k.inc (16k, 8k, and so on).

cat have_innodb_32k.inc
 
if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE LOWER(variable_name) = 'innodb_page_size' AND variable_value = 32768`)
 
{
 
  --skip Test requires InnoDB with 32k page size.
 
}

so tests are skipped, instead of starting server with this page size.

3) (not problem, just fyi) in 10.2 wl6501_tests are in innodb_zip suite, but files innodb_wl6501_crash.inc, innodb_wl6501_crash_temp.inc are in innodb suite (innodb/include)

innodb_zip.wl6501_1 'innodb_plugin'      [ fail ]
 
        Test ended at 2017-10-26 14:26:11
 
 
 
CURRENT_TEST: innodb_zip.wl6501_1
 
mysqltest: At line 220: query 'truncate table t5' succeeded - should have failed with errno 2013...
 
 
 
The result from queries just before the failure was:
 
< snip >
 
select count(*) from t2;
 
count(*)
 
3
 
select count(*) from t3;
 
count(*)
 
3
 
select count(*) from t4;
 
count(*)
 
3
 
select count(*) from t5;
 
count(*)
 
3
 
select count(*) from t6;
 
count(*)
 
3
 
SET SESSION debug_dbug="+d,ib_trunc_crash_during_drop_index_temp_table";
 
"---debug ib_trunc_crash_during_drop_index_temp_table point---"
 
# Write file to make mysql-test-run.pl expect crash and restart
 
# Run the crashing query
 
truncate table t5;

Comment by Alice Sherepa [ 2017-10-26 ]

test innodb_zip.wl6347_comp_indx_stat in 10.0 https://github.com/MariaDB/server/blob/mdev-13625/mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test

innodb_zip.wl6347_comp_indx_stat 'innodb_plugin' [ fail ]
 
        Test ended at 2017-10-26 15:11:32
 
 
 
CURRENT_TEST: innodb_zip.wl6347_comp_indx_stat
 
--- /home/alice/aliska/10.0/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result	2017-10-26 15:10:53.353849361 +0200
 
+++ /home/alice/aliska/10.0/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.reject	2017-10-26 15:11:32.308658041 +0200
 
@@ -44,7 +44,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # for deterministic reasons simple data should be inserted.
 
 # insert some 100 records
 
 # Load the data
 
@@ -965,7 +965,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 163840
 
+The size of the tab5.ibd file: 159744
 
 # fetch the compressed page and check the stats
 
 ===============
 
 Fetch Records
 
@@ -989,7 +989,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 163840
 
+The size of the tab5.ibd file: 159744
 
 # fetch the compressed same page once again and check the stats
 
 # the stat figures should be same as above query
 
 ===============
 
@@ -1014,7 +1014,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 163840
 
+The size of the tab5.ibd file: 159744
 
 DROP TABLE tab5;
 
 #******************************************************************
 
 # Test 1-8K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=0 with page size 8K
 
@@ -1690,7 +1690,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # for determintic resons simple data should be inserted.
 
 # insert some 100 records
 
 # Load the data
 
@@ -1918,7 +1918,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed page and check the stats
 
 ===============
 
 Fetch Records
 
@@ -1942,7 +1942,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed same page once again and check the stats
 
 # the stat figures should be same as above query
 
 ===============
 
@@ -1967,7 +1967,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # Restarting server
 
 # set the flag on (default off)
 
 SET GLOBAL innodb_cmp_per_index_enabled=ON;
 
@@ -3337,7 +3337,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # for determintic resons simple data should be inserted.
 
 # insert some 100 records
 
 # Load the data
 
@@ -3565,7 +3565,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed page and check the stats
 
 ===============
 
 Fetch Records
 
@@ -3589,7 +3589,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed same page once again and check the stats
 
 # the stat figures should be same as above query
 
 ===============
 
@@ -3614,7 +3614,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 DROP TABLE tab5;
 
 #******************************************************************
 
 # Test 3-2K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=Def with page size 2K
 
@@ -4838,7 +4838,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # for determintic resons simple data should be inserted.
 
 #insert some 100 records
 
 # Load the data
 
@@ -5065,7 +5065,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed page and check the stats
 
 ===============
 
 Fetch Records
 
@@ -5089,7 +5089,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed same page once again and check the stats
 
 # the stat figures should be same as above query
 
 ===============
 
@@ -5114,7 +5114,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # Restarting server
 
 # set the flag on (default off)
 
 SET GLOBAL innodb_cmp_per_index_enabled=ON;
 
@@ -6463,7 +6463,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # for determintic resons simple data should be inserted.
 
 # insert some 100 records
 
 # Load the data
 
@@ -6690,7 +6690,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed page and check the stats
 
 ===============
 
 Fetch Records
 
@@ -6714,7 +6714,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # fetch the compressed same page once again and check the stats
 
 # the stat figures should be same as above query
 
 ===============
 
@@ -6739,7 +6739,7 @@
 
 AND table_name='tab5' AND database_name='test'
 
 AND index_name like 'idx%' ;
 
 compress_stat	1
 
-The size of the tab5.ibd file: 65536
 
+The size of the tab5.ibd file: 5242880
 
 # Restarting server
 
 # set the flag on (default off)
 
 SET GLOBAL innodb_cmp_per_index_enabled=ON;
 
 
 
mysqltest: Result length mismatch

Comment by Marko Mäkelä [ 2017-10-30 ]

Tests for different innodb_page_size should be combined and innodb_page_size.inc should be used.
Also in 10.1 we have some innodb.innodb-64k and similar tests that should be merged.
In 10.2 there is innodb_zip.4k and so on, which should be combined, using innodb_page_size_small.inc.

The TRUNCATE TABLE code was rewritten in 5.7 as part of WL#6501 and in a different way in 10.2.19 by MDEV-13564. The WL#6501 TRUNCATE TABLE tests do not apply to MariaDB.

"WL#6347 InnoDB: Index level compression stats" should be in 5.6 (and 10.1) too.
The .ibd file size mismatch could be due to the same bug that causes MDEV-13013. Can you try to find out the reason?

When preparing for MDEV-11369, I merged many ALTER TABLE tests to MariaDB 10.0, but some may be missing, especially from --suite=main.

Comment by Marko Mäkelä [ 2023-10-31 ]

Because the MariaDB Server 10.0 and 10.1 branches have already reached EOL, it does not make sense to implement this. The MySQL 5.7 tests (MDEV-13626) do include tests from MySQL 5.6.

Generated at Thu Feb 08 08:07:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.