Skip to content

Commit c233e73

Browse files
robot-clickhouserobot-clickhouse
committed
Backport #68930 to 24.8: Fix 01114_database_atomic flakiness
1 parent 6ab3b11 commit c233e73

File tree

2 files changed

+78
-61
lines changed

2 files changed

+78
-61
lines changed

‎tests/queries/0_stateless/01114_database_atomic.reference‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,17 @@
11
1
2-
CREATE DATABASE test_01114_1\nENGINE = Atomic
3-
CREATE DATABASE test_01114_2\nENGINE = Atomic
4-
CREATE DATABASE test_01114_3\nENGINE = Ordinary
5-
test_01114_1 Atomic store 00001114-1000-4000-8000-000000000001 1
6-
test_01114_2 Atomic store 00001114-1000-4000-8000-000000000002 1
7-
test_01114_3 Ordinary test_01114_3 test_01114_3 1
2+
CREATE DATABASE default_1\nENGINE = Atomic
3+
CREATE DATABASE default_2\nENGINE = Atomic
4+
CREATE DATABASE default_3\nENGINE = Ordinary
5+
default_1 Atomic store 00001114-1000-4000-8000-000000000001 1
6+
default_2 Atomic store 00001114-1000-4000-8000-000000000002 1
7+
default_3 Ordinary default_3 default_3 1
88
110
99
100
10-
CREATE TABLE test_01114_2.mt UUID \'00001114-0000-4000-8000-000000000002\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
11-
mt 00001114-0000-4000-8000-000000000002 CREATE TABLE test_01114_2.mt (`n` UInt64) ENGINE = MergeTree PARTITION BY n % 5 ORDER BY tuple() SETTINGS index_granularity = 8192
10+
CREATE TABLE default_2.mt UUID \'00001114-0000-4000-8000-000000000002\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
11+
mt 00001114-0000-4000-8000-000000000002 CREATE TABLE default_2.mt (`n` UInt64) ENGINE = MergeTree PARTITION BY n % 5 ORDER BY tuple() SETTINGS index_granularity = 8192
1212
110
13-
CREATE TABLE test_01114_1.mt UUID \'00001114-0000-4000-8000-000000000001\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
14-
CREATE TABLE test_01114_2.mt UUID \'00001114-0000-4000-8000-000000000002\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
13+
CREATE TABLE default_1.mt UUID \'00001114-0000-4000-8000-000000000001\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
14+
CREATE TABLE default_2.mt UUID \'00001114-0000-4000-8000-000000000002\'\n(\n `n` UInt64\n)\nENGINE = MergeTree\nPARTITION BY n % 5\nORDER BY tuple()\nSETTINGS index_granularity = 8192
1515
5
1616
dropped
1717
110 5995

‎tests/queries/0_stateless/01114_database_atomic.sh‎

Lines changed: 68 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -9,81 +9,98 @@ CURDIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
99
# shellcheck source=../shell_config.sh
1010
. "$CURDIR"/../shell_config.sh
1111

12+
DATABASE_1="${CLICKHOUSE_DATABASE}_1"
13+
DATABASE_2="${CLICKHOUSE_DATABASE}_2"
14+
DATABASE_3="${CLICKHOUSE_DATABASE}_3"
1215

13-
$CLICKHOUSE_CLIENT -nm -q "
14-
DROP DATABASE IF EXISTS test_01114_1;
15-
DROP DATABASE IF EXISTS test_01114_2;
16-
DROP DATABASE IF EXISTS test_01114_3;
17-
"
18-
19-
$CLICKHOUSE_CLIENT --allow_deprecated_database_ordinary=0 -q "CREATE DATABASE test_01114_1 ENGINE=Ordinary" 2>&1| grep -Fac "UNKNOWN_DATABASE_ENGINE"
16+
$CLICKHOUSE_CLIENT --allow_deprecated_database_ordinary=0 -q "CREATE DATABASE ${DATABASE_1} ENGINE=Ordinary" 2>&1| grep -Fac "UNKNOWN_DATABASE_ENGINE"
2017

