当前位置:Gxlcms > 数据库问题 > Jdbc Url 设置allowMultiQueries为true和false时底层处理机制研究

Jdbc Url 设置allowMultiQueries为true和false时底层处理机制研究

时间:2021-07-01 10:21:17 帮助过:28人阅读

执行时会报错:

  1. <code class="hljs sql">com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; <span class="hljs-keyword">check the <span class="hljs-keyword">manual that corresponds <span class="hljs-keyword">to your MySQL <span class="hljs-keyword">server <span class="hljs-keyword">version <span class="hljs-keyword">for the <span class="hljs-keyword">right syntax <span class="hljs-keyword">to <span class="hljs-keyword">use near <span class="hljs-string">‘select ‘world<span class="hljs-string">‘‘ <span class="hljs-keyword">at line <span class="hljs-number">1
  2. <span class="hljs-keyword">at sun.reflect.NativeConstructorAccessorImpl.newInstance0(<span class="hljs-keyword">Native Method)
  3. <span class="hljs-keyword">at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:<span class="hljs-number">57)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

若一个sql中通过分号分割(或包含)了多个独立sql的话,如:

  1. <code class="hljs sql"><span class="hljs-keyword">select <span class="hljs-string">‘hello‘;<span class="hljs-keyword">select <span class="hljs-string">‘world‘</span></span></span></span></code>

默认就会报上述错误,当若显式设置allowMultiQueries为true的话,就可以正常执行不会报错.如下所示:

  1. <code class="hljs javascript"><span class="hljs-built_in">String url = <span class="hljs-string">"jdbc:mysql://localhost:3306?allowMultiQueries=true";</span></span></code>

官方文档解释:

  1. <code class="hljs sql">allowMultiQueries
  2. Allow the <span class="hljs-keyword">use <span class="hljs-keyword">of <span class="hljs-string">‘;‘ <span class="hljs-keyword">to delimit multiple queries during one <span class="hljs-keyword">statement (<span class="hljs-literal">true/<span class="hljs-literal">false), <span class="hljs-keyword">defaults <span class="hljs-keyword">to <span class="hljs-string">‘false‘, <span class="hljs-keyword">and does <span class="hljs-keyword">not affect the addBatch() <span class="hljs-keyword">and executeBatch() methods, which instead <span class="hljs-keyword">rely <span class="hljs-keyword">on rewriteBatchStatements.
  3. <span class="hljs-keyword">Default: <span class="hljs-literal">false
  4. Since <span class="hljs-keyword">version: <span class="hljs-number">3.1<span class="hljs-number">.1</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

想要看看当该参数设置为true和false时,源码中到底哪里有不同.经过断点调试发现了在下面代码处会有不同的结果:

  1. <code class="hljs coffeescript">   <span class="hljs-regexp">//所属类和方法:void java.net.SocketOutputStream.socketWrite(byte[] b, int <span class="hljs-literal">off, int len) throws IOException
  2.    socketWrite0(fd, b, <span class="hljs-literal">off, len);
  3.    bytesWritten = len;</span></span></span></code>

当设置为true时,执行完socketWrite0方法后查询query log,会看到这样的输出:

  1. <code class="hljs sql">   23 Query
  2. <span class="hljs-keyword">select <span class="hljs-string">‘hello‘;
  3.    23 Query
  4. <span class="hljs-keyword">select <span class="hljs-string">‘world‘</span></span></span></span></code>

当设置为false时,执行完socketWrite0后.查询query log没有任何输出.

但奇怪的是它们的参数都一样,为什么一个有输出一个就没有呢?如下所示:

设置为true参数信息

技术分享图片

设置为false时的参数信息:

技术分享图片

补充: 即使当allowMultiQueries为false时,服务端也收到了查询请求,只不过没有在query log中输出而已.

如下抓包工具(Wireshark)所示:

技术分享图片

