[MDEV-4237] Galera: Malformed packet, ER_SP_DOES_NOT_EXIST or ER_VIEW_INVALID on concurrent view creation and procedure call - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5.28a-galera
Fix Version/s: N/A
Component/s: Galera
Labels:
- galera
- need_verification

Description

Provided test case usually finishes with error 2027 (Malformed packet), and sometimes with 1356 (ER_VIEW_INVALID) or 1305 (ER_SP_DOES_NOT_EXIST), all of which are unexpected.

It doesn't happen if the test is executed with wsrep disabled.

MTR test case:

--sleep 2

--source include/have_innodb.inc

--source include/have_binlog_format_row.inc

SET GLOBAL lock_wait_timeout = 2;

SET GLOBAL innodb_lock_wait_timeout = 1;

CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;

CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1;

CREATE FUNCTION func1() RETURNS INTEGER RETURN 4;

CREATE FUNCTION func2() RETURNS INTEGER RETURN 4;

CREATE PROCEDURE proc1() SELECT func1() FROM v1;

--connect (con1,localhost,root,,test)

--connect (con2,localhost,root,,test)

--let $run = 100

while ($run)

  --connection con1

  --send

	  CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1;

  --connection con2

	  CALL proc1();

  --connection con1

  --reap

  --dec $run

MTR command line:

perl ./mtr --mysqld=--innodb_autoinc_lock_mode=2 --mysqld=--innodb_locks_unsafe_for_binlog=1  --mysqld=--wsrep-provider=/home/elenst/galera/libgalera_smm.so --mysqld=--wsrep-cluster-address=gcomm://  --nocheck-testcases main.malformed_packet

revision-id: elenst@montyprogram.com-20130302232248-uozp6ilf7kbyfrgj

revno: 3388

branch-nick: maria-5.5-galera

Attachments

Issue Links

is duplicated by

MDEV-13770 client/Server protocol violation caused by galera rerunning rolled back Stored Procedure call

Closed

Activity

Ascending order - Click to sort in descending order

Jan Lindström (Inactive) added a comment - 2013-12-02 11:34

There is something wrong with MDL and triggers/functions:

131202 11:30:31 [Note] WSREP: Set WSREPXid for InnoDB:  601afc4c-5b34-11e3-be7d-e70496efd964:0

131202 11:30:31 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

131202 11:30:31 [Note] WSREP: Assign initial position for certification: 0, protocol version: 2

131202 11:30:31 [Note] WSREP: Synchronized with group, ready for connections

131202 11:30:31 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

131202 11:30:31 [Note] WSREP: TO BEGIN: -1, 0 : CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB

131202 11:30:31 [Note] WSREP: TO BEGIN: 1, 2

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 1, 2 : CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB

131202 11:30:32 [Note] WSREP: TO END: 1

131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1

131202 11:30:32 [Note] WSREP: TO BEGIN: 2, 2

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 2, 2 : CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1

131202 11:30:32 [Note] WSREP: TO END: 2

131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE FUNCTION func1() RETURNS INTEGER RETURN 4

131202 11:30:32 [Note] WSREP: TO BEGIN: 3, 2

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 3, 2 : CREATE FUNCTION func1() RETURNS INTEGER RETURN 4

131202 11:30:32 [Note] WSREP: TO END: 3

131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE FUNCTION func2() RETURNS INTEGER RETURN 4

131202 11:30:32 [Note] WSREP: TO BEGIN: 4, 2

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 4, 2 : CREATE FUNCTION func2() RETURNS INTEGER RETURN 4

131202 11:30:32 [Note] WSREP: TO END: 4

131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE PROCEDURE proc1() SELECT func1() FROM v1

131202 11:30:32 [Note] WSREP: TO BEGIN: 5, 2

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 5, 2 : CREATE PROCEDURE proc1() SELECT func1() FROM v1

131202 11:30:32 [Note] WSREP: TO END: 5