21-
$CLICKHOUSE_CLIENT -q "CREATE DATABASE test_01114_1 ENGINE=Atomic"
22-
$CLICKHOUSE_CLIENT -q "CREATE DATABASE test_01114_2"
23-
$CLICKHOUSE_CLIENT --allow_deprecated_database_ordinary=1 -q "CREATE DATABASE test_01114_3 ENGINE=Ordinary"
18+
$CLICKHOUSE_CLIENT -q "CREATE DATABASE ${DATABASE_1} ENGINE=Atomic"
19+
$CLICKHOUSE_CLIENT -q "CREATE DATABASE ${DATABASE_2}"
20+
$CLICKHOUSE_CLIENT --allow_deprecated_database_ordinary=1 -q "CREATE DATABASE ${DATABASE_3} ENGINE=Ordinary"
2421

25-
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=0 -q "SHOW CREATE DATABASE test_01114_1"
26-
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=0 -q "SHOW CREATE DATABASE test_01114_2"
27-
$CLICKHOUSE_CLIENT -q "SHOW CREATE DATABASE test_01114_3"
22+
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=0 -q "SHOW CREATE DATABASE ${DATABASE_1}"
23+
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=0 -q "SHOW CREATE DATABASE ${DATABASE_2}"
24+
$CLICKHOUSE_CLIENT -q "SHOW CREATE DATABASE ${DATABASE_3}"
2825

29-
uuid_db_1=`$CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.databases WHERE name='test_01114_1'"`
30-
uuid_db_2=`$CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.databases WHERE name='test_01114_2'"`
26+
uuid_db_1=`$CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.databases WHERE name='${DATABASE_1}'"`
27+
uuid_db_2=`$CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.databases WHERE name='${DATABASE_2}'"`
3128
$CLICKHOUSE_CLIENT -q "SELECT name,
3229
engine,
3330
splitByChar('/', data_path)[-2],
3431
splitByChar('/', metadata_path)[-2] as uuid_path, ((splitByChar('/', metadata_path)[-3] as metadata) = substr(uuid_path, 1, 3)) OR metadata='metadata'
35-
FROM system.databases WHERE name LIKE 'test_01114_%'" | sed "s/$uuid_db_1/00001114-1000-4000-8000-000000000001/g" | sed "s/$uuid_db_2/00001114-1000-4000-8000-000000000002/g"
32+
FROM system.databases WHERE name LIKE '${CLICKHOUSE_DATABASE}_%'" | sed "s/$uuid_db_1/00001114-1000-4000-8000-000000000001/g" | sed "s/$uuid_db_2/00001114-1000-4000-8000-000000000002/g"
3633

3734
$CLICKHOUSE_CLIENT -nm -q "
38-
CREATE TABLE test_01114_1.mt_tmp (n UInt64) ENGINE=MergeTree() ORDER BY tuple();
39-
INSERT INTO test_01114_1.mt_tmp SELECT * FROM numbers(100);
40-
CREATE TABLE test_01114_3.mt (n UInt64) ENGINE=MergeTree() ORDER BY tuple() PARTITION BY (n % 5);
41-
INSERT INTO test_01114_3.mt SELECT * FROM numbers(110);
35+
CREATE TABLE ${DATABASE_1}.mt_tmp (n UInt64) ENGINE=MergeTree() ORDER BY tuple();
36+
INSERT INTO ${DATABASE_1}.mt_tmp SELECT * FROM numbers(100);
37+
CREATE TABLE ${DATABASE_3}.mt (n UInt64) ENGINE=MergeTree() ORDER BY tuple() PARTITION BY (n % 5);
38+
INSERT INTO ${DATABASE_3}.mt SELECT * FROM numbers(110);
4239
43-
RENAME TABLE test_01114_1.mt_tmp TO test_01114_3.mt_tmp; /* move from Atomic to Ordinary */
44-
RENAME TABLE test_01114_3.mt TO test_01114_1.mt; /* move from Ordinary to Atomic */
45-
SELECT count() FROM test_01114_1.mt;
46-
SELECT count() FROM test_01114_3.mt_tmp;
40+
RENAME TABLE ${DATABASE_1}.mt_tmp TO ${DATABASE_3}.mt_tmp; /* move from Atomic to Ordinary */
41+
RENAME TABLE ${DATABASE_3}.mt TO ${DATABASE_1}.mt; /* move from Ordinary to Atomic */
42+
SELECT count() FROM ${DATABASE_1}.mt;
43+
SELECT count() FROM ${DATABASE_3}.mt_tmp;
4744
48-
DROP DATABASE test_01114_3;
45+
DROP DATABASE ${DATABASE_3};
4946
"
5047

