Issues with ExportToDatabase and MySQL

Usage & Issues
#1

Hi,

I’m facing some issues with the ExportToDatabase module in CellProfiler. I’ve created a local empty MySQL database and a very simple pipeline that just extracts features from images. It works well when I export them in a CSV file with the ExportToSpreadsheet module, but now, I would like to export them to a MySQL database.

  • System 1: macOS 10.14.6, CellProfiler 3.1.8, mysql 8.0.17

Here, when I click the “Test connection” it works, but when the analysis starts running, it gives me this error (even before processing the images):

Encountered unrecoverable error in ExportToDatabase during startup: 
'ascii' codec can't decode byte 0xff in position 254: ordinal not in range(128)

The workaround suggested here didn’t work for me.

  • System 2: Ubuntu 18.04, CellProfiler 3.1.9, mysql 14.14

“Test connection” fails in this case with the following error:

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/cellprofiler/gui/moduleview.py", line 1267, in callback
    self.__on_do_something(event, setting)
  File "/usr/local/lib/python2.7/dist-packages/cellprofiler/gui/moduleview.py", line 2231, in __on_do_something
    setting.on_event_fired()
  File "/usr/local/lib/python2.7/dist-packages/cellprofiler/setting.py", line 2148, in on_event_fired
    self.__callback(*self.__args)
  File "/usr/local/lib/python2.7/dist-packages/cellprofiler/modules/exporttodatabase.py", line 1890, in test_connection
    self.db_name.value)
  File "/usr/local/lib/python2.7/dist-packages/cellprofiler/modules/exporttodatabase.py", line 308, in connect_mysql
    connection = MySQLdb.connect(host=host, user=user, passwd=pw, db=db)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 152, in __init__
    from MySQLdb.converters import conversions
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/converters.py", line 35, in <module>
    from _mysql import string_literal, escape_sequence, escape_dict, escape, NULL
ImportError: cannot import name escape_sequence

My impression is that the error is related to the same issue on both systems. Could be something related to Python 2.7 and the MySQL version? Any hints on how to fix it?

Thanks a lot!

Beatriz

#2

There are definitely some issues in the CellProfiler 3.x builds with connecting to MySQL; we’re hoping to get them fixed in CellProfiler 4, which will be out by the end of the year.

Since you’re just local hosting the MySQL database, does it work equally as well for you to try SQLite instead?

#3

Yes, SQLite works well for a test dataset of 200 images, but I have to scale up to 200000 and run CellProfiler in an HPC-like environment. That’s why MySQL seems like a suitable way to benefit from several workers writing concurrently. Let’s hope the issues are solved in v4 :slight_smile: