Bug #45420 Performance Schema: can't update setup_instruments in a loop

• Submitted: 9 Jun 2009 23:39
• Modified: 24 Jul 2009 19:03
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: mysql-6.0-perfschema
• OS: Linux (SUSE 10 / 64-bit)
• Assigned to: Marc ALFF
• CPU Architecture: Any

[9 Jun 2009 23:39] Peter Gulutzan

Description:
I'm using mysql-6.0-perfschema.
I start the server with --performance_schema=1 --log-bin=x5.

I create a procedure which, in a loop, says:
UPDATE performance_schema.SETUP_INSTRUMENTS set timed='YES';
On the first iteration of the loop: success.
On the second iteration of the loop: failure.

How to repeat:
use test
delimiter //
drop procedure if exists p2//
create procedure p2 ()
begin
  declare v int default 0;
  while v < 100 do
    select v;
    UPDATE performance_schema.SETUP_INSTRUMENTS set timed='YES';
    set v = v + 1;
    end while;
  end//
call p2()//

Sample result:

linux:/data1/mysql-6.0-perfschema # bin/mysql --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 6.0.12-alpha-log-perfschema Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> delimiter //
mysql> drop procedure if exists p2//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p2 ()
    -> begin                 
    ->   declare v int default 0;
    ->   while v < 100 do        
    ->     select v;             
    ->     UPDATE performance_schema.SETUP_INSTRUMENTS set timed='YES';
    ->     set v = v + 1;
    ->     end while;
    ->   end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p2()//
+------+
| v    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

+------+
| v    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

ERROR 1031 (HY000): Table storage engine for 'SETUP_INSTRUMENTS' doesn't have this option

[10 Jun 2009 14:41] MySQL Verification Team

Thank you for the bug report.

D:\tmp>bin\mysql -uroot --port=3340
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.12-alpha-Win X64-perfschema Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test
Database changed
mysql> delimiter //
mysql> drop procedure if exists p2//
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> create procedure p2 ()
    -> begin
    ->   declare v int default 0;
    ->   while v < 100 do
    ->     select v;
    ->     UPDATE performance_schema.SETUP_INSTRUMENTS set timed='YES';
    ->     set v = v + 1;
    ->     end while;
    ->   end//
Query OK, 0 rows affected (0.05 sec)

mysql> call p2()//
+------+
| v    |
+------+
|    0 |
+------+
1 row in set (0.04 sec)

ERROR 1146 (42S02): Table 'performance_schema.setup_instruments' doesn't exist
mysql>

[24 Jul 2009 19:03] Marc ALFF

The bug can not be repeated using mysql-azalea-perfschema

Script used:

drop procedure if exists p2;

delimiter //;

create procedure p2 ()
begin
  declare v int default 0;
  while v < 100 do
    select v;
    UPDATE performance_schema.SETUP_INSTRUMENTS set timed='YES';
    set v = v + 1;
  end while;
end//

delimiter ;//

call p2();

drop procedure p2;