Bug #114347 mysqlsh upgrade utility is reporting that views using the NOW() function
Submitted: 14 Mar 8:53 Modified: 14 Mar 9:20
Reporter: Yunus UYANIK Email Updates:
Status: Verified Impact on me:
None 
Category:Shell General / Core Client Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: zero date error for views

[14 Mar 8:53] Yunus UYANIK
Description:
The MySQL Shell upgrade utility is reporting that views using the NOW() or sysdate() function have zero dates.

How to repeat:

mysql> create table `t1` (`NOW()` datetime NOT NULL) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.03 sec)

mysql> create view `v1` AS select NOW() AS col1;
Query OK, 0 rows affected (0.00 sec)

18) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  test.v1.col1 - column has zero default value: 0000-00-00 00:00:00

https://github.com/mysql/mysql-shell/blob/54a3a4112bed9126c6a2f4ebe0803919285eaf06/modules...

mysql> select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, concat('column has zero default value: ', COLUMN_DEFAULT) from information_schema.columns where TABLE_SCHEMA not in ('performance_schema','information_schema','sys','mysql') and DATA_TYPE in ('timestamp', 'datetime', 'date') and COLUMN_DEFAULT like '0000-00-00%';
+--------------+------------+-------------+-----------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | concat('column has zero default value: ', COLUMN_DEFAULT) |
+--------------+------------+-------------+-----------------------------------------------------------+
| test         | v1         | col1        | column has zero default value: 0000-00-00 00:00:00        |
+--------------+------------+-------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
[14 Mar 9:20] MySQL Verification Team
Hello Yunus UYANIK,

Thank you for the report and feedback.

regards,
Umesh