MySQL Bugs: #104643: Defaults in performance schema tables incompatible with sql

Bug #104643	Defaults in performance schema tables incompatible with sql_mode
Submitted:	17 Aug 2021 14:11	Modified:	6 Oct 2021 16:24
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Default values in the table performance_schema.events_statements_summary_by_digest for columns FIRST_SEEN, LAST_SEEN, and QUERY_SAMPLE_SEEN are '0000-00-00 00:00:00.000000'.

At the same time default SQL mode in version 8.0 is 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'.

This does not allow to create a copy of the table events_statements_summary_by_digest for logging purpose, for example.

How to repeat:
CREATE TABLE `events_statements_summary_by_digest` (
...
  `SUM_NO_GOOD_INDEX_USED` bigint unsigned NOT NULL,
  `FIRST_SEEN` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `LAST_SEEN` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `QUANTILE_95` bigint unsigned NOT NULL,
  `QUANTILE_99` bigint unsigned NOT NULL,
  `QUANTILE_999` bigint unsigned NOT NULL,
  `QUERY_SAMPLE_TEXT` longtext,
  `QUERY_SAMPLE_SEEN` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `QUERY_SAMPLE_TIMER_WAIT` bigint unsigned NOT NULL,
  UNIQUE KEY `SCHEMA_NAME` (`SCHEMA_NAME`,`DIGEST`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0,00 sec)

mysql> create table test.foobar as select * from performance_schema.events_statements_summary_by_digest;
ERROR 1067 (42000): Invalid default value for 'FIRST_SEEN'

Suggested fix:
Adjust default values.

Thanks Sveta.

Verified as described.

Other performance schema tables are also affected:

malff@localhost.localdomain:perfschema> pwd
/home/malff/CODE/GIT/GIT_80/storage/perfschema
malff@localhost.localdomain:perfschema> grep TIMESTAMP *.cc | grep "default 0"
table_ees_by_account_by_error.cc:    "  FIRST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_account_by_error.cc:    "  LAST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_host_by_error.cc:    "  FIRST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_host_by_error.cc:    "  LAST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_thread_by_error.cc:    "  FIRST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_thread_by_error.cc:    "  LAST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_user_by_error.cc:    "  FIRST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_by_user_by_error.cc:    "  LAST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_global_by_error.cc:    "  FIRST_SEEN TIMESTAMP(0) null default 0,\n"
table_ees_global_by_error.cc:    "  LAST_SEEN TIMESTAMP(0) null default 0,\n"
table_esms_by_digest.cc:    "  FIRST_SEEN TIMESTAMP(6) NOT NULL default 0,\n"
table_esms_by_digest.cc:    "  LAST_SEEN TIMESTAMP(6) NOT NULL default 0,\n"
table_esms_by_digest.cc:    "  QUERY_SAMPLE_SEEN TIMESTAMP(6) NOT NULL default 0,\n"
table_host_cache.cc:    "  FIRST_SEEN TIMESTAMP(0) NOT NULL default 0,\n"
table_host_cache.cc:    "  LAST_SEEN TIMESTAMP(0) NOT NULL default 0,\n"
table_host_cache.cc:    "  FIRST_ERROR_SEEN TIMESTAMP(0) null default 0,\n"
table_host_cache.cc:    "  LAST_ERROR_SEEN TIMESTAMP(0) null default 0,\n"
table_replication_applier_filters.cc:    "  ACTIVE_SINCE TIMESTAMP(6) NOT NULL default 0,\n"
table_replication_applier_global_filters.cc:    "  ACTIVE_SINCE TIMESTAMP(6) NOT NULL default 0\n",

Posted by developer:
 
 Noted in 8.0.27 changelog:

 Several performance_schema tables contained default timestamp values of 0 (zero) which
 conflicted with the default sql_mode values NO_ZERO_IN_DATE and NO_ZERO_DATE.
	  
 For example, attempting to create a new table based on such a performance_schema
 table, resulted in an error similar to the following:
 ERROR 1067 (42000): Invalid default value for 'FIRST_SEEN'
 
 Default timestamp values were removed from the following tables:
  
	- events_errors_summary_by_account_by_error
	- events_errors_summary_by_host_by_error
	- events_errors_summary_by_thread_by_error
	- events_errors_summary_by_user_by_error
	- events_errors_summary_global_by_error
	- events_statements_summary_by_digest
	- host_cache
	- replication_applier_filters
	- replication_applier_global_filters