Support Questions
Find answers, ask questions, and share your expertise

Ambari Upgrade problem on Schema Upgrade (2.4.2 to 2.5.2)

New Contributor

I have done the same upgrade on a test system and made sure it works. But, in a production system it failed on ambari-server upgrade:

It shows this:

root@management:/var/lib/ambari-server/resources# ambari-server upgrade
Using python  /usr/bin/python
Upgrading ambari-server
INFO: Upgrade Ambari Server
INFO: Updating Ambari Server properties in ambari.properties ...
WARNING: Can not find ambari.properties.rpmsave file from previous version, skipping import of settings
INFO: Updating Ambari Server properties in ambari-env.sh ...
INFO: Can not find ambari-env.sh.rpmsave file from previous version, skipping restore of environment settings. ambari-env.sh may not include any user customization.
INFO: Fixing database objects owner
Ambari Server configured for MySQL. Confirm you have made a backup of the Ambari Server database [y/n] (y)? y
INFO: Upgrading database schema
INFO: Return code from schema upgrade command, retcode = 1
ERROR: Error executing schema upgrade, please check the server logs.
ERROR: Error output from schema upgrade command:
ERROR: Exception in thread "main" org.apache.ambari.server.AmbariException: Cannot add foreign key constraint
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:212)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:427)
Caused by: java.sql.SQLException: Cannot add foreign key constraint
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
        at org.apache.ambari.server.orm.DBAccessorImpl.executeQuery(DBAccessorImpl.java:872)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:517)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.createComponentVersionTable(UpgradeCatalog250.java:836)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.executeDDLUpdates(UpgradeCatalog250.java:173)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:209)
        ... 1 more




ERROR: Ambari server upgrade failed. Please look at /var/log/ambari-server/ambari-server.log, for more details.
ERROR: Exiting with exit code 11. 
REASON: Schema upgrade failed.

Checking the ambari-server.log shows:

