You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ambari.apache.org by "Jonathan Hurley (JIRA)" <ji...@apache.org> on 2017/03/09 21:29:38 UTC
[jira] [Created] (AMBARI-20387) Upgrades on MySQL Should Also Drop
Indexes With Foreign Key Names
Jonathan Hurley created AMBARI-20387:
----------------------------------------
Summary: Upgrades on MySQL Should Also Drop Indexes With Foreign Key Names
Key: AMBARI-20387
URL: https://issues.apache.org/jira/browse/AMBARI-20387
Project: Ambari
Issue Type: Bug
Components: ambari-server
Affects Versions: 2.5.0
Reporter: Jonathan Hurley
Assignee: Jonathan Hurley
Fix For: 2.5.0
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.
{code}
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.
{code}
{code}
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 |
{code}
In this case, the {{INDEX}} {{hstcmpnntdesiredstatecmpnntnme}} prevents the new FK creation.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)