MySQL Utilities with MySQL 8 Server

Question

As part of our build process, we use the mysqldiff utility (invoked from maven) to validate our database migration scripts by comparing a freshly-built copy of the schema to a version of the schema created from a baseline plus our migration script. This all works fine with MySQL 5.7.

We are looking to upgrade to MySQL 8.0.13. The database user has been configured to use mysql_native_password. When we run our build, we are getting this error from mysqldiff:

ERROR: Authentication plugin 'caching_sha2_password' is not supported

We understand that this error is due to the fact that the utility is using an old version of mysql-python-connector. We also understand that the answer might be as simple as upgrading the connector version, but we don't know how to go about trying that.

The MySQL Utilities can be found at https://github.com/mysql/mysql-utilities.

On Windows 10, we install using the Oracle windows installer. On Amazon Linux, we install with yum.

NOTE:

1. The MySQL Utilities appear to be based on an embedded python2.7 installation (we do not have standalone python installed on any of the development or build machines).
2. We do not have python expertise, so detailed steps will be helpful if we are mucking with the embedded python stuff.
3. We need to solve this problem both on Windows 10 and Amazon Linux.

How do we work around this error so that we can use mysqldiff with a MySQL 8.0.13 server on Windows 10 and Amazon Linux?

If the answer is simply to upgrade the connector, what are the detailed steps for doing that?

Are there server installation/configuration changes we can make to support clients connecting with old drivers?

Limpkin

how do you get MySQL Utilities installed, via the installer from oracle website? – Awl

@Awl Edited installation information into the question. On Windows, we use the Oracle installer. On Amazon Linux we use yum. Thanks! – Limpkin

To be honest, it'd be much easier for you to a) install Python 2.7, b) install mysql-connector (download from Oracle) then c) git clone the utilities project and run python setup.py install in the project directory. You probably have to apply a bug fix that Oracle has been ignoring (see the bug report as to why you need this). – Unlicensed

The alternative is to install cx_Freeze, the Microsoft Visual C++ compiler 9.0 for Python 2.7 package, get the mysql-connector source code and the mysql client headers, and then follow the instructions in the package.py script in the mysql-utilities distribution to put this all together, then cross your fingers and run python package.py build and hope that whatever that outputs can be useful somewhere. And then also install the MS Visual C++ redestributable package. – Unlicensed

(See cx-freeze.readthedocs.io/en/latest/… for the latter, cx-freeze.readthedocs.io/en/latest/… for the package.py commands). – Unlicensed

"we do not have standalone python installed" do you able to install a python under windows? – Awl

Answer 1

I have managed to run mysqldiff.py against mysql 8, with some patches:

clone the patched source code and enter its dir:

$ git clone https://github.com/georgexsh/mysql-utilities.git

create a virtualenv and activate it:

$ virtualenv -p python2 venv
$ . venv/bin/activate

install the newer mysql connector:

(venv) $ pip install mysql-connector-python>=8.0

install mysql-utilities to the current virtualenv:

(venv) $ pip install .

now mysqldiff.py is able to run. if you want to run without activate virtualenv, you use its full path:

/path/to/mysql-utilities/venv/bin/mysqldiff.py

steps under windows are mostly the same, except virtualenv activation:

venv\Scripts\activate.bat

Awl

Answer 2

I encountered same problem and looked into mysql python connector code and added class for caching_sha2_password. It works for me now.

1. To fix the issue download source code for mysql-python-connector from Mysql official website, then install python (any version).

2. unzip downloaded mysql-python-connector zip file, and inside you will find setup.py

3. Open terminal and type python setup.py install

4. build folder will be created in current folder. Go to build/mysql/ and copy authentication.py file

5. open Mysql Utilities ->> bin ->> library.zip, and find mysql connector- > mysql > authentication.pyc file and delete i and paste authentication.py file you copied earlier.

6. DONE !

If you dont want to mess with it, you can download ready files from this link

Here is the link for files and explanation.

https://github.com/rgaraisayev/mysqldiff

Visionary

Please don't post answers that do not attempt to answer the original question. If you are having the same trouble, read the other answers and do abit more research first, if you're still stuck by all means post a new question with all relevant details. Non-answers will be voted to be removed. – Jitter

I added explanation how to do it – Visionary

That you so much rgaraisayev. Your solution worked! I've been working on this for two days now and it's finally resolved. God Bless you! After I got rid of the 'sha256_authentication' issue, I had a "Permission Denied: 'version_check' issue in mysql utilities 'tools.py', but I got that resolved with another SO savior. I'm recovering my file structure. StackOverflow Rocks big time and it's getting better and better! If I could give you 10 points I would. Thank you so much!!! – Comedown

glad it helped you. Thank you for your gratitude – Visionary