search

datajoint / datajoint-matlab

Relational data pipelines for the science lab
MIT License
42 stars 38 forks source link

SSL Connection error #425

Open mani33 opened 1 year ago

mani33 commented 1 year ago

I installed MySQL server 8.0.32, matlab R2022b, Datajoint 3.5.0, mym 2.8.5 on my desktop 64 bit Windows 10 machine. When I typed in dj.conn( ) in matlab and entered credentials, I get the error: “Error using mym SSL connection error: unknown error number Error in dj.Connection/query (line 167) self.connId=mym(-1, ’open’, self.host, self.user, self.password, self.use_tsl); “

I tried using all the options including “No” for SSL tab in the Manage Connections tab of MySqlWorkbench. Error still persists.

guzman-raphael commented 1 year ago

@mani33 Thanks for moving this post to our issue tracker.

Before starting, I'll say that if using Python is an option, I'd highly recommend checking out datajoint-python as you will have an easier time. Contributions are much more active there and new features/fixes usually land there first.

Your issue is a bug but it is a bit involved to patch it properly. I'll outline some details and steps below so you can take the option that works best for you.

Problem

This problem is primarily related to the open mym issue datajoint/mym#55. Whenever we call dj.conn, it uses mym as the API to connect to MySQL. Until it is fixed, mym cannot make encrypted connections against MySQL >8.0.18.

dj.conn actually takes several arguments but all have defaults. Below is the full list of options.

dj.conn(host, user, pass, initQuery, reset, use_tls, nogui)

TLS (or sometimes referred to as SSL) is the primary protocol by which communication is encrypted between client and server using certs. It is the best practice to encrypt when you can. By best available we mean that the connection is first attempted with encryption and if it fails, it is supposed to revert to unencrypted (though that is obviously not working for your setup).

Workaround

As workaround, you'll need to make a choice.

  1. Require encryption -> Suggest downgrading to MySQL 8.0.18 and trying again. Check out our MySQL Docker instructions to setup a DataJoint-optimized MySQL instance.
  2. Require latest MySQL but don't require encryption -> Set use_tls to false. Here is an example of what this looks like: 
    dj.conn('fakeservices.datajoint.io', 'root', 'simple', '', true, false)

    Call this at the beginning of a DJ session or script.

Additional Notes

You might also run into the following issue:

Error using mym
Authentication plugin 'caching_sha2_password' cannot be loaded: /home/muser/MATLAB
Add-Ons/Toolboxes/mym/distribution/mexa64/caching_sha2_password.so: cannot open shared
object file: No such file or directory

Error in dj.Connection/query (line 167)
                self.connId=mym(-1, 'open', self.host, self.user, self.password,
                self.use_tls);

Error in dj.conn (line 107)
        query(connObj, 'status')

This is because MySQL changed their default authentication plugin from mysql_native_password to caching_sha2_password in 8.0.

The caching_sha2_password is still not supported in mym (datajoint/mym#52). The workaround is to change the authentication plugin on the server from caching_sha2_password to mysql_native_password. You can do this by setting the following config in your server's my.cnf.

[mysqld]
# Set default auth plugin
default_authentication_plugin=mysql_native_password

Hope this helps.

mani33 commented 1 year ago

Thank you for taking care of this.

-Mani

On Friday, February 17, 2023, Raphael Guzman @.***> wrote:

@mani33 https://github.com/mani33 Thanks for moving this post to our issue tracker.

Before starting, I'll say that if using Python is an option, I'd highly recommend checking out datajoint-python https://github.com/datajoint/datajoint-python as you will have an easier time. Contributions are much more active there and new features/fixes usually land there first.

Your issue is a bug but it is a bit involved to patch it properly. I'll outline some details and steps below so you can take the option that works best for you. Problem

This problem is primarily related to the open mym issue datajoint/mym#55 https://github.com/datajoint/mym/issues/55. Whenever we call dj.conn, it uses mym as the API to connect to MySQL. Until it is fixed, mym cannot make encrypted connections against MySQL >8.0.18.

dj.conn actually takes several arguments but all have defaults. Below is the full list of options https://github.com/datajoint/datajoint-matlab/blob/e6bf304a2fdbc90a144124880c30e83ee7cbcb6d/%2Bdj/conn.m#L21 .

dj.conn(host, user, pass, initQuery, reset, use_tls, nogui)

  • host : Database address.
  • user : Database user name.
  • pass : Database user password.
  • initQuery : Initializing function or query executed for each new session.
  • reset : Force a connection reset.
  • use_tls : Encrypt connection. Defaults to best available.
  • nogui : If true, will read prompt from terminal prompt.

TLS (or sometimes referred to as SSL) is the primary protocol by which communication is encrypted between client and server using certs. It is the best practice to encrypt when you can. By best available we mean that the connection is first attempted with encryption and if it fails, it is supposed to revert to unencrypted (though that is obviously not working for your setup). Workaround

As workaround, you'll need to make a choice.

  1. Require encryption -> Suggest downgrading to MySQL 8.0.18 and trying again. Check out our MySQL Docker instructions https://github.com/datajoint/mysql-docker#mysql-for-datajoint to setup a DataJoint-optimized MySQL instance.

  2. Require latest MySQL but don't require encryption -> Set use_tls to false. Here is an example of what this looks like:

    dj.conn('fakeservices.datajoint.io', 'root', 'simple', '', true, false)

    Call this at the beginning of a DJ session or script.

Additional Notes

You might also run into the following issue:

Error using mym Authentication plugin 'caching_sha2_password' cannot be loaded: /home/muser/MATLAB Add-Ons/Toolboxes/mym/distribution/mexa64/caching_sha2_password.so: cannot open shared object file: No such file or directory

Error in dj.Connection/query (line 167) self.connId=mym(-1, 'open', self.host, self.user, self.password, self.use_tls);

Error in dj.conn (line 107) query(connObj, 'status')

This is because MySQL changed their default authentication plugin from mysql_native_password to caching_sha2_password in 8.0.

The caching_sha2_password is still not supported in mym (datajoint/mym#52 https://github.com/datajoint/mym/issues/52). The workaround is to change the authentication plugin on the server from caching_sha2_password to mysql_native_password. You can do this by setting the following config in your server's my.cnf.

[mysqld]

Set default auth plugin

default_authentication_plugin=mysql_native_password

Hope this helps.

— Reply to this email directly, view it on GitHub https://github.com/datajoint/datajoint-matlab/issues/425#issuecomment-1435480737, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAOVORR3X33LN5OGHRRYJC3WYBGNVANCNFSM6AAAAAAU7VF55I . You are receiving this because you were mentioned.Message ID: @.***>

noamza commented 2 months ago

This method didn't work for me but running the following on my localhost did:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword'

and then connecting with TLS off as described above