PyMySQL 0.10.X Compatibility Issues

[guzman-raphael]

It appears that one of the recent PyMySQL==0.10.X releases broke a few of our tests. Seems as if most features are working but some edge cases need to be patched. Here is a TravisCI run where from PR #811 where you can see the test results. So far, I can see that if you set PyMySQL==0.9.2 the tests pass just fine but when running newer, say PyMySQL==0.10.1, 5 tests fail. I have not pinpointed anything more specific yet on this. Failed tests are:

• tests.test_fetch.TestFetch.test_misspelled_attribute
• tests.test_reconnection.TestReconnect.test_reconnect
• tests.test_reconnection.TestReconnect.test_reconnect_throws_error_in_transaction
• tests.test_relation.TestRelation.test_no_error_suppression
• tests.test_schema.test_unauthorized_database

[gmaggi]

Hi,

maybe related:

DataJoint (tested on 0.12.5 and 0.12.7) fails with pymysql:0.10.1. That throws: pymysql.err.OperationalError: (1043, 'Bad handshake'). I changed to pymysql:0.9.3 and that fixed it (tested on DJ: 0.12.5 only).

[khl02007]

Just wanted to report that this issue still persists. Connecting to remote server running mysql version 5.7

Steps to recreate the error and the error message:

Conda environment: (base) kyu@cyclone:~$ conda create --name dj_test python=3.8 (base) kyu@cyclone:~$ conda activate dj_test (dj_test) kyu@cyclone:~$ pip install datajoint

In ipython:

In [1]: import pymysql

In [2]: pymysql.__version__
Out[2]: '0.10.1'

In [3]: import datajoint as dj

In [4]: dj.__version__
Out[4]: '0.12.7'

In [5]: dj.config
Out[5]: 
{   'connection.charset': '',
    'connection.init_function': None,
    'database.host': 'lmf-db.cin.ucsf.edu',
    'database.password': '<password>',
    'database.port': 3306,
    'database.reconnect': True,
    'database.use_tls': None,
    'database.user': 'kyu',
    'display.limit': 12,
    'display.show_tuple_count': True,
    'display.width': 14,
    'enable_python_native_blobs': False,
    'fetch_format': 'array',
    'loglevel': 'INFO',
    'safemode': True}

In [6]: dj.schema('test')
Connecting kyu@lmf-db.cin.ucsf.edu:3306
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-6-3e1091301354> in <module>
----> 1 dj.schema('test')

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/datajoint/schemas.py in __init__(self, schema_name, context, connection, create_schema, create_tables)
     54         """
     55         if connection is None:
---> 56             connection = conn()
     57         self._log = None
     58 

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/datajoint/connection.py in conn(host, user, password, init_fun, reset, use_tls)
     86         init_fun = init_fun if init_fun is not None else config['connection.init_function']
     87         use_tls = use_tls if use_tls is not None else config['database.use_tls']
---> 88         conn.connection = Connection(host, user, password, None, init_fun, use_tls)
     89     return conn.connection
     90 

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/datajoint/connection.py in __init__(self, host, user, password, port, init_fun, use_tls)
    119         print("Connecting {user}@{host}:{port}".format(**self.conn_info))
    120         self._conn = None
--> 121         self.connect()
    122         if self.is_connected:
    123             logger.info("Connected {user}@{host}:{port}".format(**self.conn_info))

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/datajoint/connection.py in connect(self)
    144             warnings.filterwarnings('ignore', '.*deprecated.*')
    145             try:
--> 146                 self._conn = client.connect(
    147                     init_command=self.init_fun,
    148                     sql_mode="NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,"

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/__init__.py in Connect(*args, **kwargs)
     92     """
     93     from .connections import Connection
---> 94     return Connection(*args, **kwargs)
     95 
     96 from . import connections as _orig_conn

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/connections.py in __init__(self, host, user, password, database, port, unix_socket, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, ssl, read_default_group, compress, named_pipe, autocommit, db, passwd, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key)
    325             self._sock = None
    326         else:
--> 327             self.connect()
    328 
    329     def _create_ssl_ctx(self, sslp):

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/connections.py in connect(self, sock)
    586 
    587             self._get_server_information()
--> 588             self._request_authentication()
    589 
    590             if self.sql_mode is not None:

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/connections.py in _request_authentication(self)
    851 
    852         self.write_packet(data)
--> 853         auth_packet = self._read_packet()
    854 
    855         # if authentication method isn't accepted the first byte

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    674             if self._result is not None and self._result.unbuffered_active is True:
    675                 self._result.unbuffered_active = False
--> 676             packet.raise_for_error()
    677         return packet
    678 

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/protocol.py in raise_for_error(self)
    221         errno = self.read_uint16()
    222         if DEBUG: print("errno =", errno)
--> 223         err.raise_mysql_exception(self._data)
    224 
    225     def dump(self):

~/miniconda3/envs/dj_test/lib/python3.8/site-packages/pymysql/err.py in raise_mysql_exception(data)
    105     if errorclass is None:
    106         errorclass = InternalError if errno < 1000 else OperationalError
--> 107     raise errorclass(errno, errval)

OperationalError: (1043, 'Bad handshake')

[guzman-raphael]

Met with @ttngu207 (thanks man!) for a debug session and was able to identify the root cause and successfully reproduce the issue on my side. Should be a simple fix now with all the details.

Here is a summary of what I've found:

• Issue: Due to error classes being modified in PyMySQL==0.10.0 (specifically the error raised when a secure connection is attempted on a server that does not support secure connections), dj-python now expects the wrong error class. Essentially here now receives an OperationalError (1043).
• Why was it missed? DataJoint performs connections with encryption preferred as the default. Therefore, users who've experienced this are working with DB servers that explicitly have encrypted connections disabled (likely WIN users since this is the default configuration for native MySQL service install). This is a challenging corner case to reproduce in tests w/o spinning up 2 servers (1 encryption allowed, 1 encryption disabled) since the failover is what needs to be verified. Tests have been utilizing a server that allows encryption (also the same which has been distributed among community). Therefore, it has not been raised simply because it isn't properly covered. Will issue a fix soon.

A simple check or workaround for users who might be experiencing this is to simply connect using: dj.conn(use_tls=False).