Wei, thanks a lot. That has resolved the problem and I have managed to finish the upgrade to 4.18.0 and start acs management server.
Cheers Andrei ----- Original Message ----- > From: "Wei ZHOU" <[email protected]> > To: "users" <[email protected]> > Sent: Monday, 17 April, 2023 18:57:19 > Subject: Re: ACS upgrade SQL script error 4.17.2 > 4.18.0 > This seems to be a bug with the original commit: > https://github.com/apache/cloudstack/commit/dc151115be3e922933ea26ab1507eb6469a91e11 > It was committed to 4.4.0, but the SQL changes were added > to setup/db/db/schema-40to410.sql, which caused the users who used > 4.1.0/4.2.x/4.3.x not to have the SQL changes. > > I think what to do are: > > 1. restore the old database > > 2. create table `autoscale_vmgroup_vm_map` if not exist > CREATE TABLE `cloud`.`autoscale_vmgroup_vm_map` ( > `id` bigint unsigned NOT NULL auto_increment, > `vmgroup_id` bigint unsigned NOT NULL, > `instance_id` bigint unsigned NOT NULL, > PRIMARY KEY (`id`), > CONSTRAINT `fk_autoscale_vmgroup_vm_map__vmgroup_id` FOREIGN KEY > `fk_autoscale_vmgroup_vm_map__vmgroup_id` (`vmgroup_id`) REFERENCES > `autoscale_vmgroups` (`id`) ON DELETE CASCADE, > CONSTRAINT `fk_autoscale_vmgroup_vm_map__instance_id` FOREIGN KEY > `fk_autoscale_vmgroup_vm_map__instance_id` (`instance_id`) REFERENCES > `vm_instance` (`id`), > INDEX `i_autoscale_vmgroup_vm_map__vmgroup_id`(`vmgroup_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > 3. add column last_quiet_time to table autoscale_policies if not exist > ALTER TABLE `cloud`.`autoscale_policies` ADD COLUMN `last_quiet_time` > datetime DEFAULT NULL AFTER `quiet_time`; > > 4. add column last_interval to table autoscale_vmgroups if not exist > ALTER TABLE `cloud`.`autoscale_vmgroups` ADD COLUMN `last_interval` > datetime DEFAULT NULL AFTER `interval`; > > 5. Upgrade and restart mgmt server > > > -Wei > > On Mon, 17 Apr 2023 at 19:39, Andrei Mikhailovsky <[email protected]> > wrote: > >> Rohit, >> >> Done some more checks and I don't have this table in db backups dating >> back early 2021. I don't have older backups than that. So it seems that >> this table didn't exist in my setup for ages, if ever at all. >> >> Andrei >> >> ----- Original Message ----- >> > From: "Rohit Yadav" <[email protected]> >> > To: "users" <[email protected]> >> > Sent: Monday, 17 April, 2023 18:14:02 >> > Subject: Re: ACS upgrade SQL script error 4.17.2 > 4.18.0 >> >> > Hi Andrei, >> > >> > It appears your database schema isn't in right order, the >> > cloud.autoscale_vmgroup_vm_map table is created when we install/setup >> > cloudstack for the first time and created by >> > >> https://github.com/apache/cloudstack/blob/main/engine/schema/src/main/resources/META-INF/db/schema-40to410.sql#L405 >> > >> > Did you perhaps run the cloudstack-setup-databases or anything similar >> on your >> > database? If this is prod. DB you can try reverting to your backup and >> try >> > again. >> > >> > >> > Regards. >> > >> > ________________________________ >> > From: Andrei Mikhailovsky <[email protected]> >> > Sent: Monday, April 17, 2023 18:09 >> > To: users <[email protected]> >> > Subject: ACS upgrade SQL script error 4.17.2 > 4.18.0 >> > >> > Hello everyone, >> > >> > I've done an upgrade of ACS from 4.17.2 to 4.18.0 and faced a problem. >> The >> > management service didn't start. Log investigation showed an error >> during the >> > database upgrade script: >> > >> > 2023-04-17 13:23:26,342 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:) >> > java.sql.SQLSyntaxErrorException: Table 'cloud.autoscale_vmgroup_vm_map' >> > doesn't exist >> > 2023-04-17 13:23:26,342 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null) >> > (logid:) Unable to execute upgrade script >> > java.sql.SQLSyntaxErrorException: Table 'cloud.autoscale_vmgroup_vm_map' >> doesn't >> > exist >> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185) >> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87) >> > >> > [....] >> > >> > 2023-04-17 13:23:26,344 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null) >> > (logid:) Unable to upgrade the database >> > com.cloud.utils.exception.CloudRuntimeException: Unable to execute >> upgrade >> > script >> > at >> > >> com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:232) >> > at >> > >> com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:310) >> > at >> > >> com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:401) >> > at >> > >> org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.java:64) >> > at >> > >> org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:54) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:178) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:54) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:356) >> > at java.base/java.lang.Iterable.forEach(Iterable.java:75) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:155) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:123) >> > at >> > >> org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:935) >> > at java.base/java.lang.Iterable.forEach(Iterable.java:75) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:155) >> > at >> > >> org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:123) >> > at >> > >> org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:935) >> > at >> > >> org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:586) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContext(DefaultModuleDefinitionSet.java:144) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet$2.with(DefaultModuleDefinitionSet.java:121) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:244) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:249) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:232) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContexts(DefaultModuleDefinitionSet.java:116) >> > at >> > >> org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.load(DefaultModuleDefinitionSet.java:78) >> > at >> > >> org.apache.cloudstack.spring.module.factory.ModuleBasedContextFactory.loadModules(ModuleBasedContextFactory.java:37) >> > at >> org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.init(Clou >> > >> > [.....] >> > >> > >> > 2023-04-17 13:23:26,349 DEBUG [c.c.u.d.T.Transaction] (main:null) >> (logid:) >> > Rolling back the transaction: Time = 631 Name = Upgrade; called by >> > >> -TransactionLegacy.rollback:888-TransactionLegacy.removeUpTo:831-TransactionLegacy.close:655-DatabaseUpgradeChecker.upgrade:325-DatabaseUpgradeChecker.check:401-CloudStackExtendedLifeCycle.checkIntegrity:64-CloudStackExtendedLifeCycle.start:54-DefaultLifecycleProcessor.doStart:178-DefaultLifecycleProcessor.access$200:54-DefaultLifecycleProcessor$LifecycleGroup.start:356-Iterable.forEach:75-DefaultLifecycleProcessor.startBeans:155 >> > >> > >> > My setup: >> > >> > Ubuntu 20.04.x with latest updates for both management, agent and usage >> servers. >> > DB: mariadb-server 1:10.3.38-0ubuntu0.20.04.1 >> > >> > Has anyone faced that issue? How do I solve it? >> > >> > Many thanks >> > >> > Andrei