又经过进一步调试,发现如下几处代码可能比较关键,如下所示:

  1. <code class="hljs javascript"><span class="hljs-comment">/**
  2. *
  3. * 所属: void com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(String user, String 
  4. * password, String database, Buffer challenge) throws SQLException
  5. */
  6. <span class="hljs-comment">// We allow the user to configure whether
  7. <span class="hljs-comment">// or not they want to support multiple queries
  8. <span class="hljs-comment">// (by default, this is disabled).
  9. <span class="hljs-keyword">if (<span class="hljs-keyword">this.connection.getAllowMultiQueries()) { <span class="hljs-comment">//1701行
  10. <span class="hljs-keyword">this.clientParam |= CLIENT_MULTI_STATEMENTS;
  11. }
  12. last_sent = <span class="hljs-keyword">new Buffer(packLength);
  13. last_sent.writeLong(<span class="hljs-keyword">this.clientParam); <span class="hljs-comment">//1876行</span></span></span></span></span></span></span></span></span></span></span></code>

执行完上述语句后,对应的query log为:

  1. <code class="hljs css">150507 21<span class="hljs-selector-pseudo">:23<span class="hljs-selector-pseudo">:16
  2.    19 <span class="hljs-selector-tag">Connect
  3. <span class="hljs-selector-tag">root@<span class="hljs-keyword">localhost on</span></span></span></span></span></code>

似乎是在这一交互过程中通知了服务器端客户端允许一次查询多条语句.通过抓包工具(wireshark)可以看到在创建连接过程中更多的交互信息,如下所示:

技术分享图片

但在调试过程中又遇到了一个新问题,若在上述代码处加上了断点,就会出现如下异常:

  1. <code class="hljs css"><span class="hljs-selector-tag">Caused <span class="hljs-selector-tag">by: <span class="hljs-selector-tag">java<span class="hljs-selector-class">.io<span class="hljs-selector-class">.EOFException: <span class="hljs-selector-tag">Can <span class="hljs-selector-tag">not <span class="hljs-selector-tag">read <span class="hljs-selector-tag">response <span class="hljs-selector-tag">from <span class="hljs-selector-tag">server. <span class="hljs-selector-tag">Expected <span class="hljs-selector-tag">to <span class="hljs-selector-tag">read 4 <span class="hljs-selector-tag">bytes, <span class="hljs-selector-tag">read 0 <span class="hljs-selector-tag">bytes <span class="hljs-selector-tag">before <span class="hljs-selector-tag">connection <span class="hljs-selector-tag">was <span class="hljs-selector-tag">unexpectedly <span class="hljs-selector-tag">lost.
  2. <span class="hljs-selector-tag">at <span class="hljs-selector-tag">com<span class="hljs-selector-class">.mysql<span class="hljs-selector-class">.jdbc<span class="hljs-selector-class">.MysqlIO<span class="hljs-selector-class">.readFully(<span class="hljs-selector-tag">MysqlIO<span class="hljs-selector-class">.java<span class="hljs-selector-pseudo">:3161)
  3. <span class="hljs-selector-tag">at <span class="hljs-selector-tag">com<span class="hljs-selector-class">.mysql<span class="hljs-selector-class">.jdbc<span class="hljs-selector-class">.MysqlIO<span class="hljs-selector-class">.reuseAndReadPacket(<span class="hljs-selector-tag">MysqlIO<span class="hljs-selector-class">.java<span class="hljs-selector-pseudo">:3615)
  4. ... 43 <span class="hljs-selector-tag">more</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

尚未找到该问题原因(如在何处设置的超时时间).但是实验发现,在创建连接过程中不能有debug调试(即不能有暂停),否则就会有fin包(不知这是Mysql的协议还是TCP的协议?).如下所示:

技术分享图片

关于fin包的描述见wiki

技术分享图片

但在成功建立连接后进行断点调试没有问题.

补充: 通过telnet来模拟上述现象.

  1. <code class="hljs nginx"><span class="hljs-comment"># telnet 127.0.0.1 3306
  2. <span class="hljs-attribute">Trying <span class="hljs-number">127.0.0.1...
  3. Connected to <span class="hljs-number">127.0.0.1.
  4. Escape character is <span class="hljs-string">‘^]‘.
  5. J
  6. <span class="hljs-number">5.<span class="hljs-number">6.<span class="hljs-number">17 <<span class="hljs-number">4/-7j1S- ? 18fwG:-nh=66mysql_native_passwordConnection closed by foreign host.</span></span></span></span></span></span></span></span></span></code>

