java.sql.SQLFeatureNotSupportedException: Get value from java.sql.Clob

[shining-stars-lk]

Question

Using mysql queries, the code mapping LONGVARCHAR type is an error

Version

shardingsphere:5.3.2

errorMessage

Caused by: java.sql.SQLFeatureNotSupportedException: Get value from java.sql.Clob
at org.apache.shardingsphere.infra.merge.result.impl.memory.MemoryMergedResult.lambda$getValue$0(MemoryMergedResult.java:73) ~[shardingsphere-infra-merge-5.3.2.jar:5.3.2]
at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) ~[shardingsphere-infra-util-5.3.2.jar:5.3.2]
at org.apache.shardingsphere.infra.merge.result.impl.memory.MemoryMergedResult.getValue(MemoryMergedResult.java:72) ~[shardingsphere-infra-merge-5.3.2.jar:5.3.2]
at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getClob(ShardingSphereResultSet.java:321) ~[shardingsphere-jdbc-core-5.3.2.jar:5.3.2]
at org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSet.getClob(ShardingSphereResultSet.java:326) ~[shardingsphere-jdbc-core-5.3.2.jar:5.3.2]
at com.alibaba.druid.filter.FilterChainImpl.resultSet_getClob(FilterChainImpl.java:2214) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.stat.StatFilter.resultSet_getClob(StatFilter.java:704) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterChainImpl.resultSet_getClob(FilterChainImpl.java:2211) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.stat.StatFilter.resultSet_getClob(StatFilter.java:704) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.filter.FilterChainImpl.resultSet_getClob(FilterChainImpl.java:2211) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getClob(ResultSetProxyImpl.java:373) ~[druid-1.1.10.jar:1.1.10]
at com.alibaba.druid.pool.DruidPooledResultSet.getClob(DruidPooledResultSet.java:1179) ~[druid-1.1.10.jar:1.1.10]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_361]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_361]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_361]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_361]
at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:68) ~[mybatis-3.4.4.jar:3.4.4]
at com.sun.proxy.$Proxy313.getClob(Unknown Source) ~[?:?]
at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:41) ~[mybatis-3.4.4.jar:3.4.4]
at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:28) ~[mybatis-3.4.4.jar:3.4.4]
at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:66) ~[mybatis-3.4.4.jar:3.4.4]

Other

I see others have this problem too, link:#2956
I didn't understand the advice mentioned in it, could you explain it again? Thank you

[terrymanu]

What is your SQL, configuration and table metadata?

[shining-stars-lk]

Rule

rules:

• !SHARDING
  tables:
  mcr_rongcloud_message_history:
  actualDataNodes: ds_0.mcr_rongcloud_message_history_$->{0..1}
  tableStrategy:
  standard:
  shardingColumn: object_id
  shardingAlgorithmName: crc32_mod
  shardingAlgorithms:
  crc32_mod:
  type: CRC32_MOD

Execute SQL

[log-service] 2024-11-21 18:26:34 INFO ShardingSphere-SQL:73 - Logic SQL: select id, message_history_id, hospital_no, app_code, object_id, service_code, app_key, call_id, from_user_id, to_user_id, object_name, channel_type, msg_timestamp, msg_uid, sensitive_type, source, group_user_ids, status, status_del, create_time, create_by, last_edit_time, last_edit_by, param1, param2, param3, param4, param5, param6, param7, param8, content from mcr_rongcloud_message_history where error_type = 1 and object_name = ? group by msg_uid order by msg_timestamp asc
[log-service] 2024-11-21 18:26:34 INFO ShardingSphere-SQL:73 - Actual SQL: ds_0 ::: select id, message_history_id, hospital_no, app_code, object_id, service_code, app_key, call_id, from_user_id, to_user_id, object_name, channel_type, msg_timestamp, msg_uid, sensitive_type, source, group_user_ids, status, status_del, create_time, create_by, last_edit_time, last_edit_by, param1, param2, param3, param4, param5, param6, param7, param8, content from mcr_rongcloud_message_history_0 where error_type = 1 and object_name = ? group by msg_uid order by msg_timestamp asc ::: [test]
[log-service] 2024-11-21 18:26:34 INFO ShardingSphere-SQL:73 - Actual SQL: ds_0 ::: select id, message_history_id, hospital_no, app_code, object_id, service_code, app_key, call_id, from_user_id, to_user_id, object_name, channel_type, msg_timestamp, msg_uid, sensitive_type, source, group_user_ids, status, status_del, create_time, create_by, last_edit_time, last_edit_by, param1, param2, param3, param4, param5, param6, param7, param8, content from mcr_rongcloud_message_history_1 where error_type = 1 and object_name = ? group by msg_uid order by msg_timestamp asc ::: [test]
<== Columns: id, message_history_id, hospital_no, app_code, object_id, service_code, app_key, call_id, from_user_id, to_user_id, object_name, channel_type, msg_timestamp, msg_uid, sensitive_type, source, group_user_ids, status, status_del, create_time, create_by, last_edit_time, last_edit_by, param1, param2, param3, param4, param5, param6, param7, param8, content
<== Row: 1, 152905018811218300604510 , 40068980X4, 2, 152904775673018700604010, 10011, p5tvi9dspnae4, null, e11546946fb911e89a00005056ac3223, c, test, PERSON, 1529050187800, B409-V4G6-2U86-L07R, 0, iOS, null, 1, 1, 2018-06-15 16:09:48.0, null, 2018-06-15 16:09:48.0, null, null, null, null, null, null, null, null, null, {"content":"一个哥哥哥哥","extra":"{"serviceCode":"10011","objectId":"152904775673018700604010","hospitalNo":"40068980X4","appCode":"2"}"}

