Hi all,
I pushed a commit to fix this issue.
I confirmed listVolumes doesn't throw an error now in my environment.

Commit 890e71cb5c2f80b09cd4db7947d94e325d7bd182 in cloudstack's branch
refs/heads/master from Yoshikazu Nojima
[ https://git-wip-us.apache.org/repos/asf?p=cloudstack.git;h=890e71c ]

CLOUDSTACK-7032 bugfix: listVolumes throws an error

Since schema change made on 4.4 is not applied to the upgrade script
from 4.4 to 4.5, listVolumes api throws an error.
This commit fix the issue.


2014-07-01 14:06 GMT-06:00 Mike Tutkowski <mike.tutkow...@solidfire.com>:
> Somehow I didn't see that aliasing going on there.
>
>
> On Tue, Jul 1, 2014 at 2:05 PM, Mike Tutkowski <mike.tutkow...@solidfire.com
>> wrote:
>
>> Ah, yes, good point.
>>
>>
>> On Tue, Jul 1, 2014 at 2:02 PM, Yoshikazu Nojima <m...@ynojima.net> wrote:
>>
>>> Hi Mike,
>>> I suppose "iso" is an alias for "vm_template".
>>> See the left join syntax you refered.
>>>
>>> >            left join
>>> >        `cloud`.`vm_template` iso ON iso.id = volumes.iso_id
>>>
>>>
>>> Regards,
>>> Noji
>>>
>>> 2014-07-01 12:19 GMT-06:00 Mike Tutkowski <mike.tutkow...@solidfire.com>:
>>> > This diff shows some of the problem:
>>> >
>>> > http://i.imgur.com/PW7lqbs.png
>>> >
>>> > Six fields have been removed from the volume_view and five of those six
>>> are
>>> > still in use.
>>> >
>>> > It is not a simple matter of adding them back in, however, because the
>>> iso
>>> > table that used to be used is no longer present in 4.5.
>>> >
>>> > That being the case, we need to figure out where to get this data from
>>> in
>>> > 4.5. I don't have a lot of context, though, on why the iso table doesn't
>>> > exist anymore.
>>> >
>>> > Thoughts?
>>> >
>>> >
>>> > On Mon, Jun 30, 2014 at 11:12 PM, Mike Tutkowski <
>>> > mike.tutkow...@solidfire.com> wrote:
>>> >
>>> >> Upon further consideration, I forgot that we drop and re-create views
>>> in
>>> >> the SQL - if need be - from release to release.
>>> >>
>>> >> It looks like a new version of the volume_view went into 4.5 with
>>> >> 11f5bdd78de4121331b07995800f6e9e7c22f2c0, which is from Review Request
>>> >> 19446.
>>> >>
>>> >> It appears volume_view is missing several columns.
>>> >>
>>> >>
>>> >> On Mon, Jun 30, 2014 at 10:30 PM, Mike Tutkowski <
>>> >> mike.tutkow...@solidfire.com> wrote:
>>> >>
>>> >>> Actually, in looking at "git blame", it appears this view has never
>>> had
>>> >>> those two columns.
>>> >>>
>>> >>> I think the intent, however, is that those two columns exist in the
>>> view.
>>> >>>
>>> >>> I'm adding them in my sandbox and testing this out now.
>>> >>>
>>> >>>
>>> >>> On Mon, Jun 30, 2014 at 10:11 PM, Mike Tutkowski <
>>> >>> mike.tutkow...@solidfire.com> wrote:
>>> >>>
>>> >>>> I looked into this a bit more.
>>> >>>>
>>> >>>> It appears that the "volume_view" no longer has the following two
>>> >>>> columns:
>>> >>>>
>>> >>>> vm_template.name template_name
>>> >>>> vm_template.display_text template_display_text
>>> >>>>
>>> >>>> The problem is that the Java class, VolumeJoinVO, still has
>>> reference to
>>> >>>> them (a variable for each one).
>>> >>>>
>>> >>>> I would just remove these variables; however, they are still in use
>>> as
>>> >>>> they are returned to clients as responses (at least we try to return
>>> them,
>>> >>>> but get an exception before being able to do so).
>>> >>>>
>>> >>>> If we really want to remove these two columns, that will mean
>>> breaking
>>> >>>> the API, which really should wait until a major release.
>>> >>>>
>>> >>>>
>>> >>>> On Mon, Jun 30, 2014 at 9:07 AM, Mike Tutkowski <
>>> >>>> mike.tutkow...@solidfire.com> wrote:
>>> >>>>
>>> >>>>> Thanks for the reply.
>>> >>>>>
>>> >>>>> The weird part is that this is the second time I've recreated the DB
>>> >>>>> and seen this issue.
>>> >>>>>
>>> >>>>> If I have time a bit later, I can see if the SQL in Git is wrong and
>>> >>>>> update it with what you provided.
>>> >>>>>
>>> >>>>> Thanks!
>>> >>>>>
>>> >>>>>
>>> >>>>> On Mon, Jun 30, 2014 at 5:47 AM, Bharat Kumar <
>>> bharat.ku...@citrix.com>
>>> >>>>> wrote:
>>> >>>>>
>>> >>>>>> Hi Mike,
>>> >>>>>>
>>> >>>>>> I think the volume_view did not get created properly for some
>>> reason.
>>> >>>>>> I think recreating the volume_view will fix the problem.
>>> >>>>>>
>>> >>>>>> below commas should fix the issue.
>>> >>>>>>
>>> >>>>>> DROP VIEW IF EXISTS `cloud`.`volume_view`;
>>> >>>>>> CREATE VIEW `cloud`.`volume_view` AS
>>> >>>>>>     select
>>> >>>>>>         volumes.id,
>>> >>>>>>         volumes.uuid,
>>> >>>>>>         volumes.name,
>>> >>>>>>         volumes.device_id,
>>> >>>>>>         volumes.volume_type,
>>> >>>>>>         volumes.size,
>>> >>>>>>         volumes.min_iops,
>>> >>>>>>         volumes.max_iops,
>>> >>>>>>         volumes.created,
>>> >>>>>>         volumes.state,
>>> >>>>>>         volumes.attached,
>>> >>>>>>         volumes.removed,
>>> >>>>>>         volumes.pod_id,
>>> >>>>>>         volumes.display_volume,
>>> >>>>>>         volumes.format,
>>> >>>>>>         volumes.path,
>>> >>>>>>         volumes.chain_info,
>>> >>>>>>         account.id account_id,
>>> >>>>>>         account.uuid account_uuid,
>>> >>>>>>         account.account_name account_name,
>>> >>>>>>         account.type account_type,
>>> >>>>>>         domain.id domain_id,
>>> >>>>>>         domain.uuid domain_uuid,
>>> >>>>>>         domain.name domain_name,
>>> >>>>>>         domain.path domain_path,
>>> >>>>>>         projects.id project_id,
>>> >>>>>>         projects.uuid project_uuid,
>>> >>>>>>         projects.name project_name,
>>> >>>>>>         data_center.id data_center_id,
>>> >>>>>>         data_center.uuid data_center_uuid,
>>> >>>>>>         data_center.name data_center_name,
>>> >>>>>>         data_center.networktype data_center_type,
>>> >>>>>>         vm_instance.id vm_id,
>>> >>>>>>         vm_instance.uuid vm_uuid,
>>> >>>>>>         vm_instance.name vm_name,
>>> >>>>>>         vm_instance.state vm_state,
>>> >>>>>>         vm_instance.vm_type,
>>> >>>>>>         user_vm.display_name vm_display_name,
>>> >>>>>>         volume_store_ref.size volume_store_size,
>>> >>>>>>         volume_store_ref.download_pct,
>>> >>>>>>         volume_store_ref.download_state,
>>> >>>>>>         volume_store_ref.error_str,
>>> >>>>>>         volume_store_ref.created created_on_store,
>>> >>>>>>         disk_offering.id disk_offering_id,
>>> >>>>>>         disk_offering.uuid disk_offering_uuid,
>>> >>>>>>         disk_offering.name disk_offering_name,
>>> >>>>>>         disk_offering.display_text disk_offering_display_text,
>>> >>>>>>         disk_offering.use_local_storage,
>>> >>>>>>         disk_offering.system_use,
>>> >>>>>>         disk_offering.bytes_read_rate,
>>> >>>>>>         disk_offering.bytes_write_rate,
>>> >>>>>>         disk_offering.iops_read_rate,
>>> >>>>>>         disk_offering.iops_write_rate,
>>> >>>>>>         disk_offering.cache_mode,
>>> >>>>>>         storage_pool.id pool_id,
>>> >>>>>>         storage_pool.uuid pool_uuid,
>>> >>>>>>         storage_pool.name pool_name,
>>> >>>>>>         cluster.hypervisor_type,
>>> >>>>>>         vm_template.id template_id,
>>> >>>>>>         vm_template.uuid template_uuid,
>>> >>>>>>         vm_template.extractable,
>>> >>>>>>         vm_template.type template_type,
>>> >>>>>>         vm_template.name template_name,
>>> >>>>>>         vm_template.display_text template_display_text,
>>> >>>>>>         iso.id iso_id,
>>> >>>>>>         iso.uuid iso_uuid,
>>> >>>>>>         iso.name iso_name,
>>> >>>>>>         iso.display_text iso_display_text,
>>> >>>>>>         resource_tags.id tag_id,
>>> >>>>>>         resource_tags.uuid tag_uuid,
>>> >>>>>>         resource_tags.key tag_key,
>>> >>>>>>         resource_tags.value tag_value,
>>> >>>>>>         resource_tags.domain_id tag_domain_id,
>>> >>>>>>         resource_tags.account_id tag_account_id,
>>> >>>>>>         resource_tags.resource_id tag_resource_id,
>>> >>>>>>         resource_tags.resource_uuid tag_resource_uuid,
>>> >>>>>>         resource_tags.resource_type tag_resource_type,
>>> >>>>>>         resource_tags.customer tag_customer,
>>> >>>>>>         async_job.id job_id,
>>> >>>>>>         async_job.uuid job_uuid,
>>> >>>>>>         async_job.job_status job_status,
>>> >>>>>>         async_job.account_id job_account_id
>>> >>>>>>     from
>>> >>>>>>         `cloud`.`volumes`
>>> >>>>>>             inner join
>>> >>>>>>         `cloud`.`account` ON volumes.account_id = account.id
>>> >>>>>>             inner join
>>> >>>>>>         `cloud`.`domain` ON volumes.domain_id = domain.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`projects` ON projects.project_account_id =
>>> account.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`data_center` ON volumes.data_center_id =
>>> >>>>>> data_center.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`vm_instance` ON volumes.instance_id =
>>> vm_instance.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`user_vm` ON user_vm.id = vm_instance.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`volume_store_ref` ON volumes.id =
>>> >>>>>> volume_store_ref.volume_id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`disk_offering` ON volumes.disk_offering_id =
>>> >>>>>> disk_offering.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`storage_pool` ON volumes.pool_id =
>>> storage_pool.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`cluster` ON storage_pool.cluster_id = cluster.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`vm_template` ON volumes.template_id =
>>> vm_template.id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`vm_template` iso ON iso.id = volumes.iso_id
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`resource_tags` ON resource_tags.resource_id =
>>> >>>>>> volumes.id
>>> >>>>>>             and resource_tags.resource_type = 'Volume'
>>> >>>>>>             left join
>>> >>>>>>         `cloud`.`async_job` ON async_job.instance_id = volumes.id
>>> >>>>>>             and async_job.instance_type = 'Volume'
>>> >>>>>>             and async_job.job_status = 0;
>>> >>>>>>
>>> >>>>>> Thanks,
>>> >>>>>> Bharat.
>>> >>>>>>
>>> >>>>>> On 28-Jun-2014, at 3:30 am, Mike Tutkowski <
>>> >>>>>> mike.tutkow...@solidfire.com> wrote:
>>> >>>>>>
>>> >>>>>> > Hi,
>>> >>>>>> >
>>> >>>>>> > If you click on the Storage tab in the GUI when you have one or
>>> more
>>> >>>>>> > volumes, you receive the following exception (is this something
>>> >>>>>> someone is
>>> >>>>>> > already working on?):
>>> >>>>>> >
>>> >>>>>> > Caused by:
>>> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
>>> >>>>>> > Unknown column 'volume_view.template_name' in 'field list'
>>> >>>>>> >    at
>>> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
>>> >>>>>> Method)
>>> >>>>>> >    at
>>> >>>>>> >
>>> >>>>>>
>>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>>> >>>>>> >    at
>>> >>>>>> >
>>> >>>>>>
>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>>> >>>>>> >    at
>>> java.lang.reflect.Constructor.newInstance(Constructor.java:526)
>>> >>>>>> >    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>>> >>>>>> >    at com.mysql.jdbc.Util.getInstance(Util.java:386)
>>> >>>>>> >    at
>>> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
>>> >>>>>> >    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
>>> >>>>>> >    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
>>> >>>>>> >    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
>>> >>>>>> >    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
>>> >>>>>> >    at
>>> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
>>> >>>>>> >    at
>>> >>>>>> >
>>> >>>>>>
>>> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
>>> >>>>>> >    at
>>> >>>>>> >
>>> >>>>>>
>>> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
>>> >>>>>> >    at
>>> >>>>>> >
>>> >>>>>>
>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
>>> >>>>>> >
>>> >>>>>> > Thanks!
>>> >>>>>> >
>>> >>>>>> > --
>>> >>>>>> > *Mike Tutkowski*
>>> >>>>>> > *Senior CloudStack Developer, SolidFire Inc.*
>>> >>>>>> > e: mike.tutkow...@solidfire.com
>>> >>>>>> > o: 303.746.7302
>>> >>>>>> > Advancing the way the world uses the cloud
>>> >>>>>> > <http://solidfire.com/solution/overview/?video=play>*™*
>>> >>>>>>
>>> >>>>>>
>>> >>>>>
>>> >>>>>
>>> >>>>> --
>>> >>>>> *Mike Tutkowski*
>>> >>>>>  *Senior CloudStack Developer, SolidFire Inc.*
>>> >>>>> e: mike.tutkow...@solidfire.com
>>> >>>>> o: 303.746.7302
>>> >>>>> Advancing the way the world uses the cloud
>>> >>>>> <http://solidfire.com/solution/overview/?video=play>*™*
>>> >>>>>
>>> >>>>
>>> >>>>
>>> >>>>
>>> >>>> --
>>> >>>> *Mike Tutkowski*
>>> >>>> *Senior CloudStack Developer, SolidFire Inc.*
>>> >>>> e: mike.tutkow...@solidfire.com
>>> >>>> o: 303.746.7302
>>> >>>> Advancing the way the world uses the cloud
>>> >>>> <http://solidfire.com/solution/overview/?video=play>*™*
>>> >>>>
>>> >>>
>>> >>>
>>> >>>
>>> >>> --
>>> >>> *Mike Tutkowski*
>>> >>> *Senior CloudStack Developer, SolidFire Inc.*
>>> >>> e: mike.tutkow...@solidfire.com
>>> >>> o: 303.746.7302
>>> >>> Advancing the way the world uses the cloud
>>> >>> <http://solidfire.com/solution/overview/?video=play>*™*
>>> >>>
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> *Mike Tutkowski*
>>> >> *Senior CloudStack Developer, SolidFire Inc.*
>>> >> e: mike.tutkow...@solidfire.com
>>> >> o: 303.746.7302
>>> >> Advancing the way the world uses the cloud
>>> >> <http://solidfire.com/solution/overview/?video=play>*™*
>>> >>
>>> >
>>> >
>>> >
>>> > --
>>> > *Mike Tutkowski*
>>> > *Senior CloudStack Developer, SolidFire Inc.*
>>> > e: mike.tutkow...@solidfire.com
>>> > o: 303.746.7302
>>> > Advancing the way the world uses the cloud
>>> > <http://solidfire.com/solution/overview/?video=play>*™*
>>>
>>
>>
>>
>> --
>> *Mike Tutkowski*
>> *Senior CloudStack Developer, SolidFire Inc.*
>> e: mike.tutkow...@solidfire.com
>> o: 303.746.7302
>> Advancing the way the world uses the cloud
>> <http://solidfire.com/solution/overview/?video=play>*™*
>>
>
>
>
> --
> *Mike Tutkowski*
> *Senior CloudStack Developer, SolidFire Inc.*
> e: mike.tutkow...@solidfire.com
> o: 303.746.7302
> Advancing the way the world uses the cloud
> <http://solidfire.com/solution/overview/?video=play>*™*

Reply via email to