Mysql error 2049: exception handling for a mysql connection

Problem Description

I recently developed a script on Mac using the MySQLdb module. However, an exception message is thrown, as shown in the following stack information. This exception information has been encountered before. Using mysql client to connect to the database has the same problem. Previously, I added-skip-secure-auth to bypass this issue. However, if mysqldb is used, you can add this parameter unless otherwise specified.

Traceback (most recent call last ):
File "/Users/metaboy/script/TaskExecutor/tests. py", line 20, in test_something
Task = TaskExecutor. get_data (get_one_waiting_task)
File "/Users/metaboy/script/TaskExecutor. py", line 72, in get_data
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'hena', charset = 'utf8 ')
File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/_ init _. py", line 81, in Connect
Return Connection (* args, ** kwargs)
File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/connections. py", line 187, in _ init __
Super (Connection, self). _ init _ (* args, ** kwargs2)
OperationalError: (2049, "Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure _ auth' enabled )")

Problem analysis

Since it cannot be bypassed, you can only find a solution to this problem. The root cause of this problem was that the password protocol of the server and the client was inconsistent.

The mysql server version is 5.5, and the new user uses the old protocol. The encrypted password format is as follows:

Mysql> select User, Password, Host from mysql. user where user = 'root ';
+ ------ + ------------------ + ----------- +
| User | Password | Host |
+ ------ + ------------------ + ----------- +
| Root | 67457e226a1a15bd | % |
| Root | 67457e226a1a15bd | 127.0.0.1 |
| Root | 67457e226a1a15bd | localhost |
+ ------ + ------------------ + ----------- +
3 rows in set (0.02 sec)

The mysql client used on the Mac client is 5.6, and a new encrypted password is required. This directly leads to inconsistent password protocols between the client and the server. The simplest and most direct method is to upgrade the server's password protocol.

Before the upgrade, log in through-skip-secure-auth and enter the following command to get the corresponding prompt. We recommend that you upgrade the encryption protocol.

Mysql> show warnings \ G
* *************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'Pre-4.1 password hash' is deprecated and will be
Removed in a future release. Please use post-4.1 password hash instead
1 row in set (0.02 sec)

Solution

Assume that I only want to upgrade the password of the root account. The procedure is as follows:

Mysql> update mysql. user set password = PASSWORD ("root") where user = 'root ';
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Check the encrypted password format. It must start with "*" and contain 16 characters.

Mysql> select User, Password, Host from mysql. user where user = 'root ';
+ ------ + ------------------------------------------- + ----------- +
| User | Password | Host |
+ ------ + ------------------------------------------- + ----------- +
| Root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| Root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
| Root | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
+ ------ + ------------------------------------------- + ----------- +
3 rows in set (0.01 sec)

It takes effect after the Mysql service is restarted.