Hmm.. I have no idea yet what is causing this. Can either one of you send me the current ddl (like a mysqldump without data) of that table? I can easily fix it by adding a "limit 1" to the subquery, but I would like to understand why it is returning more results for you.
Cheers, Hugo -----Original Message----- From: Prachi Damle [mailto:prachi.da...@citrix.com] Sent: Wednesday, August 22, 2012 12:53 AM To: cloudstack-dev@incubator.apache.org; Alena Prokharchyk Subject: RE: DB upgrade to 4.0 version Hugo, I also see this issue while unittesting the db upgrade. I started with a 2.2.14 db schema. Upgrade fails with this: 2012-08-21 15:29:46,510 DEBUG [utils.db.ScriptRunner] (main:null) -- rrq 5839 2012-08-21 15:29:46,510 DEBUG [utils.db.ScriptRunner] (main:null) -- Remove the unique constraint on physical_network_id, provider_name from physical_network_service_providers 2012-08-21 15:29:46,510 DEBUG [utils.db.ScriptRunner] (main:null) -- Because the name of this contraint is not set we need this roundabout way 2012-08-21 15:29:46,510 DEBUG [utils.db.ScriptRunner] (main:null) -- The key is also used by the foreign key constraint so drop and recreate that one 2012-08-21 15:29:46,510 DEBUG [utils.db.ScriptRunner] (main:null) ALTER TABLE physical_network_service_providers DROP FOREIGN KEY fk_pnetwork_service_providers__physical_network_id 2012-08-21 15:29:46,528 DEBUG [utils.db.ScriptRunner] (main:null) SET @constraintname = (select CONCAT(CONCAT('DROP INDEX ', A.CONSTRAINT_NAME), ' ON physical_network_service_providers' ) from information_schema.key_column_usage A JOIN information_schema.key_column_usage B ON B.table_name = 'physical_network_service_providers' AND B.COLUMN_NAME = 'provider_name' AND A.COLUMN_NAME ='physical_network_id' AND B.CONSTRAINT_NAME=A.CONSTRAINT_NAME where A.table_name = 'physical_network_service_providers') 2012-08-21 15:29:46,537 ERROR [utils.db.ScriptRunner] (main:null) Error executing: SET @constraintname = (select CONCAT(CONCAT('DROP INDEX ', A.CONSTRAINT_NAME), ' ON physical_network_service_providers' ) from information_schema.key_column_usage A JOIN information_schema.key_column_usage B ON B.table_name = 'physical_network_service_providers' AND B.COLUMN_NAME = 'provider_name' AND A.COLUMN_NAME ='physical_network_id' AND B.CONSTRAINT_NAME=A.CONSTRAINT_NAME where A.table_name = 'physical_network_service_providers') 2012-08-21 15:29:46,538 ERROR [utils.db.ScriptRunner] (main:null) java.sql.SQLException: Subquery returns more than 1 row 2012-08-21 15:29:46,545 ERROR [cloud.upgrade.DatabaseUpgradeChecker] (main:null) Unable to execute upgrade script: C:\tomcat\apache-tomcat-6.0.29\webapps\client\WEB-INF\lib\db\schema-302to40.sql java.sql.SQLException: Subquery returns more than 1 row at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:181) at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:79) at com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:166) at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:226) at com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:348) at com.cloud.utils.component.ComponentLocator.runCheckers(ComponentLocator.java:273) at com.cloud.utils.component.ComponentLocator.parse(ComponentLocator.java:245) at com.cloud.utils.component.ComponentLocator.getLocatorInternal(ComponentLocator.java:836) at com.cloud.utils.component.ComponentLocator.getLocator(ComponentLocator.java:874) at com.cloud.utils.component.ComponentLocator.getComponent(ComponentLocator.java:416) at com.cloud.utils.component.ComponentLocator.getComponent(ComponentLocator.java:409) at com.cloud.servlet.CloudStartupServlet.init(CloudStartupServlet.java:44) at javax.servlet.GenericServlet.init(GenericServlet.java:212) at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173) at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993) at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4350) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4659) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546) at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1041) at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:964) at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:502) at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277) at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321) at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119) at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053) at org.apache.catalina.core.StandardHost.start(StandardHost.java:785) at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045) at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:445) at org.apache.catalina.core.StandardService.start(StandardService.java:519) at org.apache.catalina.core.StandardServer.start(StandardServer.java:710) at org.apache.catalina.startup.Catalina.start(Catalina.java:581) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289) at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414) 2012-08-21 15:29:46,647 DEBUG [db.Transaction.Transaction] (main:null) Rolling back the transaction: Time = 657 Name = Upgrade; called by -Transaction.rollback:841-Transaction.removeUpTo:784-Transaction.close:603-DatabaseUpgradeChecker.upgrade:256-DatabaseUpgradeChecker.check:348-ComponentLocator.runCheckers:273-ComponentLocator.parse:245-ComponentLocator.getLocatorInternal:836-ComponentLocator.getLocator:874-ComponentLocator.getComponent:416-ComponentLocator.getComponent:409-CloudStartupServlet.init:44 -----Original Message----- From: Hugo Trippaers [mailto:htrippa...@schubergphilis.com] Sent: Monday, August 20, 2012 4:23 AM To: Alena Prokharchyk; cloudstack-dev@incubator.apache.org Subject: RE: DB upgrade to 4.0 version Hey Alena, I did some checking and unfortunately I can't reproduce your issue. I started with a fresh 3.0.2 installation using the create_schema.sql. I get only one entry in the resulting list. Can you show me the schema only dump of the database (or only of the table physical_network_service_providers ) you are having the problems with so I can see what is going on? Cheers, Hugo -----Original Message----- From: Alena Prokharchyk [mailto:alena.prokharc...@citrix.com] Sent: Tuesday, August 14, 2012 1:31 AM To: cloudstack-dev@incubator.apache.org Cc: Hugo Trippaers Subject: DB upgrade to 4.0 version I started looking at the DB upgrade part of the code for 4.0 release, and fixed the following: 1) joined 3.0.2->3.0.3 and 3.0.3->4.0 upgrade paths to one: 3.0.2 to 4.0. Did it because asf/master was branched when no 3.0.3 was released yet, and we shouldn't have merged 302->303 to asf/master. So whoever is going to make the upgrade to 4.0 Apache cloudStack, can do it from the any version up to 3.0.2 inclusively. 2) Added upgrade code for the VPC feature Hugo, I hit some error in your code during the unittest for db upgrade: mysql -u root cloud < ./setup/db/db/schema-302to40.sql ERROR 1242 (21000) at line 237: Subquery returns more than 1 row It seems to be related to commit 420f08ab4a37fa485e6318a5c2683732b5744d7e, and the following query returns more than one row: mysql> select CONCAT(CONCAT('DROP INDEX ', A.CONSTRAINT_NAME), ' ON physical_network_service_providers' ) from information_schema.key_column_usage A JOIN information_schema.key_column_usage B ON B.table_name = 'physical_network_service_providers' AND B.COLUMN_NAME = 'provider_name' AND A.COLUMN_NAME ='physical_network_id' AND B.CONSTRAINT_NAME=A.CONSTRAINT_NAME where A.table_name = 'physical_network_service_providers';+------------------------------------- --------------------------------------------------------+ | CONCAT(CONCAT('DROP INDEX ', A.CONSTRAINT_NAME), ' ON physical_network_service_providers' ) | +----------------------------------------------------------------------- +--- -------------------+ | DROP INDEX physical_network_id ON physical_network_service_providers | | DROP INDEX physical_network_id ON physical_network_service_providers | | DROP INDEX physical_network_id ON physical_network_service_providers | | DROP INDEX physical_network_id ON physical_network_service_providers | +----------------------------------------------------------------------- +--- -------------------+ 4 rows in set (0.00 sec) So could you please take a look and let me know if I did something wrong, or is it a real bug? Steps I've made to reproduce the problem: * deployed cloudStack 3.0.2, haven't added any resources, no dataCenters, nothing * ran schema-302to40.sql against this DB. Thank you, Alena.
