Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[dev.icinga.com #10502] MySQL 5.7.9, Incorrect datetime value Error #3575

Closed
icinga-migration opened this issue Oct 30, 2015 · 21 comments
Closed

[dev.icinga.com #10502] MySQL 5.7.9, Incorrect datetime value Error #3575

icinga-migration opened this issue Oct 30, 2015 · 21 comments
Labels
area/db-ido Database output bug Something isn't working
Milestone
2.6.0

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/10502

Created by Peter_Story on 2015-10-30 19:19:45 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2016-11-18 10:42:07 +00:00)
Target Version: 2.6.0
Last Update: 2016-11-18 10:42:07 +00:00 (in Redmine)

Icinga Version: 2.3.11-1.el6
Backport?: Not yet backported
Include in Changelog: 1

I'm building a Docker container setup for Icinga, by linking a container with Icinga to the official MySQL container. The latest MySQL container uses MySQL 5.7.9, which was released just this month. I'm seeing the following error from /var/log/icinga2/icinga2.log

[2015-10-30 19:06:12 +0000] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.13.0')
[2015-10-30 19:06:12 +0000] critical/IdoMysqlConnection: Error "Incorrect datetime value: '0000-00-00 00:00:00' for column 'disconnect_time' at row 1" when executing query "INSERT INTO icinga_conninfo (instance_id, connect_time, last_checkin_time, agent_name, agent_version, connect_type, data_start_time) VALUES (1, NOW(), NOW(), 'icinga2 db_ido_mysql', 'v2.3.11', 'INITIAL', NOW())"
Context:
    (0) Reconnecting to MySQL IDO database 'ido-mysql'

[2015-10-30 19:06:12 +0000] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!

My next step is to try an older version of MySQL, to check if this problem is version-specific.

Changesets

2016-11-17 11:08:38 +00:00 by elippmann 65b35e7

ido-mysql: Add yet empty v2.6.0 schema upgrade script

refs #10502

2016-11-17 11:18:41 +00:00 by elippmann 595a8c9

ido-pgsql: Add yet empty v2.6.0 schema upgrade script

refs #10502

2016-11-17 11:19:47 +00:00 by elippmann 9a12356

ido-mysql: Change timestamp columns to default to NULL in the schema

refs #10502

2016-11-17 11:22:00 +00:00 by elippmann d4dec07

ido-mysql: Don't set the SQL mode to NO_AUTO_VALUE_ON_ZERO

This mode is obsolete because timestamp columns now default to NULL instead of '0000-00...'.

refs #10502

2016-11-17 11:24:00 +00:00 by elippmann 8068813

ido-mysql: Bump schema version to 1.14.2

refs #10502

2016-11-17 11:24:31 +00:00 by elippmann c9581b4

ido-mysql: Upgrade timestamp columns to default to NULL

refs #10502

2016-11-18 10:09:50 +00:00 by elippmann 8a21b4a

ido-mysql: Add yet empty v2.6.0 schema upgrade script

refs #10502

2016-11-18 10:09:50 +00:00 by elippmann 1bb3630

ido-mysql: Change timestamp columns to default to NULL in the schema

refs #10502

2016-11-18 10:09:50 +00:00 by elippmann 0b9046a

ido-mysql: Don't set the SQL mode to NO_AUTO_VALUE_ON_ZERO

This mode is obsolete because timestamp columns now default to NULL instead of '0000-00...'.

refs #10502

2016-11-18 10:09:50 +00:00 by elippmann bdb3f88

ido-mysql: Bump schema version to 1.14.2

refs #10502

2016-11-18 10:09:50 +00:00 by elippmann 69cb383

ido-mysql: Upgrade timestamp columns to default to NULL

refs #10502

2016-11-18 10:11:50 +00:00 by elippmann 0b0107a

ido-pgsql: Add yet empty v2.6.0 schema upgrade script

refs #10502

2016-11-18 10:39:14 +00:00 by mfriedrich 1679488

DB IDO: Bump schema version to 1.14.2

fixes #10502
fixes #13221

Relations:
@icinga-migration
Copy link
Author

Updated by Peter_Story on 2015-10-30 19:59:16 +00:00

I verified that my setup works with MySQL 5.6.27. So this does seem to be a problem with newer versions of MySQL.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-10-31 11:05:33 +00:00

Interesting, thanks for the tests. MySQL 5.7 is pretty new so we might need to test it further for both Icinga 2 and Icinga Web 2.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-10-31 11:05:52 +00:00

  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
  • Target Version set to Backlog

@icinga-migration
Copy link
Author

Updated by tgelf on 2015-11-11 14:46:00 +00:00

hint: this problem exists since quite some time. Our schema is invalid, also for very old MySQL versions. It usually "just works" as most people do not enable strict mode per default. You can easily reproduce the issue on probably any 5.x version available using strict client settings like Icinga Web 2 does. After connecting to the db, just run:

SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'

That's a more or less safe default, we use it for all our connections. The problem with the IDO schema is that once we are doing so, we are not allowed to issue even completely unrelated changes on this and similar tables - regardless of the MySQL version.

Glad to see that there seem to be stricter defaults in 5.7. It will finally force us to fix the schema :D

Cheers,
Thomas

@icinga-migration
Copy link
Author

Updated by thoth on 2016-01-05 21:18:43 +00:00

bah, ok I beat my head against the wall for abit on this one, this also affects the director module for icingaweb2. Definitely time to fix the schema!

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-01-11 09:54:56 +00:00

Do you have a patch?

@icinga-migration
Copy link
Author

Updated by simplexify on 2016-02-23 20:43:18 +00:00

We just ran into this issue when upgrading our icinga 1.x mysql cluster from 5.5 to 5.7 to benefit from the improved OPTIMIZE feature which no longer locks the tables.

Inspired by tgelf's post, we removed NO_ZERO_DATE from the sql_mode list, which overcomes this issue, at least until the schema can be fixed.

Added to my.cnf:

sql-mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

@icinga-migration
Copy link
Author

Updated by leeclemens on 2016-03-15 01:33:08 +00:00

Very useful information, but the correct fix is not loosening the sql_mode, but fixing the schema. That said, identifying that it is the NO_ZERO_DATE restriction which caused the issue is vitally important for identifying the required schema changes necessary to solve the root issue.

@icinga-migration
Copy link
Author

Updated by leeclemens on 2016-03-15 01:45:25 +00:00

This is interesting:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa.

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

As of MySQL 5.7.4, NO_ZERO_IN_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_IN_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_IN_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

So, the schema should work with NO_ZERO_DATE and NO_ZERO_IN_DATE, or the schema will not work with strict mode in the future.

@icinga-migration
Copy link
Author

Updated by leeclemens on 2016-03-15 02:56:21 +00:00

I'm working on updating this, but it's ~81 columns...so testing is tedious

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-03-18 16:41:35 +00:00

  • Parent Id set to 11412

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-03-24 10:05:48 +00:00

  • Status changed from Assigned to New
  • Assigned to deleted mfriedrich

Ok thanks :)