5148
explicit_uuid=$($CLICKHOUSE_CLIENT -q "SELECT generateUUIDv4()")
52-
$CLICKHOUSE_CLIENT -q "CREATE TABLE test_01114_2.mt UUID '$explicit_uuid' (n UInt64) ENGINE=MergeTree() ORDER BY tuple() PARTITION BY (n % 5)"
53-
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE test_01114_2.mt" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
54-
$CLICKHOUSE_CLIENT -q "SELECT name, uuid, create_table_query FROM system.tables WHERE database='test_01114_2'" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
55-
56-
57-
$CLICKHOUSE_CLIENT --function_sleep_max_microseconds_per_block 60000000 -q "SELECT count(col), sum(col) FROM (SELECT n + sleepEachRow(1.5) AS col FROM test_01114_1.mt)" & # 33s (1.5s * 22 rows per partition), result: 110, 5995
58-
$CLICKHOUSE_CLIENT --function_sleep_max_microseconds_per_block 60000000 -q "INSERT INTO test_01114_2.mt SELECT number + sleepEachRow(1.5) FROM numbers(30)" & # 45s (1.5s * 30 rows)
59-
sleep 1 # SELECT and INSERT should start before the following RENAMEs
49+
$CLICKHOUSE_CLIENT -q "CREATE TABLE ${DATABASE_2}.mt UUID '$explicit_uuid' (n UInt64) ENGINE=MergeTree() ORDER BY tuple() PARTITION BY (n % 5)"
50+
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE ${DATABASE_2}.mt" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
51+
$CLICKHOUSE_CLIENT -q "SELECT name, uuid, create_table_query FROM system.tables WHERE database='${DATABASE_2}'" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
52+
53+
RANDOM_COMMENT="$RANDOM"
54+
$CLICKHOUSE_CLIENT --max-threads 5 --function_sleep_max_microseconds_per_block 60000000 -q "SELECT count(col), sum(col) FROM (SELECT n + sleepEachRow(1.5) AS col FROM ${DATABASE_1}.mt) -- ${RANDOM_COMMENT}" & # 33s (1.5s * 22 rows per partition [Using 5 threads in parallel]), result: 110, 5995
55+
$CLICKHOUSE_CLIENT --max-threads 5 --function_sleep_max_microseconds_per_block 60000000 -q "INSERT INTO ${DATABASE_2}.mt SELECT number + sleepEachRow(1.5) FROM numbers(30) -- ${RANDOM_COMMENT}" & # 45s (1.5s * 30 rows)
56+
57+
it=0
58+
while [[ $($CLICKHOUSE_CLIENT -q "SELECT count() FROM system.processes WHERE query_id != queryID() AND current_database = currentDatabase() AND query LIKE '%-- ${RANDOM_COMMENT}%'") -ne 2 ]]; do
59+
it=$((it+1))
60+
if [ $it -ge 50 ];
61+
then
62+
echo "Failed to wait for first batch of queries"
63+
$CLICKHOUSE_CLIENT -q "SELECT count() FROM system.processes WHERE query_id != queryID() AND current_database = currentDatabase() AND query LIKE '%-- ${RANDOM_COMMENT}%'"
64+
fi
65+
sleep 0.1
66+
done
6067

6168
$CLICKHOUSE_CLIENT -nm -q "
62-
RENAME TABLE test_01114_1.mt TO test_01114_1.mt_tmp;
63-
RENAME TABLE test_01114_1.mt_tmp TO test_01114_2.mt_tmp;
64-
EXCHANGE TABLES test_01114_2.mt AND test_01114_2.mt_tmp;
65-
RENAME TABLE test_01114_2.mt_tmp TO test_01114_1.mt;
66-
EXCHANGE TABLES test_01114_1.mt AND test_01114_2.mt;
69+
RENAME TABLE ${DATABASE_1}.mt TO ${DATABASE_1}.mt_tmp;
70+
RENAME TABLE ${DATABASE_1}.mt_tmp TO ${DATABASE_2}.mt_tmp;
71+
EXCHANGE TABLES ${DATABASE_2}.mt AND ${DATABASE_2}.mt_tmp;
72+
RENAME TABLE ${DATABASE_2}.mt_tmp TO ${DATABASE_1}.mt;
73+
EXCHANGE TABLES ${DATABASE_1}.mt AND ${DATABASE_2}.mt;
6774
"
6875

