Can't create any plots (errors)

I am unable to open any modules in CPA (connected to MySQL v 5.7) and get the following error:

[code]An error occurred in the program:
DBException: ERROR: Database query failed for connection “MainThread” and failed to reconnect
Query was: "CREATE TABLE link_tables_testPer_Image_testPer_FilteredBlue (src VARCHAR(100), dest VARCHAR(100), link VARCHAR(100), ord INTEGER)"
First exception was: (1050, “Table ‘link_tables_testper_image_testper_filteredblue’ already exists”)
Second exception was: ERROR: Database query failed for connection "MainThread"
Query was: "CREATE TABLE link_tables_testPer_Image_testPer_FilteredBlue (src VARCHAR(100), dest VARCHAR(100), link VARCHAR(100), ord INTEGER)"
Exception was: (1050, “Table ‘link_tables_testper_image_testper_filteredblue’ already exists”)

Traceback (most recent call last):
File “cpa.py”, line 275, in launch_scatter_plot
File “scatter.pyc”, line 934, in init
File “scatter.pyc”, line 99, in init
File “guiutils.pyc”, line 274, in init
File “dbconnect.pyc”, line 992, in get_linkable_tables
File “dbconnect.pyc”, line 855, in do_link_tables
File “dbconnect.pyc”, line 64, in fn
File “dbconnect.pyc”, line 512, in execute
[/code]

The data are dumped into the database directly from CP 2.1.0 and the properties file is automatically generated from the same module. I am creating one table per object type and am getting 2 CPA properties files in the output. Here is the info from one of them:

[code]#Wed Jan 29 09:31:31 2014

==============================================

CellProfiler Analyst 2.0 properties file

==============================================

==== Database Info ====

db_type = mysql
db_port = 3306
db_host = localhost
db_name = cpanalyst
db_user = root
db_passwd = password

==== Database Tables ====

image_table = testPer_Image
object_table = testPer_FilteredBlue

==== Database Columns ====

Specify the database column names that contain unique IDs for images and

objects (and optionally tables).

table_id (OPTIONAL): This field lets Classifier handle multiple tables if

you merge them into one and add a table_number column as a foreign

key to your per-image and per-object tables.

image_id: must be a foreign key column between your per-image and per-object

tables

object_id: the object key column from your per-object table

image_id = ImageNumber
object_id = FilteredBlue_Number_Object_Number
plate_id =
well_id =

Also specify the column names that contain X and Y coordinates for each

object within an image.

cell_x_loc = FilteredBlue_Location_Center_X
cell_y_loc = FilteredBlue_Location_Center_Y

==== Image Path and File Name Columns ====

Classifier needs to know where to find the images from your experiment.

Specify the column names from your per-image table that contain the image

paths and file names here.

Individual image files are expected to be monochromatic and represent a single

channel. However, any number of images may be combined by adding a new channel

path and filename column to the per-image table of your database and then

adding those column names here.

NOTE: These lists must have equal length!

image_path_cols = Image_PathName_DNA
image_file_cols = Image_FileName_DNA

CPA will now read image thumbnails directly from the database, if chosen in ExportToDatabase.

image_thumbnail_cols = Image_Thumbnail_DNA,Image_Thumbnail_OrigOverlay

Give short names for each of the channels (respectively)…

image_names = DNA

Specify a default color for each of the channels (respectively)

Valid colors are: [red, green, blue, magenta, cyan, yellow, gray, none]

image_channel_colors = gray,

==== Image Accesss Info ====

image_url_prepend =

==== Dynamic Groups ====

Here you can define groupings to choose from when classifier scores your experiment. (eg: per-well)

This is OPTIONAL, you may leave "groups = ".

FORMAT:

group_XXX = MySQL select statement that returns image-keys and group-keys. This will be associated with the group name “XXX” from above.

EXAMPLE GROUPS:

groups = Well, Gene, Well+Gene,

group_SQL_Well = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Per_Image_Table.well FROM Per_Image_Table

group_SQL_Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

group_SQL_Well+Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.well, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

==== Image Filters ====

Here you can define image filters to let you select objects from a subset of your experiment when training the classifier.

FORMAT:

