Interacting with MySQL 5.1 database from computer with MySQL 8 via python/SQLAlchemy

Question

I just updated my system from Ubuntu 19.1 to 20.02 - and it looks like all the previous MySQL settings got wiped out.

Current system has MySQL 8 (server + client) installed.
The server I am trying to access has MySQL 5.1.

Every time I try to connect using SqlAlchemy, I get the following error:

sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) 1043 (08S01): Bad handshake

From what I read - this error happens because the server I'm attempting to connect to is running version of MySQL that is too old.

Is there a way to downgrade MySQL connector, to fix the connection issue?

Answer 1

I was able to make this work by uninstalling newest connector:

sudo pip3 uninstall mysql-connector-python

Then installing an older version of the connector:

sudo pip3 install -Iv mysql-connector-python==8.0.5

To check your connector version, you can use:

pip3 show mysql-connector-python

Answer 2

Sorry for the late answer. Add the parameter use_pure=True in your call to the connect() function.

From version 8.0.11 and up this option is False by default.

Answer 3

If you are trying to connect to an older installation like MySQL version 5.0.67 from a newer Python installation like Python version 3.8.10 then to stop ERROR 1043 (08S01): Bad handshake, do the following to downgrade the connector:

1. Uninstall whatever mysql-connector-python you had installed either using an msi file or pip install. For example

   py -3 -m pip uninstall mysql-connector-python-x.x.x or if it was an msi installation, uninstall it from the control panel – programs and features

2. Next download a zip file of older mysql-connector-python e.g. mysql-connector-python-2.2.2 (You’ll find the list on the Archives tab on https://dev.mysql.com/downloads/connector/python/)

3. Unzip the file to a directory e.g. If your python installation is on the root of drive C, unzip the mysql-connector you downloaded on C:\python such that you have the resulting folder structure C:\python\ mysql-connector-python-2.2.2 (You can do this on the downloads folder if you please)

4. Now open the DOS prompt using “Run as administrator” and navigate to C:\python\ mysql-connector-python-2.2.2 (or whatever version you downloaded)

5. From there, run the following command

   py -3 setup.py install

This worked for me on windows 7 OS. Logically, you can make it work for you on other operating systems.