0

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?

FlyingZebra1
  • 1,285
  • 1
  • 18
  • 28
  • looks like pip show mysql-connector-python gives me version of connector. now need to try to switch it to earlier version . lets see if this works. – FlyingZebra1 Nov 15 '20 at 05:58
  • Also consider having a database migration plan. Your state of stretching a developed app via a old connector and and older database will hamper your use of new features. – danblack Nov 15 '20 at 06:24
  • @danblack this has definitely encouraged us to migrate :-[ unfortunately, without much prep or plan. – FlyingZebra1 Nov 16 '20 at 03:59
  • We ended up migrating. Few things to keep in mind in going from 5.0 (ish) to 8: 1) GROUP BY has change (stricter) 2) login pw is sha2 encryption – FlyingZebra1 Nov 19 '20 at 07:04
  • So I hope you considered the queries rather than disabling [ONLY_FULL_GROUP_BY](https://stackoverflow.com/questions/64824498/why-should-not-disable-only-full-group-by/64831540#64831540). I'm assuming the caching_sha2_password just required a later connector version again. – danblack Nov 19 '20 at 09:01
  • yup, updated everything to newer version. hopefully this will last another 5 years :) – FlyingZebra1 Nov 20 '20 at 04:23

3 Answers3

2

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
FlyingZebra1
  • 1,285
  • 1
  • 18
  • 28
0

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.

RiveN
  • 2,595
  • 11
  • 13
  • 26
jcmendez
  • 1
  • 1
0

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.

Okello
  • 1
  • 1