ExportToDatabase module error

Hi everyone,
I did submit a post on Friday, but it either hasn’t been approved yet, or has been lost in the ether.

I posted about the ExportToDatabase module in CellProfiler 2.1.1 and its latest branch build (aa38cd9). Where, when logged in as Admin, the module fails to write to the database and crashes my pipeline completely.
Two situations have arisen:
1) Where I give the module permission to overwrite any table or anything it likes (Admin), it results in “You have an error in your SQL syntax; check the manual…” (See below, Error #1, for full details).
2) Where I do not give the module permission to overwrite anything it likes, only data within tables, it results in “Table ‘lifesci_cell_profiler.Test_Per_Experiment’ doesn’t exist” (See below, Error #2, for full details).
I have tried manually creating the table Test_Per_Experiment and then running the module. Not only does this not work, but CellProfiler seems to delete my manually created table, and makes a new one called “Experiment”. It then fails the module with Error #2 again.

[Update] I have downloaded an older version: CellProfiler version 2.0, and the ExportToDatabase function appears to run fine on this version (though CellProfiler now lacks other functions I need!). After having a bit of a dig through the source code, it seems that the table name ‘Per_Experiment’ is mentioned in only two places in the latest build’s module code. This suggests to me that there may be an issue with what table CellProfiler creates on the MySQL database, and what table it is looking for to write information to.

Any help with this would be highly appreciated!

Error #1
Traceback (most recent call last):
File “cellprofiler\pipeline.pyc”, line 2127, in prepare_run
File “cellprofiler\modules\exporttodatabase.pyc”, line 1649, in prepare_run
File “cellprofiler\modules\exporttodatabase.pyc”, line 2210, in create_database_tables
File “cellprofiler\modules\exporttodatabase.pyc”, line 262, in execute
File “MySQLdb\cursors.pyc”, line 201, in execute
File “MySQLdb\connections.pyc”, line 36, in defaulterrorhandler
ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘field(200)))’ at line 7”)

Error #2
Traceback (most recent call last):
File “cellprofiler\pipeline.pyc”, line 1948, in run_image_set
File “cellprofiler\pipeline.pyc”, line 2059, in run_module
File “cellprofiler\modules\exporttodatabase.pyc”, line 1756, in run
File “cellprofiler\modules\exporttodatabase.pyc”, line 2935, in write_data_to_db
File “cellprofiler\modules\exporttodatabase.pyc”, line 262, in execute
File “MySQLdb\cursors.pyc”, line 201, in execute
File “MySQLdb\connections.pyc”, line 36, in defaulterrorhandler
ProgrammingError: (1146, “Table ‘lifesci_cell_profiler.Test_Per_Experiment’ doesn’t exist”)

Sorry, here is an example of the settings that I have for my MySQL module. Host, username etc. replaced for privacy. This is not the full pipeline that I am using, but the errors appear just the same.
Example.cpproj (872 KB)

[Update2]

If I take the table “Experiment” (created by CellProfiler when I give the module permission to overwrite any table or anything it likes (Admin)) and rename it to “Test_Per_Experiment”, then as reported above the table gets renamed back to “Experiment”. However, if I do this table rename, and then run the pipeline and I do not give the module permission to overwrite anything it likes - only data within tables (effectively ‘User’ permissions), then I get the error: “1054, “Unknown column ‘Modification_Timestamp’ in ‘field list’””. So it now seems to accept that the correct table is present, but is unable to do anything useful with it!

Hi,

I added this as a Github issue that our lead software engineer will look at soon hopefully, since I’m not sure without further testing what the issue is.

Sorry for the trouble!
David

Hi David,
The database created by CellProfiler has several experiment-related tables:

Experiment: This table names the different experiments (analysis runs) in the database

Experiment_Properties: This table is a recapitulation of the properties file for CellProfiler Analyst for each experiment in the database

Per_Experiment: This table has analysis-wide parameters for the analysis, for instance, the pipeline.

If you rename Experiment, Per_Experiment, then the fields that CellProfiler expects in the Per_Experiment table will not be there, the Experiment table will not be there and the references in Experiment_Properties will refer to an experiment ID that doesn’t have a corresponding row in the Experiment table. In particular, the Per_Experiment table had a field called, “Modification_Timestamp” that is used for synchronization to prevent database deadlocks. The Experiment table doesn’t have that field, so by renaming, you have created the problem you have seen.

We assume that the database user has permission to create a database, create and modify tables and insert and update records in the tables - you’ll have to use a user that has those permissions. If you create the database manually, you should be able grant a user without admin privileges to do these things within that database. I’d recommend starting fresh with a new database and then grant the user all privileges on that database:

(from an admin acct)

create database MyDatabase;
grant all on MyDatabase.* to johndoe;

I’m going to take a look at the project and see if there’s anything specific that might have caused CellProfiler to emit bad SQL syntax.

–Lee

Thanks David and Lee,

Lee, I’ll give recreating the database a go in the meantime.

Thanks for your help.

Hi Guys-

I see this thread is a year old but I’ve just come across this error message. We’ve tried a) granting my account all for the database as well as b) deleting it and recreating it, but neither solution fixed the problem. Are there other things we can try?

Thanks,
Brian