[dev.icinga.com #12558] Getting error during schema update

[icinga-migration]

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

Created by chrkli193 on 2016-08-26 08:48:17 +00:00

Assignee: gbeutner
Status: Resolved (closed on 2016-08-27 08:30:03 +00:00)
Target Version: 2.6.0
Last Update: 2016-08-30 11:51:28 +00:00 (in Redmine)

Icinga Version: 2.5.3
Include in Changelog: 1

---

During updating the schema from 2.4.0 to 2.5.0 I get the following error:

ERROR 1067 (42000) at line 14: Invalid default value for 'status_update_time'

Ubuntu 16.04.1
MySQL 5.7.13

Changesets

2016-08-27 08:20:59 +00:00 by gbeutner 4dbc07e

Improve support for MySQL >=5.7

fixes #12558

2016-08-29 10:20:52 +00:00 by gbeutner 9db1e5d

Improve support for MySQL >=5.7

fixes #12558

---

Relations:

• relates #12563
• relates #10502
• duplicates #12558

[icinga-migration]

Updated by gbeutner on 2016-08-26 15:31:34 +00:00

This appears to happen for users who've updated to Ubuntu 16.04 after installing Icinga. Apparently MySQL 5.7 by default has the option NO_ZERO_DATE turned on. Consider disabling this as a workaround. :)

[icinga-migration]

Updated by tgelf on 2016-08-26 21:23:47 +00:00

As a workaround this might help, we could also write such sql_mode settings to our schema files to make life easier for our users. See the Icinga Director's schema/mysql.sql for such an example (just the other way round, it enforces strictness). We should at least do so as long as we ship invalid default values in the schema. In the (not so) long run we should really try to clean those things up. Please note that according the MySQL documentation this schema is invalid since a very long time, those rules just haven't been enforced in default MySQL installations until now.

Icinga Web 2 defaults for MySQL connections since the very first prototype are pretty strict:

STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION

This saves us a lot of trouble, as the database complains early and doesn't silently accept invalid data. However, it's current downside is that we are not able to do IDO schema operations from an Icinga Web 2 module, at least not without changing the sql_mode for the current connection.

Cheers,
Thomas

[icinga-migration]

Updated by dupondje on 2016-08-27 08:18:49 +00:00

I think its clear we just need to change the schema. And not change the sql_mode.

The '0000-00-00 00:00:00' values should just be changed to NULL.

[icinga-migration]

Updated by gbeutner on 2016-08-27 08:26:51 +00:00

As a temporary workaround we're going to set sql_mode on a per-session basis for both the schema update file and the IDO module itself. However, I agree that we should clean up the schema.

[icinga-migration]

Updated by gbeutner on 2016-08-27 08:27:40 +00:00

• Relates set to 12563

[icinga-migration]

Updated by gbeutner on 2016-08-27 08:27:49 +00:00

• Status changed from New to Assigned
• Assigned to set to gbeutner
• Target Version set to 2.6.0

[icinga-migration]

Updated by gbeutner on 2016-08-27 08:30:03 +00:00

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

Applied in changeset 4dbc07e.

[icinga-migration]

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

• Relates set to 10502

[icinga-migration]

Updated by gbeutner on 2016-08-29 10:18:45 +00:00

• Target Version changed from 2.6.0 to 2.5.4

[icinga-migration]

Updated by gbeutner on 2016-08-29 10:23:15 +00:00

• Backport? changed from Not yet backported to Already backported

[icinga-migration]

Updated by mfriedrich on 2016-08-30 11:51:28 +00:00

• Target Version changed from 2.5.4 to 2.6.0
• Backport? changed from Already backported to __

[icinga-migration]

Updated by gbeutner on 2016-09-26 06:37:34 +00:00

• Duplicated set to 12767