druid mysql 乱码_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备份并覆盖，重启服务。