I have been trying out New Relic and have found that any queries that
include '%' are breaking.
This is what Graham at New Relic advised:
As far as I can tell:
ILIKE '%|featured|%'
would cause a problem because the mogrify() function in psycopg2 cursor
which does variable substitution doesn't care about quoted values and will
try and do a % substitution where ever it sees one. The only time it will
not is when is actually %% meaning a single escaped %, which it would then
replace with just a single %.
So right now to me looks like Web2Py is generating incorrect SQL.
In general it is regarded as best practice to never do full SQL
construction yourself and to always rely on DBAPI module to do variable
text interpolation and Web2Py isn't doing that.
The errors I get look like this:
Traceback (most recent call last):
File "/opt/web2py/gluon/restricted.py", line 204, in restricted
exec ccode in environment
File "/opt/web2py/applications/init/controllers/default.py"
<https://test.pricetack.com/admin/edit/init/controllers/default.py>, line 129,
in <module>
File "/opt/web2py/gluon/globals.py", line 162, in <lambda>
self._caller = lambda f: f()
File "/opt/web2py/applications/init/controllers/default.py"
<https://test.pricetack.com/admin/edit/init/controllers/default.py>, line 20,
in home
(db.auth_user.id==db.item.seller)).select(limitby=(0,4), orderby='<random>')
File "/opt/web2py/gluon/dal.py", line 5825, in select
return self.db._adapter.select(self.query,fields,attributes)
File "/opt/web2py/gluon/dal.py", line 1275, in select
rows = response(sql)
File "/opt/web2py/gluon/dal.py", line 1265, in response
self.execute(sql)
File "/opt/web2py/gluon/dal.py", line 1351, in execute
return self.log_execute(*a, **b)
File "/opt/web2py/gluon/dal.py", line 1345, in log_execute
ret = self.cursor.execute(*a, **b)
File
"/usr/local/lib/python2.6/dist-packages/newrelic-1.0.5.156-py2.6.egg/newrelic/hooks/database_dbapi2.py",
line 38, in execute
return self._nr_cursor.execute(sql, parameters)
IndexError: tuple index out of range
*File
/usr/local/lib/python2.6/dist-packages/newrelic-1.0.5.156-py2.6.egg/newrelic/hooks/database_dbapi2.py
in execute at line 38* code argumentsvariables
Function argument list
(self=<newrelic.hooks.database_dbapi2.CursorWrapper object>, sql='SELECT
item.id, item.title, item.seller, item.s...item.seller)) ORDER BY RANDOM()
LIMIT 4 OFFSET 0;', parameters=())
Code listing
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
transaction = newrelic.api.transaction.transaction()
if not transaction:
return self._nr_cursor.execute(sql, parameters)
with newrelic.api.database_trace.DatabaseTrace(
transaction, sql, module):
return self._nr_cursor.execute(sql, parameters)
def executemany(self, sql, seq_of_parameters=[]):
transaction = newrelic.api.transaction.transaction()
if not transaction:
Variablesself._nr_cursor.execute<built-in method execute of
psycopg2._psycopg.cursor
object>self<newrelic.hooks.database_dbapi2.CursorWrapper
object>self._nr_cursor<cursor object at 0x29ffc18; closed:
0>parameters()sql'SELECT
item.id, item.title, item.seller, item.s...item.seller)) ORDER BY RANDOM()
LIMIT 4 OFFSET 0;'