18 Oct 2017 16:42:53,086  INFO [main] Configuration:3021 - Reading password from existing file
18 Oct 2017 16:42:53,105  INFO [main] Configuration:3506 - Hosts Mapping File null
18 Oct 2017 16:42:53,105  INFO [main] HostsMap:60 - Using hostsmap file null
18 Oct 2017 16:42:53,619  INFO [main] ControllerModule:221 - Detected MYSQL as the database type from the JDBC URL
18 Oct 2017 16:42:53,623  INFO [main] ControllerModule:264 - Using c3p0 ComboPooledDataSource as the EclipsLink DataSource
18 Oct 2017 16:42:53,649  INFO [MLog-Init-Reporter] MLog:212 - MLog clients using slf4j logging.
18 Oct 2017 16:42:53,757  INFO [main] C3P0Registry:212 - Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
18 Oct 2017 16:42:53,871  INFO [main] ControllerModule:539 - Searching package org.apache.ambari.server for annotations matching [interface org.apache.ambari.server.EagerSingleton, interface org.apache.ambari.server.StaticallyInject, interface org.apache.ambari.server.AmbariService]
18 Oct 2017 16:42:54,122  INFO [main] ClasspathScannerUtils:54 - Checking package [org.apache.ambari.server] for binding candidates.
18 Oct 2017 16:42:54,954  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.controller.logging.LogSearchDataRetrievalService 
18 Oct 2017 16:42:54,960  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.state.services.AlertNoticeDispatchService 
18 Oct 2017 16:42:54,961  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.state.services.AmbariServerAlertService 
18 Oct 2017 16:42:54,961  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.state.services.CachedAlertFlushService 
18 Oct 2017 16:42:55,054  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.state.services.MetricsRetrievalService 
18 Oct 2017 16:42:55,055  INFO [main] ControllerModule:581 - Registering service class org.apache.ambari.server.state.services.RetryUpgradeActionService 
18 Oct 2017 16:42:55,066  INFO [main] ControllerModule:609 - Searching package org.apache.ambari.server.notifications.dispatchers for dispatchers matching interface org.apache.ambari.server.notifications.NotificationDispatcher
18 Oct 2017 16:42:55,118  INFO [main] ControllerModule:649 - Binding and registering notification dispatcher class org.apache.ambari.server.notifications.dispatchers.AmbariSNMPDispatcher
18 Oct 2017 16:42:55,122  INFO [main] ControllerModule:649 - Binding and registering notification dispatcher class org.apache.ambari.server.notifications.dispatchers.AlertScriptDispatcher
18 Oct 2017 16:42:55,122  INFO [main] ControllerModule:649 - Binding and registering notification dispatcher class org.apache.ambari.server.notifications.dispatchers.SNMPDispatcher
18 Oct 2017 16:42:55,128  INFO [main] ControllerModule:649 - Binding and registering notification dispatcher class org.apache.ambari.server.notifications.dispatchers.EmailDispatcher
18 Oct 2017 16:42:55,129  INFO [main] ControllerModule:673 - Searching package org.apache.ambari.server.checks for classes matching class org.apache.ambari.server.checks.AbstractCheckDescriptor
18 Oct 2017 16:42:55,333  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HostsMasterMaintenanceCheck
18 Oct 2017 16:42:55,333  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesMaintenanceModeCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HostMaintenanceModeCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HostsRepositoryVersionCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.SecondaryNamenodeDeletedCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesNamenodeHighAvailabilityCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesNamenodeTruncateCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesMapReduceDistributedCacheCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesTezDistributedCacheCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ComponentsExistInRepoCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HostsHeartbeatCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ComponentsInstallationCheck
18 Oct 2017 16:42:55,334  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesUpCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ClientRetryPropertyCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.YarnRMHighAvailabilityCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HiveMultipleMetastoreCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.AutoStartDisabledCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.MapReduce2JobHistoryStatePreservingCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.RangerPasswordCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.VersionMismatchCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.KafkaKerberosCheck
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HiveNotRollingWarning
18 Oct 2017 16:42:55,335  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.RangerAuditDbCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.RangerSSLConfigCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.StormShutdownWarning
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.AtlasPresenceCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HealthCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.JavaVersionCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServiceCheckValidityCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicePresenceCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.InstallPackagesCheck
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.PreviousUpgradeCompleted
18 Oct 2017 16:42:55,336  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ServicesYarnWorkPreservingCheck
18 Oct 2017 16:42:55,337  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.YarnTimelineServerStatePreservingCheck
18 Oct 2017 16:42:55,337  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HiveDynamicServiceDiscoveryCheck
18 Oct 2017 16:42:55,337  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.HardcodedStackVersionPropertiesCheck
18 Oct 2017 16:42:55,337  INFO [main] ControllerModule:709 - Registered pre-upgrade check class org.apache.ambari.server.checks.ConfigurationMergeCheck
18 Oct 2017 16:42:55,497  INFO [main] ClasspathScannerUtils:54 - Checking package [org.apache.ambari.server.audit.request.eventcreator] for binding candidates.
18 Oct 2017 16:42:55,539  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.AlertGroupEventCreator
18 Oct 2017 16:42:55,539  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.AlertTargetEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.BlueprintEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.BlueprintExportEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ComponentEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ConfigurationChangeEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.CredentialEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.DefaultEventCreator
18 Oct 2017 16:42:55,540  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.GroupEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.HostEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.MemberEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.PrivilegeEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.RecommendationIgnoreEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.RepositoryEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.RepositoryVersionEventCreator
18 Oct 2017 16:42:55,541  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.RequestEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ServiceConfigDownloadEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ServiceEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.UnauthorizedEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.UpgradeEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.UpgradeItemEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.UserEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ValidationIgnoreEventCreator
18 Oct 2017 16:42:55,542  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ViewInstanceEventCreator
18 Oct 2017 16:42:55,543  INFO [main] AuditLoggerModule:82 - Binding audit event creator class org.apache.ambari.server.audit.request.eventcreator.ViewPrivilegeEventCreator
18 Oct 2017 16:42:56,665  INFO [main] HostRoleCommandDAO:260 - Host role command status summary cache enabled !
18 Oct 2017 16:42:56,666  INFO [main] TransactionalLock$LockArea:121 - LockArea HRC_STATUS_CACHE is enabled
18 Oct 2017 16:42:56,807  INFO [main] LockFactory:53 - Lock profiling is disabled
18 Oct 2017 16:42:56,829  INFO [main] SchemaUpgradeHelper:403 - Upgrading schema to target version = 2.5.2.0
18 Oct 2017 16:42:56,832  INFO [main] SchemaUpgradeHelper:412 - Upgrading schema from source version = 2.4.2
18 Oct 2017 16:42:56,836  INFO [main] SchemaUpgradeHelper:158 - Upgrade path: [{ upgradeCatalog: sourceVersion = 2.4.2, targetVersion = 2.5.0 }, { upgradeCatalog: sourceVersion = 2.5.0, targetVersion = 2.5.1 }, { upgradeCatalog: sourceVersion = 2.5.1, targetVersion = 2.5.2 }, { upgradeCatalog: sourceVersion = null, targetVersion = 2.5.2 }, { upgradeCatalog: sourceVersion = null, targetVersion = 2.5.2 }]
18 Oct 2017 16:42:56,837  INFO [main] SchemaUpgradeHelper:204 - Executing DDL upgrade...
18 Oct 2017 16:42:56,837  INFO [main] UpgradeCatalog250:449 - Updating the host_version table
18 Oct 2017 16:42:56,849  INFO [main] DBAccessorImpl:569 - Unique constraint UQ_host_repo already exists or columns repo_version_id, host_id not found, skipping
18 Oct 2017 16:42:56,849  INFO [main] UpgradeCatalog250:825 - Creating servicecomponent_version table
18 Oct 2017 16:42:56,857  WARN [main] DBAccessorImpl:579 - Primary constraint PK_sc_version not altered to table servicecomponent_version as column [id] not present or constraint already exists
18 Oct 2017 16:42:57,198  INFO [main] DBAccessorImpl:869 - Executing query: ALTER TABLE servicecomponent_version ADD CONSTRAINT FK_scv_component_id FOREIGN KEY (component_id) REFERENCES servicecomponentdesiredstate (id)
18 Oct 2017 16:42:57,219 ERROR [main] DBAccessorImpl:875 - Error executing query: ALTER TABLE servicecomponent_version ADD CONSTRAINT FK_scv_component_id FOREIGN KEY (component_id) REFERENCES servicecomponentdesiredstate (id)
java.sql.SQLException: Cannot add foreign key constraint
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
        at org.apache.ambari.server.orm.DBAccessorImpl.executeQuery(DBAccessorImpl.java:872)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:517)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.createComponentVersionTable(UpgradeCatalog250.java:836)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.executeDDLUpdates(UpgradeCatalog250.java:173)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:209)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:427)
