Druid.io SQL乱码问题
1、场景
1.1、依赖版本
- avatica-core 1.11.0
- druid 0.12.0
1.2、问题重现:
使用Avatica JDBC查询语句:SELECT score FROM student WHERE name='小明'
到Druid变成:SELECT score FROM student WHERE name='??'
。
2、解决过程
思路:检查请求发送前request body -> 检查收到请求后解析的文本
2.1、初步怀疑请求编码所致
初步怀疑请求的编码格式设置不正确。为了方便查看返回的结果是否是乱码,我们使用EXPLAIN PLAN FOR来调试。查看请求日志,可以知道avatica的内部使用了HttpClient实现的。
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> POST /druid/v2/sql/avatica/ HTTP/1.1
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> Content-Length: 241
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> Content-Type: application/octet-stream
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> Host: localhost:8082
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> Connection: Keep-Alive
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> User-Agent: Apache-HttpClient/4.5.3 (Java/1.8.0_131)
17:45:17.557 [main] DEBUG org.apache.http.headers - http-outgoing-0 >> Accept-Encoding: gzip,deflate
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "POST /druid/v2/sql/avatica/ HTTP/1.1[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "Content-Length: 241[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "Content-Type: application/octet-stream[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "Host: localhost:8082[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "Connection: Keep-Alive[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "User-Agent: Apache-HttpClient/4.5.3 (Java/1.8.0_131)[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "Accept-Encoding: gzip,deflate[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "[\r][\n]"
17:45:17.557 [main] DEBUG org.apache.http.wire - http-outgoing-0 >> "{"request":"prepareAndExecute","connectionId":"b4490330-c1c1-493a-b40d-a4303698cafa","statementId":1,"sql":"EXPLAIN PLAN FOR SELECT score FROM student WHERE name='[0xe6][0xb4][0x97][0xe5]'","maxRowsInFirstFrame":-1,"maxRowCount":-1}"
可以发现请求的sqlEXPLAIN PLAN FOR SELECT score FROM student WHERE name='小明'
发送时变成了EXPLAIN PLAN FOR SELECT score FROM student WHERE name='[0xe6][0xb4][0x97][0xe5]'
,这是编码乱了吗?其实不是的,我们可以通过指定avatica的HttpClient,来调试请求发送前的数据。
通过复制avatica-core-1.11.0.jar的AvaticaCommonsHttpClientImpl类,改变public byte[] send(byte[] request)
的实现,可以改变请求的方式。
同时jdbc调用的时候指定httpclient_impl,即可。
String url = "jdbc:avatica:remote:url=http://localhost:8082/druid/v2/sql/avatica/";
Properties connectionProperties = new Properties();
connectionProperties.setProperty("httpclient_impl", "com.test.MyAvaticaCommonsHttpClientImpl");
conn = DriverManager.getConnection(url, connectionProperties);
最后无论怎么改变send方法的实现,最后返回的结果始终带有??。
17:45:18.907 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "HTTP/1.1 200 OK[\r][\n]"
17:45:18.908 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "Date: Tue, 15 May 2018 09:45:30 GMT[\r][\n]"
17:45:18.908 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "Content-Type: application/json;charset=utf-8[\r][\n]"
17:45:18.908 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "Content-Length: 1641[\r][\n]"
17:45:18.908 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "Server: Jetty(9.3.19.v20170502)[\r][\n]"
17:45:18.908 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "[\r][\n]"
17:45:18.909 [main] DEBUG org.apache.http.wire - http-outgoing-0 << "{"response":"executeResults","missingStatement":false,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"master:8082"},"results":[{"response":"resultSet","connectionId":"b4490330-c1c1-493a-b40d-a4303698cafa","statementId":1,"ownStatement":false,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":true,"searchable":false,"currency":false,"nullable":0,"signed":true,"displaySize":-1,"label":"PLAN","columnName":"PLAN","schemaName":null,"precision":-1,"scale":-2147483648,"tableName":null,"catalogName":null,"type":{"type":"scalar","id":12,"name":"VARCHAR","rep":"STRING"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.String"}],"sql":"EXPLAIN PLAN FOR SELECT score FROM student WHERE name='??'","parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":"SELECT"},"firstFrame":{"offset":0,"done":true,"rows":[["DruidQueryRel(query=[{\"queryType\":\"scan\",\"dataSource\":{\"type\":\"table\",\"name\":\"student\"},\"intervals\":{\"type\":\"intervals\",\"intervals\":[\"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z\"]},\"virtualColumns\":[],\"resultFormat\":\"compactedList\",\"batchSize\":20480,\"filter\":{\"type\":\"selector\",\"dimension\":\"name\",\"value\":\"??\",\"extractionFn\":null},\"columns\":[\"score\"],\"legacy\":false,\"context\":{},\"descending\":false,\"granularity\":{\"type\":\"all\"}}], signature=[{name:STRING])\n"]]},"updateCount":-1,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"master:8082"}}]}[\n]"
17:45:18.910 [main] DEBUG org.apache.http.headers - http-outgoing-0 << HTTP/1.1 200 OK
17:45:18.910 [main] DEBUG org.apache.http.headers - http-outgoing-0 << Date: Tue, 15 May 2018 09:45:30 GMT
17:45:18.911 [main] DEBUG org.apache.http.headers - http-outgoing-0 << Content-Type: application/json;charset=utf-8
17:45:18.911 [main] DEBUG org.apache.http.headers - http-outgoing-0 << Content-Length: 1641
17:45:18.911 [main] DEBUG org.apache.http.headers - http-outgoing-0 << Server: Jetty(9.3.19.v20170502)
2.2、检查Druid收到的请求以及返回
既然检查发送前的请求没问题,那么接下来检查请求接收后的情况。
通过全局搜索Druid的源码,搜索/druid/v2/sql/avatica/,可以知道我们刚才的请求被DruidAvaticaHandler类接收并处理,最后实际处理的avatica-server,而这里用的是1.10.0版本,通过版本对比,没版本的问题。
通过检查avatica-server的AvaticaJsonHandler类的方法:
public void handle(String target, Request baseRequest, HttpServletRequest request, HttpServletResponse response)
看到不和谐的地方:
final String jsonRequest = new String(rawRequest.getBytes("ISO-8859-1"), "UTF-8");
,
UTF-8编码传来的请求居然用ISO-8859-1转换?原来问题就出现在这里。
3、解决方法
3.1、修改avatica-server源码
上github克隆avatica-server 1.10.0的代码,找到AvaticaJsonHandler类的方法:
public void handle(String target, Request baseRequest, HttpServletRequest request, HttpServletResponse response)
修改:
final String jsonRequest = new String(rawRequest.getBytes("ISO-8859-1"), "UTF-8");
=> final String jsonRequest = new String(rawRequest.getBytes("UTF-8"), "UTF-8");
3.2、重新编译avatica-server
运行clean install -Dmaven.test.skip=true -Dcheckstyle.skip=true
,重新生成avatica-server的jar
3.3、覆盖Druid的依赖
上Druid所在的服务器,进入lib,把avatica-server-1.10.0.jar备份并覆盖,重启服务。
编译好的jar包下载:https://download.csdn.net/download/yongjian_pan/10417162
最新文章
- javaScript基础语法(上)
- error 502 in ngin php5-fpm
- CSS中常见的6种文本样式
- [转]javascript Date format(js日期格式化)
- 学习jQuery的事件dblclick
- 25个CSS3 渐变和动画效果教程
- hdu1232 畅通工程
- Valid format values for declare-styleable/attr tags[转]
- lintcode :continuous subarray sum 连续子数组之和
- php处理字符串格式的计算公式
- 代码高亮插件Codemirror使用方法及下载
- [LeetCode]题解(python):144-Binary Tree Preorder Traversal
- 我在知乎上关于Laser200/310电脑的文章。
- css中margin重叠和一些相关概念(包含块containing block、块级格式化上下文BFC、不可替换元素 non-replaced element、匿名盒Anonymous boxes )
- 一个RESTful+MySQL程序
- css简单实现火焰效果
- C++ concurrency in action 读随记1
- [2019.03.21]LF, CR, CRLF and LFCR(?)
- 010 Editor v8.0.1(32 - bit) 算法逆向分析、注册机编写
- [TopCoder]棍子
热门文章
- 30行js让你的rem弹性布局适配所有分辨率(含竖屏适配)(转载)
- xpath 去除空格
- Mysql 定位执行效率低的sql 语句
- poj 1144(割点)
- servletActionContext.getContext默认获取contextmap 数据默认存储在contextmap的request中
- TortoiseSVN使用svn+ssh协议连接服务器时重复提示输入密码
- 【刷题】洛谷 P4319 变化的道路
- 【BZOJ4710】[JSOI2011]分特产(容斥)
- 解题:AHOI2017/HNOI2017 礼物
- P4887 第十四分块(前体) 莫队