[MCOL-5279] MCS Cluster: After software update from 6.x to 22.08, queries hang on one node - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 22.08.2
Fix Version/s: 22.08.3
Component/s: None
Labels:
- cluster
- triage

Sprint:
2022-22

Description

"Where this join query hangs forever on the master node, but runs fine on the replicas"

SELECT ff.acct_id, COUNT(1)

FROM ff JOIN dd  ON ff.acct_sk =dd.acct_sk

AND ff.db_source_sk =dd.db_source_sk

GROUP BY ff.acct_id LIMIT 10;

mariadb -e "create database test; use test;"

mariadb test -e "CREATE TABLE `dd`( `acct_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `db_source_sk` INT(11) UNSIGNED NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;"

mariadb test -e "CREATE TABLE `ff` ( `db_source_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `acct_sk` INT(11) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;"

mariadb test -e "INSERT INTO dd SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000; "

mariadb test -e "INSERT INTO ff SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000;"

Attachments

Issue Links

includes

MCOL-5305 Query initiated at slave MDB are stuck

Closed

is caused by

MCOL-5166 EM to PP facility communication speedup for a single-node

Closed

Activity

Ascending order - Click to sort in descending order

Edward Stoever created issue - 2022-10-25 23:19

Todd Stoffel (Inactive) made changes - 2022-10-26 04:48

Field	Original Value	New Value
Rank		Ranked higher

Todd Stoffel (Inactive) made changes - 2022-10-26 04:48

Assignee

Roman [ drrtuy ]

Todd Stoffel (Inactive) made changes - 2022-10-26 04:49

Priority

Major [ 3 ]

Blocker [ 1 ]

Todd Stoffel (Inactive) made changes - 2022-10-26 04:49

Fix Version/s

22.11.01 [ 28458 ]

Todd Stoffel (Inactive) made changes - 2022-10-26 04:49

Sprint

2022-22 [ 672 ]

Todd Stoffel (Inactive) made changes - 2022-10-26 04:49

Rank

Ranked lower

alexey vorovich (Inactive) made changes - 2022-10-26 13:46

Description

"Where this join query hangs forever on the master node, but runs fine on the replicas"
{NOFORMAT}
SELECT ff.acct_id, COUNT(1)
FROM ff JOIN dd ON ff.acct_sk =dd.acct_sk
AND ff.db_source_sk =dd.db_source_sk
GROUP BY ff.acct_id LIMIT 10;
{NOFORMAT}

{noformat}
mariadb -e "create database test; use test;"

mariadb test -e "CREATE TABLE `dd`( `acct_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `db_source_sk` INT(11) UNSIGNED NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;"
mariadb test -e "CREATE TABLE `ff` ( `db_source_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `acct_sk` INT(11) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;"

mariadb test -e "INSERT INTO dd SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000; "
mariadb test -e "INSERT INTO ff SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000;"

{noformat}

alexey vorovich (Inactive) added a comment - 2022-10-26 13:47

dleeyh please try to repro this and in your mulinode tests , and then we add the test

alexey vorovich (Inactive) added a comment - 2022-10-26 13:47 dleeyh please try to repro this and in your mulinode tests , and then we add the test

Chris Calender (Inactive) made changes - 2022-10-26 18:47

Summary

CS Cluster: After software update from 6.x to 22.08, queries hang on one node

MCS Cluster: After software update from 6.x to 22.08, queries hang on one node

Roman made changes - 2022-11-01 19:49

Link

This issue is caused by ~~MCOL-5166~~ [ ~~MCOL-5166~~ ]

alexey vorovich (Inactive) made changes - 2022-11-02 19:06

Status

Open [ 1 ]

In Progress [ 3 ]

alexey vorovich (Inactive) made changes - 2022-11-02 19:06

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Daniel Lee (Inactive) made changes - 2022-11-02 20:35

Fix Version/s		22.08.3 [ 28456 ]
Fix Version/s	22.11.01 [ 28458 ]

Roman made changes - 2022-11-03 13:43

Assignee

Roman [ drrtuy ]

Daniel Lee [ dleeyh ]

alexey vorovich (Inactive) made changes - 2022-11-03 14:08

Assignee

Daniel Lee [ dleeyh ]

Roman [ drrtuy ]

alexey vorovich (Inactive) made changes - 2022-11-03 14:09

Assigned for Testing

Daniel Lee [ dleeyh ]

David Hall (Inactive) added a comment - 2022-11-03 14:30

Here's a test for some edge case

# This test makes sense when run on a multi-node cluster.

