TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history_long;
################################################
# Quering PS statement summary and history_long#
################################################ 
# SET-UP 
CREATE DATABASE stored_programs;
USE stored_programs;
CREATE TABLE t1(
i INT NOT NULL,
j INT
);
CREATE TABLE t2(
name   CHAR(16) NOT NULL DEFAULT '',
id INT NOT NULL
);
CREATE TABLE t3(
d DATE,
n INT,
f DOUBLE,
s VARCHAR(32)
);
CREATE TABLE t4(
`k` int(10) unsigned NOT NULL AUTO_INCREMENT,
`word` varchar(100) NOT NULL,
`mean` varchar(300) NOT NULL,
PRIMARY KEY (`k`)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
############################
# Creating Stored Programs #
############################
# Stored Routine ( Procedure & Function )
CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
BEGIN
INSERT INTO t1 VALUES (x1, y);
INSERT INTO t1 VALUES (x2, y);
END|
CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
BEGIN
DECLARE z1, z2 INT;
SET z1 = y;
SET z2 = z1+2;
INSERT INTO t2 VALUES (x, z2);
END|
CREATE PROCEDURE SampleProc3()
BEGIN
DECLARE ld DATE;
DECLARE li INT;
DECLARE lf DOUBLE;
DECLARE ls VARCHAR(32);
SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
INSERT INTO t3 VALUES (ld, li, lf, ls);
INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1,    "ld is null"),
((li IS NULL), 1,    "li is null"),
((li = 0),     NULL, "li = 0"),
((lf IS NULL), 1,    "lf is null"),
((lf = 0),     NULL, "lf = 0"),
((ls IS NULL), 1,    "ls is null");
END|
CREATE PROCEDURE SampleProc4()
BEGIN
DECLARE x  INT;
SET x = 1;
WHILE x  <= 2 DO
INSERT INTO t4(word, mean) VALUES('a','a mean');
SET  x = x + 1;
END WHILE;
END|
CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
RETURN concat(s1, s2)|
CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = (n1+n2+n3*2+n4*4)/8;
RETURN avg;
END|
CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE f BIGINT UNSIGNED DEFAULT 1;
WHILE n > 1 DO
SET f = f * n;
SET n = n - 1;
END WHILE;
RETURN f;
END|
# Triggers
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
if isnull(new.j) then
SET new.j:= new.i * 10;
END if;
END|
CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
BEGIN
UPDATE t1 SET i=new.id+i ;
END|
CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
SET @change:= @change + new.id - old.id|
CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
SET @del:= @del + 1|
CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW 
SET @del:= @del + 8 + old.j|
#####################
# Executing queries #
#####################
INSERT INTO t1 VALUES (10,20);
CALL SampleProc1(30,40,50);
SET @a=1;
SELECT @a;
@a
1
CALL SampleProc2("Jwalamukhi",34);
SELECT @a;
@a
1
CALL SampleProc3();
CALL SampleProc4();
SET @change=1;
SELECT @change;
@change
1
UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
SELECT @change;
@change
-13
SET @del=1;
SELECT @del;
@del
1
DELETE FROM t1 WHERE i=76;
SELECT @del;
@del
60
SELECT wt_avg(1, 12, 1990, 1121990);
wt_avg(1, 12, 1990, 1121990)
561494
SELECT fac(5);
fac(5)
120
SELECT append("Bolly", "wood");
append("Bolly", "wood")
Bollywood
TRUNCATE TABLE performance_schema.events_statements_history_long;
# Event
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE user = 'event_scheduler' AND command = 'Daemon';
COUNT(*) = 1
1
Warnings:
Warning	1287	'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
CREATE TABLE table_t(a INT);
CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
BEGIN
INSERT INTO table_t VALUES(1);
END|
SELECT * FROM table_t;
a
1
SET GLOBAL event_scheduler=OFF;
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS 
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COUNT_STAR	COUNT_STATEMENTS
FUNCTION	stored_programs	append	1	1
EVENT	stored_programs	e1	1	1
FUNCTION	stored_programs	fac	1	19
PROCEDURE	stored_programs	SampleProc1	1	2
PROCEDURE	stored_programs	SampleProc2	1	5
PROCEDURE	stored_programs	SampleProc3	1	10
PROCEDURE	stored_programs	SampleProc4	1	11
TRIGGER	stored_programs	trg1	3	3
TRIGGER	stored_programs	trg2	1	1
TRIGGER	stored_programs	trg3	1	1
TRIGGER	stored_programs	trg4	1	1
TRIGGER	stored_programs	trg5	1	1
FUNCTION	stored_programs	wt_avg	1	3
SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
performance_schema.events_statements_history_long WHERE
CURRENT_SCHEMA='stored_programs' AND 
(SQL_TEXT not like '%count(*) = 1%' OR SQL_TEXT IS NULL)
ORDER BY OBJECT_NAME, NESTING_EVENT_LEVEL, SQL_TEXT, EVENT_NAME;
EVENT_NAME	SQL_TEXT	CURRENT_SCHEMA	OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	NESTING_EVENT_TYPE	NESTING_EVENT_LEVEL
statement/scheduler/event	NULL	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/create_event	CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
BEGIN
INSERT INTO table_t VALUES(1);
END	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/create_table	CREATE TABLE table_t(a INT)	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/select	SELECT * FROM table_t	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/select	SELECT 5 * 10	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/select	SELECT COUNT(*) = 0 FROM information_schema.processlist
WHERE user = 'event_scheduler'	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/select	SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS 
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/set_option	SET GLOBAL event_scheduler=OFF	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/show_warnings	SHOW WARNINGS	stored_programs	NULL	NULL	NULL	NULL	0
statement/sql/truncate	TRUNCATE TABLE performance_schema.events_statements_history_long	stored_programs	NULL	NULL	NULL	NULL	0
statement/sp/stmt	INSERT INTO table_t VALUES(1)	stored_programs	EVENT	stored_programs	e1	STATEMENT	1
# clean -up
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history_long;
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS 
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COUNT_STAR	COUNT_STATEMENTS
FUNCTION	stored_programs	append	0	0
EVENT	stored_programs	e1	0	0
FUNCTION	stored_programs	fac	0	0
PROCEDURE	stored_programs	SampleProc1	0	0
PROCEDURE	stored_programs	SampleProc2	0	0
PROCEDURE	stored_programs	SampleProc3	0	0
PROCEDURE	stored_programs	SampleProc4	0	0
TRIGGER	stored_programs	trg1	0	0
TRIGGER	stored_programs	trg2	0	0
TRIGGER	stored_programs	trg3	0	0
TRIGGER	stored_programs	trg4	0	0
TRIGGER	stored_programs	trg5	0	0
FUNCTION	stored_programs	wt_avg	0	0
DROP PROCEDURE SampleProc1;
DROP PROCEDURE SampleProc2;
DROP PROCEDURE SampleProc3;
DROP PROCEDURE SampleProc4;
DROP FUNCTION wt_avg;
DROP FUNCTION fac;
DROP FUNCTION append;
DROP TRIGGER trg1;
DROP TRIGGER trg2;
DROP TRIGGER trg3;
DROP TRIGGER trg4;
DROP TRIGGER trg5;
DROP EVENT IF EXISTS e1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE table_t;
DROP DATABASE stored_programs;
SET GLOBAL event_scheduler=ON;
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS 
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COUNT_STAR	COUNT_STATEMENTS
# SET-UP 
CREATE DATABASE stored_programs;
USE stored_programs;
CREATE TABLE t1(
i INT NOT NULL,
j INT
);
CREATE TABLE t2(
name   CHAR(16) NOT NULL DEFAULT '',
id INT NOT NULL
);
CREATE TABLE t3(
d DATE,
n INT,
f DOUBLE,
s VARCHAR(32)
);
CREATE TABLE t4(
`k` int(10) unsigned NOT NULL AUTO_INCREMENT,
`word` varchar(100) NOT NULL,
`mean` varchar(300) NOT NULL,
PRIMARY KEY (`k`)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
############################
# Creating Stored Programs #
############################
# Stored Routine ( Procedure & Function )
CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
BEGIN
INSERT INTO t1 VALUES (x1, y);
INSERT INTO t1 VALUES (x2, y);
END|
CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
BEGIN
DECLARE z1, z2 INT;
SET z1 = y;
SET z2 = z1+2;
INSERT INTO t2 VALUES (x, z2);
END|
CREATE PROCEDURE SampleProc3()
BEGIN
DECLARE ld DATE;
DECLARE li INT;
DECLARE lf DOUBLE;
DECLARE ls VARCHAR(32);
SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
INSERT INTO t3 VALUES (ld, li, lf, ls);
INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1,    "ld is null"),
((li IS NULL), 1,    "li is null"),
((li = 0),     NULL, "li = 0"),
((lf IS NULL), 1,    "lf is null"),
((lf = 0),     NULL, "lf = 0"),
((ls IS NULL), 1,    "ls is null");
END|
CREATE PROCEDURE SampleProc4()
BEGIN
DECLARE x  INT;
SET x = 1;
WHILE x  <= 2 DO
INSERT INTO t4(word, mean) VALUES('a','a mean');
SET  x = x + 1;
END WHILE;
END|
CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
RETURN concat(s1, s2)|
CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = (n1+n2+n3*2+n4*4)/8;
RETURN avg;
END|
CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE f BIGINT UNSIGNED DEFAULT 1;
WHILE n > 1 DO
SET f = f * n;
SET n = n - 1;
END WHILE;
RETURN f;
END|
# Triggers
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
if isnull(new.j) then
SET new.j:= new.i * 10;
END if;
END|
CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
BEGIN
UPDATE t1 SET i=new.id+i ;
END|
CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
SET @change:= @change + new.id - old.id|
CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
SET @del:= @del + 1|
CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW 
SET @del:= @del + 8 + old.j|
update performance_schema.setup_instruments set enabled='YES', timed='NO'
  where name like "statement/sp/%" order by name;
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history_long;
#####################
# Executing queries #
#####################
INSERT INTO t1 VALUES (10,20);
CALL SampleProc1(30,40,50);
SET @a=1;
SELECT @a;
@a
1
CALL SampleProc2("Jwalamukhi",34);
SELECT @a;
@a
1
CALL SampleProc3();
CALL SampleProc4();
SET @change=1;
SELECT @change;
@change
1
UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
SELECT @change;
@change
-13
SET @del=1;
SELECT @del;
@del
1
DELETE FROM t1 WHERE i=76;
SELECT @del;
@del
60
SELECT wt_avg(1, 12, 1990, 1121990);
wt_avg(1, 12, 1990, 1121990)
561494
SELECT fac(5);
fac(5)
120
SELECT append("Bolly", "wood");
append("Bolly", "wood")
Bollywood
TRUNCATE TABLE performance_schema.events_statements_history_long;
# Event
SELECT COUNT(*) = 1 FROM information_schema.processlist
WHERE user = 'event_scheduler' AND command = 'Daemon';
COUNT(*) = 1
1
Warnings:
Warning	1287	'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
CREATE TABLE table_t(a INT);
CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND DO
BEGIN
INSERT INTO table_t VALUES(1);
END|
SELECT * FROM table_t;
a
1
SET GLOBAL event_scheduler=OFF;
SELECT EVENT_NAME, TIMER_START, TIMER_END, TIMER_WAIT FROM
performance_schema.events_statements_history_long WHERE
CURRENT_SCHEMA='stored_programs' AND EVENT_NAME like "statement/sp/%";
EVENT_NAME	TIMER_START	TIMER_END	TIMER_WAIT
statement/sp/stmt	NULL	NULL	NULL
update performance_schema.setup_instruments set enabled='YES', timed='YES'
  where name like "statement/sp/%" order by name;
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history_long;
DROP PROCEDURE SampleProc1;
DROP PROCEDURE SampleProc2;
DROP PROCEDURE SampleProc3;
DROP PROCEDURE SampleProc4;
DROP FUNCTION wt_avg;
DROP FUNCTION fac;
DROP FUNCTION append;
DROP TRIGGER trg1;
DROP TRIGGER trg2;
DROP TRIGGER trg3;
DROP TRIGGER trg4;
DROP TRIGGER trg5;
DROP EVENT IF EXISTS e1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE table_t;
DROP DATABASE stored_programs;
SET GLOBAL event_scheduler=ON;
# set-up
CREATE DATABASE sp;
USE sp;
CREATE TABLE t1(
a INT,
b INT
);
# let the creation of the following stored programs fail 
CREATE PROCEDURE fail1(IN a INT  OUT x CHAR(16)) 
SET a=1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUT x CHAR(16)) 
SET a=1' at line 1
CREATE FUNCTION fail2(a INT , b INT) RETURNS INT
x=SELECT COUNT(*) FROM t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=SELECT COUNT(*) FROM t' at line 2
CREATE EVENT fail3 SCHEDULE EVERY MICROSECOND DO
DROP TABLE t;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SCHEDULE EVERY MICROSECOND DO
DROP TABLE t' at line 1
# the below query on PS table doesn't show any rows 
# as the creation of stored programs failed 
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='sp';
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COUNT_STAR	COUNT_STATEMENTS
# create few stored programs 
CREATE PROCEDURE p(x1 INT, x2 INT)
BEGIN
INSERT INTO t1 VALUES (x1, x2);
END|
CREATE FUNCTION f(y1 INT, y2 INT) RETURNS INT
RETURN y1+y2;
CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
SET @neg=-1;
# execute the created stored programs such that they fail.
CALL p(7);
ERROR 42000: Incorrect number of arguments for PROCEDURE sp.p; expected 2, got 1
SELECT f("add",1,3);
ERROR 42000: Incorrect number of arguments for FUNCTION sp.f; expected 2, got 3
INSERT INTO t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
# the below query on PS table doesn't expose any statistics as 
# execution of the created stored porgrams failed.
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='sp';
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COUNT_STAR	COUNT_STATEMENTS
PROCEDURE	sp	p	0	0
FUNCTION	sp	f	0	0
#clean-up
DROP PROCEDURE p;
DROP FUNCTION f;
DROP TRIGGER trg;
DROP TABLE t1;
DROP DATABASE sp;