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 2022/06/11 07:19:29 UTC

[GitHub] [shardingsphere] taojintianxia opened a new issue, #18301: could not create index for encrypt column through ShardingSphere JDBC

taojintianxia opened a new issue, #18301:
URL: https://github.com/apache/shardingsphere/issues/18301

   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot reproduce it on current information, we will **close it**.
   
   Please answer these questions before submitting your issue. Thanks!
   
   ### Which version of ShardingSphere did you use?
   5.1.1 and 5.1.2-SNAPSHOT
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   
   ### Expected behavior
   index could be created on the encrypt column
   
   ### Actual behavior
   when create index on encrypt column, got error like followings :
   Key column 'name' doesn't exist in table
   <img width="1593" alt="image" src="https://user-images.githubusercontent.com/4112856/173177694-d3b1a0ad-a53e-4b86-9141-691946e8a757.png">
   
   
   ### Reason analyze (If you can)
   the actual SQL is trying to add index on the logical column name, not on the encrypt column
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   1. prepare the config for encryption:
   config-encryption.yaml
   ```yaml
   schemaName: encrypt_test
   dataSources:
     unique_ds:
       dataSourceClassName: com.zaxxer.hikari.HikariDataSource
       driverClassName: com.mysql.jdbc.Driver
       jdbcUrl: jdbc:mysql://127.0.0.1:3306/encrypt_test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
   
   rules:
     - !ENCRYPT
       encryptors:
         aes_encryptor:
           type: AES
           props:
             aes-key-value: 123456abc
         sm4_encryptor:
           type: SM4
           props:
             sm4-key: 4D744E003D713D054E7E407C350E447E
             sm4-mode: ECB
             sm4-padding: PKCS5Padding
       tables:
         tb_f_user10:
           columns:
             birthday:
               plainColumn: birthday_plain
               cipherColumn: birthday_cipher
               encryptorName: sm4_encryptor
             name:
               plainColumn: name_plain
               cipherColumn: name_cipher
               encryptorName: sm4_encryptor
             contact_person:
               plainColumn: contact_person_plain
               cipherColumn: contact_person_cipher
               encryptorName: sm4_encryptor
   props:
     sql-show: true
   ```
   
   2. coding a Java snippet like followings : 
   ```java
   String TABLE_CREATION = "CREATE TABLE `tb_f_user10` (\n" +
               "  `id` varchar(64) NOT NULL COMMENT '主键',\n" +
               "  `name` varchar(50) NOT NULL COMMENT '姓名',\n" +
               "  `birthday` date DEFAULT NULL COMMENT '生日',\n" +
               "  `gender` char(1) DEFAULT NULL COMMENT '性别 M-男 F-女',\n" +
               "  `nationality` char(2) DEFAULT NULL COMMENT '国籍',\n" +
               "  `contact_person` varchar(50) DEFAULT NULL COMMENT '联系人',\n" +
               "  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
               "  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',\n" +
               "  `version` int(11) DEFAULT NULL COMMENT '数据版本',\n" +
               "  `updator` varchar(64) DEFAULT NULL COMMENT '更新人',\n" +
               "  `disable` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标识',\n" +
               "  PRIMARY KEY (`id`),\n" +
               "  KEY `tb_f_user_nationality_index` (`nationality`),\n" +
               "  KEY `tb_f_user_create_time_index` (`create_time`) USING BTREE,\n" +
               "  KEY `tb_f_user_update_time_index` (`update_time`) USING BTREE\n" +
               ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
   
   DataSource dataSource = YamlShardingSphereDataSourceFactory.createDataSource(new File("config-encrypt.yaml"));
   dataSource.getConnection().createStatement().execute(TABLE_CREATION);
   dataSource.getConnection().createStatement().execute("create index tb_f_user_name_index on tb_f_user10(name)");
   ```
   


-- 
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.apache.org

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


[GitHub] [shardingsphere] tuichenchuxin closed issue #18301: could not create index for encrypt column through ShardingSphere JDBC

Posted by GitBox <gi...@apache.org>.
tuichenchuxin closed issue #18301: could not create index for encrypt column through ShardingSphere JDBC
URL: https://github.com/apache/shardingsphere/issues/18301


-- 
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 #18301: could not create index for encrypt column through ShardingSphere JDBC

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

   Thank you for your feedback, I will investigate this issue.


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