18 Oct 2017 16:42:57,223  WARN [main] DBAccessorImpl:519 - Add FK constraint failed, constraintName = FK_scv_component_id, tableName = servicecomponent_version
18 Oct 2017 16:42:57,223 ERROR [main] SchemaUpgradeHelper:211 - Upgrade failed. 
java.sql.SQLException: Cannot add foreign key constraint
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
        at org.apache.ambari.server.orm.DBAccessorImpl.executeQuery(DBAccessorImpl.java:872)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:517)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.createComponentVersionTable(UpgradeCatalog250.java:836)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.executeDDLUpdates(UpgradeCatalog250.java:173)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:209)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:427)
18 Oct 2017 16:42:57,224 ERROR [main] SchemaUpgradeHelper:446 - Exception occurred during upgrade, failed
org.apache.ambari.server.AmbariException: Cannot add foreign key constraint
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:212)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:427)
Caused by: java.sql.SQLException: Cannot add foreign key constraint
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
        at org.apache.ambari.server.orm.DBAccessorImpl.executeQuery(DBAccessorImpl.java:872)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:517)
        at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:482)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.createComponentVersionTable(UpgradeCatalog250.java:836)
        at org.apache.ambari.server.upgrade.UpgradeCatalog250.executeDDLUpdates(UpgradeCatalog250.java:173)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:925)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:209)
        ... 1 more
^A

What is wrong with the database? Can I resolve this manually?

Please, help! It was the production server and it is down!!!

1 REPLY 1

Super Guru
@Lucian Builder

Can you please take backup of your ambari database and follow below steps:

This is known issue for MySQL DBs. All the tables have to be converted to use InnoDB engine instead of MyISam.

To resolve this issue, do the following:

  1. Use the following SQL to generate the commands to alter the tables to InnoDB engine:
    SELECT concat('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
    FROM Information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'ambaridb' AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE';
  2. Once the output is displayed , remove the ' | '.
  3. Run the alter commands.
  4. Commit and exit MySQL
  5. Run the ambari-server upgrade.