You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/10/22 06:53:21 UTC

[GitHub] [shardingsphere] carlyin0801 opened a new issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

carlyin0801 opened a new issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219


   ![企业微信截图_1634885570733](https://user-images.githubusercontent.com/5268164/138407057-b9bcc46c-d036-4fa2-af42-8b24f58df954.png)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] tristaZero closed issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] lanchengx commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949345970


   Hi @carlyin0801 thanks for your feedback, @strongduanmu could you have a review?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 edited a comment on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 edited a comment on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-950508124


   **hi，The database version I use is mysql, and its version is 5.7.20：**
   ![企业微信截图_16351344086387](https://user-images.githubusercontent.com/5268164/138632653-800b4c0d-d9d9-414f-b827-8c38970ef1bb.png)
   ![企业微信截图_16351345079361](https://user-images.githubusercontent.com/5268164/138632780-ca8d1915-bf58-4389-8397-e9c0f5791a43.png)
   
   **My table structure is as follows：**
   
   CREATE TABLE `T_PIPELINE_INFO` (
     `PIPELINE_ID` varchar(34) CHARACTER SET utf8 NOT NULL DEFAULT '' ,
     `PROJECT_ID` varchar(64) CHARACTER SET utf8 NOT NULL ,
     `PIPELINE_NAME` varchar(255) DEFAULT NULL,
     `PIPELINE_DESC` varchar(255) DEFAULT NULL,
     `VERSION` int(11) DEFAULT '1',
     `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `CREATOR` varchar(64) CHARACTER SET utf8 NOT NULL,
     `UPDATE_TIME` timestamp NULL DEFAULT NULL,
     `LAST_MODIFY_USER` varchar(64) CHARACTER SET utf8 NOT NULL,
     `CHANNEL` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
     `MANUAL_STARTUP` int(11) DEFAULT '1',
     `ELEMENT_SKIP` int(11) DEFAULT '0',
     `TASK_COUNT` int(11) DEFAULT '0',
     `DELETE` bit(1) DEFAULT b'0',
     `ID` bigint(20) NOT NULL AUTO_INCREMENT,
     `PIPELINE_NAME_PINYIN` varchar(1300) CHARACTER SET utf8 DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     UNIQUE KEY `T_PIPELINE_INFO_NAME_uindex` (`PROJECT_ID`,`PIPELINE_NAME`),
     KEY `PROJECT_ID` (`PROJECT_ID`,`PIPELINE_ID`),
     KEY `INX_TPI_CREATE_TIME` (`CREATE_TIME`),
     KEY `INX_TPI_CHANNEL` (`CHANNEL`),
     KEY `IDX_DELETED` (`DELETE`) USING BTREE,
     KEY `INX_TPI_DELETE` (`DELETE`),
     KEY `ID` (`ID`)
   ) ENGINE=InnoDB AUTO_INCREMENT=372817 DEFAULT CHARSET=utf8mb4;
   
   CREATE TABLE `T_PIPELINE_SETTING` (
     `PIPELINE_ID` varchar(34) NOT NULL,
     `DESC` varchar(1024) CHARACTER SET utf8mb4 DEFAULT NULL,
     `RUN_TYPE` int(11) DEFAULT NULL,
     `NAME` varchar(128) DEFAULT NULL,
     `SUCCESS_RECEIVER` mediumtext,
     `FAIL_RECEIVER` mediumtext,
     `SUCCESS_GROUP` mediumtext,
     `FAIL_GROUP` mediumtext,
     `SUCCESS_TYPE` varchar(32) DEFAULT NULL,
     `FAIL_TYPE` varchar(32) DEFAULT NULL,
     `PROJECT_ID` varchar(64) DEFAULT NULL,
     `SUCCESS_WECHAT_GROUP_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `SUCCESS_WECHAT_GROUP` varchar(1024) NOT NULL DEFAULT '',
     `FAIL_WECHAT_GROUP_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `FAIL_WECHAT_GROUP` varchar(1024) NOT NULL DEFAULT '',
     `RUN_LOCK_TYPE` int(11) DEFAULT '1',
     `SUCCESS_DETAIL_FLAG` bit(1) DEFAULT b'0',
     `FAIL_DETAIL_FLAG` bit(1) DEFAULT b'0',
     `SUCCESS_CONTENT` longtext CHARACTER SET utf8mb4,
     `FAIL_CONTENT` longtext CHARACTER SET utf8mb4,
     `WAIT_QUEUE_TIME_SECOND` int(11) DEFAULT '7200' ,
     `MAX_QUEUE_SIZE` int(11) DEFAULT '10',
     `IS_TEMPLATE` bit(1) DEFAULT b'0',
     `SUCCESS_WECHAT_GROUP_MARKDOWN_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `FAIL_WECHAT_GROUP_MARKDOWN_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `MAX_PIPELINE_RES_NUM` int(11) DEFAULT '50',
     `MAX_CON_RUNNING_QUEUE_SIZE` int(11) DEFAULT '50',
     `BUILD_NUM_RULE` varchar(512) DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     UNIQUE KEY `PROJECT_ID` (`PROJECT_ID`,`NAME`,`IS_TEMPLATE`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
   
   CREATE TABLE `T_PIPELINE_BUILD_SUMMARY` (
     `PIPELINE_ID` varchar(34) NOT NULL,
     `PROJECT_ID` varchar(64) NOT NULL,
     `BUILD_NUM` int(11) DEFAULT '0',
     `BUILD_NO` int(11) DEFAULT '0',
     `FINISH_COUNT` int(11) DEFAULT '0',
     `RUNNING_COUNT` int(11) DEFAULT '0',
     `QUEUE_COUNT` int(11) DEFAULT '0',
     `LATEST_BUILD_ID` varchar(34) DEFAULT NULL,
     `LATEST_TASK_ID` varchar(34) DEFAULT NULL,
     `LATEST_START_USER` varchar(64) DEFAULT NULL,
     `LATEST_START_TIME` timestamp NULL DEFAULT NULL,
     `LATEST_END_TIME` timestamp NULL DEFAULT NULL,
     `LATEST_TASK_COUNT` int(11) DEFAULT NULL,
     `LATEST_TASK_NAME` varchar(128) DEFAULT NULL,
     `LATEST_STATUS` int(11) DEFAULT NULL,
     `BUILD_NUM_ALIAS` varchar(256) DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     KEY `PRJOECT_ID` (`PROJECT_ID`,`PIPELINE_ID`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   **They all have the same sharding field projectId，the sharding rule is：**
   
   fun getTableRuleConfiguration(
       tableName: String,
       specifyDataSourceName: String? = null
   ): ShardingTableRuleConfiguration? {
       // 生成实际节点规则
       val actualDataNodes = if (specifyDataSourceName != null) {
           "$specifyDataSourceName.$tableName"
       } else {
           "ds_\${0..1}.$tableName"
       }
       val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
       tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
       tableRuleConfig.databaseShardingStrategy =
           StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
       return tableRuleConfig
   }


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-951829414


   Hi @carlyin0801 , thank you for your detailed information, this is indeed a bug, and I will fix it asap. You can wait for the upcoming 5.0.0 GA release.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-950508124


   hi，The database version I use is mysql, and its version is 5.7.20：
   ![企业微信截图_16351344086387](https://user-images.githubusercontent.com/5268164/138632653-800b4c0d-d9d9-414f-b827-8c38970ef1bb.png)
   ![企业微信截图_16351345079361](https://user-images.githubusercontent.com/5268164/138632780-ca8d1915-bf58-4389-8397-e9c0f5791a43.png)
   My table structure is as follows：
   
   CREATE TABLE `T_PIPELINE_INFO` (
     `PIPELINE_ID` varchar(34) CHARACTER SET utf8 NOT NULL DEFAULT '' ,
     `PROJECT_ID` varchar(64) CHARACTER SET utf8 NOT NULL ,
     `PIPELINE_NAME` varchar(255) DEFAULT NULL,
     `PIPELINE_DESC` varchar(255) DEFAULT NULL,
     `VERSION` int(11) DEFAULT '1',
     `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `CREATOR` varchar(64) CHARACTER SET utf8 NOT NULL,
     `UPDATE_TIME` timestamp NULL DEFAULT NULL,
     `LAST_MODIFY_USER` varchar(64) CHARACTER SET utf8 NOT NULL,
     `CHANNEL` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
     `MANUAL_STARTUP` int(11) DEFAULT '1',
     `ELEMENT_SKIP` int(11) DEFAULT '0',
     `TASK_COUNT` int(11) DEFAULT '0',
     `DELETE` bit(1) DEFAULT b'0',
     `ID` bigint(20) NOT NULL AUTO_INCREMENT,
     `PIPELINE_NAME_PINYIN` varchar(1300) CHARACTER SET utf8 DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     UNIQUE KEY `T_PIPELINE_INFO_NAME_uindex` (`PROJECT_ID`,`PIPELINE_NAME`),
     KEY `PROJECT_ID` (`PROJECT_ID`,`PIPELINE_ID`),
     KEY `INX_TPI_CREATE_TIME` (`CREATE_TIME`),
     KEY `INX_TPI_CHANNEL` (`CHANNEL`),
     KEY `IDX_DELETED` (`DELETE`) USING BTREE,
     KEY `INX_TPI_DELETE` (`DELETE`),
     KEY `ID` (`ID`)
   ) ENGINE=InnoDB AUTO_INCREMENT=372817 DEFAULT CHARSET=utf8mb4;
   CREATE TABLE `T_PIPELINE_SETTING` (
     `PIPELINE_ID` varchar(34) NOT NULL,
     `DESC` varchar(1024) CHARACTER SET utf8mb4 DEFAULT NULL,
     `RUN_TYPE` int(11) DEFAULT NULL,
     `NAME` varchar(128) DEFAULT NULL,
     `SUCCESS_RECEIVER` mediumtext,
     `FAIL_RECEIVER` mediumtext,
     `SUCCESS_GROUP` mediumtext,
     `FAIL_GROUP` mediumtext,
     `SUCCESS_TYPE` varchar(32) DEFAULT NULL,
     `FAIL_TYPE` varchar(32) DEFAULT NULL,
     `PROJECT_ID` varchar(64) DEFAULT NULL,
     `SUCCESS_WECHAT_GROUP_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `SUCCESS_WECHAT_GROUP` varchar(1024) NOT NULL DEFAULT '',
     `FAIL_WECHAT_GROUP_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `FAIL_WECHAT_GROUP` varchar(1024) NOT NULL DEFAULT '',
     `RUN_LOCK_TYPE` int(11) DEFAULT '1',
     `SUCCESS_DETAIL_FLAG` bit(1) DEFAULT b'0',
     `FAIL_DETAIL_FLAG` bit(1) DEFAULT b'0',
     `SUCCESS_CONTENT` longtext CHARACTER SET utf8mb4,
     `FAIL_CONTENT` longtext CHARACTER SET utf8mb4,
     `WAIT_QUEUE_TIME_SECOND` int(11) DEFAULT '7200' ,
     `MAX_QUEUE_SIZE` int(11) DEFAULT '10',
     `IS_TEMPLATE` bit(1) DEFAULT b'0',
     `SUCCESS_WECHAT_GROUP_MARKDOWN_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `FAIL_WECHAT_GROUP_MARKDOWN_FLAG` bit(1) NOT NULL DEFAULT b'0',
     `MAX_PIPELINE_RES_NUM` int(11) DEFAULT '50',
     `MAX_CON_RUNNING_QUEUE_SIZE` int(11) DEFAULT '50',
     `BUILD_NUM_RULE` varchar(512) DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     UNIQUE KEY `PROJECT_ID` (`PROJECT_ID`,`NAME`,`IS_TEMPLATE`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
   CREATE TABLE `T_PIPELINE_BUILD_SUMMARY` (
     `PIPELINE_ID` varchar(34) NOT NULL,
     `PROJECT_ID` varchar(64) NOT NULL,
     `BUILD_NUM` int(11) DEFAULT '0',
     `BUILD_NO` int(11) DEFAULT '0',
     `FINISH_COUNT` int(11) DEFAULT '0',
     `RUNNING_COUNT` int(11) DEFAULT '0',
     `QUEUE_COUNT` int(11) DEFAULT '0',
     `LATEST_BUILD_ID` varchar(34) DEFAULT NULL,
     `LATEST_TASK_ID` varchar(34) DEFAULT NULL,
     `LATEST_START_USER` varchar(64) DEFAULT NULL,
     `LATEST_START_TIME` timestamp NULL DEFAULT NULL,
     `LATEST_END_TIME` timestamp NULL DEFAULT NULL,
     `LATEST_TASK_COUNT` int(11) DEFAULT NULL,
     `LATEST_TASK_NAME` varchar(128) DEFAULT NULL,
     `LATEST_STATUS` int(11) DEFAULT NULL,
     `BUILD_NUM_ALIAS` varchar(256) DEFAULT NULL,
     PRIMARY KEY (`PIPELINE_ID`),
     KEY `PRJOECT_ID` (`PROJECT_ID`,`PIPELINE_ID`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   They all have the same sharding field projectId，the sharding rule is：
   
   fun getTableRuleConfiguration(
       tableName: String,
       specifyDataSourceName: String? = null
   ): ShardingTableRuleConfiguration? {
       // 生成实际节点规则
       val actualDataNodes = if (specifyDataSourceName != null) {
           "$specifyDataSourceName.$tableName"
       } else {
           "ds_\${0..1}.$tableName"
       }
       val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
       tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
       tableRuleConfig.databaseShardingStrategy =
           StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
       return tableRuleConfig
   }


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949427390


   **My configuration is as follows：**
       @Bean
       fun shardingDataSource(): DataSource {
           val shardingRuleConfig = ShardingRuleConfiguration()
           // 设置表的路由规则
           val tableRuleConfigs = shardingRuleConfig.tables
           val projectStrategyTableNames = shardingProjectStrategyTableConfig?.split(",")
           if (!projectStrategyTableNames.isNullOrEmpty()) {
               projectStrategyTableNames.forEach { projectStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(projectStrategyTableName))
               }
           }
           val specifyDataSourceStrategyTableNames = shardingSpecifyDataSourceStrategyTableConfig?.split(",")
           if (!specifyDataSourceStrategyTableNames.isNullOrEmpty()) {
               specifyDataSourceStrategyTableNames.forEach { specifyDataSourceStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(specifyDataSourceStrategyTableName, FIRST_DATA_SOURCE_NAME))
               }
           }
           // 设置绑定表
           shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO,T_PIPELINE_SETTING,T_PIPELINE_BUILD_SUMMARY")
           // 设置广播表
           val broadcastTables = shardingRuleConfig.broadcastTables
           val broadcastStrategyTableNames = shardingBroadcastStrategyTableConfig?.split(",")
           if (!broadcastStrategyTableNames.isNullOrEmpty()) {
               broadcastStrategyTableNames.forEach { broadcastStrategyTableName ->
                   broadcastTables.add(broadcastStrategyTableName)
               }
           }
           val dbShardingAlgorithmrProps = Properties()
           dbShardingAlgorithmrProps.setProperty("strategy", "STANDARD")
           dbShardingAlgorithmrProps.setProperty("algorithmClassName", "com.xxx.config.BkProcessDatabaseShardingAlgorithm")
           shardingRuleConfig.shardingAlgorithms["bkProcessDatabaseShardingAlgorithm"] = ShardingSphereAlgorithmConfiguration("CLASS_BASED", dbShardingAlgorithmrProps)
   
           shardingRuleConfig.defaultTableShardingStrategy = NoneShardingStrategyConfiguration()
           shardingRuleConfig.defaultDatabaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           val properties = Properties()
           // 是否打印SQL解析和改写日志
           properties.setProperty("sql-show", "true")
           return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap(), listOf(shardingRuleConfig), properties)
       }
   
       fun getTableRuleConfiguration(
           tableName: String,
           specifyDataSourceName: String? = null
       ): ShardingTableRuleConfiguration? {
           // 生成实际节点规则
           val actualDataNodes = if (specifyDataSourceName != null) {
               "$specifyDataSourceName.$tableName"
           } else {
               "ds_\${0..1}.$tableName"
           }
           val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
           tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
           tableRuleConfig.databaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           return tableRuleConfig
       }
   
   **The executed sql is：**
   select count(*) from `T_PIPELINE_INFO` join `T_PIPELINE_SETTING` on `T_PIPELINE_INFO`.`PIPELINE_ID` = `T_PIPELINE_SETTING`.`PIPELINE_ID` join `T_PIPELINE_BUILD_SUMMARY` on `T_PIPELINE_SETTING`.`PIPELINE_ID` = `T_PIPELINE_BUILD_SUMMARY`.`PIPELINE_ID` where (`T_PIPELINE_INFO`.`PROJECT_ID` = ? and `T_PIPELINE_INFO`.`CHANNEL` = ? and `T_PIPELINE_INFO`.`DELETE` = ? and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and true and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?))
   
   I just compared it and did not configure the binding table relationship [shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO, T_PIPELINE_SETTING, T_PIPELINE_SETTING, T_PIPELINE_BUILD_SUMMARY")], the table name after conversion is normal, and the table name of some tables has changed after configuring this relationship Lowercase。The following figure is the execution log without the configuration of the binding table relationship：
   ![企业微信截图_1634893328896](https://user-images.githubusercontent.com/5268164/138426132-898de406-2340-4758-877d-39972bfe308e.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-950494322


   @carlyin0801 Sorry for later reply. 
   
   I have tested it with the oracle database, and the table and the configuration all use uppercase table names, but there is no problem that you reported that it becomes lowercase after rewriting. Can you provide more information? Such as the configuration of the logical table and the actual table in the sharding rule?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949359497


   @carlyin0801 Can you provide more information to help us reproduce this exception?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 edited a comment on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 edited a comment on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949372167


   @strongduanmu 
   Hello, this is the information with detailed error stack,the version is 5.0.0-beta
   
   ![企业微信截图_16348900533303](https://user-images.githubusercontent.com/5268164/138417641-1828eda5-fad1-4e58-9da2-6c2b8b7f524d.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949372167


   @strongduanmu 
   Hello, this is the information with detailed error stack
   
   ![企业微信截图_16348886043678](https://user-images.githubusercontent.com/5268164/138414039-d4df0ba6-1a74-4186-9f5e-f0f56b0918ca.png)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949552221


   @strongduanmu Yes,They all have the same sharding field projectId


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949418133


   Can you provide steps to reproduce the problem? For example, how to configure, what kind of SQL to execute, this exception will occur.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 edited a comment on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 edited a comment on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949427390


   **My configuration is as follows：**
   
       @Bean
       fun shardingDataSource(): DataSource {
           val shardingRuleConfig = ShardingRuleConfiguration()
           // 设置表的路由规则
           val tableRuleConfigs = shardingRuleConfig.tables
           val projectStrategyTableNames = shardingProjectStrategyTableConfig?.split(",")
           if (!projectStrategyTableNames.isNullOrEmpty()) {
               projectStrategyTableNames.forEach { projectStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(projectStrategyTableName))
               }
           }
           val specifyDataSourceStrategyTableNames = shardingSpecifyDataSourceStrategyTableConfig?.split(",")
           if (!specifyDataSourceStrategyTableNames.isNullOrEmpty()) {
               specifyDataSourceStrategyTableNames.forEach { specifyDataSourceStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(specifyDataSourceStrategyTableName, FIRST_DATA_SOURCE_NAME))
               }
           }
           // 设置绑定表
           shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO,T_PIPELINE_SETTING,T_PIPELINE_BUILD_SUMMARY")
           // 设置广播表
           val broadcastTables = shardingRuleConfig.broadcastTables
           val broadcastStrategyTableNames = shardingBroadcastStrategyTableConfig?.split(",")
           if (!broadcastStrategyTableNames.isNullOrEmpty()) {
               broadcastStrategyTableNames.forEach { broadcastStrategyTableName ->
                   broadcastTables.add(broadcastStrategyTableName)
               }
           }
           val dbShardingAlgorithmrProps = Properties()
           dbShardingAlgorithmrProps.setProperty("strategy", "STANDARD")
           dbShardingAlgorithmrProps.setProperty("algorithmClassName", "com.xxx.config.BkProcessDatabaseShardingAlgorithm")
           shardingRuleConfig.shardingAlgorithms["bkProcessDatabaseShardingAlgorithm"] = ShardingSphereAlgorithmConfiguration("CLASS_BASED", dbShardingAlgorithmrProps)
   
           shardingRuleConfig.defaultTableShardingStrategy = NoneShardingStrategyConfiguration()
           shardingRuleConfig.defaultDatabaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           val properties = Properties()
           // 是否打印SQL解析和改写日志
           properties.setProperty("sql-show", "true")
           return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap(), listOf(shardingRuleConfig), properties)
       }
   
       fun getTableRuleConfiguration(
           tableName: String,
           specifyDataSourceName: String? = null
       ): ShardingTableRuleConfiguration? {
           // 生成实际节点规则
           val actualDataNodes = if (specifyDataSourceName != null) {
               "$specifyDataSourceName.$tableName"
           } else {
               "ds_\${0..1}.$tableName"
           }
           val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
           tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
           tableRuleConfig.databaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           return tableRuleConfig
       }
   
   **The executed sql is：**
   select count(*) from `T_PIPELINE_INFO` join `T_PIPELINE_SETTING` on `T_PIPELINE_INFO`.`PIPELINE_ID` = `T_PIPELINE_SETTING`.`PIPELINE_ID` join `T_PIPELINE_BUILD_SUMMARY` on `T_PIPELINE_SETTING`.`PIPELINE_ID` = `T_PIPELINE_BUILD_SUMMARY`.`PIPELINE_ID` where (`T_PIPELINE_INFO`.`PROJECT_ID` = ? and `T_PIPELINE_INFO`.`CHANNEL` = ? and `T_PIPELINE_INFO`.`DELETE` = ? and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and true and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?))
   
   I just compared it and did not configure the binding table relationship [shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO, T_PIPELINE_SETTING, T_PIPELINE_SETTING, T_PIPELINE_BUILD_SUMMARY")], the table name after conversion is normal, and the table name of some tables has changed after configuring this relationship Lowercase。The following figure is the execution log without the configuration of the binding table relationship：
   ![企业微信截图_1634893328896](https://user-images.githubusercontent.com/5268164/138426132-898de406-2340-4758-877d-39972bfe308e.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 edited a comment on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 edited a comment on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949372167


   @strongduanmu 
   Hello, this is the information with detailed error stack
   
   ![企业微信截图_16348900533303](https://user-images.githubusercontent.com/5268164/138417641-1828eda5-fad1-4e58-9da2-6c2b8b7f524d.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-952472644


   
   Hi,when is the 5.0.0 GA version expected to be released?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] carlyin0801 edited a comment on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
carlyin0801 edited a comment on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949427390


   @strongduanmu 
   **My configuration is as follows：**
   
       @Bean
       fun shardingDataSource(): DataSource {
           val shardingRuleConfig = ShardingRuleConfiguration()
           // 设置表的路由规则
           val tableRuleConfigs = shardingRuleConfig.tables
           val projectStrategyTableNames = shardingProjectStrategyTableConfig?.split(",")
           if (!projectStrategyTableNames.isNullOrEmpty()) {
               projectStrategyTableNames.forEach { projectStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(projectStrategyTableName))
               }
           }
           val specifyDataSourceStrategyTableNames = shardingSpecifyDataSourceStrategyTableConfig?.split(",")
           if (!specifyDataSourceStrategyTableNames.isNullOrEmpty()) {
               specifyDataSourceStrategyTableNames.forEach { specifyDataSourceStrategyTableName ->
                   tableRuleConfigs.add(getTableRuleConfiguration(specifyDataSourceStrategyTableName, FIRST_DATA_SOURCE_NAME))
               }
           }
           // 设置绑定表
           shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO,T_PIPELINE_SETTING,T_PIPELINE_BUILD_SUMMARY")
           // 设置广播表
           val broadcastTables = shardingRuleConfig.broadcastTables
           val broadcastStrategyTableNames = shardingBroadcastStrategyTableConfig?.split(",")
           if (!broadcastStrategyTableNames.isNullOrEmpty()) {
               broadcastStrategyTableNames.forEach { broadcastStrategyTableName ->
                   broadcastTables.add(broadcastStrategyTableName)
               }
           }
           val dbShardingAlgorithmrProps = Properties()
           dbShardingAlgorithmrProps.setProperty("strategy", "STANDARD")
           dbShardingAlgorithmrProps.setProperty("algorithmClassName", "com.xxx.config.BkProcessDatabaseShardingAlgorithm")
           shardingRuleConfig.shardingAlgorithms["bkProcessDatabaseShardingAlgorithm"] = ShardingSphereAlgorithmConfiguration("CLASS_BASED", dbShardingAlgorithmrProps)
   
           shardingRuleConfig.defaultTableShardingStrategy = NoneShardingStrategyConfiguration()
           shardingRuleConfig.defaultDatabaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           val properties = Properties()
           // 是否打印SQL解析和改写日志
           properties.setProperty("sql-show", "true")
           return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap(), listOf(shardingRuleConfig), properties)
       }
   
       fun getTableRuleConfiguration(
           tableName: String,
           specifyDataSourceName: String? = null
       ): ShardingTableRuleConfiguration? {
           // 生成实际节点规则
           val actualDataNodes = if (specifyDataSourceName != null) {
               "$specifyDataSourceName.$tableName"
           } else {
               "ds_\${0..1}.$tableName"
           }
           val tableRuleConfig = ShardingTableRuleConfiguration(tableName, actualDataNodes)
           tableRuleConfig.tableShardingStrategy = NoneShardingStrategyConfiguration()
           tableRuleConfig.databaseShardingStrategy =
               StandardShardingStrategyConfiguration(PROJECT_ID_FIELD, "bkProcessDatabaseShardingAlgorithm")
           return tableRuleConfig
       }
   
   **The executed sql is：**
   select count(*) from `T_PIPELINE_INFO` join `T_PIPELINE_SETTING` on `T_PIPELINE_INFO`.`PIPELINE_ID` = `T_PIPELINE_SETTING`.`PIPELINE_ID` join `T_PIPELINE_BUILD_SUMMARY` on `T_PIPELINE_SETTING`.`PIPELINE_ID` = `T_PIPELINE_BUILD_SUMMARY`.`PIPELINE_ID` where (`T_PIPELINE_INFO`.`PROJECT_ID` = ? and `T_PIPELINE_INFO`.`CHANNEL` = ? and `T_PIPELINE_INFO`.`DELETE` = ? and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?) and true and `T_PIPELINE_INFO`.`PIPELINE_ID` in (?))
   
   I just compared it and did not configure the binding table relationship [shardingRuleConfig.bindingTableGroups.add("T_PIPELINE_INFO, T_PIPELINE_SETTING, T_PIPELINE_SETTING, T_PIPELINE_BUILD_SUMMARY")], the table name after conversion is normal, and the table name of some tables has changed after configuring this relationship Lowercase。The following figure is the execution log without the configuration of the binding table relationship：
   ![企业微信截图_1634893328896](https://user-images.githubusercontent.com/5268164/138426132-898de406-2340-4758-877d-39972bfe308e.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #13219: During operation, the original SQL table name was changed from uppercase to lowercase, which affected the execution of business logic

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13219:
URL: https://github.com/apache/shardingsphere/issues/13219#issuecomment-949526338


   @carlyin0801 Thank you for your detailed information, does all tables(`T_PIPELINE_INFO,T_PIPELINE_SETTING,T_PIPELINE_BUILD_SUMMARY`) are sharding tables and have same sharding strategy?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org