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:[email protected]]
Sent: Monday, August 20, 2012 4:23 AM
To: Alena Prokharchyk; [email protected]
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:[email protected]]
Sent: Tuesday, August 14, 2012 1:31 AM
To: [email protected]
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.
