Race Condition in table creation in batch mode

I’m running CP in batch mode, and am getting some table already exists errors during MySQL export in the first few batches:

Pipeline saved with CellProfiler version 20140124145122 Failed to prepare run for module ExportToDatabase Traceback (most recent call last): File "/usr/cellprofiler/src/CellProfiler/cellprofiler/pipeline.py", line 2088, in prepare_run if ((not module.prepare_run(workspace)) or File "/usr/cellprofiler/src/CellProfiler/cellprofiler/modules/exporttodatabase.py", line 1597, in prepare_run self.create_database_tables(self.cursor, workspace) File "/usr/cellprofiler/src/CellProfiler/cellprofiler/modules/exporttodatabase.py", line 2153, in create_database_tables execute(cursor, statement, return_result=False) File "/usr/cellprofiler/src/CellProfiler/cellprofiler/modules/exporttodatabase.py", line 256, in execute cursor.execute(query) File "/usr/cellprofiler/lib/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/cellprofiler/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (1050, "Table 'Plate_12465_Per_Experiment' already exists")

I’m splitting my job into 40-some batches to run on our cluster with a series of commands like:

cellprofiler -c -r -p /cluster/data/clususer/christop/mdc/12471/pipeline_12471.cppipe --data-file=/cluster/data/clususer/christop/mdc/12471/plate_12471.csv -f 1 -l 5 cellprofiler -c -r -p /cluster/data/clususer/christop/mdc/12471/pipeline_12471.cppipe --data-file=/cluster/data/clususer/christop/mdc/12471/plate_12471.csv -f 6 -l 10 cellprofiler -c -r -p /cluster/data/clususer/christop/mdc/12471/pipeline_12471.cppipe --data-file=/cluster/data/clususer/christop/mdc/12471/plate_12471.csv -f 11 -l 15 cellprofiler -c -r -p /cluster/data/clususer/christop/mdc/12471/pipeline_12471.cppipe --data-file=/cluster/data/clususer/christop/mdc/12471/plate_12471.csv -f 16 -l 20

Each job is writing to its own log file, and when I grep for ‘Error’ in the log files, I get:

plate_12465_01.log:OperationalError: (1050, "Table 'Plate_12465_Per_Experiment' already exists") plate_12465_02.log:OperationalError: (1050, "Table 'Plate_12465_Per_Object' already exists") plate_12465_03.log:OperationalError: (1050, "Table 'Plate_12465_Per_Image' already exists")

I can only assume that this is a race condition, as all jobs are started at about the same time. Job 00 and Job 01 both see that Plate_12465_Per_Experiment needs to be created as it doesn’t exist yet, and both try to create it. Job 00 gets there first, so that succeeds, but Job 01 gets an error when it tries to create the table.

The same thing happens for _Per_Object and _Per_Image with Jobs 02 and 03, respectively.

I do have the “Data only” overwrite mode set in my export module:

Is there a known work-around for this race? I’m planning on submitting Job00 first and waiting for it complete before submitting all the other jobs in the batch, but that’s a sub-optimal solution, as it will reduce overall throughput.

Thanks,
Jon

Hi Jon,
The best practice is to use the CreateBatchFiles module to create a Batch_Data.h5 file which you can pass into the command-line of each of your jobs. Some process has to create the database tables - we decided that the tables should be created prior to job submission through this mechanism. See this page here for best practices: github.com/CellProfiler/CellPro … nvironment

I’ve entered an issue for this anyway: github.com/CellProfiler/CellPro … ssues/1176 . The plan would be to add a command-line switch that would prevent CellProfiler from creating the schema. This seems like it would be the most flexible solution - you could even run one job without the switch, wait for it to finish and then run the rest. Is that OK?

–Lee