Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mysql 8 new authentication method caching_sha2_password #454

Closed
chilio opened this issue Jul 18, 2018 · 10 comments

Comments

Projects
None yet
6 participants
@chilio
Copy link

commented Jul 18, 2018

This new auth method brings breaking changes to a lot of frameworks/workflows.
I see a lot of emerging questions regarding this...
Maybe it would be good idea to have 8 version images with old auth method also, for backwards compatibility?

@wglambert

This comment has been minimized.

Copy link

commented Jul 19, 2018

I don't believe this is a viable solution -- to maintain a non-default feature that is rather simple to work around if need be: connecting with mysql --default-auth=mysql_native_password -p. Users should be familiar with the product that they're using as well, so changes such as this isn't our realm to interfere.

This is upstream's explanation as for why it's the preferred authentication plugin https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

@chilio

This comment has been minimized.

Copy link
Author

commented Jul 19, 2018

@wglambert hmmm yes and no.
Although it is pretty simple to turn backwards compatibility, there are tons of repositories using old auth, which will stuck until upgraded (and some of them taking pretty long way to make it work).
And I believe hashing algo is a pretty default feature.
And to security measures which I treat really seriously, - does it mean all mysql 5.7 and former installations are vulnerable (I believe not, they mainly exist in private networks, so still protected)?
That's why I suggested having backwards compatible images...
BTW. And believe me there are cases where workaround is not even found so far....

@tianon

This comment has been minimized.

Copy link
Member

commented Jul 20, 2018

In this case, changing the default authentication method was an intentional choice by upstream, which is their prerogative. In this case, they even decided to only do so when changing from MySQL 5 to MySQL 8, which is a major version bump (and I'd be very surprised if this is the only breakage introduced in the change), and that was very kind of them.

As packagers of their solution, I do not believe it is appropriate for us to change this new default simply for the sake of compatibility with the older version, especially given that folks can still continue to use mysql:5.7 and mysql:5.6 (and even mysql:5.5), and that there is a trivial workaround by adding an additional command-line flag (or other means of adding extra mysqld configuration) to the container.

Any folks using mysql:latest and expecting it to continue to be compatible with their application are going to have a bad time -- even just mysql:5 would be better to avoid issues like this very one.

See #409 and #419 for additional information/discussion.

@tianon tianon closed this Jul 20, 2018

@chilio

This comment has been minimized.

Copy link
Author

commented Jul 23, 2018

In this case could you please suggest simplest set of commands I should issue on clean running mysql 8 container to be able to use default-auth=mysql_native_password but on the server, not client ?

@tianon

This comment has been minimized.

Copy link
Member

commented Jul 23, 2018

@chilio simply add --default-authentication-plugin=mysql_native_password to your mysql:8 container invocation (either via the command-line or in the command: field of your relevant YAML file)

@chilio

This comment has been minimized.

Copy link
Author

commented Jul 23, 2018

@tianon thank you, just a 5 mins ago figured that out.
And I can confirm it works... :)

@EnziinSystem

This comment has been minimized.

Copy link

commented Sep 28, 2018

version: '3'
services:
  author_db:
    image: mysql:8.0.12
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    ports:
      - "3306:3306"

It not working.

@wglambert

This comment has been minimized.

Copy link

commented Sep 28, 2018

You didn't give a password

version: '3'
services:
  author_db:
    image: mysql:8.0.12
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: pass
    restart: always
    ports:
      - "3306:3306"
$ docker-compose up -d
Creating network "mysql-454_default" with the default driver
Pulling author_db (mysql:8.0.12)...
8.0.12: Pulling from library/mysql
Digest: sha256:038f5f6ea8c8f63cfce1bce9c057ab3691cad867e18da8ad4ba6c90874d0537a
Status: Downloaded newer image for mysql:8.0.12
Creating mysql-454_author_db_1 ... done
$ docker-compose logs --tail 3
Attaching to mysql-454_author_db_1
author_db_1  | 2018-09-28T17:27:18.867303Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
author_db_1  | 2018-09-28T17:27:18.867325Z 0 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
author_db_1  | 2018-09-28T17:27:18.870988Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.12'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
$ docker exec -it mysql-454_author_db_1 mysql -uroot -ppass                                                               
mysql: [Warning] Using a password on the command line interface can be insecure.                                                                              
Welcome to the MySQL monitor.  Commands end with ; or \g.                                                                                                     
Your MySQL connection id is 10                                                                                                                                
Server version: 8.0.12 MySQL Community Server - GPL                                                                                                           
                                                                                                                                                              
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.                                                                                  
                                                                                                                                                              
Oracle is a registered trademark of Oracle Corporation and/or its                                                                                             
affiliates. Other names may be trademarks of their respective                                                                                                 
owners.                                                                                                                                                       
                                                                                                                                                              
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                
                                                                                                                                                              
mysql> 
@djanshuman

This comment has been minimized.

Copy link

commented Oct 20, 2018

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 👍

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

screen shot 2018-10-21 at 12 17 17 am

screen shot 2018-10-21 at 12 17 59 am
screen shot 2018-10-21 at 12 18 47 am
screen shot 2018-10-21 at 12 18 58 am
screen shot 2018-10-21 at 12 19 09 am

wa-pis pushed a commit to wa-pis/testdriven-app that referenced this issue Jan 10, 2019

@GreatFireWall

This comment was marked as disruptive content.

Copy link

commented Mar 26, 2019

Fucking upgrade, Waste my life

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.