MySQLdb._exceptions.OperationalError: (2012, 'Error in server handshake')

Help Connectors and Pipelines
1

Hi,
Unable to connect to memsql via python

my code:
from memsql.common import database

db = database.Connection(host=“127.0.0.1”, port = 3306 ,user=“root”, password=“root”)

error:
MySQLdb._exceptions.OperationalError: (2012, ‘Error in server handshake’)

memsql version:
memsql-client Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper

mysql version:
mysql Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))

2

MySql changed the default authentication protocol in MySQL 8, so I would suggest using the older (pre-8.0) client if possible. If you want to use the MySQL 8 client then you need to pass along default-auth=mysql_native_password in some fashion (which switches it to use the older auth method by default).

The same issue exists using the MySQL 8 client and connecting to an older version of MySQL (see https://bugs.mysql.com/bug.php?id=90994). i.e., MySQL also broke themselves here as well.

3

Thanks adam for useful info.
But I tried a different solution.

First install mysql properly on port 3306
and when creating memsql cluster start from port 3307.

i.e.
host:3307 (master)
host:3308 (child aggregator)
host:3308 (leaf)

4

I stumbled across this post from googling the same error but within SQL Workbench v8.0.22.

The solution for SQL WorkBench, which uses the Connector/C++, is to set the following option/value pair under connection > advanced > Others:
defaultAuth=mysql_native_password

2020-12-10_15-09-07
2020-12-10_15-09-07886×593 22.7 KB

Note: There is no dash in between “default” and “Auth” for the Connector/C++ per this page

defaultAuth

The name of the authentication plugin to use. This option corresponds to the MYSQL_DEFAULT_AUTH option for the mysql_options() C API function. The value is a string. This option was added in Connector/C++ 1.1.5.

1 Like
5

For anyone wanting to use the mysql CLI, the parameter you are looking for to do this is --loose-default-auth=mysql_native_password.

To for example directly create a database mysql with the MySQL 8 CLI from the command line, this command can be used:

mysql -h 127.0.0.1 -P 3306 -u root -pprisma -e "CREATE DATABASE mysql;" --loose-default-auth=mysql_native_password