Ошибка 08s01 bad handshake

Bug description

Hi, I’ve been trying to set up SSL for my prisma connection but I’m getting a «Bad Handshake» error, even though on MySQL Workbench, tableplus… my connection works fine with certificates.
I’ve followed the guide on the prisma docs website here. And placed both client-identity.p12 and rootca.cert inside my ./prisma folder.
The connection string i’m using is:
'mysql://user:password@ip:port/database?sslidentity=client-identity.p12&sslpassword=mypassword&sslcert=rootca.cart'

Prisma information

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Environment & setup

Trying to connect to: MySQL Server V8.0.31 @ Ubuntu 22.04

Prisma Version

prisma                  : 4.7.1
@prisma/client          : 4.7.1

Answer by Lauryn Wise

make sure you’ve installed mysql-connector and not mysql-connector-python, to make this sure just run the following commands: pip3 uninstall mysql-connector-python pip3 install mysql-connector,

2

This was my problem, I was using pyCharm and it installed mysql-connector-python so I had to follow your recommendation to uninstall it and install the right one in my venv environment.

– fanbondi

Mar 1 at 9:18

,I am trying to connect to a mysql database (hosted on media temple) with my python script (ran locally) but I am receiving an error when I run it. ,Connect and share knowledge within a single location that is structured and easy to search.

The error is:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Bad handshake

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/Charlie/Documents/python/myscript/mysql_insert.py", line 8, in <module>
    port="3306"
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/__init__.py", line 172, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
    self.connect(**kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/abstracts.py", line 735, in connect
    self._open_connection()
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 182, in _open_connection
    sqlstate=exc.sqlstate)
mysql.connector.errors.OperationalError: 1043 (08S01): Bad handshake

Here is the code from the script

import mysql.connector

mydb = mysql.connector.connect(
  host="external-db.s157688.gridserver.com",
  user="myusername",
  passwd="mypassword",
  database="mydatabase",
  port="3306"
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (post_id, title) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

Answer by Clayton Rice

After updating mysql-connector-python to 8.0.11 (released April 19, 2018), I received an «OperationalError: 1043 (08S01): Bad handshake» error when querying a MySQL database. I found that I needed to set ‘use_pure: True’ in the connection string.,The C extension was added in version 2.1.1 and is enabled by default as of 8.0.11. The use_pure option determines whether the Python or C version of this connector is enabled and used.1,Since I don’t have the C extension installed on my server, I reverted back to the Python implementation. Though it is good to know that the C extension can improve performance for large queries—something to tuck away for future use.

For example²:

import mysql.connector

config = {
  'user': 'scott',
  'password': 'password',
  'host': '127.0.0.1',
  'database': 'employees',
  'use_pure': True,
}

cnx = mysql.connector.connect(**config)

Answer by Conner Choi

afer entering password I’m getting that error:,There is old Bug Report about MySQL Workbench connecting to older databases.,When you google the subject of «mysql bad handshake», you will see two basic issues:,

Help Center

Detailed answers to any questions you might have

I’m having problem to connect to the remote DB from the terminal as follow

mysql -u db_user -p -h 10.100.1.100
Enter password:

afer entering password I’m getting that error:

ERROR 1043 (08S01): Bad handshake

Answer by Axl Freeman

Trying to connect to the database and getting a really weird error. Tried doing this without aiomysql but rather directly with PyMySQL and the connection worked fine. Would greatly appreciate some help.,

The text was updated successfully, but these errors were encountered:
,
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
,

Sorry, something went wrong.

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/connection.py", line 447, in _connect
    yield from self._request_authentication()
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/connection.py", line 615, in _request_authentication
    auth_packet = yield from self._read_packet()
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/connection.py", line 500, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/err.py", line 115, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
pymysql.err.InternalError: (1043, 'Bad handshake')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/lib/python3.5/code.py", line 91, in runcode
    exec(code, self.locals)
  File "<input>", line 22, in <module>
  File "/usr/lib/python3.5/asyncio/base_events.py", line 373, in run_until_complete
    return future.result()
  File "/usr/lib/python3.5/asyncio/futures.py", line 274, in result
    raise self._exception
  File "/usr/lib/python3.5/asyncio/tasks.py", line 240, in _step
    result = coro.send(None)
  File "<input>", line 11, in test_example
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/utils.py", line 57, in __await__
    resp = yield from self._coro
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/pool.py", line 28, in _create_pool
    yield from pool._fill_free_pool(False)
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/pool.py", line 163, in _fill_free_pool
    **self._conn_kwargs)
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/utils.py", line 52, in __iter__
    resp = yield from self._coro
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/connection.py", line 76, in _connect
    yield from conn._connect()
  File "/usr/local/lib/python3.5/dist-packages/aiomysql/connection.py", line 467, in _connect
    self._host) from e
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'XXX.XXX.XXX.XXX'")

