Changeset: 371f3c7115bc for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/371f3c7115bc Added Files: sql/test/BugTracker-2022/Tests/rerun-temp-table-index-crashes-server.Bug.test sql/test/BugTracker-2022/Tests/rerun-temp-table-index-fails-to-restart-server.Bug.test sql/test/BugTracker-2022/Tests/temp-table-index.Bug.test Modified Files: sql/test/BugTracker-2022/Tests/All Branch: Jul2021 Log Message:
Adding tests to reproduce issue where Jul2021 server crashes and next fails to restart due to database corruption. The 3 tests do exactly the same sequence of statements, but had to be given unique names as it Mtest.py does not allow to use the same name multiple times in the All file. The problem is that dropping indexes of temporary tables should remove the index names from sys.idxs and tmp.idxs tables, but is doesn't. Internal corruption occurs. When the test is repeated, it falsely thinks the index names tmp_pk_uc_i, tmp_nopk_twoucs_i, glbl_pk_uc_i and glbl_nopk_twoucs_i already exist (due to the internal corruption). When next the query: SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name is run the server crashes. When next the test is repeated again, it fails to restart the server, probably due to the corrupted catalog tables. diffs (truncated from 371 to 300 lines): diff --git a/sql/test/BugTracker-2022/Tests/All b/sql/test/BugTracker-2022/Tests/All --- a/sql/test/BugTracker-2022/Tests/All +++ b/sql/test/BugTracker-2022/Tests/All @@ -1,1 +1,4 @@ transaction-conflict.Bug-7228 +temp-table-index.Bug +rerun-temp-table-index-crashes-server.Bug +rerun-temp-table-index-fails-to-restart-server.Bug diff --git a/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-crashes-server.Bug.test b/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-crashes-server.Bug.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-crashes-server.Bug.test @@ -0,0 +1,116 @@ +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +query T rowsort +SELECT name FROM sys.tables WHERE NOT system ORDER BY name +----- +glbl_nopk_twoucs +glbl_pk_uc +tmp_nopk_twoucs +tmp_pk_uc + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +CREATE INDEX tmp_pk_uc_i ON tmp.tmp_pk_uc (id1, name1) + +statement ok +CREATE INDEX tmp_nopk_twoucs_i ON tmp.tmp_nopk_twoucs (id2, name2) + +statement ok +CREATE INDEX glbl_pk_uc_i ON tmp.glbl_pk_uc (id1, name1) + +statement ok +CREATE INDEX glbl_nopk_twoucs_i ON tmp.glbl_nopk_twoucs (id2, name2) + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_i +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_i +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_i +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_i +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +DROP INDEX tmp.tmp_pk_uc_i + +statement ok +DROP INDEX tmp.tmp_nopk_twoucs_i + +statement ok +DROP INDEX tmp.glbl_pk_uc_i + +statement ok +DROP INDEX tmp.glbl_nopk_twoucs_i + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +DROP TABLE tmp.tmp_pk_uc + +statement ok +DROP TABLE tmp.glbl_pk_uc + +statement ok +DROP TABLE tmp.tmp_nopk_twoucs + +statement ok +DROP TABLE tmp.glbl_nopk_twoucs + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- + diff --git a/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-fails-to-restart-server.Bug.test b/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-fails-to-restart-server.Bug.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2022/Tests/rerun-temp-table-index-fails-to-restart-server.Bug.test @@ -0,0 +1,116 @@ +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +query T rowsort +SELECT name FROM sys.tables WHERE NOT system ORDER BY name +----- +glbl_nopk_twoucs +glbl_pk_uc +tmp_nopk_twoucs +tmp_pk_uc + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +CREATE INDEX tmp_pk_uc_i ON tmp.tmp_pk_uc (id1, name1) + +statement ok +CREATE INDEX tmp_nopk_twoucs_i ON tmp.tmp_nopk_twoucs (id2, name2) + +statement ok +CREATE INDEX glbl_pk_uc_i ON tmp.glbl_pk_uc (id1, name1) + +statement ok +CREATE INDEX glbl_nopk_twoucs_i ON tmp.glbl_nopk_twoucs (id2, name2) + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_i +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_i +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_i +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_i +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +DROP INDEX tmp.tmp_pk_uc_i + +statement ok +DROP INDEX tmp.tmp_nopk_twoucs_i + +statement ok +DROP INDEX tmp.glbl_pk_uc_i + +statement ok +DROP INDEX tmp.glbl_nopk_twoucs_i + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +DROP TABLE tmp.tmp_pk_uc + +statement ok +DROP TABLE tmp.glbl_pk_uc + +statement ok +DROP TABLE tmp.tmp_nopk_twoucs + +statement ok +DROP TABLE tmp.glbl_nopk_twoucs + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- + diff --git a/sql/test/BugTracker-2022/Tests/temp-table-index.Bug.test b/sql/test/BugTracker-2022/Tests/temp-table-index.Bug.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2022/Tests/temp-table-index.Bug.test @@ -0,0 +1,116 @@ +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE LOCAL TEMP TABLE tmp.tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_pk_uc (id1 INT NOT NULL PRIMARY KEY, name1 VARCHAR(99) UNIQUE) + +statement ok +CREATE GLOBAL TEMP TABLE tmp.glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2 VARCHAR(99) UNIQUE) + +query T rowsort +SELECT name FROM sys.tables WHERE NOT system ORDER BY name +----- +glbl_nopk_twoucs +glbl_pk_uc +tmp_nopk_twoucs +tmp_pk_uc + +query T rowsort +SELECT name FROM sys.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +glbl_nopk_twoucs_id2_unique +glbl_nopk_twoucs_name2_unique +glbl_pk_uc_id1_pkey +glbl_pk_uc_name1_unique + +query T rowsort +SELECT name FROM tmp.idxs WHERE (table_id) IN (SELECT id FROM sys.tables WHERE NOT system) ORDER BY name +----- +tmp_nopk_twoucs_id2_unique +tmp_nopk_twoucs_name2_unique +tmp_pk_uc_id1_pkey +tmp_pk_uc_name1_unique + +statement ok +CREATE INDEX tmp_pk_uc_i ON tmp.tmp_pk_uc (id1, name1) + +statement ok +CREATE INDEX tmp_nopk_twoucs_i ON tmp.tmp_nopk_twoucs (id2, name2) + +statement ok +CREATE INDEX glbl_pk_uc_i ON tmp.glbl_pk_uc (id1, name1) + _______________________________________________ checkin-list mailing list -- checkin-list@monetdb.org To unsubscribe send an email to checkin-list-le...@monetdb.org
