After upgrading Redash v7 to v8, I have an issue where I cannot connect MySQL to Redash with the following error.
Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so
It seems like this problem is caused by the MySQL user’s authentication type “caching_sha2_password”.
So I created a new user with the type “mysql_native_password” and try to connect again, which ended up with the following error.
Plugin sha256_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/sha256_password.so:
We merged this PR in V8. So my guess is this is an issue with mysqlclient.
I suggest you try upgrading your version of mysqlclient. V8 is pinned to 1.4.1. But in 1.4.6 they merged the following PR:
Relevant comment from that PR
I’m using Proxysql which only supports mysql_native_password . When using libmysqclient21 the new default is caching_sha2_password so I’m unable to connect to Proxysql.
I understand there are some protocol capabilities for negotiating authentication method, but these seem to be unsupported by Proxysql. So, my solution is to set the correct auth plugin before connecting.
I bet MariaDB doesn’t support the authentication negotiation features either.
Once you install the new mysqlclient you can set a new environment variable in your dockerfile:
MYSQL_DEFAULT_AUTH: caching_sha2_password
Just set it equal to whatever your version of MySQL requires.
Alternative Theory
I searched the mysqlclient issue history and found this which might be related:
I know you’re not on Windows, but I’m looking specifically at this comment:
Connector 6.1 can’t connect to MySQL 8.
I’m waiting MariaDB Connector. It supports sha256_password without OpenSSL.
Thank you for your quick and detailed reply.
I upgrade Redash from v7 to v8 again and it is working well with a MySQL user called redash whose authentication type is mysql_native_password.
mysql> select User, Plugin from user;
+------------------+-----------------------+
| User | Plugin |
+------------------+-----------------------+
| root | caching_sha2_password |
| redash | mysql_native_password |
...
+------------------+-----------------------+
8 rows in set (0.00 sec)
I will use Redash with a mysql_native_password user, so my problem is solved now. Thank you so much for your help.
FYI, let me just write down what I looked into about this issue.
When I clicked the “Test Connection” button with a caching_sha2_password user called root, Redash still shows up the following error.
Connection Test Failed:
(2059, ‘Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory’)
I upgraded mysqlclient in the Redash server.
$ docker exec -it --user root redash_server_1 bash
root@xxx/app# pip list | grep mysql
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
mysqlclient 1.4.4
WARNING: You are using pip version 19.2.2, however version 20.1.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
root@xxx:/app# pip install mysqlclient --upgrade
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Collecting mysqlclient
Downloading https://files.pythonhosted.org/packages/d0/97/7326248ac8d5049968bf4ec708a5d3d4806e412a42e74160d7f266a3e03a/mysqlclient-1.4.6.tar.gz (85kB)
|████████████████████████████████| 92kB 9.5MB/s
Building wheels for collected packages: mysqlclient
Building wheel for mysqlclient (setup.py) ... done
Created wheel for mysqlclient: filename=mysqlclient-1.4.6-cp27-cp27mu-linux_x86_64.whl size=107400 sha256=921f8d3f2f2f17bcfcecb831a83981dad1728e108901a27b7bd44c664bb215a6
Stored in directory: /root/.cache/pip/wheels/37/3d/24/5327fa50817a65ed0ee4dc8809e5c39962b0dd5e078ebf4dc1
Successfully built mysqlclient
ERROR: memsql 3.0.0 has requirement mysqlclient==1.3.13, but you'll have mysqlclient 1.4.6 which is incompatible.
Installing collected packages: mysqlclient
Found existing installation: mysqlclient 1.4.4
Uninstalling mysqlclient-1.4.4:
Successfully uninstalled mysqlclient-1.4.4
Successfully installed mysqlclient-1.4.6
WARNING: You are using pip version 19.2.2, however version 20.1.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
root@xxx:/app# exit
exit
$ docker-compose stop
Stopping redash_nginx_1 ... done
Stopping redash_scheduled_worker_1 ... done
Stopping redash_adhoc_worker_1 ... done
Stopping redash_server_1 ... done
Stopping redash_scheduler_1 ... done
Stopping redash_postgres_1 ... done
Stopping redash_redis_1 ... done
$ docker-compose up -d
Starting redash_redis_1 ... done
Starting redash_postgres_1 ... done
Starting redash_scheduled_worker_1 ... done
Starting redash_adhoc_worker_1 ... done
Starting redash_scheduler_1 ... done
Starting redash_server_1 ... done
Starting redash_nginx_1 ... done
Then I clicked the “Test Connection” button with the caching_sha2_password user again. But it still says the same error.
Connection Test Failed:
(2059, ‘Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory’)