[MDEV-4237] Galera: Malformed packet, ER_SP_DOES_NOT_EXIST or ER_VIEW_INVALID on concurrent view creation and procedure call Created: 2013-03-04  Updated: 2023-11-03

Status: Open
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 5.5.28a-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Ramesh Sivaraman
Resolution: Unresolved Votes: 0
Labels: galera, need_verification

Issue Links:
Duplicate
is duplicated by MDEV-13770 client/Server protocol violation caus... Closed
Relates

 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



 Comments   
Comment by Jan Lindström (Inactive) [ 2013-12-02 ]

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()

Comment by Andrii Nikitin (Inactive) [ 2017-09-27 ]

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

Comment by Andrii Nikitin (Inactive) [ 2017-09-28 ]

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/'

Comment by Andrii Nikitin (Inactive) [ 2017-10-03 ]

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

Generated at Thu Feb 08 06:54:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.