GAE: BadRequestError: projection and keys_only cannot both be set

[jaimesemp]

Hi,

Im using web2py 2.9.12, Im developing on local gae, and when I tried to execute this query:

    pics = db(db.pics.user_id==user_id).select(db.pics.img_link, projection=True    ,orderby=~db.pics.up_date, limitby=(num_min, num_max))

I got that error

 WARNING  2015-08-21 23:40:54,728 tasklets.py:410] suspended generator run_to_queue(query.py:938) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-21 23:40:54,729 tasklets.py:410] suspended generator helper(context.py:876) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-21 23:40:54,729 tasklets.py:410] suspended generator has_next_async(query.py:1760) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-21 23:40:54,730 tasklets.py:410] suspended generator _fetch_page_async(query.py:1349) raised BadRequestError(projection and keys_only cannot both be set)
ERROR    2015-08-21 23:40:54,736 restricted.py:69]  Unable to store in FILE: /home/jukvoxman/uweb2py2.9.12/applications/relatos/controllers/default.py

Traceback (most recent call last):
  File "/home/jukvoxman/uweb2py2.9.12/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/jukvoxman/uweb2py2.9.12/applications/relatos/controllers/default.py", line 224, in <module>
  File "/home/jukvoxman/uweb2py2.9.12/gluon/globals.py", line 412, in <lambda>
    self._caller = lambda f: f()
  File "/home/jukvoxman/uweb2py2.9.12/applications/relatos/controllers/default.py", line 82, in getPicsRangeByUserId
    projection=True    ,orderby=~db.pics.up_date, limitby=(num_min, num_max))
  File "/home/jukvoxman/uweb2py2.9.12/gluon/packages/dal/pydal/objects.py", line 2026, in select
    return adapter.select(self.query,fields,attributes)
  File "/home/jukvoxman/uweb2py2.9.12/gluon/packages/dal/pydal/adapters/google_adapters.py", line 484, in select
    (items, tablename, fields) = self.select_raw(query,fields,attributes)
  File "/home/jukvoxman/uweb2py2.9.12/gluon/packages/dal/pydal/adapters/google_adapters.py", line 449, in select_raw
    keys, cursor, more = items.fetch_page(limit,**fetch_args)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/utils.py", line 142, in positional_wrapper
    return wrapped(*args, **kwds)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1331, in fetch_page
    return self.fetch_page_async(page_size, **q_options).get_result()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 326, in get_result
    self.check_success()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 369, in _help_tasklet_along
    value = gen.throw(exc.__class__, exc, tb)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1349, in _fetch_page_async
    while (yield it.has_next_async()):
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 369, in _help_tasklet_along
    value = gen.throw(exc.__class__, exc, tb)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1760, in has_next_async
    yield self._fut
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/context.py", line 876, in helper
    batch, i, ent = yield inq.getq()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 938, in run_to_queue
    batch = yield rpc
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 455, in _on_rpc_completion
    result = rpc.get_result()
  File "/home/jukvoxman/google_appengine/google/appengine/api/apiproxy_stub_map.py", line 613, in get_result
    return self.__get_result_hook(self)
  File "/home/jukvoxman/google_appengine/google/appengine/datastore/datastore_query.py", line 2897, in __query_result_hook
    self._batch_shared.conn.check_rpc_success(rpc)
  File "/home/jukvoxman/google_appengine/google/appengine/datastore/datastore_rpc.py", line 1373, in check_rpc_success
    raise _ToDatastoreError(err)
BadRequestError: projection and keys_only cannot both be set

First I was playing with index.yaml automatically generated, but after reading some info about GAE and projection I have set manually an index on img_link row (pics entity) like this to be able to make the projection query on this row:

• kind: pics
  properties:
  • name: user_id
  • name: img_link
  • name: up_date
    direction: desc

But still no luck... is this a bug ? Could someone give me a workaround to fix it?

[mdipierro]

Please upgrade. You have an old version of web2py and this may have been already fixed in the newer version.

[jaimesemp]

Opsss... Sorry I did not notice the new version. Thanks a lot!

[mdipierro]

I do not know with 100% certainty the problem is solved although I think so. Please let us know. I'd rather have a false alarm than an unchecked bug. :-)

[jaimesemp]

Sure, I will upgrade tomorrow and inform about it. Thanks a lot for the support Massimo

[jaimesemp]

Ouch... same error again with 2.9.13 too

WARNING  2015-08-23 13:30:21,954 tasklets.py:410] suspended generator run_to_queue(query.py:938) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-23 13:30:21,955 tasklets.py:410] suspended generator helper(context.py:876) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-23 13:30:21,955 tasklets.py:410] suspended generator has_next_async(query.py:1760) raised BadRequestError(projection and keys_only cannot both be set)
WARNING  2015-08-23 13:30:21,956 tasklets.py:410] suspended generator _fetch_page_async(query.py:1349) raised BadRequestError(projection and keys_only cannot both be set)
ERROR    2015-08-23 13:30:22,202 restricted.py:69]  Unable to store in FILE: /home/jukvoxman/web2py2.9.13/applications/relatos/controllers/default.py

