[dev.icinga.com #13009] Schema migration is not working

[icinga-migration]

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

Created by tuxifier on 2016-10-30 20:57:53 +00:00

Assignee: (none)
Status: Closed (closed on 2016-12-05 08:55:11 +00:00)
Target Version: (none)
Last Update: 2016-12-05 08:55:11 +00:00 (in Redmine)

---

Icingaweb2 - Apply schema migrations results in:
Migration 110 failed (SQLSTATE[42S02]: Base table or view not found: 1146 Table 'director.icinga_notification_var' doesn't exist) while running UPDATE icinga_notification_var SET varvalue = 'false', format = 'json' WHERE varvalue = 'n' AND varname IN ( SELECT DISTINCT varname FROM director_datafield WHERE datatype LIKE '%DataTypeBoolean' )

icingacli director migration pending --verbose
There are 9 pending migrations

icingacli director migration run --verbose
ERROR: Icinga\Exception\IcingaException in /usr/share/icingaweb2/modules/director/library/Director/Db/Migration.php:52 with message: Migration 110 failed (SQLSTATE[42S02]: Base table or view not found: 1146 Table 'director.icinga_notification_var' doesn't exist) while running UPDATE icinga_notification_var
SET varvalue = 'false',
format = 'json'
WHERE varvalue = 'n'
AND varname IN (
SELECT DISTINCT varname
FROM director_datafield
WHERE datatype LIKE '%DataTypeBoolean'
)

[icinga-migration]

Updated by tuxifier on 2016-10-30 21:29:45 +00:00

Just figured out - the missing table should have been created by mysql upgrade_77.sql. Apparently this did not work. I tried to manually execute the CREATE statements for icinga_notification_var.
Results in error:
ERROR 1171 (42000) at line 1: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

[icinga-migration]

Updated by tgelf on 2016-10-31 13:40:36 +00:00

• Status changed from New to Feedback
• Assigned to set to tuxifier

tuxifier wrote:

Just figured out - the missing table should have been created by mysql upgrade_77.sql.
Apparently this did not work.

I do not see how you could manage it to reach migration 110 with a failing migration 77. Did you tweak other things manually?

I tried to manually execute the CREATE statements for icinga_notification_var.
Results in error:
ERROR 1171 (42000) at line 1: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

This is then probably MySQL 5.7, however also other parts of the schema should then have failed at the time being. This has been fixed in the regular schema file with migration 92. But I still do not see how you managed it to get where you currently are.

Could you please tell me more details regarding the history of your installation? Involved software versions, upgrade steps etc.

Thanks,
Thomas

[icinga-migration]

Updated by tgelf on 2016-11-02 16:48:41 +00:00

ping :-)

[icinga-migration]

Updated by tuxifier on 2016-12-03 22:03:51 +00:00

Hi Tom,

sorry for the late response - living on the street too much at the moment (you know that ;))
The truth is I have no clue how I got to this messy database state. I solved it with just creating a new one.
As the system was a kind of orphaned prototyping VM - the "loss" was not that big. Most probably I messed up director's db myself while ragin' around during tests :)

consider that closed.

[icinga-migration]

Updated by tgelf on 2016-12-05 08:55:11 +00:00

• Tracker changed from Bug to Support
• Status changed from Feedback to Closed
• Assigned to deleted tuxifier

Thanks!