dj.set_password does not work in MySQL 8

[rly]

Bug Report

Description

dj.set_password runs the MySQL query SET PASSWORD = PASSWORD('new_password') (source code). Loren Frank's lab recently updated their MySQL database to version 8.0.34, and the PASSWORD function in MySQL has been removed in MySQL version 8. As a result, dj.set_password no longer works.

The new recommended MySQL query is ALTER USER 'user_name' IDENTIFIED BY 'new_password';
Running that works.

See LorenFrankLab/spyglass#639

Reproducibility

Include:

• OS (WIN | MACOS | Linux) MacOS
• Python Version OR MATLAB Version 3.9
• MySQL Version 8.0.34
• MySQL Deployment Strategy (local-native | local-docker | remote) remote
• DataJoint Version 0.14.1
• Minimum number of steps to reliably reproduce the issue dj.set_password
• Complete error stack as a result of evaluating the above steps

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('******')")