Geek Answers Handbook

Django / MySQL-python - disconnected connection using the old (pre-4.1.1) authentication protocol (the "secure_auth" client option is enabled)

So many people have experienced this problem on SO, but almost all of 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)")
+6
source share
3 answers

After much trial / error, the problem was reduced to passwords using the old encryption style.

 mysql> select User, Password from mysql.user; | user1 | *113D91F3A36D29C287C457A20D602FA384B8569F | | user2 | 5d78f2535e56dfe0 |

Tools like Navicat do not automatically use the new style passwords, and you must explicitly tell it not to use the old style (in Navicat, which is called Advanced / Use OLD_PASSWORD tncryption) - this is where we worked.

If you are using mysql shell you can just 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 the users affected by this issue,

 mysql> SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) = 16;

Some versions of Django do not allow secure-auth to be used inside parameters, but for those that you can use;

 'OPTIONS': { 'secure-auth': False }

If it is not supported, this will result in:

 TypeError: 'secure-auth' is an invalid keyword argument for this function

This, I hope, will serve as a quick answer for those who are experiencing problems in the future.

+8
source

For MySQL Workbench 6.08, in the section "Manage server connections", "Connection tab", "Advanced", you must check the box " Use the old authentication protocol . "

enter image description here

+25
source

I had the same problem trying to connect to the mysql command line client, and I could avoid the error with the --skip-secure-auth option.

For instance:

 mysql DBNAME -h HOST_NAME_OR_IP -u DB_USER --password="PASSWORD" --skip-secure-auth

The same parameter for other mysqlX commands, such as mysqldump .

+13
source

More articles:


All Articles