seata similar table names cause "Not support the table has the same column name with different case yet"

yhqotfr8  于 5个月前  发布在  其他
关注(0)|答案(3)|浏览(54)

Ⅰ. Issue Description

similar table names cause "Not support the table has the same column name with different case yet".
such as:bill_test、bill_test_data_0.

Ⅱ. Describe what happened

io.seata.rm.datasource.sql.struct.cache.MysqlTableMetaCache#resultSetMetaToSchema
line 117:ResultSet rsColumns = dbmd.getColumns(catalogName, schemaName, tableName, "%")

Caused by: io.seata.common.exception.NotSupportYetException: Not support the table has the same column name with different case yet
	at io.seata.rm.datasource.sql.struct.cache.MysqlTableMetaCache.resultSetMetaToSchema(MysqlTableMetaCache.java:142) ~[seata-all-1.5.2.jar:1.5.2]
	at io.seata.rm.datasource.sql.struct.cache.MysqlTableMetaCache.fetchSchema(MysqlTableMetaCache.java:83) ~[seata-all-1.5.2.jar:1.5.2]
	... 147 common frames omitted

Ⅲ. Describe what you expected to happen

Ⅳ. How to reproduce it (as minimally and precisely as possible)

Ⅴ. Anything else we need to know?

Ⅵ. Environment:

  • JDK 1.8
  • Seata server 1.7.0
  • Seata client 1.5.2
  • MySQL 5.7
  • shardingsphere-jdbc-core 5.3.1
  • shardingsphere-transaction-base-seata-at 5.3.1
  • mysql-connector-java 8.0.21
35g0bw71

35g0bw711#

Please check if your column name exists with just a difference in uppercase and lowercase.

8fsztsew

8fsztsew2#

You can paste the table structure here by executing show create table.

cyvaqqii

cyvaqqii3#

You can paste the table structure here by executing show create table.

CREATE TABLE `bill_storage` (
  `id` bigint(20) NOT NULL,
  `project_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `company_id` bigint(20) NOT NULL,
  `dept_id` bigint(20) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `type` char(2) DEFAULT '0',
  `audit_user_ids` varchar(200) DEFAULT NULL,
  `audit_user_names` varchar(200) DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL,
  `status` varchar(2) DEFAULT '0',
  `create_user_id` bigint(20) DEFAULT NULL,
  `create_user_name` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE `bill_storage_data_0` (
  `id` bigint(20) NOT NULL,
  `project_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `company_id` bigint(20) NOT NULL,
  `dept_id` bigint(20) DEFAULT NULL,
  `tag_no` varchar(7) DEFAULT NULL,
  `tag_sn` varchar(50) DEFAULT NULL,
  `tag_serial` varchar(50) DEFAULT NULL,
  `device_sn` varchar(50) DEFAULT NULL,
  `device_serial` varchar(50) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `epc` varchar(100) DEFAULT NULL,
  `assets_type_id` bigint(20) DEFAULT NULL,
  `source` varchar(200) DEFAULT NULL,
  `brand` varchar(200) DEFAULT NULL,
  `model` varchar(200) DEFAULT NULL,
  `spec` varchar(200) DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `supplier_name` varchar(100) DEFAULT NULL,
  `supplier_contact` varchar(100) DEFAULT NULL,
  `supplier_phone` varchar(100) DEFAULT NULL,
  `agent_name` varchar(100) DEFAULT NULL,
  `agent_contact` varchar(100) DEFAULT NULL,
  `agent_phone` varchar(100) DEFAULT NULL,
  `purchase_time` date DEFAULT NULL,
  `stock_in_time` date DEFAULT NULL,
  `install_time` date DEFAULT NULL,
  `purchase_price` double(11,2) DEFAULT '1.00',
  `current_price` double(11,2) DEFAULT '0.00',
  `purchase_user_id` bigint(10) DEFAULT NULL,
  `purchase_user_name` varchar(50) DEFAULT NULL,
  `receive_date` datetime DEFAULT NULL,
  `extend1` varchar(100) DEFAULT NULL,
  `extend2` varchar(100) DEFAULT NULL,
  `extend3` varchar(100) DEFAULT NULL,
  `extend4` varchar(100) DEFAULT NULL,
  `extend5` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `bill_id` bigint(20) NOT NULL,
  `tag_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

ShardingSphere-JDBC may have configuration problems. We formatted the configuration rules ourselves and finally loaded them by ShardingSphere-JDBC

- logic-table: bill_storage
  algorithm-inline-expression: bill_storage
  sharding-column: id
  algorithm-expression: bill_storage
- logic-table: bill_storage_data
  algorithm-inline-expression: bill_storage_data_${0..0}
  sharding-column: id
  algorithm-expression: bill_storage_data_${id % 1}

just delete the next part and it'll work

- logic-table: bill_storage
  algorithm-inline-expression: bill_storage
  sharding-column: id
  algorithm-expression: bill_storage

if there is a configuration, mysql-connector-java:8.0.21 will find two tables

seata-all:1.5.2 atches the id of bill_storage_data_0 with the data of bill_storage once

相关问题