CREATE TABLE `dd`( `acct_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `db_source_sk` INT(11) UNSIGNED NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ff` ( `db_source_sk` INT(11) UNSIGNED NOT NULL, `acct_id` VARCHAR(128) NOT NULL DEFAULT 'None', `acct_sk` INT(11) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;

INSERT INTO dd SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000;

INSERT INTO ff SELECT ROUND(RAND() * 10, 2), substring(MD5(RAND()),1,1), ROUND(RAND() * 100, 2) FROM seq_1_to_300000;

# The purpose is to run the statement that doesn't hang.

SELECT * FROM (SELECT ff.acct_id, COUNT(1) FROM ff JOIN dd  ON ff.acct_sk =dd.acct_sk AND ff.db_source_sk =dd.db_source_sk GROUP BY ff.acct_id LIMIT 10)s LIMIT 0;

# Clean UP

DROP DATABASE mcol_5279;

David Hall (Inactive) added a comment - 2022-11-03 14:30 Here's a test for some edge case # This test makes sense when run on a multi-node cluster. CREATE TABLE `dd`( `acct_sk` INT( 11 ) UNSIGNED NOT NULL, `acct_id` VARCHAR( 128 ) NOT NULL DEFAULT 'None' , `db_source_sk` INT( 11 ) UNSIGNED NOT NULL) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4; CREATE TABLE `ff` ( `db_source_sk` INT( 11 ) UNSIGNED NOT NULL, `acct_id` VARCHAR( 128 ) NOT NULL DEFAULT 'None' , `acct_sk` INT( 11 ) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4; INSERT INTO dd SELECT ROUND(RAND() * 10 , 2 ), substring(MD5(RAND()), 1 , 1 ), ROUND(RAND() * 100 , 2 ) FROM seq_1_to_300000; INSERT INTO ff SELECT ROUND(RAND() * 10 , 2 ), substring(MD5(RAND()), 1 , 1 ), ROUND(RAND() * 100 , 2 ) FROM seq_1_to_300000; # The purpose is to run the statement that doesn't hang. SELECT * FROM (SELECT ff.acct_id, COUNT( 1 ) FROM ff JOIN dd ON ff.acct_sk =dd.acct_sk AND ff.db_source_sk =dd.db_source_sk GROUP BY ff.acct_id LIMIT 10 )s LIMIT 0 ; # Clean UP DROP DATABASE mcol_5279;

Daniel Lee (Inactive) added a comment - 2022-11-03 20:56

Build verified: 22.08.3 (RC from Jenkins)

Query no longer hangs. MTR tests were able to complete.

Daniel Lee (Inactive) added a comment - 2022-11-03 20:56 Build verified: 22.08.3 (RC from Jenkins) Query no longer hangs. MTR tests were able to complete.

Daniel Lee (Inactive) made changes - 2022-11-03 20:56

Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

alexey vorovich (Inactive) made changes - 2022-11-11 15:13

Fix Version/s		22.08.5 [ 28533 ]
Fix Version/s	22.08.3 [ 28456 ]

alexey vorovich (Inactive) added a comment - 2022-11-11 15:15 - edited

additional scenario discovered and fixed

sporadic hang (repoduced in VM or k8s - not in dockercompose
In the sequence below SELECT to next host hangs. Sporadic

        self.connect(self.HOST_0)

        self.create_table()

        self.ex_sql("insert into t1 (f1) values ( 1 )",silent=False)

        self.connection.commit()

        self.ex_sql("Select count(*)  from t1")

        self.connect(self.HOST_1)

        self.ex_sql("use d1")

        self.ex_sql("select count(*)  from t1")

alexey vorovich (Inactive) added a comment - 2022-11-11 15:15 - edited additional scenario discovered and fixed sporadic hang (repoduced in VM or k8s - not in dockercompose In the sequence below SELECT to next host hangs. Sporadic self.connect(self.HOST_0) self.create_table() self.ex_sql( "insert into t1 (f1) values ( 1 )" ,silent=False) self.connection.commit() self.ex_sql( "Select count(*) from t1" ) self.connect(self.HOST_1) self.ex_sql( "use d1" ) self.ex_sql( "select count(*) from t1" )

Roman made changes - 2022-11-11 16:31

Link

This issue includes ~~MCOL-5305~~ [ ~~MCOL-5305~~ ]

alexey vorovich (Inactive) made changes - 2022-11-11 16:37

Fix Version/s		22.08.3 [ 28456 ]
Fix Version/s	22.08.5 [ 28533 ]

Gagan Goel (Inactive) made changes - 2023-01-17 09:07

Labels

triage

cluster triage

Jira Automation (IT) made changes - 2024-07-04 12:52

Zendesk Related Tickets

175578 127889

People

Assignee:: Roman

Reporter:: Edward Stoever

Assigned for Testing:: Daniel Lee (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2022-10-25 23:19

Updated:: 2024-07-08 01:25

Resolved:: 2022-11-03 20:56

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB ColumnStore

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration