CODEWITHSUNDEEP
mysqlpython-2.7
Ominus
Ominus

Reputation: 5721

Mysql python - randomly (every other attempt) gets access denied

I can't figure this out and i am not sure how to code for it.

def get_connection():
    cnx = MySQLdb.connect(**DB_CONFIG)
    print("Connected")
    cnx.close()
    print("Closed")


12:08 $ python test_mysql.py  && python test_mysql.py 
Connected
Closed
Traceback (most recent call last):
  File "test_mysql.py", line 4, in <module>
    get_connection()
  File "XX"/mysql/tbred_mysql.py", line 7, in get_connection
    cnx = MySQLdb.connect(**DB_CONFIG)
  File "XX/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "XX/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (1045, "Access denied for user 'XXXX'@'10.0.8.5' (using password: YES)")

I ran them right after each other because it was easier to demonstrate but you can wait 10 or 15 seconds and it will still happen. I works and then it doesn't.

When it fails like this nothing is written to the mysql error log. If i change the user to something that doesn't exist to force this error a record is written to the mysql error log.

EDIT: I can reproduce problem without python. If i try and remote connect via the mysql command line client in linux i get the same results. Also i have discovered its not random its every other connection regardless of time between attempts. 1st works, 2nd error, 3rd works, 4th errors again it doesn't matter the time between them. Also with these failures they are not recorded to the mysql error log like a normal access denied message is.

Losing my mind!

Upvotes: 2

Views: 451

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562368

Repeating this from my comment above.

Setting option skip_name_resolve fixes your issue.

By default, MySQL Server tries to do a reverse DNS lookup of the client IP address to make sure your user is logging in from an authorized hostname. This is a problem if your local DNS server is slow or intermittently flaky. It can slow down or even cause errors for MySQL connections, and it's not clear why.

Using skip_name_resolve tells the server to skip this validation. This should eliminate errors and slow performance due to DNS.

One implication of this is that you cannot use hostnames in GRANT statements. You must identify users' authorized client hosts by IP addresses or wildcards like %.

https://dev.mysql.com/doc/refman/5.7/en/host-cache.html says:

To disable DNS host name lookups, start the server with the --skip-name-resolve option. In this case, the server uses only IP addresses and not host names to match connecting hosts to rows in the MySQL grant tables. Only accounts specified in those tables using IP addresses can be used. (Be sure that an account exists that specifies an IP address or you may not be able to connect.)

Wildcards work too. You can GRANT ALL PRIVILEGES ON *.* TO 'username'@'192.168.%' for example.

Upvotes: 2

Ominus
Ominus

Reputation: 5721

Had to use the ip of the server instead of the hostname. Problem was caused by dns resolution issues on the server. The way that it failed still boggles my mind.

Upvotes: 1

Related Questions