mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-03 20:36:16 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/perfschema/r/prepared_statements.result
Daniel Black 55227234cc
 MDEV-26872 perfschema.prepared_statements non-deterministic test failure (#2290) 
Correct by ORDER BY in the output.
2022-10-19 09:52:16 +01:00

672 lines
22 KiB
Text
Raw Permalink Blame History

CREATE DATABASE db;
USE db;
TRUNCATE TABLE performance_schema.prepared_statements_instances ;
TRUNCATE TABLE performance_schema.events_statements_history_long ;
CREATE TABLE t1 (a INT NOT NULL);
INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @table = 't1';
SET @s = CONCAT('SELECT * FROM ', @table);
PREPARE st2 FROM @s;
PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?';
PREPARE st4 FROM
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st1
SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st2
SQL_TEXT SELECT * FROM t1
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st3
SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=?
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st4
SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/create_table
SQL_TEXT CREATE TABLE t1 (a INT NOT NULL)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/insert
SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @table = 't1'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st2 FROM @s
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st4 FROM
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
SET @a = 3;
SET @b = 4;
EXECUTE st1 USING @a, @b;
hypotenuse
5
EXECUTE st2;
a
4
8
11
32
80
SET @c=3;
EXECUTE st3 using @c;
EXECUTE st4;
a
4
8
11
14
18
21
32
42
80
90
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st1
SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 1
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st2
SQL_TEXT SELECT * FROM t1
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 5
SUM_SELECT_SCAN 1
SUM_NO_INDEX_USED 1
STATEMENT_NAME st3
SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=?
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 1
SUM_NO_INDEX_USED 1
STATEMENT_NAME st4
SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 10
SUM_SELECT_SCAN 3
SUM_NO_INDEX_USED 1
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/create_table
SQL_TEXT CREATE TABLE t1 (a INT NOT NULL)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/insert
SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @table = 't1'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st2 FROM @s
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st4 FROM
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @a = 3
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @b = 4
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st1 USING @a, @b
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st2
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @c=3
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st3 using @c
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st4
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
TRUNCATE TABLE performance_schema.prepared_statements_instances ;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st1
SQL_TEXT SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st2
SQL_TEXT SELECT * FROM t1
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st3
SQL_TEXT INSERT INTO t1 SELECT * FROM t1 WHERE a<=?
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
STATEMENT_NAME st4
SQL_TEXT (SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
DEALLOCATE PREPARE st1;
DEALLOCATE PREPARE st2;
DEALLOCATE PREPARE st3;
DEALLOCATE PREPARE st4;
DROP TABLE t1;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/create_table
SQL_TEXT CREATE TABLE t1 (a INT NOT NULL)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/insert
SQL_TEXT INSERT INTO t1 VALUES (4), (8), (11), (32), (80)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @table = 't1'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @s = CONCAT('SELECT * FROM ', @table)
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st2 FROM @s
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/prepare_sql
SQL_TEXT PREPARE st4 FROM
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @a = 3
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @b = 4
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st1 USING @a, @b
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st2
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/set_option
SQL_TEXT SET @c=3
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st3 using @c
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/execute_sql
SQL_TEXT EXECUTE st4
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db'
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.prepared_statements_instances
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/dealloc_sql
SQL_TEXT DEALLOCATE PREPARE st1
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/dealloc_sql
SQL_TEXT DEALLOCATE PREPARE st2
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/dealloc_sql
SQL_TEXT DEALLOCATE PREPARE st3
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/dealloc_sql
SQL_TEXT DEALLOCATE PREPARE st4
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/drop_table
SQL_TEXT DROP TABLE t1
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
EVENT_NAME statement/sql/select
SQL_TEXT SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
TRUNCATE TABLE performance_schema.events_statements_history_long ;
UPDATE performance_schema.setup_consumers SET ENABLED = 'NO';
CREATE TABLE t1 (a INT NOT NULL);
INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @table = 't1';
SET @s = CONCAT('SELECT * FROM ', @table);
PREPARE st2 FROM @s;
PREPARE st3 FROM 'INSERT INTO t1 SELECT * FROM t1 WHERE a<=?';
PREPARE st4 FROM
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
SET @a = 3;
SET @b = 4;
EXECUTE st1 USING @a, @b;
hypotenuse
5
EXECUTE st2;
a
4
8
11
32
80
SET @c=3;
EXECUTE st3 using @c;
EXECUTE st4;
a
4
8
11
14
18
21
32
42
80
90
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
DEALLOCATE PREPARE st1;
DEALLOCATE PREPARE st2;
DEALLOCATE PREPARE st3;
DEALLOCATE PREPARE st4;
DROP TABLE t1;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
SELECT EVENT_NAME, SQL_TEXT, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA='db' ;
EVENT_NAME statement/sql/truncate
SQL_TEXT TRUNCATE TABLE performance_schema.events_statements_history_long
OBJECT_TYPE NULL
OBJECT_SCHEMA NULL
OBJECT_NAME NULL
TRUNCATE TABLE performance_schema.events_statements_history_long ;
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
PREPARE st FROM 'SELECT SUM(1000 + ?) AS total';
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d=100;
EXECUTE st USING @d;
total
1100
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 1
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d = @d + 100;
EXECUTE st USING @d;
total
1200
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 2
SUM_ROWS_SENT 2
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d = @d + 100;
EXECUTE st USING @d;
total
1300
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 3
SUM_ROWS_SENT 3
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d = @d + 100;
EXECUTE st USING @d;
total
1400
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 4
SUM_ROWS_SENT 4
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d = @d + 100;
EXECUTE st USING @d;
total
1500
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 5
SUM_ROWS_SENT 5
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
SET @d = @d + 100;
EXECUTE st USING @d;
total
1600
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 6
SUM_ROWS_SENT 6
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
TRUNCATE TABLE performance_schema.prepared_statements_instances ;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
WHERE NAME like "statement/sql/execute%";
SET @d=3274;
EXECUTE st USING @d;
total
4274
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT SUM(1000 + ?) AS total
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO'
WHERE NAME like "statement/sql/prepare%";
DEALLOCATE PREPARE st;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME like "statement/sql/prepare%";
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME like "statement/sql/execute%";
CREATE TABLE tab(
Id INT,
name CHAR(10),
age INT
);
INSERT INTO tab VALUES(1,"Nakshatr",25),(2,"chanda",24),(3,"tejas",78);
PREPARE st FROM 'SELECT * FROM tab';
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT * FROM tab
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
EXECUTE st;
Id name age
1 Nakshatr 25
2 chanda 24
3 tejas 78
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT * FROM tab
COUNT_REPREPARE 0
COUNT_EXECUTE 1
SUM_ROWS_SENT 3
SUM_SELECT_SCAN 1
SUM_NO_INDEX_USED 1
ALTER TABLE tab DROP COLUMN age;
EXECUTE st;
Id name
1 Nakshatr
2 chanda
3 tejas
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT * FROM tab
COUNT_REPREPARE 1
COUNT_EXECUTE 2
SUM_ROWS_SENT 6
SUM_SELECT_SCAN 2
SUM_NO_INDEX_USED 2
ALTER TABLE tab ADD COLUMN age INT NULL;
EXECUTE st;
Id name age
1 Nakshatr NULL
2 chanda NULL
3 tejas NULL
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT * FROM tab
COUNT_REPREPARE 2
COUNT_EXECUTE 3
SUM_ROWS_SENT 9
SUM_SELECT_SCAN 3
SUM_NO_INDEX_USED 3
TRUNCATE TABLE performance_schema.prepared_statements_instances ;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
STATEMENT_NAME st
SQL_TEXT SELECT * FROM tab
COUNT_REPREPARE 0
COUNT_EXECUTE 0
SUM_ROWS_SENT 0
SUM_SELECT_SCAN 0
SUM_NO_INDEX_USED 0
DEALLOCATE PREPARE st;
SELECT STATEMENT_NAME, SQL_TEXT, COUNT_REPREPARE, COUNT_EXECUTE, SUM_ROWS_SENT, SUM_SELECT_SCAN, SUM_NO_INDEX_USED FROM performance_schema.prepared_statements_instances ORDER BY STATEMENT_NAME, SQL_TEXT;
TRUNCATE TABLE performance_schema.prepared_statements_instances ;
TRUNCATE TABLE performance_schema.events_statements_history_long ;
DROP TABLE tab;
DROP DATABASE db;
Reference in a new issue View git blame Copy permalink