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