ERROR: too many SQL variables when running delete_records

[dnowacki-usgs]

Description

We have pycsw accessing an sqlite3 database, which has a records table containing 2647 rows. Running python bin/pycsw-admin.py -c delete_records -f default.cfg -y results in RuntimeError: ERROR: too many SQL variables.

Manually clearing the database by running sqlite> delete from records is successful. Success is short-lived; after re-loading the database with python bin/pycsw-admin.py -c load_records -r -f default.cfg -p /store and trying to do another delete_records results in the same error.

CC @rsignell-usgs, see also USGS-CMG/usgs-cmg-portal#309

Environment

• operating system: Linux
• Python version: 3.5.2
• pycsw version: 2.1-dev
• source/distribution
  • DebianGIS/UbuntuGIS
  • git clone
  • PyPI
  • zip/tar.gz
  • other (please specify):
• web server
  • Apache/mod_wsgi
  • CGI
  • other (please specify):

Steps to Reproduce

Additional Information

sqlalchemy version 1.1.4

[tomkralidis]

@dnowacki-usgs thanks for the report. I can't reproduce/works for me. Which version of SQLAlchemy are you using? Which version of pycsw are you using?

[tomkralidis]

Which version of pycsw are you using?

I see you specify pycsw version: 2.1-dev in the ticket, which looks like a version of master at a given time. Are you able to upgrade to a later/stable version of pycsw and test?

[dnowacki-usgs]

Thanks for the follow-up @tomkralidis. This is using sqlalchemy 1.1.4; maybe more recent versions batch queries with 1000+ parameters?

[tomkralidis]

@dnowacki-usgs thanks. I can't reproduce with SQLAlchemy 1.1.4 either. Are you able to test in a fresh environment with an updated pycsw and dependency chain?

[tomkralidis]

Alternatively if you are able to send your SQLite3 db file (offline) I can do more testing (might be a limit issue).

[dnowacki-usgs]

@tomkralidis thanks for your continued help. We are looking at updating pycsw etc. but it may take a while to get there. I can send a link to the db file that is failing. I don't see your email address in your profile, but if you email me at the address in my profile I can get it to you.

[dnowacki-usgs]

Hi @tomkralidis. An update: we are now running pycsw 2.2.0, sqlalchemy 1.2.10, python 3.6.0 using the latest axiom/docker-pycsw from Docker Hub. The delete_records error persists with a DB with ~2,000 records.

[rsignell-usgs]

@dnowacki-usgs , pycsw 2.2.0 is from March 2018, so I submitted this PR to upgrade axiom/docker-pycsw to pycsw 2.4.1, sqlalchemy 1.3.10 and python 3.7. We can test this version to see if the problem remains.