@icinga-migration
Copy link
Author

Updated by gbeutner on 2016-08-29 09:08:04 +00:00

  • Target Version changed from Backlog to 2.6.0

@icinga-migration
Copy link
Author

Updated by gbeutner on 2016-08-29 09:08:24 +00:00

  • Relates set to 12558

@icinga-migration
Copy link
Author

Updated by gbeutner on 2016-08-29 09:08:46 +00:00

  • Duplicated set to 12563

@icinga-migration
Copy link
Author

Updated by gbeutner on 2016-08-29 09:09:17 +00:00

@leeclemens: Any progress on this?

@icinga-migration
Copy link
Author

Updated by elippmann on 2016-09-28 13:23:52 +00:00

  • Status changed from New to Assigned
  • Assigned to set to elippmann
  • Parent Id deleted 11412

@icinga-migration
Copy link
Author

Updated by leeclemens on 2016-11-10 18:39:50 +00:00

sorry for the long delay. Unfortunately I'm not sure a schema-only approach will work without updating the logic to handle null-dates and "infinitely" future dates.

Dates as 0000-00-00 for expiration time cannot be set to CURRENT_TIME, or they will immediately be expirable. Similarly goes for last_ok or last_unknown - where 0000-00-00 is treated as "never".

I think application logic would need to be applied and these default 0000-00-00 values could be set to either 1970 (timestamp) or 1001 (datetime). That would allow the application to continue treating 1970/1001 dates as "never" and use 9999 dates as "do not expire". Appropriate defaults could be put in to the schema - but the query logic and display of these dates would still need to treat them as special (at least in the approach I'm thinking).

One potential issue with converting to DATETIME is that timezone data would be lost and would need to be handled in another layer. TIMESTAMP is 1970-2038. DATETIME is 1001-9999. If the Y2K38 bug is acceptable, we could stick with TIMESTAMP and use 1970-01-01 00:00:01.000000 and 2038-01-19 03:14:07.999999 and min/max values explicitly.

@icinga-migration
Copy link
Author

Updated by elippmann on 2016-11-17 12:12:10 +00:00

  • Status changed from Assigned to New
  • Assigned to deleted elippmann

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-11-18 10:38:49 +00:00

  • Status changed from New to Assigned
  • Assigned to set to mfriedrich

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2016-11-18 10:42:07 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 0 to 100

Applied in changeset 1679488.

@icinga-migration icinga-migration added bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.6.0 milestone Jan 17, 2017
@leeclemens leeclemens mentioned this issue Jul 1, 2017
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/db-ido Database output bug Something isn't working
Projects
None yet
Milestone
2.6.0
Development

No branches or pull requests
1 participant
@icinga-migration