[AMBARI-20387] Upgrades on MySQL Should Also Drop Indexes With Foreign Key Names

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.5.0
• Fix Version/s: 2.5.0
• Component/s: ambari-server
• Assignee: Jonathan Hurley
• Reporter: Jonathan Hurley
• Created: 09/Mar/17 21:29
• Updated: 26/Apr/17 14:13
• Resolved: 10/Mar/17 13:45

Description

There are some MySQL installations of Ambari which are correctly setup to use InnoDB, but still are missing foreign key relationships on certain tables. When the upgrade catalogs try to drop foreign keys by name, they are not found. However, when re-creating them, the existing INDEX constraints are named the same and prevent the new FKs from being created.

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:210)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.main(SchemaUpgradeHelper.java:350)
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:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
        at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
        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:1621)
        at org.apache.ambari.server.upgrade.UpgradeCatalog240.executeDDLUpdates(UpgradeCatalog240.java:294)
        at org.apache.ambari.server.upgrade.AbstractUpgradeCatalog.upgradeSchema(AbstractUpgradeCatalog.java:898)
        at org.apache.ambari.server.upgrade.SchemaUpgradeHelper.executeUpgrade(SchemaUpgradeHelper.java:207)
        ... 1 more
ERROR: Error executing schema upgrade, please check the server logs.

mysql> show create table hostcomponentdesiredstate;
----------------------------------------------------------------------------------+
| hostcomponentdesiredstate | CREATE TABLE `hostcomponentdesiredstate` (
  `cluster_id` bigint(20) NOT NULL,
  `component_name` varchar(255) NOT NULL,
  `desired_state` varchar(255) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `admin_state` varchar(32) DEFAULT NULL,
  `maintenance_state` varchar(32) NOT NULL DEFAULT 'ACTIVE',
  `security_state` varchar(32) NOT NULL DEFAULT 'UNSECURED',
  `restart_required` tinyint(1) NOT NULL DEFAULT '0',
  `host_id` bigint(20) NOT NULL,
  `desired_stack_id` bigint(20) NOT NULL,
  PRIMARY KEY (`cluster_id`,`component_name`,`host_id`,`service_name`),
  KEY `hstcmpnntdesiredstatecmpnntnme` (`component_name`,`cluster_id`,`service_name`),
  KEY `FK_hostcomponentdesiredstate_desired_stack_id` (`desired_stack_id`),
  KEY `FK_hostcomponentdesiredstate_host_id` (`host_id`),
  CONSTRAINT `FK_hcdesiredstate_host_id` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`host_id`),
  CONSTRAINT `fk_hcds_desired_stack_id` FOREIGN KEY (`desired_stack_id`) REFERENCES `stack` (`stack_id`),
  CONSTRAINT `FK_hostcomponentdesiredstate_desired_stack_id` FOREIGN KEY (`desired_stack_id`) REFERENCES `stack` (`stack_id`),
  CONSTRAINT `FK_hostcomponentdesiredstate_host_id` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`host_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

In this case, the INDEX hstcmpnntdesiredstatecmpnntnme prevents the new FK creation.

Issue Links

• links to: Reviewboard