从源码解析ERROR 1129 (HY000)(2)以及unauthenticated user

  1. 云栖社区>
  2. 博客>
  3. 正文

从源码解析ERROR 1129 (HY000)(2)以及unauthenticated user

重庆八怪 2017-06-01 16:33:46 浏览582
展开阅读全文
原创 水平有限,只为抛砖,有误请指出


这里主要找一下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);

  2. ...
  3.   
  4.    case CR_AUTH_PLUGIN_ERROR:
  5.       errors.m_auth_plugin= 1;
  6.       break;
  7.     case CR_AUTH_HANDSHAKE:
  8.       errors.m_handshake= 1;//这里m_handshake=1也就是m_connect=1,这里会在后面进行聚合
  9.       break;
  10.     case CR_AUTH_USER_CREDENTIALS:
  11.       errors.m_authentication= 1;
  12.       break;
  13.     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_errorsthis->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.

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

  40.   A plugin may return this value if the number of roundtrips in the
  41.   authentication protocol is not known in advance, and the client plugin
  42.   needs to read one packet more to determine if the authentication is finished
  43.   or not.
  44. */
  45. #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
作者微信:

               

网友评论

登录后评论
0/500
评论
重庆八怪
+ 关注