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