Table

CREATE TABLE `mcr_rongcloud_message_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '[消息唯一id]',
  `message_history_id` char(32) NOT NULL COMMENT '[消息业务id]ID值来自IdGeneratorUtil',
  `hospital_no` varchar(64) DEFAULT NULL COMMENT '[医院编号]',
  `app_code` varchar(64) DEFAULT NULL COMMENT '[应用编码]应用id',
  `object_id` varchar(64) NOT NULL COMMENT '[万能Id]',
  `service_code` varchar(64) DEFAULT NULL COMMENT '[服务编码]目前写 10012',
  `app_key` varchar(32) DEFAULT NULL COMMENT '[APPKEY]',
  `call_id` varchar(300) DEFAULT NULL COMMENT '[视频聊天会话标识]',
  `chat_type` int(1) DEFAULT '1' COMMENT '[聊天类型]1融云2腾讯云',
  `from_user_id` varchar(32) DEFAULT NULL COMMENT '[发送用户ID]',
  `to_user_id` varchar(32) DEFAULT NULL COMMENT '[接受者用户ID]',
  `object_name` varchar(32) DEFAULT NULL COMMENT '[消息类型]消息类型，文本消息 RC:TxtMsg 、 图片消息 RC:ImgMsg 、语音消息 RC:VcMsg 、图文消息 RC:ImgTextMsg 、位置消息 RC:LBSMsg 、添加联系人消息 RC:ContactNtf 、提示条通知消息 RC:InfoNtf 、资料通知消息 RC:ProfileNtf 、通用命令通知消息 RC:CmdNtf',
  `content` longtext COMMENT '[消息内容]',
  `tencent_content` longtext COMMENT '[腾讯消息内容]',
  `channel_type` varchar(32) DEFAULT NULL COMMENT '[会话类型]会话类型，二人会话是 PERSON 、讨论组会话是 PERSONS 、群组会话是 GROUP 、聊天室会话是 TEMPGROUP 、客服会话是 CUSTOMERSERVICE 、 系统通知是 NOTIFY 、应用公众服务是 MC 、公众服务是 MP。对应客户端 SDK 中 ConversationType 类型，二人会话是 1 、讨论组会话是 2 、群组会话是 3 、聊天室会话是 4 、客服会话是 5 、 系统通知是 6 、应用公众服务是 7 、公众服务是 8',
  `msg_timestamp` varchar(32) DEFAULT NULL COMMENT '[消息时间]服务端收到客户端发送消息时的服务器时间（1970年到现在的毫秒数）',
  `msg_uid` varchar(64) DEFAULT NULL COMMENT '[消息ID]可通过 msgUID 确定消息唯一',
  `sensitive_type` int(11) DEFAULT NULL COMMENT '[是否含有敏感词]消息中是否含有敏感词标识，0 为不含有敏感词，1 为含有屏蔽敏感词，2 为含有替换敏感词。消息路由功能默认含有屏蔽敏感词的消息不进行路由，可提交工单开通含有敏感词的消息路由功能，未开通情况下 sensitiveType 值默认为 0 不代表任何意义。开通后可通过该属性判断消息中是否含有敏感词。目前支持单聊、群聊、聊天室会话类型，其他会话类型默认为 0 ，开通后含有屏蔽敏感词的消息也不会进行下发，只会进行消息路由',
  `source` varchar(32) DEFAULT NULL COMMENT '[消息来源]包括：iOS、Android、Websocket',
  `group_user_ids` varchar(4096) DEFAULT NULL COMMENT '[用户ID]channelType 为 GROUP 时此参数有效，显示为群组中指定接收消息的用户 ID 数组，该条消息为群组定向消息。非定向消息时内容为空，如指定的用户不在群组中内容也为空',
  `status` int(11) DEFAULT NULL COMMENT '[状态]1. 有效 (默认1)',
  `status_del` int(11) DEFAULT NULL COMMENT '[删除标识]1. 未删除 -1. 已删除  (默认1)',
  `create_time` datetime DEFAULT NULL COMMENT '[创建时间]',
  `create_by` varchar(32) DEFAULT NULL COMMENT '[创建人]',
  `last_edit_time` datetime DEFAULT NULL COMMENT '[修改时间]',
  `last_edit_by` varchar(32) DEFAULT NULL COMMENT '[修改人]',
  `param1` int(11) DEFAULT NULL,
  `param2` int(11) DEFAULT NULL,
  `param3` int(11) DEFAULT NULL,
  `param4` int(11) DEFAULT NULL,
  `param5` varchar(32) DEFAULT NULL,
  `param6` varchar(32) DEFAULT NULL,
  `param7` varchar(32) DEFAULT NULL,
  `param8` varchar(32) DEFAULT NULL,
  `error_type` int(1) DEFAULT '1' COMMENT '[聊天数据是否正常]1正常2异常(默认1)',
  `sync_status` int(1) DEFAULT '2' COMMENT '[同步状态]1已同步2未同步',
  `data_status` int(1) DEFAULT '1' COMMENT '[数据状态]1正常2补全',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_message_history_id` (`message_history_id`) USING BTREE,
  KEY `uk_object_id` (`object_id`) USING BTREE,
  KEY `idx_hospital_no` (`hospital_no`) USING BTREE,
  KEY `idx_service_code` (`service_code`) USING BTREE,
  KEY `idx_to_user_id` (`to_user_id`) USING BTREE,
  KEY `idx_app_code` (`app_code`) USING BTREE,
  KEY `idx_msg_uid` (`msg_uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3672988 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

[terrymanu]

Which column is mapping to LONGVARCHAR?

[shining-stars-lk]

The content field in the code is received with LONGVARCHAR