Error in CellProfiler Analyst, linking tables

cellprofiler
mysql
cellprofiler-analyst

#1

Hi

I am receiving an error in CellProfiler Analyst, linked to reading from a MySQL database.

I can open a tool, eg. the Histogram, and plot things. When I then attempt to open another tool, such as the Plate Viewer or another Histogram, I receive an error in relation to linking tables:

An error occurred in the program:
DBException: ERROR: Database query failed for connection "MainThread" and failed to reconnect
Query was: "CREATE TABLE _link_tables_Plate00001Per_Image_Plate00001Per_Object_ (src VARCHAR(100), dest VARCHAR(100), link VARCHAR(100), ord INTEGER)"
First exception was: (1050, "Table '_link_tables_plate00001per_image_plate00001per_object_' already exists")
Second exception was: ERROR: Database query failed for connection "MainThread"
Query was: "CREATE TABLE _link_tables_Plate00001Per_Image_Plate00001Per_Object_ (src VARCHAR(100), dest VARCHAR(100), link VARCHAR(100), ord INTEGER)"
Exception was: (1050, "Table '_link_tables_plate00001per_image_plate00001per_object_' already exists")



Traceback (most recent call last):
  File "CellProfiler-Analyst.py", line 267, in launch_plate_map_browser
  File "cpa\plateviewer.pyc", line 61, in __init__
  File "cpa\guiutils.pyc", line 274, in __init__
  File "cpa\dbconnect.pyc", line 1018, in get_linkable_tables
  File "cpa\dbconnect.pyc", line 880, in do_link_tables
  File "cpa\dbconnect.pyc", line 64, in fn
  File "cpa\dbconnect.pyc", line 525, in execute

Screenshot:

In the background the MySQL workspace can be seen with the table overview.

Pipeline used for generation properties file: 211analysis.cppipe (39.4 KB)

Properties file: analysis_Plate00001.properties (7.0 KB)

Dropping the tables
_link_tables_plate…
_link_columns_plate…
Allows me to open a tool again, which then recreates the tables, and thus the error when opening a new tool.

I am receiving the error in both CPA2.0 and CPA2.2.0. Is the error caused by the setup of the MySQL database?
Edit: I am also receiving an error in CP when running the pipeline, also related to the SQL database, see: Error when Exporting to MySQL database
I don’t know if they are linked?
I fixed this by increasing the lock wait timeout, see the post.

I hope you can help.

With Kind Regards
Esben Svenningsen
Aarhus University


#2

@Tim_1 @cells2numbers @bcimini
Can you assist in any way?


#3

@Tim_1, @cells2numbers, @bcimini

I have found a temporary solution, but as far as I can tell it should have been fixed?

The error has to do with capital letters and MySQL: The tables are created with upper case letters but converted to lower case in MySQL. When CellProfiler analyst then checks if:
_link_tables_plate00001_Per_Image_plate00001_Per_Object_

is already created a false is returned since it is called:

_link_tables_plate00001_per_image_plate00001_per_object_

It then attempts to create the linked tables, but as they already exists an error is returned.

I “solved” the problem by changing the names of the tables in the .properties file to all lowercase letters in:
image_table = plate00001_per_image
object_table = plate00001_per_object

and

group_SQL_Well = SELECT ImageNumber, Image_Metadata_Plate, Image_Metadata_Well FROM plate00001_per_image

But according to this commit it should have been fixed?

Anyway, it sort of works now.

/Esben


#4

Hey Esben,

great that you figured that out! I do not use CPA regularly, but I assume that the version that you used did not include the fixes yet. CPA 2.2.0 was released in Feb 2016.

You could try to install CPA from source following https://github.com/CellProfiler/CellProfiler-Analyst/wiki#Installation

Best,
T