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)
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)