131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1

131202 11:30:32 [Note] WSREP: TO BEGIN: 6, 2

131202 11:30:32 [Note] WSREP: MDL conflict

request: (5 	seqno 6 	wsrep (2, 1, 0) cmd 3 99 	CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1)

granted: (6 	seqno -1 	wsrep (0, 1, 0) cmd 3 0 	SELECT func1() FROM v1)

131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock)

131202 11:30:32 [Note] WSREP: MDL conflict-> BF abort

request: (5 	seqno 6 	wsrep (2, 1, 0) cmd 3 99 	CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1)

granted: (6 	seqno -1 	wsrep (0, 1, 0) cmd 3 0 	SELECT func1() FROM v1)

131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock)

131202 11:30:32 [Note] WSREP: wsrep_abort_thd, by: 139995269011200, victim: 139995268712192

131202 11:30:32 [Note] WSREP: abort transaction: BF: CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 victim: SELECT func1() FROM v1

131202 11:30:32 [Note] WSREP: victim does not have transaction

131202 11:30:32 [Note] WSREP: MDL conflict

request: (5 	seqno 6 	wsrep (2, 1, 0) cmd 3 99 	CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1)

granted: (6 	seqno -1 	wsrep (0, 1, 1) cmd 3 0 	SELECT func1() FROM v1)

131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock)

131202 11:30:32 [Note] WSREP: MDL conflict-> BF abort

request: (5 	seqno 6 	wsrep (2, 1, 0) cmd 3 99 	CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1)

granted: (6 	seqno -1 	wsrep (0, 1, 1) cmd 3 0 	SELECT func1() FROM v1)

131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock)

131202 11:30:32 [Note] WSREP: wsrep_abort_thd, by: 139995269011200, victim: 139995268712192

131202 11:30:32 [Note] WSREP: abort transaction: BF: CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 victim: SELECT func1() FROM v1

131202 11:30:32 [Note] WSREP: victim does not have transaction

131202 11:30:32 [Note] WSREP: MDL victim killed at add_ticket

131202 11:30:32 [Note] WSREP: client rollback due to BF abort for (6), query: CALL proc1()

131202 11:30:32 [Note] WSREP: abort in exec query state, avoiding autocommit

131202 11:30:32 [Note] WSREP: wsrep retrying AC query: CALL proc1()

131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1

131202 11:30:32 [Note] WSREP: TO END: 6, 2 : CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1

131202 11:30:32 [Note] WSREP: TO END: 6

131202 11:30:32 [Note] WSREP: skipping select metadata

131202 11:30:32 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0  errno 0 SQL CALL proc1()

