Can't connect MySQL to Redash v8

Issue Summary

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:

I installed Redash v7 with the procedure below.

After I upgrade Redash to v8 with the following guide, the problems above started to appear.
https://redash.io/help/open-source/admin-guide/how-to-upgrade.

I took a backup before upgrading, so I restored our Redash with the backup and got it to v7. I do not have any authentication issues with v7.

How can I upgrade Redash to v8 and use it with MySQL without any problems?

Technical details:

  • Redash Version: 8.0.0
  • OS: CentOS Linux release 7.6.1810 (Core)
  • How did you install Redash: With the procedure above

Theory

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.

It’s probably unrelated.

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’)

2 Likes

Confirm this is my experience as well. Tried to upgrade in all containers with mysqlclient installed (4 IIRC) and no change.

Only work around now is to recreate the password on the MySQL server using the old native method:

ALTER USER user IDENTIFIED WITH mysql_native_password BY 'pw';