1# Embedded server doesn't support external clients
2--source include/not_embedded.inc
3
4--echo #
5--echo # MDEV-17429 mysqldump uses 10.3 options with pre-10.3 servers and breaks
6--echo #
7
8# Make sure the server reports itself as 10.2.1-MariaDB
9SELECT @@version;
10
11SET sql_mode=ORACLE;
12CREATE DATABASE db1_mdev17429;
13USE db1_mdev17429;
14
15DELIMITER $$;
16
17CREATE PROCEDURE p1(a INT)
18AS BEGIN
19  NULL;
20END;
21$$
22
23CREATE OR REPLACE PACKAGE employee_tools AS
24  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
25  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
26  PROCEDURE raiseSalaryStd(eid INT);
27  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
28END;
29$$
30CREATE PACKAGE BODY employee_tools AS
31  -- package body variables
32  stdRaiseAmount DECIMAL(10,2):=500;
33
34  -- private routines
35  PROCEDURE log (eid INT, ecmnt TEXT) AS
36  BEGIN
37    INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
38  END;
39
40  -- public routines
41  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)) AS
42    eid INT;
43  BEGIN
44    INSERT INTO employee (name, salary) VALUES (ename, esalary);
45    eid:= last_insert_id();
46    log(eid, 'hire ' || ename);
47  END;
48
49  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
50    nSalary DECIMAL(10,2);
51  BEGIN
52    SELECT salary INTO nSalary FROM employee WHERE id=eid;
53    log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
54    RETURN nSalary;
55  END;
56
57  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)) AS
58  BEGIN
59    UPDATE employee SET salary=salary+amount WHERE id=eid;
60    log(eid, 'raiseSalary id=' || eid || ' amount=' || amount);
61  END;
62
63  PROCEDURE raiseSalaryStd(eid INT) AS
64  BEGIN
65    raiseSalary(eid, stdRaiseAmount);
66    log(eid, 'raiseSalaryStd id=' || eid);
67  END;
68
69BEGIN
70  -- This code is executed when the current session
71  -- accesses any of the package routines for the first time
72  log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
73END;
74$$
75DELIMITER ;$$
76
77# mysqldump output is expected to have standalone PROCEDURE/FUNCTION, but not PACKAGE/PACKAGE BODY.
78
79--replace_regex  /-- MariaDB dump.*[^\n]/-- MariaDB dump DUMPVERSION  Distrib DISTVERSION, for OS/ / on [0-9 :-]+/ on TIMESTAMP/
80--exec $MYSQL_DUMP --quick --routines --triggers --no-create-info --skip-lock-tables --no-data --compress --default-character-set=utf8mb4 -uroot db1_mdev17429
81
82DROP DATABASE db1_mdev17429;
83SET sql_mode=DEFAULT;
84