6976
# Check that nothing changed
70-
$CLICKHOUSE_CLIENT -q "SELECT count() FROM test_01114_1.mt"
71-
uuid_mt1=$($CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.tables WHERE database='test_01114_1' AND name='mt'")
72-
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE test_01114_1.mt" | sed "s/$uuid_mt1/00001114-0000-4000-8000-000000000001/g"
73-
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE test_01114_2.mt" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
77+
$CLICKHOUSE_CLIENT -q "SELECT count() FROM ${DATABASE_1}.mt"
78+
uuid_mt1=$($CLICKHOUSE_CLIENT -q "SELECT uuid FROM system.tables WHERE database='${DATABASE_1}' AND name='mt'")
79+
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE ${DATABASE_1}.mt" | sed "s/$uuid_mt1/00001114-0000-4000-8000-000000000001/g"
80+
$CLICKHOUSE_CLIENT --show_table_uuid_in_table_create_query_if_not_nil=1 -q "SHOW CREATE TABLE ${DATABASE_2}.mt" | sed "s/$explicit_uuid/00001114-0000-4000-8000-000000000002/g"
7481

7582
$CLICKHOUSE_CLIENT -nm -q "
76-
DROP TABLE test_01114_1.mt SETTINGS database_atomic_wait_for_drop_and_detach_synchronously=0;
77-
CREATE TABLE test_01114_1.mt (s String) ENGINE=Log();
78-
INSERT INTO test_01114_1.mt SELECT 's' || toString(number) FROM numbers(5);
79-
SELECT count() FROM test_01114_1.mt
83+
DROP TABLE ${DATABASE_1}.mt SETTINGS database_atomic_wait_for_drop_and_detach_synchronously=0;
84+
CREATE TABLE ${DATABASE_1}.mt (s String) ENGINE=Log();
85+
INSERT INTO ${DATABASE_1}.mt SELECT 's' || toString(number) FROM numbers(5);
86+
SELECT count() FROM ${DATABASE_1}.mt
8087
" # result: 5
8188

82-
$CLICKHOUSE_CLIENT --function_sleep_max_microseconds_per_block 60000000 -q "SELECT tuple(s, sleepEachRow(3)) FROM test_01114_1.mt" > /dev/null & # 15s (3s * 5 rows)
83-
sleep 1
84-
$CLICKHOUSE_CLIENT -q "DROP DATABASE test_01114_1" --database_atomic_wait_for_drop_and_detach_synchronously=0 && echo "dropped"
89+
RANDOM_TUPLE="${RANDOM}_tuple"
90+
$CLICKHOUSE_CLIENT --max-threads 5 --function_sleep_max_microseconds_per_block 60000000 -q "SELECT tuple(s, sleepEachRow(3)) FROM ${DATABASE_1}.mt -- ${RANDOM_TUPLE}" > /dev/null & # 15s (3s * 5 rows)
91+
it=0
92+
while [[ $($CLICKHOUSE_CLIENT -q "SELECT count() FROM system.processes WHERE query_id != queryID() AND current_database = currentDatabase() AND query LIKE '%-- ${RANDOM_TUPLE}%'") -ne 1 ]]; do
93+
it=$((it+1))
94+
if [ $it -ge 50 ];
95+
then
96+
echo "Failed to wait for second batch of queries"
97+
$CLICKHOUSE_CLIENT -q "SELECT count() FROM system.processes WHERE query_id != queryID() AND current_database = currentDatabase() AND query LIKE '%-- ${RANDOM_TUPLE}%'"
98+
fi
99+
sleep 0.1
100+
done
101+
$CLICKHOUSE_CLIENT -q "DROP DATABASE ${DATABASE_1}" --database_atomic_wait_for_drop_and_detach_synchronously=0 && echo "dropped"
85102

86103
wait # for INSERT and SELECT
87104

88-
$CLICKHOUSE_CLIENT -q "SELECT count(n), sum(n) FROM test_01114_2.mt" # result: 30, 435
89-
$CLICKHOUSE_CLIENT -q "DROP DATABASE test_01114_2" --database_atomic_wait_for_drop_and_detach_synchronously=0
105+
$CLICKHOUSE_CLIENT -q "SELECT count(n), sum(n) FROM ${DATABASE_2}.mt" # result: 30, 435
106+
$CLICKHOUSE_CLIENT -q "DROP DATABASE ${DATABASE_2}" --database_atomic_wait_for_drop_and_detach_synchronously=0

0 commit comments

Comments
 (0)