Jan Lindström (Inactive) added a comment - 2013-12-02 11:34 There is something wrong with MDL and triggers/functions: 131202 11:30:31 [Note] WSREP: Set WSREPXid for InnoDB: 601afc4c-5b34-11e3-be7d-e70496efd964:0 131202 11:30:31 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 131202 11:30:31 [Note] WSREP: Assign initial position for certification: 0, protocol version: 2 131202 11:30:31 [Note] WSREP: Synchronized with group, ready for connections 131202 11:30:31 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 131202 11:30:31 [Note] WSREP: TO BEGIN: -1, 0 : CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB 131202 11:30:31 [Note] WSREP: TO BEGIN: 1, 2 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 1, 2 : CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB 131202 11:30:32 [Note] WSREP: TO END: 1 131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1 131202 11:30:32 [Note] WSREP: TO BEGIN: 2, 2 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 2, 2 : CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1 131202 11:30:32 [Note] WSREP: TO END: 2 131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE FUNCTION func1() RETURNS INTEGER RETURN 4 131202 11:30:32 [Note] WSREP: TO BEGIN: 3, 2 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 3, 2 : CREATE FUNCTION func1() RETURNS INTEGER RETURN 4 131202 11:30:32 [Note] WSREP: TO END: 3 131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE FUNCTION func2() RETURNS INTEGER RETURN 4 131202 11:30:32 [Note] WSREP: TO BEGIN: 4, 2 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 4, 2 : CREATE FUNCTION func2() RETURNS INTEGER RETURN 4 131202 11:30:32 [Note] WSREP: TO END: 4 131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE PROCEDURE proc1() SELECT func1() FROM v1 131202 11:30:32 [Note] WSREP: TO BEGIN: 5, 2 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 5, 2 : CREATE PROCEDURE proc1() SELECT func1() FROM v1 131202 11:30:32 [Note] WSREP: TO END: 5 131202 11:30:32 [Note] WSREP: TO BEGIN: -1, 0 : CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 131202 11:30:32 [Note] WSREP: TO BEGIN: 6, 2 131202 11:30:32 [Note] WSREP: MDL conflict request: (5 seqno 6 wsrep (2, 1, 0) cmd 3 99 CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1) granted: (6 seqno -1 wsrep (0, 1, 0) cmd 3 0 SELECT func1() FROM v1) 131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock) 131202 11:30:32 [Note] WSREP: MDL conflict-> BF abort request: (5 seqno 6 wsrep (2, 1, 0) cmd 3 99 CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1) granted: (6 seqno -1 wsrep (0, 1, 0) cmd 3 0 SELECT func1() FROM v1) 131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock) 131202 11:30:32 [Note] WSREP: wsrep_abort_thd, by: 139995269011200, victim: 139995268712192 131202 11:30:32 [Note] WSREP: abort transaction: BF: CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 victim: SELECT func1() FROM v1 131202 11:30:32 [Note] WSREP: victim does not have transaction 131202 11:30:32 [Note] WSREP: MDL conflict request: (5 seqno 6 wsrep (2, 1, 0) cmd 3 99 CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1) granted: (6 seqno -1 wsrep (0, 1, 1) cmd 3 0 SELECT func1() FROM v1) 131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock) 131202 11:30:32 [Note] WSREP: MDL conflict-> BF abort request: (5 seqno 6 wsrep (2, 1, 0) cmd 3 99 CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1) granted: (6 seqno -1 wsrep (0, 1, 1) cmd 3 0 SELECT func1() FROM v1) 131202 11:30:32 [Note] WSREP: MDL ticket: type: shared read space: TABLE db: test name: v1 (Waiting for table metadata lock) 131202 11:30:32 [Note] WSREP: wsrep_abort_thd, by: 139995269011200, victim: 139995268712192 131202 11:30:32 [Note] WSREP: abort transaction: BF: CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 victim: SELECT func1() FROM v1 131202 11:30:32 [Note] WSREP: victim does not have transaction 131202 11:30:32 [Note] WSREP: MDL victim killed at add_ticket 131202 11:30:32 [Note] WSREP: client rollback due to BF abort for (6), query: CALL proc1() 131202 11:30:32 [Note] WSREP: abort in exec query state, avoiding autocommit 131202 11:30:32 [Note] WSREP: wsrep retrying AC query: CALL proc1() 131202 11:30:32 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1 131202 11:30:32 [Note] WSREP: TO END: 6, 2 : CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1 131202 11:30:32 [Note] WSREP: TO END: 6 131202 11:30:32 [Note] WSREP: skipping select metadata 131202 11:30:32 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0 errno 0 SQL CALL proc1()

Andrii Nikitin (Inactive) added a comment - 2017-09-27 18:19

While working on ~~MDEV-13770~~ - I was trying to reproduce this on 10.0-galera, 10.1 and 10.2 and the test in suites/galera/t doesn't show any errors:

--source include/galera_cluster.inc

--source include/have_innodb.inc

# --source include/have_binlog_format_row.inc

# SET GLOBAL lock_wait_timeout = 2;

# SET GLOBAL innodb_lock_wait_timeout = 1;

CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;

CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1;

CREATE FUNCTION func1() RETURNS INTEGER RETURN 4;

CREATE FUNCTION func2() RETURNS INTEGER RETURN 4;

CREATE PROCEDURE proc1() SELECT func1() FROM v1;

--connect (con1,localhost,root,,test)

--connect (con2,localhost,root,,test)

--let $run = 100

while ($run)

  --connection con1

  --send

          CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1;

  --connection con2

          CALL proc1();

  --connection con1

  --reap

  --dec $run

So, I suggest closing this for now.
For those who observe 2027 (Malformed packet) with stored programs in Galera - my idea is to try as workaround option wsrep_retry_autocommit=0

Andrii Nikitin (Inactive) added a comment - 2017-09-27 18:19 While working on MDEV-13770 - I was trying to reproduce this on 10.0-galera, 10.1 and 10.2 and the test in suites/galera/t doesn't show any errors: --source include/galera_cluster.inc --source include/have_innodb.inc # --source include/have_binlog_format_row.inc # SET GLOBAL lock_wait_timeout = 2; # SET GLOBAL innodb_lock_wait_timeout = 1; CREATE TABLE t1 (pk INT PRIMARY KEY ) ENGINE=InnoDB; CREATE OR REPLACE VIEW v1 AS SELECT pk FROM t1; CREATE FUNCTION func1() RETURNS INTEGER RETURN 4; CREATE FUNCTION func2() RETURNS INTEGER RETURN 4; CREATE PROCEDURE proc1() SELECT func1() FROM v1; --connect (con1,localhost,root,,test) --connect (con2,localhost,root,,test) --let $run = 100 while ($run) { --connection con1 --send CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT func2() AS pk FROM t1; --connection con2 CALL proc1(); --connection con1 --reap --dec $run } So, I suggest closing this for now. For those who observe 2027 (Malformed packet) with stored programs in Galera - my idea is to try as workaround option wsrep_retry_autocommit=0

Andrii Nikitin (Inactive) added a comment - 2017-09-28 10:26

I wasn't able to reproduce yesterday while running single test, but today I got the error on 10.0-galera while running whole suite:

galera.MDEV-13770 'xtradb'               w2 [ fail ]

        Test ended at 2017-09-28 12:13:16

CURRENT_TEST: galera.MDEV-13770

mysqltest: At line 28: query 'CALL proc1()' failed: 2027: Malformed packet

 - saving '/home/a/env1/m9-10.0-galera/build/mysql-test/var/2/log/galera.MDEV-13770-xtradb/' to '/home/a/env1/m9-10.0-galera/build/mysql-test/var/log/galera.MDEV-13770-xtradb/'

Andrii Nikitin (Inactive) added a comment - 2017-09-28 10:26 I wasn't able to reproduce yesterday while running single test, but today I got the error on 10.0-galera while running whole suite: galera.MDEV-13770 'xtradb' w2 [ fail ] Test ended at 2017-09-28 12:13:16 CURRENT_TEST: galera.MDEV-13770 mysqltest: At line 28: query 'CALL proc1()' failed: 2027: Malformed packet - saving '/home/a/env1/m9-10.0-galera/build/mysql-test/var/2/log/galera.MDEV-13770-xtradb/' to '/home/a/env1/m9-10.0-galera/build/mysql-test/var/log/galera.MDEV-13770-xtradb/'

Andrii Nikitin (Inactive) added a comment - 2017-10-03 08:57

The problem has been reported to codership https://github.com/codership/mysql-wsrep/issues/313

Andrii Nikitin (Inactive) added a comment - 2017-10-03 08:57 The problem has been reported to codership https://github.com/codership/mysql-wsrep/issues/313

MariaDB Server

Galera: Malformed packet, ER_SP_DOES_NOT_EXIST or ER_VIEW_INVALID on concurrent view creation and procedure call

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration