[jira] [Updated] (AMBARI-20387) Upgrades on MySQL Should Also Drop Indexes With Foreign Key Names
[
https://issues.apache.org/jira/browse/AMBARI-20387?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jonathan Hurley updated AMBARI-20387:
-
Resolution: Fixed
Status: Resolved (was: Patch Available)
> 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
>
> Attachments: AMBARI-20387.patch
>
>
> 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`),
> CON
[jira] [Updated] (AMBARI-20387) Upgrades on MySQL Should Also Drop Indexes With Foreign Key Names
[
https://issues.apache.org/jira/browse/AMBARI-20387?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jonathan Hurley updated AMBARI-20387:
-
Attachment: AMBARI-20387.patch
> 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
>
> Attachments: AMBARI-20387.patch
>
>
> 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_hostcomponentdesiredstat
[jira] [Updated] (AMBARI-20387) Upgrades on MySQL Should Also Drop Indexes With Foreign Key Names
[
https://issues.apache.org/jira/browse/AMBARI-20387?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jonathan Hurley updated AMBARI-20387:
-
Status: Patch Available (was: Open)
> 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
> (`d
