Unable to connect to MySQL 8

[danmrichards]

Issue description

MySQL 8 has now gone into general availability and I am seemingly unable to connect to a MySQL 8 instance as I have previously done with 5.x.

Lets say for example I have a MySQL instance running locally via Docker like so:

docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=dbname -d mysql:8

Then I can try to connect to this instance:

Example code

package main

import (
  "fmt"
  "database/sql"
  "os"
  _ "github.com/go-sql-driver/mysql"
)

func main() {
  db, err := sql.Open("mysql", "root:my-secret-pw@tcp(localhost:3306)/dbname")
  if err != nil {
    fmt.Printf("conn fail: %s\n", err)
    os.Exit(1)
  }

  if err = db.Ping(); err != nil {
    fmt.Printf("ping fail: %s\n", err)
    os.Exit(1)
  }

  fmt.Println("all good")
}

Of course I would expect this to work and I'd see the all good message (which is the case with a 5.7 instance for example). However I see the following:

Error log

ping fail: this authentication plugin is not supported
exit status 1

Configuration

Driver version (or git SHA):
3287d94

Go version: run go version in your console
go version go1.10 darwin/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
8.0.11

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
Mac OS 10.12.6

[PeterZaitsev]

I've been looking into it. It looks like to make it work you need to set legacy Authentication in MySQL 8 (which is less secure of course).

Installation scripts for Ubuntu 16.04 ship this file (if you select Legacy MySQL 8 authentication)

[mysqld]
default-authentication-plugin = mysql_native_password

Note: It looks like currently it is NOT enough to simply create user using native_password authentication - it will still fail with different error message

Cannot connect to MySQL: this user requires mysql native password authentication.

It has been suggested in other issue #625

to do this to make native password accounts to work:

Note that you will need to explicitly set "allowNativePasswords=True" in the DSN.

[rykr]

Hi
I'm the manager of connectors here at MySQL and we want to help you guys fix this. Not a great deal of Go experience here on the team. We can try to work on a pull request but we may need some help

[arthurnn]

@rykr @PeterZaitsev I am at PerconaLive conf this week, if you down here, we could work on this together. Lemme know

[datacharmer]

I was bitten by a similar issue while working on dbdeployer.

The workaround is to change authentication plugin for the user. However, the root user is created during a previous run of the server, where the default plugin was caching_sha2_password.

After changing the authentication plugin in the user, you need to set the password again. With ALTER USER you can do both things at once.

alter user root@'localhost' identified with mysql_native_password by 'my-secret-pw';

Your sample program will run successfully after the change.

The suggestion from @PeterZaitsev works for all users created after restarting the server with the new directive.

[rykr]

@arthurnn I wish I was there. I'm back at the office but would love to compare notes with you on this as I try to get up a pull request to fix this.

[johnemb]

Here is a link to an article that may help for anyone trying to implement a real fix for this issue:
https://insidemysql.com/preparing-your-community-connector-for-mysql-8-part-2-sha256/

[cajund]

I have verified @datacharmer's suggested workaround - the change is on a per-user basis. So if you want to maintain the overall config, disabling for one user appears to be an acceptable condition.

Thanks!

[julienschmidt]

#794, adding support for MySQL 8.0's default auth plugin was merged. Some feedback whether further problems occur would be great!

[DerfOh]

I had to do add the following to my stack.yml when using a mysql docker container then it worked. Posting here in hopes that it helps someone else: command: --default-authentication-plugin=mysql_native_password

[JasonZhang1021]

I changed the authentication plugin and set the DSN, but it is still not work, anyone can suggest more?

[djanshuman]

import mysql.connector

def connect():
conn = mysql.connector.connect(host='localhost',
database='mydb',
user='root_new',
password='root_new')
if conn.is_connected():
print('Connected to MySQL database')

if name == 'main':
connect()

Output : Connected to MySQL database

Follow the Screenshot 👍

1. Stop database server in preferences.
2. initialise DB with legacy authentication.
3. Open mysqlWorkBench and Create a new user with standard authentication.
4. Create a new schema(DB) in sqlWorkbench.
5. Execute python Code in Eclipse.