[GitHub] [shardingsphere] quzhixue-Kimi commented on issue #14976: We have two datasources, but choose the wrong one

[GitBox <gi...@apache.org>]

quzhixue-Kimi commented on issue #14976:
URL: https://github.com/apache/shardingsphere/issues/14976#issuecomment-1133012559

   hi there,
   
   I just got the same issue, in which I have more than two data sources as below:
   
   spring:
     shardingsphere:
       props:
         sql:
           show: true
           allow.range.query.with.inline.sharding: true
       datasource:
         names: loan,accountpay,limit,blaze,customer,lm
         loan:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://127.0.0.1:3306/db-migration-test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
         accountpay:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://127.0.0.1:3306/db-migration-test-1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
         limit:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://127.0.0.1:3306/db-migration-test-2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
         blaze:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://127.0.0.1:3306/blaze?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
         customer:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://127.0.0.1:3306/loan_customer_center?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
         lm:
           driver-class-name: com.mysql.jdbc.Driver
           password: pwd1
           type: com.alibaba.druid.pool.DruidDataSource
           url: jdbc:mysql://192.168.1.105:3306/accountinginside_trans1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
           username: root
   
       sharding:
         tables:
           loan_lc_appl:
             key-generator-column-name: id
             actual-data-nodes: loan.loan_lc_appl_${1..10}
             table-strategy:
               standard:
                 sharding-column: apply_code
                 
           accountpay_order_bank_card:
             key-generator-column-name: tx_log_seq
             actual-data-nodes: accountpay.accountpay_order_bank_card_${1..10}
             table-strategy:
               standard:
                 sharding-column: biz_code
                 
           limit_cust_credit_limit_info:
             key-generator-column-name: id
             actual-data-nodes: limit.limit_cust_credit_limit_info_${1..10}
             table-strategy:
               standard:
                 sharding-column: cust_id
                 
           irce_blaze_rep_limit_amount:
             key-generator-column-name: id
             actual-data-nodes: blaze.irce_blaze_rep_limit_amount_${2021..2022}_${1..4}
             table-strategy:
               standard:
                 sharding-column: id
                 
           customer_basic_info:
             key-generator-column-name: id
             actual-data-nodes: customer.customer_basic_info_${1..5}
             table-strategy:
               standard:
                 sharding-column: cust_id
                 
           account_lm_loan:
             key-generator-column-name: id
             actual-data-nodes: lm.account_lm_loan_${1..6}
             table-strategy:
               standard:
                 sharding-column: id
                 
   When I run my java code within the blaze data source but, it will use the RANDOM data source () to run the SQL, which caused the error like:
   
   [22:45:41.729] [mainraceId] [main] INFO  ShardingSphere-SQL - Logic SQL: select count(*) from ( SELECT t1.grade_limit_amount AS total_amount, t1.id_num AS idcard_no  FROM irce_blaze_rep_limit_amount as t1 join  (select max(id) as id from irce_blaze_rep_limit_amount WHERE id_num is not null GROUP BY id_num) t2 on t1.id = t2.id WHERE t1.id_num is not null ) tt;
   
   [22:45:41.729] [mainraceId] [main] INFO  ShardingSphere-SQL - Actual SQL: lm ::: select count(*) from ( SELECT t1.grade_limit_amount AS total_amount, t1.id_num AS idcard_no  FROM irce_blaze_rep_limit_amount as t1 join  (select max(id) as id from irce_blaze_rep_limit_amount WHERE id_num is not null GROUP BY id_num) t2 on t1.id = t2.id WHERE t1.id_num is not null ) tt;
   
   I wonder why the lm data source will be used instead of the blaze! AND, sometimes, it will choose another one, when I run the application again later, for this moment, the limit data source will be used!
   
   [22:57:05.861] [mainraceId] [main] INFO  ShardingSphere-SQL - Actual SQL: limit ::: select count(*) from ( SELECT t1.grade_limit_amount AS total_amount, t1.id_num AS idcard_no  FROM irce_blaze_rep_limit_amount as t1 join  (select max(id) as id from irce_blaze_rep_limit_amount WHERE id_num is not null GROUP BY id_num) t2 on t1.id = t2.id WHERE t1.id_num is not null ) tt;
   
   
   And, for your information:
   There are some data sources code as below, each one will scan the mapper folder respectively.
   
   @Bean(name = "lmLoanMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.lm")
       public DataSource lmLoanMysqlDataSource() {
           logger.info("lmLoan mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
   @Bean(name = "accountPayMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.accountpay")
       public DataSource accountPayMysqlDataSource() {
           logger.info("account mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
       @Bean(name = "customerMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.customer")
       public DataSource customerMysqlDataSource() {
           logger.info("customer mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
   @Bean(name = "gradeMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.blaze")
       public DataSource gradeMysqlDataSource() {
           logger.info("grade mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
    @Bean(name = "limitMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.limit")
       public DataSource limitMysqlDataSource() {
           logger.info("limit mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
       @Bean(name = "loanMysqlDataSource")
       @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.loan")
       public DataSource loanMysqlDataSource() {
           logger.info("loan mysql datasource init....");
           return DruidDataSourceBuilder.create().build();
       }
   
   
   BR
   Kimi
   
   
   
   
   


-- 
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