Druid.io SQL亂碼問題

Gin.p發表於2018-05-15
UISQL

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

相關文章