MySQL8.0的JSON类型字段在使用useCursorFetch=true或者useServerPrepStmts=true场景下无法进行解析和转换

[TommyZC]

• dble version：2.19.03/lts

• preconditions ：

• configs：

schema.xml

<?xml version="1.0"?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd">
<dble:schema xmlns:dble="http://dble.cloud/" version="2.19.03.14">

    <schema name="testdb">

        <!-- random sharding using mod sharind rule -->
		
        <table name="testjson" dataNode="dn1,dn2" rule="sharding-by-mod"/>
		
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
    <dataNode name="dn1" dataHost="host1" database="dbletest1"/>
    <dataNode name="dn2" dataHost="host1" database="dbletest2"/>
    <dataHost name="host1" maxCon="100" minCon="10" balance="0" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="host1" url="localhost:3307" user="xxx" password="xxx">
        </writeHost>
        <!-- <writeHost host="hostM2" url="localhost:3316" user="xxx" password="xxx"/> -->
    </dataHost>
</dble:schema>

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dble:rule SYSTEM "rule.dtd">
<dble:rule xmlns:dble="http://dble.cloud/" version="2.19.03.14">

    <tableRule name="sharding-by-mod">
        <rule>
            <columns>id</columns>
            <algorithm>hashmod</algorithm>
        </rule>
    </tableRule>

    <!-- eg:  mod 4 -->
    <function name="hashmod" class="Hash">
        <property name="partitionCount">2</property>
        <property name="partitionLength">1</property>
    </function>

</dble:rule>

server.xml

• steps：
  step1. 创建表testjson CREATE TABLE testjson ( id int NOT NULL, jsonvalue json DEFAULT NULL, PRIMARY KEY (id) ) step2. 更新配置文件

  step3. 插入数据insert into testjson values(1,'{"type":"select"}');

  step4. 编写程序（JDBC连接中配置useCursorFetch=true或者useServerPrepStmts=true） import java.sql.*;

public class testdemo { public static final String URL = "jdbc:mysql://localhost:8066/testdb?useCursorFetch=false&useServerPrepStmts=true"; public static final String USER_HOST = "xxxx"; public static final String PASSWORD = "xxxx"; public static void main(String[] args) throws SQLException { Connection conn=null; PreparedStatement ps; ResultSet rs = null; try { //1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); //2. 获得数据库连接 conn = DriverManager.getConnection(URL, USER_HOST, PASSWORD); String sql = "select * from testjson;"; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); ps.close(); }catch (Exception e){ System.out.println(" jdbc error : "+e); }finally { conn.close(); System.out.println("task done"); } } }

step5. 执行程序

• expect result：
  返回查询结果
• real result：
  返回java.sql.SQLException: java.lang.IllegalArgumentException: Field type is not supported
• supplements：
  使用useCursorFetch=true或者useServerPrepStmts=true参数后，传输的数据会被转化为二进制格式，由于com/actiontech/dble/net/mysql/BinaryRowDataPacket.java类的covert(byte[] fv, FieldPacket fieldPk)方法中缺少对json字段类型的解析，因此返回了Field type is not supported的报错。