MySQL Bugs: #75794: Performance Schema statement digest generation is broken with ALTER PARTITION

Bug #75794	Performance Schema statement digest generation is broken with ALTER PARTITION
Submitted:	5 Feb 2015 13:32	Modified:	16 Feb 2015 23:50
Reporter:	Joe Grasse (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.23/5.7	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	digest, performance_schema

Description:
ALTER PARTITION statements generate more statement digests than they should.

How to repeat:
use test
CREATE TABLE `digest_partition_test` (
  `id` int NOT NULL,
  `end_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(end_time))
(PARTITION invalid_dates VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION day_20150201 VALUES LESS THAN (735996) ENGINE = InnoDB,
 PARTITION day_20150202 VALUES LESS THAN (735997) ENGINE = InnoDB,
 PARTITION day_20150203 VALUES LESS THAN (735998) ENGINE = InnoDB,
 PARTITION day_20150204 VALUES LESS THAN (735999) ENGINE = InnoDB,
 PARTITION day_20150205 VALUES LESS THAN (736000) ENGINE = InnoDB,
 PARTITION day_20150206 VALUES LESS THAN (736001) ENGINE = InnoDB,
 PARTITION day_20150207 VALUES LESS THAN (736002) ENGINE = InnoDB,
 PARTITION day_20150208 VALUES LESS THAN (736003) ENGINE = InnoDB,
 PARTITION day_20150209 VALUES LESS THAN (736004) ENGINE = InnoDB,
 PARTITION day_20150210 VALUES LESS THAN (736005) ENGINE = InnoDB,
 PARTITION day_20150211 VALUES LESS THAN (736006) ENGINE = InnoDB,
 PARTITION day_20150212 VALUES LESS THAN (736007) ENGINE = InnoDB,
 PARTITION day_20150213 VALUES LESS THAN (736008) ENGINE = InnoDB,
 PARTITION day_20150214 VALUES LESS THAN (736009) ENGINE = InnoDB,
 PARTITION max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%'; 

ALTER TABLE digest_partition_test DROP PARTITION day_20150201;
ALTER TABLE digest_partition_test DROP PARTITION day_20150202;
SELECT SQL_TEXT,DIGEST,DIGEST_TEXT FROM performance_schema.events_statements_history;

Suggested fix:
The alter partition statements listed should only generate one digest.

Thank you for the bug report.

output

Attachment: alter_digest.txt (text/plain), 8.51 KiB.

Hey Joe,

I don't think this is a bug. What you are expecting to be normalized away are actually identifiers, rather than literals.

It's actually no different than, for example:

ALTER TABLE foo DROP COLUMN bar;

And expecting the "bar" column name to be normalized away. I don't think that's right.

I can understand why you might want this (especially if you have a lot of ongoing ALTER TABLEs for rolling partition management), but even so, I'm not sure this can be classified as a bug.

One possible work around that you might consider is to turn off the "statement/sql/alter_table" instrument (though you would lose visibility in to all ALTER TABLE statements then). Another possibility would be to just disable instrumentation for the thread doing these specific alter table commands.

Hello Mark,

Partition management is the exact reason why I would want these to generate only one digest. As for your reason on why they should be different digests, I would have to disagree. I would think the two (ALTER DROP COLUMN, ALTER DROP PARTITION) statements represent different "things". When you are dropping a partition, the partitions are the same types of "things". You are doing in essence the same action. 

As for ALTER TABLE foo DROP COLUMN bar vs ALTER TABLE foo DROP COLUMN foobar digest generation, I could see why you would want these to be different digests, but what about the following example.

Column bar is defined as follows.

bar enum('item1','item2', 'item3')

Then you do

ALTER TABLE foo DROP COLUMN bar;
ALTER TABLE foo ADD COLUMN bar int(10) unsigned;

If you then do another ALTER TABLE foo DROP COLUMN bar, this digest would be the same as the original ALTER TABLE foo DROP bar. Obviously this is a very contrived example, but are these really the same?

The digest generated are as intended,
since the partition name is the name of an object and not just a literal.

With this:

ALTER TABLE digest_partition_test DROP PARTITION day_20150201;
ALTER TABLE digest_partition_test DROP PARTITION day_20150202;

the partition may not actually be "the same", this really depends on the partition definition.

The performance schema does not have rules to further group together things like:
- PARTITION day_20150201 / PARTITION day_20150202
- TABLE order_123 / TABLE order_456
so for cases like this, yes, separate digests are generated.