dd_version_check.test

--source include/have_debug.inc

--disable_query_log

--disable_warnings
drop table if exists test.pfs_published_schema;
drop table if exists test.pfs_check_table;
drop procedure if exists test.pfs_check_proc;
--enable_warnings

--echo "Checking the data dictionary properties ..."

SET SESSION debug= '+d,skip_dd_table_access_check';
SELECT SUBSTRING_INDEX(SUBSTRING(properties, LOCATE('PS_VERSION', properties), 30), ';', 1)
  into @actual_dd_version_tag
  FROM mysql.dd_properties;
SET SESSION debug= '-d,skip_dd_table_access_check';

SELECT @actual_dd_version_tag as "DATA DICTIONARY TAG";

SELECT SUBSTRING(@actual_dd_version_tag, 12) into @actual_dd_version;
SELECT @actual_dd_version as "DATA DICTIONARY VERSION";

SET @old_group_concat_max_len = @@group_concat_max_len;

# We need 1700 rows of varchar(64) concatenated
SET group_concat_max_len = 200000;

create table test.pfs_published_schema
  (version varchar(20) not null,
   pfs_dd_version integer not null,
   signature varchar(64) not null,
   primary key (version),
   unique key(pfs_dd_version));

#
# MAINTAINER:
#
# ONCE A RELEASE IS PUBLISHED,
# DO NOT EVER CHANGE THE SIGNATURES HERE.
#
# The whole point of this MTR test script is that
# it will fail if any change to the performance schema
# database structure is detected, acting as a fail safe.
#
# If a change was intended, then:
# - (a) Go in storage/perfschema/pfs_dd_version.h,
# find the declaration of variable PFS_DD_VERSION,
# and create a new version number for this release
# - (b) Add a row in table test.pfs_published_schema
# for the new schema published, using the new PFS_DD_VERSION
# and the signature of the new schema.
#
# (a) will ensure that upgrades from old releases
# will notice the schema change, and upgrade
# the performance_schema structure in the data dictionary accordingly.
# (b) will ensure this test pass again without the SIGNAL complaining.
#
# BEWARE, failure to do (a) when doing (b) alone
# **WILL** result in broken upgrades,
# with a server binary using a new table definition
# in the code and an old, different, table definition
# in the data dictionary found on disk.
#
# This test is designed to be a reminder to do (a)
# by detecting differences in (b)
#
# If you fail to properly update PFS_DD_VERSION,
# git blame will find you.
#

# Keeping track of all signatures published in GA ...

insert into test.pfs_published_schema
 values("MySQL 8.0.17", 80017,
        "b77ece4deaa3c4676d817ed98c3cc33f5b1a08a001bf610a7d02fb52a42b613d");

insert into test.pfs_published_schema
 values("MySQL 8.0.172", 800172,
        "4b2535cee3a558d09b93caaa1fe9021da9e758ffcacd0922bea81ae9799175dc");

insert into test.pfs_published_schema
 values("MySQL 8.0.18", 80018,
        "1d6747d842bb2483c860cd7b28e886bdc6111ff61fd6b4d07e367aa7b3e9d9bf");

insert into test.pfs_published_schema
 values("MySQL 8.0.19", 80019,
        "f58dba20c4234c34cedfcacd3b9a295dcac77a09b79d5f41d5f740878f272d4d");

insert into test.pfs_published_schema
 values("MySQL 8.0.20", 80020,
        "a9aa0ab4a94ca91622145d735e60bbb03577616970351032d3194d8bfd7a69c7");

insert into test.pfs_published_schema
 values("MySQL 8.0.21", 80021,
        "112972e1f7d2b3a351a14979ecc3b419efc81e79dc029078a3a94955be218e5a");

insert into test.pfs_published_schema
 values("MySQL 8.0.22", 80022,
        "b9000c4a106a8351d2274028965f19d7902c3ccb9faa9407603c9258812f77fe");

insert into test.pfs_published_schema
 values("MySQL 8.0.23", 80023,
        "903e1e93e60651094708033160338ba66b6e59a733111e773cac126adc932488");

insert into test.pfs_published_schema
 values("MySQL 8.0.24", 80024,
        "2db5b001fae56232df1c5fab4be6bbf10d47873a6ad55f36ad9d9be20632e973");

