time datatype leads to failure when populating dependent tables

[Ahmedjjj]

trafficstars

Bug Report

Description

Populating dependent tables with a foreign key of the time datatype fails

Reproducibility

Include:

• OS : Linux
• Python Version : 3.9
• MySQL Version : datajoint/mysql docker image
• MySQL Deployment Strategy: local-docker
• DataJoint Version : 0.13.2
• Minimum number of steps to reliably reproduce the issue

schema = dj.Schema('test', locals())

@schema
class Foo(dj.Manual):
    definition = """
    start_time : time 
    """

@schema
class Bar(dj.Computed):
    definition="""
    -> Foo
    """
    def make(self, key):
        pass

Foo().insert1(dict(start_time='01:01:01'))
Bar().populate()

• Complete error stack as a result of evaluating the above steps:

OperationalError                          Traceback (most recent call last)
<ipython-input-14-23e1f8428949> in <module>
      1 Foo().insert1(dict(start_time='01:01:01'))
----> 2 Bar().populate()

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/autopopulate.py in populate(self, suppress_errors, return_exception_objects, reserve_jobs, order, limit, max_calls, display_progress, *restrictions)
    142             if not reserve_jobs or jobs.reserve(self.target.table_name, self._job_key(key)):
    143                 self.connection.start_transaction()
--> 144                 if key in self.target:  # already populated
    145                     self.connection.cancel_transaction()
    146                     if reserve_jobs:

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/expression.py in __contains__(self, item)
    464         executed more efficiently.
    465         """
--> 466         return bool(self & item)  # May be optimized e.g. using an EXISTS query
    467 
    468     def __iter__(self):

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/expression.py in __bool__(self)
    451             faster e.g. ``bool(q1)``.
    452         """
--> 453         return bool(self.connection.query(
    454             'SELECT EXISTS(SELECT 1 FROM {from_}{where})'.format(
    455                 from_=self.from_clause(),

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings, reconnect)
    298         cursor = self._conn.cursor(cursor=cursor_class)
    299         try:
--> 300             self._execute_query(cursor, query, args, suppress_warnings)
    301         except errors.LostConnectionError:
    302             if not reconnect:

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/connection.py in _execute_query(cursor, query, args, suppress_warnings)
    264                 cursor.execute(query, args)
    265         except client.err.Error as err:
--> 266             raise translate_query_error(err, query)
    267 
    268     def query(self, query, args=(), *, as_dict=False, suppress_warnings=True, reconnect=None):

~/anaconda3/envs/testing/lib/python3.9/site-packages/datajoint/connection.py in _execute_query(cursor, query, args, suppress_warnings)
    262                     # suppress all warnings arising from underlying SQL library
    263                     warnings.simplefilter("ignore")
--> 264                 cursor.execute(query, args)
    265         except client.err.Error as err:
    266             raise translate_query_error(err, query)

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    546             sql = sql.encode(self.encoding, "surrogateescape")
    547         self._execute_command(COMMAND.COM_QUERY, sql)
--> 548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows
    550 

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    723             if self._result is not None and self._result.unbuffered_active is True:
    724                 self._result.unbuffered_active = False
--> 725             packet.raise_for_error()
    726         return packet
    727 

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/protocol.py in raise_for_error(self)
    219         if DEBUG:
    220             print("errno =", errno)
--> 221         err.raise_mysql_exception(self._data)
    222 
    223     def dump(self):

~/anaconda3/envs/testing/lib/python3.9/site-packages/pymysql/err.py in raise_mysql_exception(data)
    141     if errorclass is None:
    142         errorclass = InternalError if errno < 1000 else OperationalError
--> 143     raise errorclass(errno, errval)

OperationalError: (1305, 'FUNCTION datetime.timedelta does not exist')

Expected Behavior

Table Bar above to populate without errors.

Screenshots

If applicable, add screenshots to help explain your problem.

Additional Research and Context

Add any additional research or context that was conducted in creating this report.

For example:

• Related GitHub issues and PR's either within this repository or in other relevant repositories.
• Specific links to specific lines or a focus within source code.
• Relevant summary of Maintainers development meetings, milestones, projects, etc.

[guzman-raphael]

@Ahmedjjj Thanks for the report! This does appear to be a bug. We are investigating it further.