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

原创 水平有限，只为抛砖，有误请指出

这里主要找一下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_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.
  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
作者微信：

               

时间： 2024-09-27 17:04:33

从源码解析ERROR 1129 (HY000)(2)以及unauthenticated user的相关文章

EventBus用法及源码解析

EventBus用法及源码解析 目录介绍 1.EventBus简介 1.1 EventBus的三要素 1.2 EventBus的四种ThreadMode(线程模型) 1.3 EventBus怎么调用 2.EventBus使用 2.1 最简单的使用 3.EventBus注册源码解析 3.1 EventBus.getDefault()获取对象 3.2 register(this)注册源码解析 3.2.1 首先看register(this)源码 3.2.2 接下来看findSubscriberMeth

EventBus源码解析

前面一篇文章讲解了EventBus的使用,但是作为开发人员,不能只停留在仅仅会用的层面上,我们还需要弄清楚它的内部实现原理.所以本篇博文将分析EventBus的源码,看看究竟它是如何实现"发布/订阅"功能的. 相关文章EventBus使用详解EventBus源码解析 事件注册 根据前一讲EventBus使用详解我们已经知道EventBus使用首先是需要注册的,注册事件的代码如下: EventBus.getDefault().register(this); EventBus对外提供了一个

Redux系列x：源码解析

写在前面 redux的源码很简洁,除了applyMiddleware比较绕难以理解外,大部分还是 这里假设读者对redux有一定了解,就不科普redux的概念和API啥的啦,这部分建议直接看官方文档. 此外,源码解析的中文批注版已上传至github,可点击查看.本文相关示例代码,可点击查看. 源码解析概览 将redux下载下来,然后看下他的目录结构. npm install redux 这里我们需要关心的主要是src目录,源码解析需要关心的文件都在这里面了 index.js:redux主文件,主

Redux入坑进阶之源码解析

预热 redux 函数内部包含了大量柯里化函数以及代码组合思想 柯里化函数(curry) 通俗的来讲,可以用一句话概括柯里化函数:返回函数的函数 // example  const funcA = (a) => {    return const funcB = (b) => {      return a + b    }  };   上述的funcA函数接收一个参数,并返回同样接收一个参数的funcB函数. 柯里化函数有什么好处呢? 避免了给一个函数传入大量的参数--我们可以通过柯里化来构

Android图片加载利器之Picasso源码解析

看到了这里,相信大家对Picasso的使用已经比较熟悉了,本篇博客中将从基本的用法着手,逐步的深入了解其设计原理. Picasso的代码量在众多的开源框架中算得上非常少的一个了,一共只有35个class文件,但是麻雀虽小,五脏俱全.好了下面跟随我的脚步,出发了. 基本用法 Picasso.with(this).load(imageUrl).into(imageView); with(this)方法 public static Picasso with(Context context) { if

Java 集合系列12之 TreeMap详细介绍(源码解析)和使用示例

概要 这一章,我们对TreeMap进行学习.我们先对TreeMap有个整体认识,然后再学习它的源码,最后再通过实例来学会使用TreeMap.内容包括:第1部分 TreeMap介绍第2部分 TreeMap数据结构第3部分 TreeMap源码解析(基于JDK1.6.0_45)第4部分 TreeMap遍历方式第5部分 TreeMap示例 转载请注明出处:http://www.cnblogs.com/skywang12345/admin/EditPosts.aspx?postid=3310928   第

Step.js 使用教程（附源码解析）

Step.js(https://github.com/creationix/step)是控制流程工具(大小仅 150 行代码),解决回调嵌套层次过多等问题.适用于读文件.查询数据库等回调函数相互依赖,或者分别获取内容最后组合数据返回等应用情景.异步执行简单地可以分为"串行执行"和"并行"执行,下面我们分别去看看. 串行执行 这个库只有一个方法 Step(fns...).Step 方法其参数 fns 允许是多个函数,这些函数被依次执行.Step 利用 this 对象指

Java集合学习（十七） TreeSet详细介绍(源码解析)和使用示例

这一章,我们对TreeSet进行学习. 我们先对TreeSet有个整体认识,然后再学习它的源码,最后再通过实例来学会使用TreeSet. 第1部分 TreeSet介绍 TreeSet简介 TreeSet 是一个有序的集合,它的作用是提供有序的Set集合.它继承于AbstractSet抽象类,实现了NavigableSet<E>, Cloneable, java.io.Serializable接口. TreeSet 继承于AbstractSet,所以它是一个Set集合,具有Set的属性和方法.

Java集合学习（十六） HashSet详细介绍(源码解析)和使用示例

这一章,我们对HashSet进行学习. 我们先对HashSet有个整体认识,然后再学习它的源码,最后再通过实例来学会使用HashSet. 第1部分 HashSet介绍 HashSet 简介 HashSet 是一个没有重复元素的集合. 它是由HashMap实现的,不保证元素的顺序,而且HashSet允许使用 null 元素. HashSet是非同步的.如果多个线程同时访问一个哈希 set,而其中至少一个线程修改了该 set,那么它必须 保持外部同步.这通常是通过对自然封装该 set 的对象执行同步