On 04-05-16 12:23, Hans Rönnbäck wrote:
> Cacti 0.8.8f which is the default version for Ubuntu 16.04 uses invalid
> datetime values ("0000-00-00 00:00:00"). MySQL 5.7 does not support
> zero-dates,
Are you sure?
Reading the upstream upgrade manual¹, it seems to only do this when you
ask it to (see below). I can't test right now, but isn't this sql_mode
something that you set somewhere? I.e. isn't this something that you
could avoid?
"""
Incompatible change: It is possible for a column DEFAULT value to be
valid for the sql_mode value at table-creation time but invalid for the
sql_mode value when rows are inserted or updated. Example:
SET sql_mode = '';
CREATE TABLE t (d DATE DEFAULT 0);
SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';
INSERT INTO t (d) VALUES(DEFAULT);
In this case, 0 should be accepted for the CREATE TABLE but rejected for
the INSERT. However, previously the server did not evaluate DEFAULT
values used for inserts or updates against the current sql_mode. In the
example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.
As of MySQL 5.7.2, the server applies the proper sql_mode checks to
generate a warning or error at insert or update time.
"""
Hi Hans,
Thanks for reporting this issue.
On 04-05-16 12:23, Hans Rönnbäck wrote:
> Cacti 0.8.8f which is the default version for Ubuntu 16.04 uses invalid
> datetime values ("0000-00-00 00:00:00"). MySQL 5.7 does not support
> zero-dates,
Are you sure?
Reading the upstream upgrade manual¹, it seems to only do this when you
ask it to (see below). I can't test right now, but isn't this sql_mode
something that you set somewhere? I.e. isn't this something that you
could avoid?
"""
Incompatible change: It is possible for a column DEFAULT value to be
valid for the sql_mode value at table-creation time but invalid for the
sql_mode value when rows are inserted or updated. Example:
SET sql_mode = ''; DATE,STRICT_ ALL_TABLES' ;
CREATE TABLE t (d DATE DEFAULT 0);
SET sql_mode = 'NO_ZERO_
INSERT INTO t (d) VALUES(DEFAULT);
In this case, 0 should be accepted for the CREATE TABLE but rejected for
the INSERT. However, previously the server did not evaluate DEFAULT
values used for inserts or updates against the current sql_mode. In the
example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.
As of MySQL 5.7.2, the server applies the proper sql_mode checks to
generate a warning or error at insert or update time.
"""
Paul
¹ http:// dev.mysql. com/doc/ refman/ 5.7/en/ upgrading- from-previous- series. html