Traceback (most recent call last):
  File "/home/jukvoxman/web2py2.9.13/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/jukvoxman/web2py2.9.13/applications/relatos/controllers/default.py", line 263, in <module>
  File "/home/jukvoxman/web2py2.9.13/gluon/globals.py", line 412, in <lambda>
    self._caller = lambda f: f()
  File "/home/jukvoxman/web2py2.9.13/applications/relatos/controllers/default.py", line 122, in getPicsRangeByUserId
    orderby=~db.pics.up_date, projection=True, limitby=(num_min, num_max))
  File "/home/jukvoxman/web2py2.9.13/gluon/packages/dal/pydal/objects.py", line 2002, in select
    return adapter.select(self.query,fields,attributes)
  File "/home/jukvoxman/web2py2.9.13/gluon/packages/dal/pydal/adapters/google_adapters.py", line 500, in select
    (items, tablename, fields) = self.select_raw(query,fields,attributes)
  File "/home/jukvoxman/web2py2.9.13/gluon/packages/dal/pydal/adapters/google_adapters.py", line 465, in select_raw
    keys, cursor, more = items.fetch_page(limit,**fetch_args)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/utils.py", line 142, in positional_wrapper
    return wrapped(*args, **kwds)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1331, in fetch_page
    return self.fetch_page_async(page_size, **q_options).get_result()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 326, in get_result
    self.check_success()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 369, in _help_tasklet_along
    value = gen.throw(exc.__class__, exc, tb)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1349, in _fetch_page_async
    while (yield it.has_next_async()):
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 369, in _help_tasklet_along
    value = gen.throw(exc.__class__, exc, tb)
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 1760, in has_next_async
    yield self._fut
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/context.py", line 876, in helper
    batch, i, ent = yield inq.getq()
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/query.py", line 938, in run_to_queue
    batch = yield rpc
  File "/home/jukvoxman/google_appengine/google/appengine/ext/ndb/tasklets.py", line 455, in _on_rpc_completion
    result = rpc.get_result()
  File "/home/jukvoxman/google_appengine/google/appengine/api/apiproxy_stub_map.py", line 613, in get_result
    return self.__get_result_hook(self)
  File "/home/jukvoxman/google_appengine/google/appengine/datastore/datastore_query.py", line 2897, in __query_result_hook
    self._batch_shared.conn.check_rpc_success(rpc)
  File "/home/jukvoxman/google_appengine/google/appengine/datastore/datastore_rpc.py", line 1373, in check_rpc_success
    raise _ToDatastoreError(err)
BadRequestError: projection and keys_only cannot both be set


INFO     2015-08-23 13:30:22,203 gaehandler.py:75] **** Request: 308.51ms/298.68ms (real time/cpu time)
INFO     2015-08-23 13:30:22,227 module.py:808] default: "GET /getPicsRangeByUserId?user_id=5838406743490560&num_min=0&num_max=016 HTTP/1.1" 500 841

[stephenrauch]

The problem appears to come about because of these lines in google_adapters.py:

if args_get('limitby', None):
    (lmin, lmax) = attributes['limitby']
    limit, fetch_args = lmax-lmin, {'offset':lmin,'keys_only':True}

The adapter sets 'keys_only' on an intermediate query to implement 'limitby'. I have not studied GAE's various databases, so I don't know whether or not this is the only way to get the 'limitby' functionality. But since, in the short term, I will not be suggesting anything that requires changes to google_adapters.py, let's assume 'keys_only' is necessary in the adapter.

So, some choices I see which may solve your problem:

1. If you don't need 'limitby', you could remove the 'limitby' and have the query work.

2. If you are using the 'projection' attribute for attempted performance improvements, you could remove 'projection' and have the query work.

3. If you were using the 'projection' attribute to trim the results to just the selected fields, there is a work around: You can use the 'filterfields' attribute instead of the 'projection' attribute.

4. You mentioned using GAE locally. I assume this is because you intend to target GAE remotely. If this is not so, then there are other databases which are more capable and a little better supported by pydal and web2py.

Editorial Note:

For case 3), I consider the need to use the 'filterfields' attribute a bug in google_adapters.py, or at least an ill-advised design choice. Most (all?) of the other adapters return only the selected fields. If the user went to the trouble to select fields in the query, why would they ever want additional fields in the response?

[jaimesemp]

Thanks for your answer..

My reason of using projection was performance (the table stores image files, but I dont need recover them on that query). I think get all the row, with image files, is overworking.
Anyway if I understand all your response, my best bet is using 'filterfields', I will gain a little of performance I think 'cause I have read this:

 optional attribute 'filterfields' when set to True web2py will only
                parse the explicitly listed fields into the Rows object, even though
                all fields are returned in the query.  This can be used to reduce
                memory usage in cases where true projection queries are not
                usable.

Other posibility is remove limitby and catch the desired range after getting all the rows... I don't have any idea about performance of this versus filterfields...