Answer by Rosalia Macdonald

Content reproduced on this site is the property of the
respective copyright holders.It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.

The file you have requested (bug.php?id=91828)
does not exist.

bug.php?id=91828

C

I upgraded an old server from 5.1.34sp1 to 5.1.67 some time back… that was about the same amount of «jump» in minor versions as you are contemplating. In this case, the system worked without incident.
Within the same major release of MySQL (e.g. 5.1.x to 5.1.y), assuming the versions involved are after the GA milestone, upgrading should be relatively straightforward.

For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture.
— http://dev.mysql.com/doc/refman/5.1/en/upgrading.html

Nevertheless, a complete backup is essential.
Importantly, if you haven’t tried restoring your backup on a different server, then for all practical purposes, you don’t actually have a backup.
Giving you the «ssh commands» (actually, shell commands) to do the upgrade isn’t possible, since there are variations in installation that make it impossible to provide a list of «type these things and it will work» commands. Assuming your current installation is using an Oracle tarball binary distribution, you’ll need to download and extract the binaries into a new directory, stop the server, move or relink the data files where the new copy of MySQL can see them, and rename a couple of directories or change a couple of symlinks. There’s not really a concept of «uninstalling» the old version, since the old version can live harmlessly on your server in a different directory. You just move it out of the way, and if the upgraded version doesn’t start up properly, will often still be able to start the old version back up, if needed. Eventually, you can remove the old directory, with caution, to avoid removing the wrong things.
You’ll need to read the documentation.
http://dev.mysql.com/doc/refman/5.1/en/upgrading.html
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
After you do the upgrade, don’t forget to run the mysql_upgrade utility. This utility does not upgrade the version of your server; instead, it upgrades your data files to be fully compatible with the new version you’ve just installed. You run this after you do the upgrade, and after you restart the MySQL server daemon.
http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html
Depending on how well the server was maintained in the past, if it was upgraded in the past, then it’s possible that this was not done when it was upgraded previously. If so, it would actually be a good idea to run it before doing the upgrade, so that all of your structures are all consistent. Two important points, here: The version of the mysql_upgrade utility that you run needs to be the version bundled with the currently running version of the server, and you need to make your backup before you run this. If you have some serious latent structural corruption that can’t be automatically fixed, this can be theoretically be uncovered by mysql_upgrade and the server may shut down to prevent the detected corruption from causing even more serious problems. This is because mysql_upgrade uses CHECK TABLE as part of its work, and:

If CHECK TABLE finds a problem for an InnoDB table, the server may shut down to prevent error propagation. Details of the error will be written to the error log.
— http://dev.mysql.com/doc/refman/5.1/en/check-table.html

This is not a likely scenario, but it’s a documented possibility of which you need to be aware.
As a general rule, the slave server should be upgraded before the master, not after. This is somewhat less critical when remaining within 5.1 (if you were upgrading to 5.5, you absolutely need to upgrade the slaves first) but upgrading the master last is most definitely the most correct approach.
Upgrading the slave first will also give you a better idea of what to expect, and if the slave’s data is consistent with the master, you can always promote it to master if your upgrade of the actual master fails.