[MDEV-39748] Package function calls are not replicated well in statement and mixed modes - Jira

XML

Word

Printable

I run this test:

--source include/not_embedded.inc

--source include/have_binlog_format_statement.inc

--disable_query_log

reset master; # get rid of previous tests binlog

--enable_query_log

--echo #

--echo #

--echo #

SET sql_mode=ORACLE;

CREATE TABLE t1 (a INT);

DELIMITER $$;

CREATE PACKAGE pkg AS

  PROCEDURE p1;

END;

$$

CREATE PACKAGE BODY pkg AS

  FUNCTION f1 RETURN INT AS

  BEGIN

    INSERT INTO t1 VALUES (10);

    RETURN 0;

  END;

  PROCEDURE p1 AS

    a INT;

  BEGIN

    a:= f1();

  END;

END;

$$

DELIMITER ;$$

CALL pkg.p1();

DROP PACKAGE pkg;

DROP TABLE t1;

--let $binlog_file = LAST

source include/show_binlog_events.inc;

It produces the following outout:

...

master-bin.000001	#	Query	#	#	use `test`; SELECT "test"."pkg.f1"()

...

It's wrong to replicate package function calls this ways, because:

The function is private in the package. So the call for test.pkg.f1() will fail on the slave.
Using test.pkg.f1() for package public functions is still wrong: Packages have variables (kind of members) and the result of the package function call depends on these package variables. It's wrong to call a package function without the context.

The above two problems can be fixed by using per-statement replication for package function calls (like we do for CALL db.pkg.proc()).

Moreover, there is a third problem: the code in sp_head::execute_function() is simply incorrect for package functions:

  if (need_binlog_call)

    binlog_buf.length(0);

    binlog_buf.append(STRING_WITH_LEN("SELECT "));

    append_identifier(thd, &binlog_buf, &m_db);

    binlog_buf.append('.');

    append_identifier(thd, &binlog_buf, &m_name);

    binlog_buf.append('(');

it produces this statement:

SELECT "test"."pkg.f1"()

while for package functions the expected statement is:

SELECT "test"."pkg"."f1"()

(but, as written above, it won't work for all packages anyway. Fixing this can solve the problem only for some packages.)

blocks

MDEV-39518 Allow prepared statements in stored functions in assignment right hand

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