Error in New Version of CPA

We just downloaded the new version of CPA and attempted to open up an experiment’s property file like we have many times previously. Upon doing so we go the following error:

An error occurred in the program:
OperationalError: too many SQL variables

Traceback (most recent call last):
File “/Applications/CPAnalyst-1053.app/Contents/Resources/cpa.py”, line 192, in launch_scatter_plot
File “scatter.pyc”, line 870, in init
File “scatter.pyc”, line 127, in init
File “scatter.pyc”, line 198, in update_column_fields
File “dbconnect.pyc”, line 643, in GetColumnNames
File “dbconnect.pyc”, line 47, in fn
File “dbconnect.pyc”, line 404, in execute
File “dbconnect.pyc”, line 357, in connect

This error does seem to be variable dependent, I was able to successfully open up experiments with less variable strings in the SQL file. Going through several of my experiments, I have discovered that the cutoff of variables is somewhere between 900 and 1200 variables. Do you know what could have changed between the previous version and this one to cause this problem?

Thanks,
Heather

Hi Heather,

I’m not completely sure under what context this error is occurring or what “variables” are being referred to. Are you using SQLite for your database. You mention an SQL file… is this SQL file output by CP? It would help to see whatever files are involved.

The variables, as I take it, should be the individual measurements the CellProfiler created.

We are using SQLite for our database. And the SQL file I am talking about is the one generated from CP. Would you like me to upload a copy of the properties file we use, along with the SQL file, and the image and object data tables (these are fairly large).

Our programming guys had a similar problem, and they said that the error is due to a change from pysqlite2 to sqlite3 which happened in r9800, and in order to use sqlite3 with large runs, it will need to be recompiled with a larger value set for SQLITE_MAX_COLUMN (currently set to 2000, with an upper limit of 32676).

Hope that helps.

Heather

Okay, I figured that was the problem but wanted to be sure. I’ll make a note to recompile SQLite3 with a higher limit for our next release, but I’ve got a lot on my plate at the moment and won’t be able to push that out the door for a while.

Until then, you have 3 options:

  1. Try MySQL (which also has it’s limits: dev.mysql.com/doc/refman/5.0/en/ … limit.html)
  2. Try to reduce the size of your database. We often export about 500 measurements from CPA, and even still most of these are never truly used. There is also a danger in slowing down the performance of SQLite by having too many columns since some of the algorithms they use are O(N^2) (demand time/operations that is in the worst case equal to the number of columns squared). In other words, you can expect things to take about 16X longer for 2000 columns than it would for 500 columns.
  3. Run CPA from source and build SQLite yourself.

Good luck, and let me know if you have any further questions.

-Adam