Django/MySQL-python - Connection refused using old (pre-4.1.1) authentication protocol (client option "secure_auth" enabled)
sleepy
So many people have experienced this problem, but almost all the answers are useless.
Traceback (most recent call last):
File "/venv/local/lib/python2.7/site-packages/django/core/management/commands/runserver.py", line 91, in inner_run
self.validate(display_num_errors=True)
File "/venv/local/lib/python2.7/site-packages/django/core/management/base.py", line 266, in validate
num_errors = get_validation_errors(s, app)
File "/venv/local/lib/python2.7/site-packages/django/core/management/validation.py", line 103, in get_validation_errors
connection.validation.validate_field(e, opts, f)
File "/venv/local/lib/python2.7/site-packages/django/db/
backends/mysql/validation.py", line 14, in validate_field
db_version = self.connection.get_server_version()
File "/venv/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 415, in get_server_version
self.cursor().close()
File "/venv/local/lib/python2.7/site-packages/django/db/backends/__init__.py", line 306, in cursor
cursor = self.make_debug_cursor(self._cursor())
File "/venv/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 387, in _cursor
self.connection = Database.connect(**kwargs)
File "/venv/local/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
return Connection(*args, **kwargs)
File "/venv/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2049, "Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)")
sleepy
After a lot of trial/error, the problem was still narrowed down to a password using the old encryption.
mysql> select User, Password from mysql.user;
| user1 | *113D91F3A36D29C287C457A20D602FA384B8569F |
| user2 | 5d78f2535e56dfe0 |
A tool like Navicat doesn't automatically use newer style passwords, and you have to explicitly tell it not to use the old style (in Navicat it's called Advanced/Use OLD_PASSWORD tncryption) - this is where we come across.
If you are using the mysql shell, you can use;
mysql> update mysql.user set password=PASSWORD("NEWPASSWORD") where User='testuser';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select User, Password from mysql.user WHERE user = 'testuser';
| testuser | *B845F78DCA29B8AE945AB9CFFAC24A9D17EB5063 |
If you want to find all users affected by this issue;
mysql> SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) = 16;
Some versions of Django don't allow you to use secure-authinside options , but for those that do, you can use these options.
'OPTIONS': {
'secure-auth': False
}
If not supported, will result in:
TypeError: 'secure-auth' is an invalid keyword argument for this function
Hope this provides a quick answer for anyone having trouble in the future.