You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by Rakesh Venkatesh <ww...@gmail.com> on 2021/04/08 14:42:27 UTC
Db schema upgrade fails from 4.14 to 4.16
Hello users
Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
failing because of the db schema upgrade. Any idea on how to fix this.
This is the commit which is causing the issue
https://github.com/apache/cloudstack/pull/4128
2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
-- Schema upgrade from 4.14.0.0 to 4.15.0.0
2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
`user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
`fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
`fk_project_account__account_id`, DROP INDEX `account_id`
2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
java.sql.SQLSyntaxErrorException: Can't DROP
'fk_project_account__account_id'; check that column/key exists
2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
(logid:) Unable to execute upgrade script
java.sql.SQLSyntaxErrorException: Can't DROP
'fk_project_account__account_id'; check that column/key exists
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
'fk_project_account__account_id'; check that column/key exists
--
Thanks and regards
Rakesh
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Harikrishna Patnala <Ha...@shapeblue.com>.
Hi Rakesh,
As Pearl mentioned I don't see any issue in upgrading from 4.14 to master. The specific error what you are getting is about the foreign key constraint being missed on that table.
Can you check on your 4.14 setup if "'fk_project_account__account_id'" foreign key is there are not on the table `cloud`.`project_account` before upgrade ?
This foreign key is there on that table since long time on CloudStack. Also please try with a different OS for management server just to see if that is causing any issue.
Thanks,
Harikrishna
________________________________
From: Rakesh Venkatesh <ww...@gmail.com>
Sent: Friday, April 9, 2021 2:13 PM
To: dev <de...@cloudstack.apache.org>
Cc: users <us...@cloudstack.apache.org>
Subject: Re: Db schema upgrade fails from 4.14 to 4.16
Hello Pearl
No, this was the first time I tried to upgrade from 4.14 to master. 4.14
was running for quite long time.
I took db backup, registered 4.16 systemvm, changed global settings and
then just deployed new packages. It failed in the first attempt.
I was able to resolve this by dropping the project_account and
project_invitation table, creating them and restarted the mgt service. This
time it applied successfully
To double check it, I created a fresh 4.14 system, did the above steps and
the upgrade still failed.
So solution right now is to drop the tables, create it again and run the
upgrade
On Thu, Apr 8, 2021 at 6:52 PM Pearl d'Silva <pe...@shapeblue.com>
wrote:
> Hi Rakesh,
>
> Was there a prior attempt made to upgrade which didn't go through
> successfully? It seems like while it failed to upgrade, the DB changes were
> applied, hence resulting in the failure that is noticed. To address the
> issue, you will have to restore the DB, if you've taken a backup of it and
> redo the upgrade.
> However, to avoid such situations, an enhancement was added with
> https://github.com/apache/cloudstack/pull/4582 which prevents upgrading
> the DB in case of missing systemvm template.
>
> Thanks
> Pearl
>
> ________________________________
> From: Andrija Panic <an...@gmail.com>
> Sent: Thursday, April 8, 2021 9:09 PM
> To: dev <de...@cloudstack.apache.org>
> Cc: users <us...@cloudstack.apache.org>
> Subject: Re: Db schema upgrade fails from 4.14 to 4.16
>
> 4.16 doesn't exist yet, and it's the master branch - so failures are
> expected...
>
> On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <<http://>www.rakeshv.com@gmail.com<
> http://www.rakeshv.com@gmail.com>>
> wrote:
>
> > Hello users
> >
> >
> > Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> > failing because of the db schema upgrade. Any idea on how to fix this.
> >
> > This is the commit which is causing the issue
> > https://github.com/apache/cloudstack/pull/4128
> >
> >
> >
> > 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > -- Schema upgrade from 4.14.0.0 to 4.15.0.0
> >
> > 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> > `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> > AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> > KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> > DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> > 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> > `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> > REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> > `fk_project_account__account_id`, DROP INDEX `account_id`
> > 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> > (logid:) Unable to execute upgrade script
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
> >
> >
> > Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > --
> > Thanks and regards
> > Rakesh
> >
>
>
> --
>
> Andrija Panić
>
> pearl.dsilva@shapeblue.com
> www.shapeblue.com<http://www.shapeblue.com>
> 3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
> @shapeblue
>
>
>
>
--
Thanks and regards
Rakesh venkatesh
Harikrishna.Patnala@shapeblue.com
www.shapeblue.com
3 London Bridge Street, 3rd floor, News Building, London SE1 9SG
@shapeblue
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Harikrishna Patnala <Ha...@shapeblue.com>.
Hi Rakesh,
As Pearl mentioned I don't see any issue in upgrading from 4.14 to master. The specific error what you are getting is about the foreign key constraint being missed on that table.
Can you check on your 4.14 setup if "'fk_project_account__account_id'" foreign key is there are not on the table `cloud`.`project_account` before upgrade ?
This foreign key is there on that table since long time on CloudStack. Also please try with a different OS for management server just to see if that is causing any issue.
Thanks,
Harikrishna
________________________________
From: Rakesh Venkatesh <ww...@gmail.com>
Sent: Friday, April 9, 2021 2:13 PM
To: dev <de...@cloudstack.apache.org>
Cc: users <us...@cloudstack.apache.org>
Subject: Re: Db schema upgrade fails from 4.14 to 4.16
Hello Pearl
No, this was the first time I tried to upgrade from 4.14 to master. 4.14
was running for quite long time.
I took db backup, registered 4.16 systemvm, changed global settings and
then just deployed new packages. It failed in the first attempt.
I was able to resolve this by dropping the project_account and
project_invitation table, creating them and restarted the mgt service. This
time it applied successfully
To double check it, I created a fresh 4.14 system, did the above steps and
the upgrade still failed.
So solution right now is to drop the tables, create it again and run the
upgrade
On Thu, Apr 8, 2021 at 6:52 PM Pearl d'Silva <pe...@shapeblue.com>
wrote:
> Hi Rakesh,
>
> Was there a prior attempt made to upgrade which didn't go through
> successfully? It seems like while it failed to upgrade, the DB changes were
> applied, hence resulting in the failure that is noticed. To address the
> issue, you will have to restore the DB, if you've taken a backup of it and
> redo the upgrade.
> However, to avoid such situations, an enhancement was added with
> https://github.com/apache/cloudstack/pull/4582 which prevents upgrading
> the DB in case of missing systemvm template.
>
> Thanks
> Pearl
>
> ________________________________
> From: Andrija Panic <an...@gmail.com>
> Sent: Thursday, April 8, 2021 9:09 PM
> To: dev <de...@cloudstack.apache.org>
> Cc: users <us...@cloudstack.apache.org>
> Subject: Re: Db schema upgrade fails from 4.14 to 4.16
>
> 4.16 doesn't exist yet, and it's the master branch - so failures are
> expected...
>
> On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <<http://>www.rakeshv.com@gmail.com<
> http://www.rakeshv.com@gmail.com>>
> wrote:
>
> > Hello users
> >
> >
> > Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> > failing because of the db schema upgrade. Any idea on how to fix this.
> >
> > This is the commit which is causing the issue
> > https://github.com/apache/cloudstack/pull/4128
> >
> >
> >
> > 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > -- Schema upgrade from 4.14.0.0 to 4.15.0.0
> >
> > 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> > `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> > AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> > KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> > DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> > 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> > `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> > REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> > `fk_project_account__account_id`, DROP INDEX `account_id`
> > 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> > (logid:) Unable to execute upgrade script
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
> >
> >
> > Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > --
> > Thanks and regards
> > Rakesh
> >
>
>
> --
>
> Andrija Panić
>
> pearl.dsilva@shapeblue.com
> www.shapeblue.com<http://www.shapeblue.com>
> 3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
> @shapeblue
>
>
>
>
--
Thanks and regards
Rakesh venkatesh
Harikrishna.Patnala@shapeblue.com
www.shapeblue.com
3 London Bridge Street, 3rd floor, News Building, London SE1 9SG
@shapeblue
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Rakesh Venkatesh <ww...@gmail.com>.
Hello Pearl
No, this was the first time I tried to upgrade from 4.14 to master. 4.14
was running for quite long time.
I took db backup, registered 4.16 systemvm, changed global settings and
then just deployed new packages. It failed in the first attempt.
I was able to resolve this by dropping the project_account and
project_invitation table, creating them and restarted the mgt service. This
time it applied successfully
To double check it, I created a fresh 4.14 system, did the above steps and
the upgrade still failed.
So solution right now is to drop the tables, create it again and run the
upgrade
On Thu, Apr 8, 2021 at 6:52 PM Pearl d'Silva <pe...@shapeblue.com>
wrote:
> Hi Rakesh,
>
> Was there a prior attempt made to upgrade which didn't go through
> successfully? It seems like while it failed to upgrade, the DB changes were
> applied, hence resulting in the failure that is noticed. To address the
> issue, you will have to restore the DB, if you've taken a backup of it and
> redo the upgrade.
> However, to avoid such situations, an enhancement was added with
> https://github.com/apache/cloudstack/pull/4582 which prevents upgrading
> the DB in case of missing systemvm template.
>
> Thanks
> Pearl
>
> ________________________________
> From: Andrija Panic <an...@gmail.com>
> Sent: Thursday, April 8, 2021 9:09 PM
> To: dev <de...@cloudstack.apache.org>
> Cc: users <us...@cloudstack.apache.org>
> Subject: Re: Db schema upgrade fails from 4.14 to 4.16
>
> 4.16 doesn't exist yet, and it's the master branch - so failures are
> expected...
>
> On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <www.rakeshv.com@gmail.com<
> http://www.rakeshv.com@gmail.com>>
> wrote:
>
> > Hello users
> >
> >
> > Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> > failing because of the db schema upgrade. Any idea on how to fix this.
> >
> > This is the commit which is causing the issue
> > https://github.com/apache/cloudstack/pull/4128
> >
> >
> >
> > 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > -- Schema upgrade from 4.14.0.0 to 4.15.0.0
> >
> > 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> > `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> > AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> > KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> > DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> > 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> > `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> > REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> > `fk_project_account__account_id`, DROP INDEX `account_id`
> > 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> > (logid:) Unable to execute upgrade script
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
> >
> >
> > Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > --
> > Thanks and regards
> > Rakesh
> >
>
>
> --
>
> Andrija Panić
>
> pearl.dsilva@shapeblue.com
> www.shapeblue.com
> 3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
> @shapeblue
>
>
>
>
--
Thanks and regards
Rakesh venkatesh
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Rakesh Venkatesh <ww...@gmail.com>.
Hello Pearl
No, this was the first time I tried to upgrade from 4.14 to master. 4.14
was running for quite long time.
I took db backup, registered 4.16 systemvm, changed global settings and
then just deployed new packages. It failed in the first attempt.
I was able to resolve this by dropping the project_account and
project_invitation table, creating them and restarted the mgt service. This
time it applied successfully
To double check it, I created a fresh 4.14 system, did the above steps and
the upgrade still failed.
So solution right now is to drop the tables, create it again and run the
upgrade
On Thu, Apr 8, 2021 at 6:52 PM Pearl d'Silva <pe...@shapeblue.com>
wrote:
> Hi Rakesh,
>
> Was there a prior attempt made to upgrade which didn't go through
> successfully? It seems like while it failed to upgrade, the DB changes were
> applied, hence resulting in the failure that is noticed. To address the
> issue, you will have to restore the DB, if you've taken a backup of it and
> redo the upgrade.
> However, to avoid such situations, an enhancement was added with
> https://github.com/apache/cloudstack/pull/4582 which prevents upgrading
> the DB in case of missing systemvm template.
>
> Thanks
> Pearl
>
> ________________________________
> From: Andrija Panic <an...@gmail.com>
> Sent: Thursday, April 8, 2021 9:09 PM
> To: dev <de...@cloudstack.apache.org>
> Cc: users <us...@cloudstack.apache.org>
> Subject: Re: Db schema upgrade fails from 4.14 to 4.16
>
> 4.16 doesn't exist yet, and it's the master branch - so failures are
> expected...
>
> On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <www.rakeshv.com@gmail.com<
> http://www.rakeshv.com@gmail.com>>
> wrote:
>
> > Hello users
> >
> >
> > Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> > failing because of the db schema upgrade. Any idea on how to fix this.
> >
> > This is the commit which is causing the issue
> > https://github.com/apache/cloudstack/pull/4128
> >
> >
> >
> > 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > -- Schema upgrade from 4.14.0.0 to 4.15.0.0
> >
> > 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> > `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> > AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> > KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> > DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> > 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> > `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> > REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> > `fk_project_account__account_id`, DROP INDEX `account_id`
> > 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> > (logid:) Unable to execute upgrade script
> > java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
> >
> >
> > Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> > 'fk_project_account__account_id'; check that column/key exists
> > --
> > Thanks and regards
> > Rakesh
> >
>
>
> --
>
> Andrija Panić
>
> pearl.dsilva@shapeblue.com
> www.shapeblue.com
> 3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
> @shapeblue
>
>
>
>
--
Thanks and regards
Rakesh venkatesh
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Pearl d'Silva <pe...@shapeblue.com>.
Hi Rakesh,
Was there a prior attempt made to upgrade which didn't go through successfully? It seems like while it failed to upgrade, the DB changes were applied, hence resulting in the failure that is noticed. To address the issue, you will have to restore the DB, if you've taken a backup of it and redo the upgrade.
However, to avoid such situations, an enhancement was added with https://github.com/apache/cloudstack/pull/4582 which prevents upgrading the DB in case of missing systemvm template.
Thanks
Pearl
________________________________
From: Andrija Panic <an...@gmail.com>
Sent: Thursday, April 8, 2021 9:09 PM
To: dev <de...@cloudstack.apache.org>
Cc: users <us...@cloudstack.apache.org>
Subject: Re: Db schema upgrade fails from 4.14 to 4.16
4.16 doesn't exist yet, and it's the master branch - so failures are
expected...
On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <ww...@gmail.com>>
wrote:
> Hello users
>
>
> Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> failing because of the db schema upgrade. Any idea on how to fix this.
>
> This is the commit which is causing the issue
> https://github.com/apache/cloudstack/pull/4128
>
>
>
> 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> -- Schema upgrade from 4.14.0.0 to 4.15.0.0
>
> 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> `fk_project_account__account_id`, DROP INDEX `account_id`
> 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> (logid:) Unable to execute upgrade script
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
>
>
> Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> --
> Thanks and regards
> Rakesh
>
--
Andrija Panić
pearl.dsilva@shapeblue.com
www.shapeblue.com
3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
@shapeblue
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Pearl d'Silva <pe...@shapeblue.com>.
Hi Rakesh,
Was there a prior attempt made to upgrade which didn't go through successfully? It seems like while it failed to upgrade, the DB changes were applied, hence resulting in the failure that is noticed. To address the issue, you will have to restore the DB, if you've taken a backup of it and redo the upgrade.
However, to avoid such situations, an enhancement was added with https://github.com/apache/cloudstack/pull/4582 which prevents upgrading the DB in case of missing systemvm template.
Thanks
Pearl
________________________________
From: Andrija Panic <an...@gmail.com>
Sent: Thursday, April 8, 2021 9:09 PM
To: dev <de...@cloudstack.apache.org>
Cc: users <us...@cloudstack.apache.org>
Subject: Re: Db schema upgrade fails from 4.14 to 4.16
4.16 doesn't exist yet, and it's the master branch - so failures are
expected...
On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <ww...@gmail.com>>
wrote:
> Hello users
>
>
> Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> failing because of the db schema upgrade. Any idea on how to fix this.
>
> This is the commit which is causing the issue
> https://github.com/apache/cloudstack/pull/4128
>
>
>
> 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> -- Schema upgrade from 4.14.0.0 to 4.15.0.0
>
> 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> `fk_project_account__account_id`, DROP INDEX `account_id`
> 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> (logid:) Unable to execute upgrade script
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
>
>
> Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> --
> Thanks and regards
> Rakesh
>
--
Andrija Panić
pearl.dsilva@shapeblue.com
www.shapeblue.com
3 London Bridge Street, 3rd floor, News Building, London SE1 9SGUK
@shapeblue
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Andrija Panic <an...@gmail.com>.
4.16 doesn't exist yet, and it's the master branch - so failures are
expected...
On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <ww...@gmail.com>
wrote:
> Hello users
>
>
> Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> failing because of the db schema upgrade. Any idea on how to fix this.
>
> This is the commit which is causing the issue
> https://github.com/apache/cloudstack/pull/4128
>
>
>
> 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> -- Schema upgrade from 4.14.0.0 to 4.15.0.0
>
> 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> `fk_project_account__account_id`, DROP INDEX `account_id`
> 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> (logid:) Unable to execute upgrade script
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
>
>
> Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> --
> Thanks and regards
> Rakesh
>
--
Andrija Panić
Re: Db schema upgrade fails from 4.14 to 4.16
Posted by Andrija Panic <an...@gmail.com>.
4.16 doesn't exist yet, and it's the master branch - so failures are
expected...
On Thu, 8 Apr 2021 at 16:42, Rakesh Venkatesh <ww...@gmail.com>
wrote:
> Hello users
>
>
> Im trying to upgrade cloudstack from 4.14.0 to 4.16.0 and the upgrade is
> failing because of the db schema upgrade. Any idea on how to fix this.
>
> This is the commit which is causing the issue
> https://github.com/apache/cloudstack/pull/4128
>
>
>
> 2021-04-08 13:59:26,983 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
> -- Schema upgrade from 4.14.0.0 to 4.15.0.0
>
> 2021-04-08 13:59:27,002 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> Error executing: ALTER TABLE `cloud`.`project_account` ADD COLUMN
> `user_id` bigint unsigned COMMENT 'ID of user to be added to the project'
> AFTER `account_id`, ADD CONSTRAINT `fk_project_account__user_id` FOREIGN
> KEY `fk_project_account__user_id`(`user_id`) REFERENCES `user`(`id`) ON
> DELETE CASCADE, ADD COLUMN `project_role_id` bigint unsigned COMMENT
> 'Project role id' AFTER `project_account_id`, ADD CONSTRAINT
> `fk_project_account__project_role_id` FOREIGN KEY (`project_role_id`)
> REFERENCES `project_role` (`id`) ON DELETE SET NULL, DROP FOREIGN KEY
> `fk_project_account__account_id`, DROP INDEX `account_id`
> 2021-04-08 13:59:27,003 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> 2021-04-08 13:59:27,004 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
> (logid:) Unable to execute upgrade script
> java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
>
>
> Caused by: java.sql.SQLSyntaxErrorException: Can't DROP
> 'fk_project_account__account_id'; check that column/key exists
> --
> Thanks and regards
> Rakesh
>
--
Andrija Panić