Robot load not going into ispyb

[olliesilvester]

After testing Hyperion on v9.3.1, we found that robot load is still not going into ispyb. We saw:

[2024-05-21 15:47:43,228] Hyperion ISPyB and Zocalo callbacks plan_reactive_callback ERROR: DatabaseError(-1): Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in _process_params
    res = [to_mysql(value) for value in res]
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in <listcomp>
    res = [to_mysql(value) for value in res]
           ^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/conversion.py", line 237, in to_mysql
    raise TypeError(
TypeError: Python 'ca_float' cannot be converted to a MySQL type

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/ispyb/connector/mysqlsp/main.py", line 97, in call_sp_write
    result_args = cursor.callproc(procname=procname, args=args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 950, in callproc
    self.execute(f"SET {placeholders}", argvalues)
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 730, in execute
    psub = _ParamSubstitutor(self._process_params(params))
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 571, in _process_params
    raise ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'ca_float' cannot be converted to a MySQL type
Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in _process_params
    res = [to_mysql(value) for value in res]
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in <listcomp>
    res = [to_mysql(value) for value in res]
           ^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/conversion.py", line 237, in to_mysql
    raise TypeError(
TypeError: Python 'ca_float' cannot be converted to a MySQL type

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/ispyb/connector/mysqlsp/main.py", line 97, in call_sp_write
    result_args = cursor.callproc(procname=procname, args=args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 950, in callproc
    self.execute(f"SET {placeholders}", argvalues)
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 730, in execute
    psub = _ParamSubstitutor(self._process_params(params))
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 571, in _process_params
    raise ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'ca_float' cannot be converted to a MySQL type

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/callbacks/plan_reactive_callback.py", line 50, in _run_activity_gated
    return self.emit(name, func(doc))
                           ^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/callbacks/xray_centre/ispyb_callback.py", line 138, in activity_gated_event
    doc = super().activity_gated_event(doc)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/callbacks/ispyb_callback_base.py", line 109, in activity_gated_event
    self.ispyb_ids = self.ispyb.update_deposition(self.ispyb_ids, scan_data_infos)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/ispyb/ispyb_store.py", line 77, in update_deposition
    return self._begin_or_update_deposition(ispyb_ids, None, scan_data_infos)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/ispyb/ispyb_store.py", line 112, in _begin_or_update_deposition
    new_data_collection_id, grid_id = self._store_single_scan_data(
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/ispyb/ispyb_store.py", line 223, in _store_single_scan_data
    data_collection_id = self._store_data_collection_table(
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/ispyb/ispyb_store.py", line 218, in _store_data_collection_table
    return self._upsert_data_collection(conn, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/apps/python/miniforge/4.10.0-0/envs/python3.11/lib/python3.11/contextlib.py", line 81, in inner
    return func(*args, **kwds)
           ^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/src/hyperion/external_interaction/ispyb/ispyb_store.py", line 284, in _upsert_data_collection
    return conn.mx_acquisition.upsert_data_collection(list(params.values()))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/ispyb/sp/acquisition.py", line 166, in upsert_data_collection
    return self.get_connection().call_sp_write("upsert_dc", values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/ispyb/connector/mysqlsp/main.py", line 105, in call_sp_write
    raise ReadWriteError(
ispyb.ReadWriteError: DatabaseError(-1): Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in _process_params
    res = [to_mysql(value) for value in res]
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 564, in <listcomp>
    res = [to_mysql(value) for value in res]
           ^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/conversion.py", line 237, in to_mysql
    raise TypeError(
TypeError: Python 'ca_float' cannot be converted to a MySQL type

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/ispyb/connector/mysqlsp/main.py", line 97, in call_sp_write
    result_args = cursor.callproc(procname=procname, args=args)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 950, in callproc
    self.execute(f"SET {placeholders}", argvalues)
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 730, in execute
    psub = _ParamSubstitutor(self._process_params(params))
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/dls_sw/i03/software/bluesky/hyperion_v9.3.1/hyperion/.venv/lib/python3.11/site-packages/mysql/connector/cursor.py", line 571, in _process_params
    raise ProgrammingError(
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'ca_float' cannot be converted to a MySQL type

Acceptance Criteria

• Robot load plans correctly deposits to ispyb

[DominicOram]

To fix:

• Look in the logs and see if we can work out which type is wrong (remembering that the logs are in the old graylog for ispyb still)
• If logs don't tell us much test on beamline

[rtuck99]

This is also causing #1412

[rtuck99]

It appears that this is caused by undulator-current_gap being of type ca_float

[rtuck99]

It seems that ophyd-async and ophyd use two completely different libraries to access epics PVs - ophyd uses pyepics whereas ophyd-async uses aioca. pyepics I believe returns genuine python primitive types, whereas aioca returns ca_xxx subclasses of those primitives.

As we also seem to have similar problems with numpy and ispyb-api library, I think the best solution is to just add code in our ispyb backend to convert subclasses of primitives to actual primitives.

[rtuck99]

This should now be addressed by https://github.com/bluesky/ophyd-async/pull/364