從原始碼解析ERROR 1129 (HY000)(2)以及unauthenticated user

gaopengtttt發表於2017-06-01
原始碼Error
原創 水平有限，只為拋磚，有誤請指出


這裡主要找一下m_connect是什麼，以及怎麼增加的，因為前文我沒有找到，再次用斷點的方式
描述一下unauthenticated user

一、m_connect是什麼怎麼增加的
m_connect實際上就是m_handshake下面是

點選(此處)摺疊或開啟

  1. Hostname.h
  2.   void sum_connect_errors()
  3.   {
  4.     /* Current (historical) behavior: */
  5.     m_connect= m_handshake;
  6.   }

sql_authentication.cc:
acl_authenticate函式

點選(此處)摺疊或開啟

  1. res= do_auth_once(thd, auth_plugin_name, &mpvio);

  3. ...
  4.   
  5.    case CR_AUTH_PLUGIN_ERROR:
  6.       errors.m_auth_plugin= 1;
  7.       break;
  8.     case CR_AUTH_HANDSHAKE:
  9.       errors.m_handshake= 1;//這裡m_handshake=1也就是m_connect=1，這裡會在後面進行聚合
  10.       break;
  11.     case CR_AUTH_USER_CREDENTIALS:
  12.       errors.m_authentication= 1;
  13.       break;
  14.     case CR_ERROR:
聚合函式Hostname.cc

