MySQL Bugs: #70025: Update on P_S setup_consumers and threads through JOIN only updates first row

Bug #70025	Update on P_S setup_consumers and threads through JOIN only updates first row
Submitted:	14 Aug 2013 7:00	Modified:	26 Nov 2013 18:57
Reporter:	Jesper wisborg Krogh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.13	OS:	Linux (Oracle Linux 6)
Assigned to:		CPU Architecture:	Any
Tags:	P_S

Description:
When an update on either the setup_consumers or threads table in the Performance Schema is updated through a JOIN, then only the first matching row is updated rather than all matching rows.

Interestingly enough, setup_instruments does not show this behaviour.

How to repeat:
CALL ps_tools.ps_reset_setup(FALSE);
DROP DATABASE IF EXISTS pstest;
CREATE DATABASE pstest;
use pstest;

SELECT * FROM performance_schema.setup_consumers;
DROP TEMPORARY TABLE IF EXISTS tmp_setup_consumers;

CREATE TEMPORARY TABLE tmp_setup_consumers
  LIKE performance_schema.setup_consumers;

INSERT INTO tmp_setup_consumers
SELECT * FROM performance_schema.setup_consumers;

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

SELECT * FROM performance_schema.setup_consumers;

SELECT NAME,
       performance_schema.setup_consumers.ENABLED AS 'P_S',
       tmp_setup_consumers.ENABLED AS 'tmp'
  FROM performance_schema.setup_consumers
       INNER JOIN tmp_setup_consumers USING (NAME);
       
UPDATE performance_schema.setup_consumers
       INNER JOIN tmp_setup_consumers USING (NAME)
   SET performance_schema.setup_consumers.ENABLED = tmp_setup_consumers.ENABLED;

SELECT * FROM performance_schema.setup_consumers;

Suggested fix:
N/A

Ignore the "CALL ps_tools.ps_reset_setup(FALSE);" in the test case - that just resets the Performance Schema settings to the defaults (i.e. equal to restarting MySQL when using all defaults for the Performance Schema).

Test cases for setup_consumers, threads, and setup_instruments

Attachment: reset_test.sql (application/octet-stream, text), 3.55 KiB.

Noted in 5.6.16, 5.7.4 changelogs.

Complex updates of Performance Schema tables involving joins or
subqueries failed to update every row.

5.6$ bzr log -r 5647 -n0
------------------------------------------------------------
revno: 5647 [merge]
committer: Marc Alff <marc.alff@oracle.com>
branch nick: mysql-5.6-push
timestamp: Tue 2013-11-26 01:16:59 +0100
message:
  Push to mysql-5.6
    ------------------------------------------------------------
    revno: 5646.1.1
    committer: Marc Alff <marc.alff@oracle.com>
    branch nick: mysql-5.6-bug17309657
    timestamp: Tue 2013-11-26 00:03:50 +0100
    message:
      Bug#17309657 UPDATE ON P_S SETUP_CONSUMERS THROUGH JOIN ONLY UPDATES FIRST ROW
      
      Before this fix, complex updates to performance schema tables,
      when involving joins / sub queries, failed to update every row as expected.
      
      The root cause is that a storage engine implementation is supposed to
      initialize the table->status field every time a row is scanned,
      but the performance_schema failed to do so.
      
      table->status is used in particular in multi_update::send_data()
      to keep track of which rows have been updated already.
      The lack of initialization of table->status for each new row confused this
      code, causing the bug found.
      
      The fix is to initialize table->status in ha_perfschema::rnd_next() and
      ha_perfschema::rnd_pos(), in a manner consistent with exiting storage engines
      such as innodb or myisam.