MySQL Bugs: #70437: "TIMESTAMP ... is deprecated." warning for mysql.event and mysql.proc

Bug #70437	"TIMESTAMP ... is deprecated." warning for mysql.event and mysql.proc
Submitted:	26 Sep 2013 13:47	Modified:	9 Nov 2017 13:22
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql_upgrade, timestamp, upgrade

Description:
According to the manual, http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html:

"Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp system variable at server startup."

So, whenever one upgrades from older versions to recent MySQL 5.6.x she gets this warning upon every startup. That's because of the following:

mysql> select table_schema, table_name, column_name, column_default from informa
tion_schema.columns where column_type='TIMESTAMP';
+--------------+--------------+-------------+---------------------+
| table_schema | table_name   | column_name | column_default      |
+--------------+--------------+-------------+---------------------+
| mysql        | columns_priv | Timestamp   | CURRENT_TIMESTAMP   |
| mysql        | event        | created     | CURRENT_TIMESTAMP   |
| mysql        | event        | modified    | 0000-00-00 00:00:00 |
| mysql        | general_log  | event_time  | CURRENT_TIMESTAMP   |
| mysql        | proc         | created     | CURRENT_TIMESTAMP   |
| mysql        | proc         | modified    | 0000-00-00 00:00:00 |
| mysql        | procs_priv   | Timestamp   | CURRENT_TIMESTAMP   |
| mysql        | proxies_priv | Timestamp   | CURRENT_TIMESTAMP   |
| mysql        | slow_log     | start_time  | CURRENT_TIMESTAMP   |
| mysql        | tables_priv  | Timestamp   | CURRENT_TIMESTAMP   |
+--------------+--------------+-------------+---------------------+
10 rows in set (0.81 sec)

That is, MySQL 5.6.x has tables in mysql database, proc and event, with TIMESTAMP columns relying on deprecated behavior. Should they be fixed and get explicit default value maybe?

As same may be the case for some of user tables also, it would be nice to have mysql_upgrade extended to output similar warning for every table affected.

How to repeat:
Upgrade typical MySQL 5.5 to recent 5.6.x and check error log. Try to find out what tables caused this warning to appear.

Suggested fix:
Fix mysql.proc and mysql.event tables?

Make mysql_upgrade output similar warning for every table with a TIMESTAMP column having implicit default value?

Thank you for the bug report.

I'm still seeing this problem in 5.6.20...

mysql> select table_schema, table_name, column_name, column_default from information_schema.columns where column_type='TIMESTAMP';
+--------------------+-------------------------------------+------------------+---------------------+
| table_schema       | table_name                          | column_name      | column_default      |
+--------------------+-------------------------------------+------------------+---------------------+
| mysql              | columns_priv                        | Timestamp        | CURRENT_TIMESTAMP   |
| mysql              | event                               | created          | CURRENT_TIMESTAMP   |
| mysql              | event                               | modified         | CURRENT_TIMESTAMP   |
| mysql              | general_log                         | event_time       | CURRENT_TIMESTAMP   |
| mysql              | innodb_index_stats                  | last_update      | CURRENT_TIMESTAMP   |
| mysql              | innodb_table_stats                  | last_update      | CURRENT_TIMESTAMP   |
| mysql              | proc                                | created          | CURRENT_TIMESTAMP   |
| mysql              | proc                                | modified         | CURRENT_TIMESTAMP   |
| mysql              | procs_priv                          | Timestamp        | CURRENT_TIMESTAMP   |
| mysql              | proxies_priv                        | Timestamp        | CURRENT_TIMESTAMP   |
| mysql              | slow_log                            | start_time       | CURRENT_TIMESTAMP   |
| mysql              | tables_priv                         | Timestamp        | CURRENT_TIMESTAMP   |
| performance_schema | events_statements_summary_by_digest | FIRST_SEEN       | 0000-00-00 00:00:00 |
| performance_schema | events_statements_summary_by_digest | LAST_SEEN        | 0000-00-00 00:00:00 |
| performance_schema | host_cache                          | FIRST_SEEN       | 0000-00-00 00:00:00 |
| performance_schema | host_cache                          | LAST_SEEN        | 0000-00-00 00:00:00 |
| performance_schema | host_cache                          | FIRST_ERROR_SEEN | 0000-00-00 00:00:00 |
| performance_schema | host_cache                          | LAST_ERROR_SEEN  | 0000-00-00 00:00:00 |
+--------------------+-------------------------------------+------------------+---------------------+

I manually corrected proc and event "modified" fields but am unable to change the performance_schema database... I get access denied when I try to alter tables within performance_schema. 

I still get the error "[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)" when the database starts up.

Is there any ETA as to when this will get fixed, or is there a way I can correct it manually?  I'd rather not set explicit_defaults_for_timestamp since it's deprecated as well.

Thank you

Posted by developer:

[29 Aug 2017 0:16] Praveenkumar Hulakund

Following warning is printed on server startup if
--explicit_defaults_for_timestamp option is not used in 5.6,

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

The above warning is *not* reported because of mysql.proc, mysql.events
or any other user table having timestamp column(s) with implicit default
value.