Error in backing up cloud database

[Indra Pramana <in...@sg.or.id>]

Dear all,

I tried to perform another upgrade today with Abhinav's assistance, and
right now I am not able to backup the cloud database. This is the error
when doing mysqldump:

root@pri-db-01:~/backup/20131001# mysqldump -u cloud -p cloud >
cloudstack-backup3.sql
Enter password:
mysqldump: Couldn't execute 'show create table `template_view`': View
'cloud.template_view' references invalid table(s) or column(s) or
function(s) or definer/invoker of view lack rights to use them (1356)

When I tried to do a select command to the table directly, I got this error
message:

mysql> select * from template_view;
ERROR 1356 (HY000): View 'cloud.template_view' references invalid table(s)
or column(s) or function(s) or definer/invoker of view lack rights to use
them

We noted that the problem started to happen last week when I performed a
failed upgrade attempt from 4.1.1 to 4.2.0. At that time, the database was
already upgraded but because of some issues, we needed to revert back to
4.1.1 and I did a restore of the 4.1.1 version database. However, there was
no error message during restoration and CloudStack was running fine since
then so I didn't realise about this problem until today when I perform
another upgrade attempt.

Anyone has encountered this problem before and what can we do to resolve
this problem?

Looking forward to your reply, thank you.

Cheers.

RE: Error in backing up cloud database

[David Ortiz <dp...@outlook.com>]

Indra,
     Have you tried importing the backup into an empty database and seeing if that query works?
Thanks,     David Ortiz

> Date: Tue, 1 Oct 2013 21:25:57 +0800
> Subject: Error in backing up cloud database
> From: indra@sg.or.id
> To: dev@cloudstack.apache.org; users@cloudstack.apache.org
> 
> Dear all,
> 
> I tried to perform another upgrade today with Abhinav's assistance, and
> right now I am not able to backup the cloud database. This is the error
> when doing mysqldump:
> 
> root@pri-db-01:~/backup/20131001# mysqldump -u cloud -p cloud >
> cloudstack-backup3.sql
> Enter password:
> mysqldump: Couldn't execute 'show create table `template_view`': View
> 'cloud.template_view' references invalid table(s) or column(s) or
> function(s) or definer/invoker of view lack rights to use them (1356)
> 
> When I tried to do a select command to the table directly, I got this error
> message:
> 
> mysql> select * from template_view;
> ERROR 1356 (HY000): View 'cloud.template_view' references invalid table(s)
> or column(s) or function(s) or definer/invoker of view lack rights to use
> them
> 
> We noted that the problem started to happen last week when I performed a
> failed upgrade attempt from 4.1.1 to 4.2.0. At that time, the database was
> already upgraded but because of some issues, we needed to revert back to
> 4.1.1 and I did a restore of the 4.1.1 version database. However, there was
> no error message during restoration and CloudStack was running fine since
> then so I didn't realise about this problem until today when I perform
> another upgrade attempt.
> 
> Anyone has encountered this problem before and what can we do to resolve
> this problem?
> 
> Looking forward to your reply, thank you.
> 
> Cheers.
 		 	   		  

Re: Error in backing up cloud database

[Indra Pramana <in...@sg.or.id>]

Dear all,

We just realised that the 'template_view' is a new view introduced only on
Cloudstack database version 4.2. CloudStack 4.1.1 doesn't have this. It
seems that during my failed upgrade attempt last time, I did a restore of
the 4.1.1 database over the existing 4.2 database (instead of over an empty
database), causing some new views/tables introduced on 4.2 still exists on
my restored 4.1.1 database.

Anyone can advise how can I do a clean-up of the existing database so that
I can do a proper upgrade from 4.1.1 to 4.2? Other than template_view, any
other view/tables or column (on an existing table) I need to delete?

Looking forward to your reply, thank you.

Cheers.



On Tue, Oct 1, 2013 at 9:25 PM, Indra Pramana <in...@sg.or.id> wrote:

> Dear all,
>
> I tried to perform another upgrade today with Abhinav's assistance, and
> right now I am not able to backup the cloud database. This is the error
> when doing mysqldump:
>
> root@pri-db-01:~/backup/20131001# mysqldump -u cloud -p cloud >
> cloudstack-backup3.sql
> Enter password:
> mysqldump: Couldn't execute 'show create table `template_view`': View
> 'cloud.template_view' references invalid table(s) or column(s) or
> function(s) or definer/invoker of view lack rights to use them (1356)
>
> When I tried to do a select command to the table directly, I got this
> error message:
>
> mysql> select * from template_view;
> ERROR 1356 (HY000): View 'cloud.template_view' references invalid table(s)
> or column(s) or function(s) or definer/invoker of view lack rights to use
> them
>
> We noted that the problem started to happen last week when I performed a
> failed upgrade attempt from 4.1.1 to 4.2.0. At that time, the database was
> already upgraded but because of some issues, we needed to revert back to
> 4.1.1 and I did a restore of the 4.1.1 version database. However, there was
> no error message during restoration and CloudStack was running fine since
> then so I didn't realise about this problem until today when I perform
> another upgrade attempt.
>
> Anyone has encountered this problem before and what can we do to resolve
> this problem?
>
> Looking forward to your reply, thank you.
>
> Cheers.
>
>

Re: Error in backing up cloud database

[Indra Pramana <in...@sg.or.id>]

Dear all,

We just realised that the 'template_view' is a new view introduced only on
Cloudstack database version 4.2. CloudStack 4.1.1 doesn't have this. It
seems that during my failed upgrade attempt last time, I did a restore of
the 4.1.1 database over the existing 4.2 database (instead of over an empty
database), causing some new views/tables introduced on 4.2 still exists on
my restored 4.1.1 database.

Anyone can advise how can I do a clean-up of the existing database so that
I can do a proper upgrade from 4.1.1 to 4.2? Other than template_view, any
other view/tables or column (on an existing table) I need to delete?

Looking forward to your reply, thank you.

Cheers.



On Tue, Oct 1, 2013 at 9:25 PM, Indra Pramana <in...@sg.or.id> wrote:

> Dear all,
>
> I tried to perform another upgrade today with Abhinav's assistance, and
> right now I am not able to backup the cloud database. This is the error
> when doing mysqldump:
>
> root@pri-db-01:~/backup/20131001# mysqldump -u cloud -p cloud >
> cloudstack-backup3.sql
> Enter password:
> mysqldump: Couldn't execute 'show create table `template_view`': View
> 'cloud.template_view' references invalid table(s) or column(s) or
> function(s) or definer/invoker of view lack rights to use them (1356)
>
> When I tried to do a select command to the table directly, I got this
> error message:
>
> mysql> select * from template_view;
> ERROR 1356 (HY000): View 'cloud.template_view' references invalid table(s)
> or column(s) or function(s) or definer/invoker of view lack rights to use
> them
>
> We noted that the problem started to happen last week when I performed a
> failed upgrade attempt from 4.1.1 to 4.2.0. At that time, the database was
> already upgraded but because of some issues, we needed to revert back to
> 4.1.1 and I did a restore of the 4.1.1 version database. However, there was
> no error message during restoration and CloudStack was running fine since
> then so I didn't realise about this problem until today when I perform
> another upgrade attempt.
>
> Anyone has encountered this problem before and what can we do to resolve
> this problem?
>
> Looking forward to your reply, thank you.
>
> Cheers.
>
>