filter_SQL_XXX = MySQL select statement that returns image keys you wish to filter out. This will be associated with the filter name “XXX” from above.

EXAMPLE FILTERS:

filters = EMPTY, CDKs,

filter_SQL_EMPTY = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene=“EMPTY”

filter_SQL_CDKs = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene REGEXP ‘CDK.*’

==== Meta data ====

What are your objects called?

FORMAT:

object_name = singular object name, plural object name,

object_name = cell, cells,

What size plates were used? 96, 384 or 5600? This is for use in the PlateViewer. Leave blank if none

plate_type =

==== Excluded Columns ====

OPTIONAL

Classifier uses columns in your per_object table to find rules. It will

automatically ignore ID columns defined in table_id, image_id, and object_id

as well as any columns that contain non-numeric data.

Here you may list other columns in your per_object table that you wish the

classifier to ignore when finding rules.

You may also use regular expressions here to match more general column names.

Example: classifier_ignore_columns = WellID, Meta_., ._Position

This will ignore any column named “WellID”, any columns that start with

“Meta_”, and any columns that end in “_Position”.

A more restrictive example:

classifier_ignore_columns = ImageNumber, ObjectNumber, .Parent., .Children., .Location_Center.,.Metadata.

classifier_ignore_columns = table_number_key_column, image_number_key_column, object_number_key_column

==== Other ====

Specify the approximate diameter of your objects in pixels here.

image_tile_size = 50

======== Auto Load Training Set ========

OPTIONAL

You may enter the full path to a training set that you would like Classifier

to automatically load when started.

training_set =

======== Area Based Scoring ========

OPTIONAL

You may specify a column in your per-object table which will be summed and

reported in place of object-counts when scoring. The typical use for this

is to report the areas of objects on a per-image or per-group basis.

area_scoring_column =

======== Output Per-Object Classes ========

OPTIONAL

Here you can specify a MySQL table in your Database where you would like

Classifier to write out class information for each object in the

object_table

class_table =

======== Check Tables ========

OPTIONAL

[yes/no] You can ask classifier to check your tables for anomalies such

as orphaned objects or missing column indices. Default is on.

This check is run when Classifier starts and may take up to a minute if

your object_table is extremely large.

check_tables = yes
[/code]

And here is the other:

[code]#Wed Jan 29 09:31:31 2014

==============================================

CellProfiler Analyst 2.0 properties file

==============================================

==== Database Info ====

db_type = mysql
db_port = 3306
db_host = localhost
db_name = cpanalyst
db_user = root
db_passwd = password

==== Database Tables ====

image_table = testPer_Image
object_table = testPer_Nuclei

==== Database Columns ====

Specify the database column names that contain unique IDs for images and

objects (and optionally tables).

table_id (OPTIONAL): This field lets Classifier handle multiple tables if

you merge them into one and add a table_number column as a foreign

key to your per-image and per-object tables.

image_id: must be a foreign key column between your per-image and per-object

tables

object_id: the object key column from your per-object table

image_id = ImageNumber
object_id = Nuclei_Number_Object_Number
plate_id =
well_id =

Also specify the column names that contain X and Y coordinates for each

object within an image.

cell_x_loc = Nuclei_Location_Center_X
cell_y_loc = Nuclei_Location_Center_Y

==== Image Path and File Name Columns ====

Classifier needs to know where to find the images from your experiment.

Specify the column names from your per-image table that contain the image

paths and file names here.

Individual image files are expected to be monochromatic and represent a single

channel. However, any number of images may be combined by adding a new channel

path and filename column to the per-image table of your database and then

adding those column names here.

NOTE: These lists must have equal length!

image_path_cols = Image_PathName_DNA
image_file_cols = Image_FileName_DNA

CPA will now read image thumbnails directly from the database, if chosen in ExportToDatabase.

image_thumbnail_cols = Image_Thumbnail_DNA,Image_Thumbnail_OrigOverlay

Give short names for each of the channels (respectively)…

image_names = DNA

Specify a default color for each of the channels (respectively)

Valid colors are: [red, green, blue, magenta, cyan, yellow, gray, none]

image_channel_colors = gray,

==== Image Accesss Info ====

image_url_prepend =

==== Dynamic Groups ====

