Also, it may help to enable the MySQL slow query log (if it isn't already enabled), and set MySQL's long_query_time to 0. Then you should be able to search through the slow log to see what the actual query being generated by your code looks like.<div>
<br></div><div>-Deva</div><div><br><div><div><div><br><div class="gmail_quote">On Thu, Oct 4, 2012 at 10:59 PM, Craig Vyvial <span dir="ltr"><<a href="mailto:cp16net@gmail.com" target="_blank">cp16net@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">This may help you in your travels.<div><br></div><div>Add these nova.conf options</div><div><pre style="margin-top:0px;margin-bottom:0px;padding:0px;border:0px;font-size:12px;font-family:Consolas,'Liberation Mono',Courier,monospace;color:rgb(51,51,51);line-height:16px">
<div style="margin:0px;padding:0px 0px 0px 10px;border:0px"># sql_connection_debug=100</div><div style="margin:0px;padding:0px 0px 0px 10px;border:0px">#### (IntOpt) Verbosity of SQL debugging information. 0=None,</div>
<div style="margin:0px;padding:0px 0px 0px 10px;border:0px">#### 100=Everything</div><div style="margin:0px;padding:0px 0px 0px 10px;border:0px"><br></div><div style="margin:0px;padding:0px 0px 0px 10px;border:0px">
# sql_connection_trace=true</div><div style="margin:0px;padding:0px 0px 0px 10px;border:0px">#### (BoolOpt) Add python stack traces to SQL as comment strings</div><div style="margin:0px;padding:0px 0px 0px 10px;border:0px">
<br></div></pre>-Craig Vyvial</div><div>cp16net</div><div><br></div><div><br><div class="gmail_quote"><div><div class="h5">On Wed, Oct 3, 2012 at 7:06 AM, Trinath Somanchi <span dir="ltr"><<a href="mailto:trinath.somanchi@gmail.com" target="_blank">trinath.somanchi@gmail.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5">Hi-<div><br></div><div>While developing the new extension, I was struck at the SQLalchemy ORM query,</div>
<div><br></div>
<div>To achieve, the query below in ORM,</div><div><br></div><div><div><font face="courier new, monospace">select distinct(inst.hostname) as server_name, </font></div>
<div><font face="courier new, monospace"> fip.address as fixed_ip_address,</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span> vif.address as fixed_ip_mac_address</font></div>
<div><font face="courier new, monospace">from instances inst, instance_metadata mtd, virtual_interfaces vif, fixed_ips fip</font></div><div><font face="courier new, monospace">where <a href="http://inst.id" target="_blank">inst.id</a> = mtd.instance_id<span style="white-space:pre-wrap"> </span> and</font></div>
<div><font face="courier new, monospace"> mtd.instance_id = vif.instance_id and</font></div><div><font face="courier new, monospace"> vif.instance_id = fip.instance_id and</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span> inst.project_id = 'e216fcb54dc944a8ab16e4e325299643' and</font></div>
<div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span> mtd.key = 'Server Group' and</font></div><div><font face="courier new, monospace"><span style="white-space:pre-wrap"> </span> mtd.value = 'DOM1'</font></div>
<div><font face="courier new, monospace">group by mtd.key,mtd.value;</font></div><div><br></div><div><font face="courier new, monospace">SQL></font></div><div><font face="courier new, monospace">+-------------+------------------+----------------------+</font></div>
<div><font face="courier new, monospace">| server_name | fixed_ip_address | fixed_ip_mac_address |</font></div><div><font face="courier new, monospace">+-------------+------------------+----------------------+</font></div>
<div><font face="courier new, monospace">| serverpoc | 172.15.1.2 | fa:16:3e:56:47:71 |</font></div><div><font face="courier new, monospace">| serverpoc2 | 172.15.1.3 | fa:16:3e:4f:3c:9b |</font></div>
<div><font face="courier new, monospace">+-------------+------------------+----------------------+</font></div><div><br></div><div><br></div><div>I have written the ORM query as</div><div><br></div><div><div><font face="courier new, monospace">result = session.query(models.Instance.hostname.distinct(),models.FixedIp.address,models.VirtualInterface.address).\</font></div>
<div><font face="courier new, monospace"> join((models.InstanceMetadata,</font></div><div><font face="courier new, monospace"> models.InstanceMetadata.instance_id == <a href="http://models.Instance.id" target="_blank">models.Instance.id</a>)).\</font></div>
<div><font face="courier new, monospace"> join ((models.FixedIp,</font></div><div><font face="courier new, monospace"> models.FixedIp.instance_id == models.InstanceMetadata.instance_id)).\</font></div>
<div><font face="courier new, monospace"> join ((models.VirtualInterface,</font></div><div><font face="courier new, monospace"> models.VirtualInterface.instance_id == models.FixedIp.instance_id)).\</font></div>
<div><font face="courier new, monospace"> filter(and_(models.Instance.project_id == search_opts['project_id'])).\</font></div><div><font face="courier new, monospace"> filter(and_(models.InstanceMetadata.key == str(search_opts['key']) )).\</font></div>
<div><font face="courier new, monospace"> filter(and_(models.InstanceMetadata.value == str(search_opts['value']))).\</font></div><div><font face="courier new, monospace"> all()</font></div>
</div><div><br></div><div>I'm new to sqlalchemy and with the help from existing queries in nova api, I have come upto this level. </div><div><br></div><div>Can any one guide me why the above ORM query is not returning rows? Is the ORM query reflecting correctly the SQL query above.</div>
<div><br></div><div>Please guide me troubleshoot the same. I was unable to plot the mistake in the ORM. Am i correct with respect to the ORM query preparation?</div><div><br></div><div><br></div><div>Thanks a lot in advance.</div>
<span><font color="#888888">
<div><br></div>-- <br>Regards,<br>----------------------------------------------<br>Trinath Somanchi,<div><a href="tel:%2B91%209866%20235%20130" value="+919866235130" target="_blank">+91 9866 235 130</a></div><br>
</font></span></div>
<br></div></div>_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br></blockquote></div><br></div>
<br>_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br></blockquote></div><br></div></div></div></div>