Connection to remote DJ database out-competed by locally running database once dj.config.save_global() used

[a-darcher]

Bug Report

Description

I am running DataJoint locally via Docker for one project, and would like to connect to a remote DataJoint database for another project. While trying to set-up my remote schema, I saved the dj.config details for the remote database within the directory (dj.set_password) and globally on my local machine (dj.config.save_global). This prevented me from successfully logging into either my locally running database and the remote. I manually removed the files generated by dj.set_password and dj.config.save_global, dj_local_conf.json and ~/.datajoint_config.json respectively, which restored normal access/behavior for my local database. The issue is that I cannot access the remote database, as it seems like my local IP is always used instead of the remote IP.

Reproducibility

Specs:

• OS: Linux Ubuntu
• Python: 3.8.3
• MySQL: 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64
• MySQL Deployment Strategy: local-docker & remote
• DataJoint Version: 0.12.6

Issue:

1. With local docker database running in the background, connected to remote database successfully via:

dj.config['database.host'] = '123.45.6.78' # remote host
dj.config['database.user'] = 'username'
dj.config['database.password'] = 'mycoolpassword'
dj.conn()

2. Re-set password, and allowed update of local settings:

dj.set_password()

3. Set global parameters (guessing that this caused the initial error with local):

dj.config.save_global()

4. Local access no longer worked, and logging in to remote in a new python instance also no longer worked. I manually removed dj_local_conf.json and ~/.datajoint_config.json, and local access was restored.
5. Attempted to access remote via the following snippets:

   import datajoint as dj
   dj.config['database.host'] = '123.45.6.78' # remote host
   dj.config['database.user'] = 'username'
   dj.config['database.password'] = 'mycoolpassword'
   dj.conn()

   and

   import datajoint as dj
   dj.conn(host='123.45.6.78',  user='username', password='mycoolpassword') 

   but it fails, as it defaults to using my local IP address and won't use the host address for the connection.

Error stack:

Connecting username@<remote IP address>:3306
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-5-7f9ad80d8d42> in <module>
      5 # dj.conn()
      6 
----> 7 dj.conn(host='<remote IP address', user='username', password='mycoolpassword')

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/datajoint/connection.py in conn(host, user, password, init_fun, reset, use_tls)
    117         init_fun = init_fun if init_fun is not None else config['connection.init_function']
    118         use_tls = use_tls if use_tls is not None else config['database.use_tls']
--> 119         conn.connection = Connection(host, user, password, None, init_fun, use_tls)
    120     return conn.connection
    121 

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/datajoint/connection.py in __init__(self, host, user, password, port, init_fun, use_tls)
    176         self._conn = None
    177         self._query_cache = None
--> 178         connect_host_hook(self)
    179         if self.is_connected:
    180             logger.info("Connected {user}@{host}:{port}".format(**self.conn_info))

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/datajoint/connection.py in connect_host_hook(connection_obj)
     43                 "Connection plugin '{}' not found.".format(plugin_name))
     44     else:
---> 45         connection_obj.connect()
     46 
     47 

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/datajoint/connection.py in connect(self)
    199             warnings.filterwarnings('ignore', '.*deprecated.*')
    200             try:
--> 201                 self._conn = client.connect(
    202                     init_command=self.init_fun,
    203                     sql_mode="NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,"

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/pymysql/connections.py in __init__(self, user, password, host, database, unix_socket, port, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)
    351             self._sock = None
    352         else:
--> 353             self.connect()
    354 
    355     def __enter__(self):

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/pymysql/connections.py in connect(self, sock)
    631 
    632             self._get_server_information()
--> 633             self._request_authentication()
    634 
    635             if self.sql_mode is not None:

~/miniconda3/envs/dj_env/lib/python3.9/site-packages/pymysql/connections.py in _request_authentication(self)
    905 
    906         self.write_packet(data)
--> 907         auth_packet = self._read_packet()
    908 
    909         # if authentication method isn't accepted the first byte

~/miniconda3/envs/dj_env/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 

~/miniconda3/envs/dj_env/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):

~/miniconda3/envs/dj_env/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: (1045, "Access denied for user 'username'@'<my local IP address>' (using password: YES)")

Expected Behavior

To be able to access a remote datajoint database from my machine via Jupyter Notebook/Lab, while I have a local datajoint database running from a docker-compose image.

Thanks so much for any help!

[MadhuMPandurangi]

Hi @a-darcher @dimitri-yatsenko I'm interested to contribute. I'm the first time contributor and have idea about docker, MYSQL and django.

Thank you

[guzman-raphael]

@a-darcher Thank you for the report but I'm afraid there may be bit of confusion here. :sweat_smile:

In DataJoint Python, you must connect to a MySQL-compatible database server to store your pipeline data. dj.config provides a way for you to manage in memory several important configuration. That configuration can be dumped or saved to a file using the options: dj.config.save(), dj.config.save_local(), dj.config.save_global(). You can find more information on this here.

One of the configurations that is important to manage is the credentials for you to connect with the database server. dj.set_password(), however, is a utility that allows you to change your password on the database server. It also provides an option to automatically save out the config to a file to ensure it is in sync but bear in mind that each time you call dj.set_password(), you are changing your database password.

Regarding access, it is important to add the user+grants properly into the database server before trying to connect. Normally this is done by issuing 2 queries. This is normally done in SQL initially only until you can connect with DataJoint Python.

• CREATE USER 'john'@'%%' IDENTIFIED BY 'secret'; -> Allow john user to connect from any IP address using secret as a password.
• GRANT ALL PRIVILEGES ON *.* TO 'john'@'%%'; -> Allow john user to have full privileges to any schema and any table in the database server. This is usually much higher privilege than is necessary. We'd recommend fine tuning this down in proper production applications once you have finishing prototyping.

Let us know if you have any trouble after reviewing the above. We'll plan to mark this as resolved if we don't hear back from you after a week but we can always reopen if needed.

[guzman-raphael]

Thanks for your interest @MadhuMPandurangi! We always appreciate any help you can provide. :smiley:

We welcome all PR's but I might suggest having a look at these good-first-issues. We've recently updated them and they should reflect some easy ones to get started.

Please let me know if any of those catch your eye and I can assign them to you.