insert into test.pfs_published_schema
 values("MySQL 8.0.27", 80027,
        "b229e37b78fcb1a522fb01991615e092888071981515ceca629030d7c9ae66c4");

insert into test.pfs_published_schema
 values("MySQL 8.0.28", 80028,
        "9984eaa4462410f61339028551966d92815f4287dac45358ca326b5c55333ddf");

insert into test.pfs_published_schema
 values("MySQL 8.0.29", 80029,
        "94c77e0c2a4f4e6616648a8ec30aebc10eee06889338a06473434fd39dc80b3a");

insert into test.pfs_published_schema
 values("MySQL 8.0.30", 80030,
        "977641d414b2df02e404d2ca3b9fbdf6d0af30f196f7de729a67d5b6183c17a8");

insert into test.pfs_published_schema
 values("MySQL 8.0.31", 80031,
        "572cf94b0e4b955d16ae57de514a8d93ef746f7c97e74c16b6666687bff51c6d");

insert into test.pfs_published_schema
 values("MySQL 8.0.32", 80032,
        "f1f4333cbce3fb0e8840ecacd6d0d417abfdf7dcbf61b01180c73a837ec0753b");

insert into test.pfs_published_schema
 values("MySQL 8.0.33", 80033,
        "00421db55e31bdc58161e2051c78e6b2040250db74f63afe3e473441d43769b4");

insert into test.pfs_published_schema
 values("MySQL 8.0.40", 80040,
        "b56610322b2a64779b7cfe96976cd0b5c6542a098bec88dacb0bb8a1fe7be458");

insert into test.pfs_published_schema
 values("MySQL 8.2.0", 80200,
        "283b7ecc142b0463234dab5ec178f3562dd3fa3c7daa5e3abc67e2a2609226c3");

# MySQL 8.3.0 was incorrectly published with PFS_DD_VERSION 80200,
# which is a bug.
# The tooling now detects this, to prevent similar bugs:
#
# --error ER_DUP_ENTRY
# insert into test.pfs_published_schema
#  values("MySQL 8.3.0", 80200,
#         "This should have been caught in 8.3.0");

# Correct entry
insert into test.pfs_published_schema
 values("MySQL 8.3.0", 80300,
        "afbf2a0d7acbbfb83e77e4e3fcf78af9d5d83131c5f275c19017b5d1be6e0d88");

insert into test.pfs_published_schema
 values("MySQL 8.4.3", 80403,
        "d80d586539a4f2292716e8620fe48360b9a9a2bf34bf06f45e25a0a5616cb0a5");

insert into test.pfs_published_schema
 values("MySQL 9.0.0", 90000,
        "fefb8c85575ab81e8c5a9517b11d21180bf8ae90213740e8636ef0ebf59cfe94");

insert into test.pfs_published_schema
 values("MySQL 9.1.0", 90100,
        "b4d93fec13a721f3070b8e87b8025b1e224b35325b3e00085926bbfa1b792b60");

create table test.pfs_check_table
  (id int NOT NULL AUTO_INCREMENT,
   t text NOT NULL,
   row_hash varchar(64) default null,
   PRIMARY KEY (id));

delimiter $$;