对应的抓包信息:

技术分享图片

因未能及时输入密码,导致服务端发出FIN包中断了连接.

或者也可以通过如下Java代码来模拟此现象:

  1. <code class="hljs coffeescript">Socket socket = <span class="hljs-keyword">new  Socket(<span class="hljs-string">"127.0.0.1",<span class="hljs-number">3306);
  2. System.<span class="hljs-keyword">in.read();</span></span></span></span></code>

此时看到的抓包信息如下所示:

技术分享图片

补充:

1. 如何查看query log:

  1. <code class="hljs shell"><span class="hljs-meta">mysql><span class="bash"> show variables like <span class="hljs-string">‘general_log%‘;
  2. +------------------+--------------------------------------------------------------------+
  3. | Variable_name    | Value                                                              |
  4. +------------------+--------------------------------------------------------------------+
  5. | general_log      | OFF                                                                 |
  6. | general_log_file | /opt/mysql/server-5.6/data/zhuguowei-Presario-CQ43-Notebook-PC.log |
  7. +------------------+--------------------------------------------------------------------+
  8. <span class="hljs-meta">mysql><span class="bash"> <span class="hljs-built_in">set global general_log = <span class="hljs-string">‘on‘ ;
  9. <span class="hljs-meta">#<span class="bash">开启另一终端
  10. tail -f /opt/mysql/server-5.6/data/zhuguowei-Presario-CQ43-Notebook-PC.log</span></span></span></span></span></span></span></span></span></code>

2. 关于使用抓包工具(Wireshark)

参考文档:

http://www.maketecheasier.com/using-wireshark-ubuntu/

http://floss.zoomquiet.io/data/20120511105248/index.html

注意:

本地调试的话Interface(网卡)选择any(或lo), 过滤条件如下所示:

技术分享图片

 

其他问题:

1. 为什么在mysql交互终端中执行的命令不能被Wireshark捕捉到?

需要显式指定-h参数 如

  1. <code class="hljs css"> <span class="hljs-selector-tag">mysql <span class="hljs-selector-tag">-u <span class="hljs-selector-tag">root <span class="hljs-selector-tag">-p <span class="hljs-selector-tag">-h 127<span class="hljs-selector-class">.0<span class="hljs-selector-class">.0<span class="hljs-selector-class">.1</span></span></span></span></span></span></span></span></code>

注意若为-h localhost的话,仍不能被Wireshark捕捉到,不知两者有什么区别?通过StackOverFlow中提问得到解答.

  1. <code class="hljs coffeescript">On Unix, MySQL programs treat the host name localhost specially, <span class="hljs-keyword">in a way that <span class="hljs-keyword">is likely different <span class="hljs-keyword">from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server <span class="hljs-keyword">by using a Unix socket file. This occurs even <span class="hljs-keyword">if a --port <span class="hljs-keyword">or -P option <span class="hljs-keyword">is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host <span class="hljs-keyword">or -h to specify a host name value <span class="hljs-keyword">of <span class="hljs-number">127.0<span class="hljs-number">.0<span class="hljs-number">.1, <span class="hljs-keyword">or the IP address <span class="hljs-keyword">or name <span class="hljs-keyword">of the local server. You can also specify the connection protocol explicitly, even <span class="hljs-keyword">for localhost, <span class="hljs-keyword">by using the --protocol=TCP option.</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

摘自: https://dev.mysql.com/doc/refman/5.0/en/connecting.html

2. 在mysql交互终端中是否默认设置了allowMultiQueries=true, 能有办法将其改为false吗?

确实默认设置了allowMultiQueries=true, 如下所示:

技术分享图片

上图是通过Wireshark抓取终端mysql登录时(mysql -u root -p -h 127.0.0.1)抓取的包.

似乎没有修改的办法.

3. 如何在telent 127.0.0.1 3306后输入密码? 或者如何设置使得不用输入密码,试过使用--skip-grant-tables 启动mysql服务,但不起作用.

Jdbc Url 设置allowMultiQueries为true和false时底层处理机制研究

标签:建立   ber   nbsp   like   时间   结果   manage   协议   port   

人气教程排行