點選(此處)摺疊或開啟

  1. void Host_errors::aggregate(const Host_errors *errors)//接受一個Host_errors型別指標，和當前this->* 進行相加
  2. {
  3.   m_connect+= errors->m_connect; //指標指向的Host_errors Host_errors 和this->m_connect相加 相加後存入 this->m_connect
  4.   m_host_blocked+= errors->m_host_blocked;
  5.   m_nameinfo_transient+= errors->m_nameinfo_transient;
  6.   m_nameinfo_permanent+= errors->m_nameinfo_permanent;
  7.   m_format+= errors->m_format;
  8.   m_addrinfo_transient+= errors->m_addrinfo_transient;
  9.   m_addrinfo_permanent+= errors->m_addrinfo_permanent;
  10.   m_FCrDNS+= errors->m_FCrDNS;
  11.   m_host_acl+= errors->m_host_acl;
  12.   m_no_auth_plugin+= errors->m_no_auth_plugin;
  13.   m_auth_plugin+= errors->m_auth_plugin;
  14.   m_handshake+= errors->m_handshake;
  15.   m_proxy_user+= errors->m_proxy_user;
  16.   m_proxy_user_acl+= errors->m_proxy_user_acl;
  17.   m_authentication+= errors->m_authentication;
  18.   m_ssl+= errors->m_ssl;
  19.   m_max_user_connection+= errors->m_max_user_connection;
  20.   m_max_user_connection_per_hour+= errors->m_max_user_connection_per_hour;
  21.   m_default_database+= errors->m_default_database;
  22.   m_init_connect+= errors->m_init_connect;
  23.   m_local+= errors->m_local;




sql_authentication.cc:1900
do_auth_once函式呼叫

點選(此處)摺疊或開啟

  1. sql_authentication.cc:2672
  2. native_password_authenticate函式
  3.   if (mpvio->write_packet(mpvio, (uchar*) mpvio->scramble, SCRAMBLE_LENGTH + 1))
  4.     DBUG_RETURN(CR_AUTH_HANDSHAKE);//這裡看到了錯誤
  5.     ......
  6.   if ((pkt_len= mpvio->read_packet(mpvio, &pkt)) < 0)
  7.     DBUG_RETURN(CR_AUTH_HANDSHAKE); //這裡看到了錯誤
  8.   DBUG_PRINT("info", ("reply read : pkt_len=%d", pkt_len));

下面是錯誤的宏定義


點選(此處)摺疊或開啟

  1. #define CR_AUTH_PLUGIN_ERROR 3
  2. /**
  3.   Authentication failed, client server handshake.
  4.   An error occurred during the client server handshake.
  5.   These errors are reported in table performance_schema.host_cache,
  6.   column COUNT_HANDSHAKE_ERRORS.
  7. */
  8. #define CR_AUTH_HANDSHAKE 2
  9. /**
  10.   Authentication failed, user credentials.
  11.   For example, wrong passwords.
  12.   These errors are reported in table performance_schema.host_cache,
  13.   column COUNT_AUTHENTICATION_ERRORS.
  14. */
  15. #define CR_AUTH_USER_CREDENTIALS 1
  16. /**
  17.   Authentication failed. Additionally, all other CR_xxx values
  18.   (libmysql error code) can be used too.

  20.   The client plugin may set the error code and the error message directly
  21.   in the MYSQL structure and return CR_ERROR. If a CR_xxx specific error
  22.   code was returned, an error message in the MYSQL structure will be
  23.   overwritten. If CR_ERROR is returned without setting the error in MYSQL,
  24.   CR_UNKNOWN_ERROR will be user.
  25. */
  26. #define CR_ERROR 0
  27. /**
  28.   Authentication (client part) was successful. It does not mean that the
  29.   authentication as a whole was successful, usually it only means
  30.   that the client was able to send the user name and the password to the
  31.   server. If CR_OK is returned, the libmysql reads the next packet expecting
  32.   it to be one of OK, ERROR, or CHANGE_PLUGIN packets.
  33. */
  34. #define CR_OK -1
  35. /**
  36.   Authentication was successful.
  37.   It means that the client has done its part successfully and also that
  38.   a plugin has read the last packet (one of OK, ERROR, CHANGE_PLUGIN).
  39.   In this case, libmysql will not read a packet from the server,
  40.   but it will use the data at mysql->net.read_pos.

  42.   A plugin may return this value if the number of roundtrips in the
  43.   authentication protocol is not known in advance, and the client plugin
  44.   needs to read one packet more to determine if the authentication is finished
  45.   or not.
  46. */
  47. #define CR_OK_HANDSHAKE_COMPLETE -2

也就是說每次由於mpvio->write_packet函式問題導致的握手失敗都會觸發他+1，其實這很可能是網路
問題，關於mpvio->write_packet的返回值感興趣的可以繼續跟中下去
下面是官方關於錯誤的解釋
If the following error occurs, it means that mysqldhas received many connection requests from the given
host that were interrupted in the middle:
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
The value of the max_connect_errorssystem variable determines how many successive
interrupted connection requests are permitted. (See Section 6.1.4, “Server System Variables”.) After
max_connect_errorsfailed requests without a successful connection, mysqldassumes that something
is wrong (for example, that someone is trying to break in), and blocks the host from further connections
until you issue a FLUSH HOSTSstatement or execute a mysqladmin flush-hostscommand.
By default, mysqldblocks a host after 100 connection errors. You can adjust the value by setting
max_connect_errorsat server startup:
shell> mysqld_safe --max_connect_errors=10000 &
The value can also be set at runtime:
mysql> SET GLOBAL max_connect_errors=10000;
If you get the Host 'host_name' is blockederror message for a given host, you should first verify
that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it
does you no good to increase the value of the max_connect_errorsvariable.

二、  unauthenticated user為什麼和反解析有關

點選(此處)摺疊或開啟

  1. 斷點：
  2. (gdb) info b
  3. Num Type Disp Enb Address What
  4. 1 breakpoint keep y 0x0000000000ebd3b3 in main(int, char**) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25
  5.         breakpoint already hit 1 time
  6. 2 breakpoint keep y 0x0000000000ee48ac in do_auth_once(THD*, LEX_CSTRING const&, MPVIO_EXT*)
  7.                                                at /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authentication.cc:1877
  8.         breakpoint already hit 2 times
  9. 3 breakpoint keep y 0x0000000000f7aeb7 in ip_to_hostname(sockaddr_storage*, char const*, char**, uint*)
  10.                                                at /root/mysql5.7.14/percona-server-5.7.14-7/sql/hostname.cc:412
  11.         breakpoint already hit 1 time
  12. 4 breakpoint keep y 0x0000000000ee6f8b in native_password_authenticate(MYSQL_PLUGIN_VIO*, MYSQL_SERVER_AUTH_INFO*)
  13.                                                at /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authentication.cc:2672
  14.         breakpoint already hit 1 time


點選(此處)摺疊或開啟

  1. ip_to_hostname棧幀：
  2. (gdb) bt
  3. #0 ip_to_hostname (ip_storage=0x7fffe80102e8, ip_string=0x7fffe800d020 "192.168.190.60", hostname=0x7fffec16dc38, connect_errors=0x7fffec16dc5c)
  4.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/hostname.cc:412
  5. #1 0x000000000154b73c in check_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1166
  6. #2 0x000000000154be1c in login_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1292
  7. #3 0x000000000154c67f in thd_prepare_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1447
  8. #4 0x00000000016e1d22 in handle_connection (arg=0x3d6d120) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:306
  9. #5 0x0000000001d72124 in pfs_spawn_thread (arg=0x3f25420) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188
  10. #6 0x0000003ca62079d1 in start_thread () from /lib64/libpthread.so.0
  11. #7 0x0000003ca5ee8b6d in clone () from /lib64/libc.so.6


點選(此處)摺疊或開啟

  1. acl_authenticate棧幀：
  2. (gdb) bt
  3. #0 native_password_authenticate (vio=0x7fffec16d140, info=0x7fffec16d158) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authentication.cc:2672
  4. #1 0x0000000000ee499f in do_auth_once (thd=0x7fffe8007e20, auth_plugin_name=..., mpvio=0x7fffec16d140)
  5.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authentication.cc:1900
  6. #2 0x0000000000ee54f0 in acl_authenticate (thd=0x7fffe8007e20, command=COM_CONNECT, extra_port_connection=false)
  7.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authentication.cc:2170
  8. #3 0x000000000154bca2 in check_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1243
  9. #4 0x000000000154be1c in login_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1292
  10. #5 0x000000000154c67f in thd_prepare_connection (thd=0x7fffe8007e20, extra_port_connection=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_connect.cc:1447
  11. #6 0x00000000016e1d22 in handle_connection (arg=0x3d6d120) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:306
  12. #7 0x0000000001d72124 in pfs_spawn_thread (arg=0x3f25420) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188
  13. #8 0x0000003ca62079d1 in start_thread () from /lib64/libpthread.so.0
  14. #9 0x0000003ca5ee8b6d in clone () from /lib64/libc.so.6

check_connection會呼叫ip_to_hostname和acl_authenticate進行解析和密碼認證過程，但是
ip_to_hostname出現在sql_connect.cc:1166而acl_authenticate出現在sql_connect.cc:1243
可以看到ip_to_hostname確實在acl_authenticate函式前，也就是反解析在密碼認證前，也是在MYSQL協議握手成功前。
這也說明什麼了為什麼解析慢會導致unauthenticated user的使用者。
mysql> show processlist;
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User                 | Host                 | db   | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
|  5 | unauthenticated user | 192.168.190.60:12770 | NULL | Connect |   35 | login    | NULL             |         0 |             0 |
|  6 | root                 | localhost            | NULL | Query   |    0 | starting | show processlist |         0 |             0 |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+

 為了驗證我在原始碼check_connection處增加了等待10秒並且打日誌的來驗證不然不好觀察這個問題，確實如上所訴

點選(此處)摺疊或開啟

  1. sql_print_information("Before acl_authenticate sleep(10) check unauthenticated user");
  2.   sleep(10);
  3.   
  4.   auth_rc= acl_authenticate(thd, COM_CONNECT, extra_port_connection);
  5.   
  6.   sql_print_information("After acl_authenticate sleep(10) check authenticated user");
  7.   sleep(10);
mysql> show processlist;
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User                 | Host                 | db   | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
|  4 | root                 | localhost            | NULL | Query   |    0 | starting | show processlist |         0 |             0 |
|  6 | unauthenticated user | 192.168.190.60:61688 | NULL | Connect |    8 | login    | NULL             |         0 |             0 |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+---------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User    | Host                 | db   | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+----+---------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
|  4 | root    | localhost            | NULL | Query   |    0 | starting | show processlist |         0 |             0 |
|  6 | testuuu | 192.168.190.60:61688 | NULL | Connect |   12 | login    | NULL             |         0 |             0 |
+----+---------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
2 rows in set (0.00 sec)

日誌輸出：
2017-05-31T22:34:28.605325Z 10 [Note] Before acl_authenticate sleep(10) check unauthenticated user
2017-05-31T22:34:38.606414Z 10 [Note] After acl_authenticate sleep(10) check authenticated user
作者微信：

               

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2140177/，如需轉載，請註明出處，否則將追究法律責任。

相關文章