create procedure test.pfs_check_proc(actual_dd_version integer)
begin
  declare whole_schema longtext;
  declare max_length int;
  declare found_signature varchar(64);
  declare found_version varchar(20);
  declare found_dd_version int;

  insert into test.pfs_check_table(t)
    select concat(CATALOG_NAME, "-",
                  SCHEMA_NAME, "-",
                  DEFAULT_CHARACTER_SET_NAME, "-",
                  DEFAULT_COLLATION_NAME, "-",
                  ifnull(SQL_PATH, "NULL"))
    from INFORMATION_SCHEMA.SCHEMATA
    where SCHEMA_NAME = "performance_schema";

  insert into test.pfs_check_table(t)
    select concat(TABLE_CATALOG, "-",
                  TABLE_SCHEMA, "-",
                  TABLE_NAME, "-",
                  TABLE_TYPE, "-",
                  ENGINE, "-",
                  VERSION, "-",
                  ROW_FORMAT, "-",
                  ifnull(AUTO_INCREMENT, "no_auto_increment"), "-",
                  ifnull(TABLE_COLLATION, "no_collation"), "-",
                  ifnull(CREATE_OPTIONS, "no_create_options"), "-",
                  ifnull(TABLE_COMMENT, "no_table_comments"))
    from INFORMATION_SCHEMA.TABLES
    where TABLE_SCHEMA = "performance_schema"
    order by TABLE_NAME;

  insert into test.pfs_check_table(t)
    select concat(TABLE_CATALOG, "-",
                  TABLE_SCHEMA, "-",
                  TABLE_NAME, "-",
                  COLUMN_NAME, "-",
                  ORDINAL_POSITION, "-",
                  ifnull(COLUMN_DEFAULT, "no_default"), "-",
                  IS_NULLABLE, "-",
                  COLUMN_TYPE, "-",
                  ifnull(CHARACTER_SET_NAME, "no_charset"), "-",
                  ifnull(COLLATION_NAME, "no_collation"), "-",
                  ifnull(COLUMN_COMMENT, "no_comment"))
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA = "performance_schema"
    order by TABLE_NAME, ORDINAL_POSITION;

  insert into test.pfs_check_table(t)
    select concat(TABLE_CATALOG, "-",
                  TABLE_SCHEMA, "-",
                  TABLE_NAME, "-",
                  NON_UNIQUE, "-",
                  INDEX_SCHEMA, "-",
                  INDEX_NAME, "-",
                  SEQ_IN_INDEX, "-",
                  COLUMN_NAME, "-",
                  NULLABLE, "-",
                  INDEX_TYPE, "-",
                  COMMENT)
    from INFORMATION_SCHEMA.STATISTICS
    where TABLE_SCHEMA = "performance_schema"
    order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

/*
  There is a lot of data to crunch,
  doing an intermediate checksum by row.
*/

  update test.pfs_check_table
    set row_hash = sha2(t, 256);

/*
  And then doing a checksum of all rows.
*/

  select group_concat(row_hash order by id)
    from test.pfs_check_table
    into whole_schema;

  select length(whole_schema) into max_length;

  if (max_length = @@group_concat_max_len)
  then
    signal sqlstate "50000" set message_text = "Failed to check, whole_schema string truncated";
  end if;

  select SHA2(whole_schema, 256) into found_signature;

  select version, pfs_dd_version
         into found_version, found_dd_version
    from test.pfs_published_schema
    where signature = found_signature;

  if found_version is null
  then
    begin
      /* Limitation on SIGNAL */
      declare full_msg text;

      /*
        Booby trap: some people do not analyze test failures and
        blindly mtr --record a test to make it 'pass'.
        Make sure, by using now(), that this will not happen.
        See the comments in the MAINTAINER section of this test.
      */

      select "YOU MUST READ THE MAINTAINER NOTES IN THIS TEST."
        as MAINTAINER_ACTION_NEEDED, NOW() as "NOW";

      select concat("Unknown signature ",
                    found_signature,
                    ", fix PFS_DD_VERSION now (",
                    now(),
                    ")") into full_msg;

      signal sqlstate "50000" set message_text = full_msg;
    end;
  end if;

  if (actual_dd_version != found_dd_version)
  then
    begin
      declare full_msg text;

      /*
        Booby trap: actually verify that the PFS_DD_VERSION
        declared in table test.pfs_published_schema corresponds to reality,
        that is, that is corresponds to the PS_VERSION
        contained in the data dictionary on disk.
      */

      select "YOU MUST READ THE MAINTAINER NOTES IN THIS TEST."
        as MAINTAINER_ACTION_NEEDED, NOW() as "NOW";

      select concat("Incoherent PFS_DD_VERSION ",
                    found_dd_version,
                    ", fix PFS_DD_VERSION now (",
                    now(),
                    ")") into full_msg;

      signal sqlstate "50000" set message_text = full_msg;
    end;
  end if;

  select concat("The tables in the performance_schema were last changed in ",
                found_version, ", with a PFS_DD_VERSION = ",
                found_dd_version) as "CHECK STATUS";

end
$$

delimiter ;$$

--echo "Checking the performance schema database structure ..."

call test.pfs_check_proc(@actual_dd_version);

# Debug
# select count(*) from test.pfs_check_table;

SET group_concat_max_len = @old_group_concat_max_len;

drop table test.pfs_published_schema;
drop table test.pfs_check_table;
drop procedure test.pfs_check_proc;

--enable_query_log