dj.set_password does not work in Frank Lab production database (MySQL 8)

[rly]

Describe the bug

dj.set_password runs the MySQL query SET PASSWORD = PASSWORD('new_password') (source code). This used to work, but I think the MySQL database was recently updated to version 8.0.34, and the PASSWORD function in MySQL has been removed in MySQL version 8.

I'll report this in DataJoint but want to log it here as it appears to be an incompatibility between the Frank Lab database MySQL version and the MySQL version expected by DataJoint.

Note that dj.set_password is also called in config/dj_config.py

New password: 
Confirm password: 
[2023-08-31 22:06:46,571][ERROR]: Uncaught exception
Traceback (most recent call last):
  File "/Users/rly/Documents/NWB/spyglass/config/dj_config.py", line 115, in <module>
    main(*sys.argv[1:])
  File "/Users/rly/Documents/NWB/spyglass/config/dj_config.py", line 109, in main
    set_configuration(config)
  File "/Users/rly/Documents/NWB/spyglass/config/dj_config.py", line 96, in set_configuration
    dj.set_password()  # set the users password
  File "/Users/rly/mambaforge/envs/spyglass/lib/python3.9/site-packages/datajoint/admin.py", line 19, in set_password
    connection.query("SET PASSWORD = PASSWORD('%s')" % new_password)
  File "/Users/rly/mambaforge/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py", line 340, in query
    self._execute_query(cursor, query, args, suppress_warnings)
  File "/Users/rly/mambaforge/envs/spyglass/lib/python3.9/site-packages/datajoint/connection.py", line 296, in _execute_query
    raise translate_query_error(err, query)
datajoint.errors.QuerySyntaxError: ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('******')' at line 1", "SET PASSWORD = PASSWORD('******')")

[lfrank]

My guess is that they are much more likely to get to this if it is a pull request, so I just wanted to note that in case someone has time to fork datajoint and change the query to (I believe)
ALTER USER user_name IDENTIFIED BY 'password';

[lfrank]

@CBroz1 Your thoughts on how to fix this would be greatly appreciated; this seems like a high priority to me.

[CBroz1]

The edits to DJ seem pretty straightforward, checking version before sending the command. I'll mention it in their slack to get it on their radar. I have a busy week ahead with the move, but I can tackle a PR thereafter if needed

[rly]

I took care of it in datajoint/datajoint-python#1106 . It was a fun little challenge to set up the tests right. Hopefully it is merged soon. In the meantime, we can run dj.conn().query("ALTER USER user_name IDENTIFIED BY 'password';")

[edeno]

This has been merged into datajoint so I am closing. If this becomes an issue again, please reoopen.