Here you can define groupings to choose from when classifier scores your experiment. (eg: per-well)

This is OPTIONAL, you may leave "groups = ".

FORMAT:

group_XXX = MySQL select statement that returns image-keys and group-keys. This will be associated with the group name “XXX” from above.

EXAMPLE GROUPS:

groups = Well, Gene, Well+Gene,

group_SQL_Well = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Per_Image_Table.well FROM Per_Image_Table

group_SQL_Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

group_SQL_Well+Gene = SELECT Per_Image_Table.TableNumber, Per_Image_Table.ImageNumber, Well_ID_Table.well, Well_ID_Table.gene FROM Per_Image_Table, Well_ID_Table WHERE Per_Image_Table.well=Well_ID_Table.well

==== Image Filters ====

Here you can define image filters to let you select objects from a subset of your experiment when training the classifier.

FORMAT:

filter_SQL_XXX = MySQL select statement that returns image keys you wish to filter out. This will be associated with the filter name “XXX” from above.

EXAMPLE FILTERS:

filters = EMPTY, CDKs,

filter_SQL_EMPTY = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene=“EMPTY”

filter_SQL_CDKs = SELECT TableNumber, ImageNumber FROM CPA_per_image, Well_ID_Table WHERE CPA_per_image.well=Well_ID_Table.well AND Well_ID_Table.Gene REGEXP ‘CDK.*’

==== Meta data ====

What are your objects called?

FORMAT:

object_name = singular object name, plural object name,

object_name = cell, cells,

What size plates were used? 96, 384 or 5600? This is for use in the PlateViewer. Leave blank if none

plate_type =

==== Excluded Columns ====

OPTIONAL

Classifier uses columns in your per_object table to find rules. It will

automatically ignore ID columns defined in table_id, image_id, and object_id

as well as any columns that contain non-numeric data.

Here you may list other columns in your per_object table that you wish the

classifier to ignore when finding rules.

You may also use regular expressions here to match more general column names.

Example: classifier_ignore_columns = WellID, Meta_., ._Position

This will ignore any column named “WellID”, any columns that start with

“Meta_”, and any columns that end in “_Position”.

A more restrictive example:

classifier_ignore_columns = ImageNumber, ObjectNumber, .Parent., .Children., .Location_Center.,.Metadata.

classifier_ignore_columns = table_number_key_column, image_number_key_column, object_number_key_column

==== Other ====

Specify the approximate diameter of your objects in pixels here.

image_tile_size = 50

======== Auto Load Training Set ========

OPTIONAL

You may enter the full path to a training set that you would like Classifier

to automatically load when started.

training_set =

======== Area Based Scoring ========

OPTIONAL

You may specify a column in your per-object table which will be summed and

reported in place of object-counts when scoring. The typical use for this

is to report the areas of objects on a per-image or per-group basis.

area_scoring_column =

======== Output Per-Object Classes ========

OPTIONAL

Here you can specify a MySQL table in your Database where you would like

Classifier to write out class information for each object in the

object_table

class_table =

======== Check Tables ========

OPTIONAL

[yes/no] You can ask classifier to check your tables for anomalies such

as orphaned objects or missing column indices. Default is on.

This check is run when Classifier starts and may take up to a minute if

your object_table is extremely large.

check_tables = yes
[/code]

Any help addressing this problem would be great :smile:

Is there any other information I can provide to help address this problem?

Is there a recommended SQL version to use? I really would like to get CPA going.

Hi Jordan,
Sorry for the slow reply. I am using this MySQL ‘Server version: 5.1.71-log MySQL Community Server (GPL)’ so I doubt that is the issue.

When exactly do you get the error? It looks like you are trying to run ScatterPlot? Does CPA error before that? Other than your properties files looking like they have default login info, they look ok.

At this point, I would ensure that your MySQL is setup properly and that you manually inspect the PerImage and PerObject tables to see that they look ok. On Mac, you can use Sequel Pro and on Windows, SQLYog.
The error is "

– you can safely remove all the ‘_link_tables…’ and CPA will re-generate as needed if this was the issue.

Another tack is to use SQLite in ExportToDatabase as you don’t need a SQL server instance running and it is a bit easier to debug.

Let us know how it goes!
Thanks,
David