Why is it possible to connect to MySQL 8 via CLI but not via an application?

user_547262 0
Viewed 68

I somehow can not connect with my python application to a new MySQL server on localhost. The pw seems to be OK as I can login, but the application can not.

Grants:

mysql> show grants for test@localhost;
+-------------------------------------------------------------------------------+
| Grants for test@localhost                                                  |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost`                                   |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `Project`.* TO `test`@`localhost` |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Logfile:

2021-02-11T10:49:13.897580Z    10 Connect   crawler@localhost on Project using SSL/TLS
2021-02-11T10:49:13.897736Z    10 Connect   Access denied for user 'test'@'localhost' (using password: YES)

BUT, I can connect from CLI:

$ mysql -u test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL

Logfile:

2021-02-11T10:51:48.590555Z    11 Connect   test@localhost on  using Socket
2021-02-11T10:51:48.591527Z    11 Query select @@version_comment limit 1

The password contains this string: 42$7&Z and is 16 char long.

I have set: default_authentication_plugin= mysql_native_password now, but still no change.

Connection from Python 3.8 (works on other servers):

def create_connection(self):
    settings = get_project_settings()

    self.conn = mysql.connector.connect(
        host=settings.get('MYSQL_SERVER'),
        user=settings.get('MYSQL_USER'),
        passwd=settings.get('MYSQL_PW'),
        database=settings.get('MYSQL_DB'),
        charset='utf8'
    )
    self.conn._time_zone = '+00:00'
    self.conn.autocommit = True
    self.curr = self.conn.cursor()
    self.curb = self.conn.cursor(buffered=True)

Is there something I have overlooked?

mysql
1 Answers

Try deleting the MySQL 'test'@'localhost' user and creating it again (instructions below). Then grant privileges and do not use get_project_settings(). Just enter the str values.

To recreate the user run these commands:

DROP USER 'test'@'localhost';
CREATE USER 'test'@'localhost' IDENTIFIED BY 'password'

This is the modified code with the str values (if that works check your configuration and try again with your code):

def create_connection(self):
    self.conn = mysql.connector.connect(
        host='localhost',
        user='test',
        password='password,
        database='database',
        charset='utf8'
    )

    self.conn._time_zone = '+00:00'
    self.conn.autocommit = True
    self.curr = self.conn.cursor()
    self.curb = self.conn.cursor(buffered=True)
Admin1
Related