ambari-server mysql schema database upgrade from 2.2.2 to 2.4.1 fails

[#1]

i get below error when i upgraded the mysql schema

[root@hbase-perf-amb yum.repos.d]# ambari-server upgrade

Using python /usr/bin/python

Upgrading ambari-server

Updating properties in ambari.properties ...

WARNING: Original file ambari-env.sh kept

Fixing database objects owner

Ambari Server configured for MySQL. Confirm you have made a backup of the Ambari Server database [y/n] (y)? y

Upgrading database schema

Error output from schema upgrade command:

Exception in thread "main" org.apache.ambari.server.AmbariException: Duplicate key name 'hstcmpnntdesiredstatecmpnntnme'

at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:207)

at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:347)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'hstcmpnntdesiredstatecmpnntnme'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.Util.getInstance(Util.java:386)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)

at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:824)

at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:667)

at org.apache.ambari.server.orm.DBAccessorImpl.executeQuery(DBAccessorImpl.java:827)

at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:472)

at org.apache.ambari.server.orm.DBAccessorImpl.addFKConstraint(DBAccessorImpl.java:456)

at org.apache.ambari.server.upgrade.UpgradeCatalog240.updateServiceComponentDesiredStateTableDDL(UpgradeCatalog240.java:1630)

at org.apache.ambari.server.upgrade.UpgradeCatalog240.executeDDLUpdates(UpgradeCatalog240.java:303)

at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:889)

at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:204)

... 1 more

Please suggest how this can be resolved..

[#2 · Accepted solution]

@Pradheep Shan

Try to drop that constraint from your ambari database as following, then continue the upgrade and then add the constraint back to the table.

ALTER TABLE hostcomponentdesiredstate drop foreign key hstcmpnntdesiredstatecmpnntnme;
flush privileges

.

[#3]

@jss. Thank you for the reply. The solution worked as you suggested.

Should the upgrade package has to be fixed or the upgrade from 2.2.2 to 2.4.1 is not supported/tested?

Also the grafana UI has a bug,

"Problem! java.lang.Exception: Invalid number of functions specified."

[#4]

@Pradheep Shan

Good to know that your issue is resolved.

For the issue [Duplicate key name 'hstcmpnntdesiredstatecmpnntnme'] i guess you should file an Ambari bug.

Regarding grafana UI has a bug. I am not sure when exactly you are getting that error. May be as part of a separate Discussion thread we can discuss about that.