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/09/14 10:09:46 UTC
[GitHub] [shardingsphere] AdelaHong opened a new issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
AdelaHong opened a new issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427
shardingshpere version number：5.0.0-RC1-SNAPSHOT
query.with.cipher.column: flase
`Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno",null,"DELETE_USER_NAME","sm4");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);`
When using plaintext query, the following error occurred：Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Print sql found that all fields are replaced with bankno instead of the table for the encryption strategy
`2021-09-14 17:58:15.296 [DubboServerHandler-192.168.88.101:20880-thread-6] INFO ShardingSphere-SQL - Logic SQL: select
t.ID,t.BANKNO,tt.bankno banknono
from tf_b_bank_user t
join tf_b_bank_info tt on tt.id=t.bank_id
where t.CUSTOMER_ID = ? and t.bankno = ? and t.status = 1
2021-09-14 17:58:15.296 [DubboServerHandler-192.168.88.101:20880-thread-6] INFO ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2021-09-14 17:58:15.296 [DubboServerHandler-192.168.88.101:20880-thread-6] INFO ShardingSphere-SQL - Actual SQL: ds_0 ::: select
t.ID,t.DELETE_USER_NAME AS BANKNO,tt.DELETE_USER_NAME AS banknono
from tf_b_bank_user t
join tf_b_bank_info tt on tt.id=t.bank_id
where t.CUSTOMER_ID = ? and t.DELETE_USER_NAME = ? and t.status = 1 ::: [1370285443137175555, 123456789]`
--
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 #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-979042658
This issue has been fixed.
--
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] AdelaHong commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-920059718
I will provide the configuration again：
`CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE tf_b_bank_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
DELETE_USER_NAME varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';``
execute sql statement：
SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
Execute the final result：
When two tables have a bankno field at the same time, the bankno field of the two tables will be converted to DELETE_USER_NAME instead of only the bankno field of the specified table
java api configuration :
@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno",null,"DELETE_USER_NAME ","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}
}`
--
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] menghaoranss commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-922162985
> Encrypt module do not support join table segment very well for now, see #12462, #12504
Good job.
--
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] menghaoranss commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-919667357
@AdelaHong could you please provide the complete configuration of Java API, I will try to reproduce 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
[GitHub] [shardingsphere] strongduanmu closed issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427
--
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] menghaoranss edited a comment on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
menghaoranss edited a comment on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-919887328
@AdelaHong I couldn't reproduce your issue with example in ShardingSphere , if you set `queryWithCipherColumn=false`, ShardingSphere will query with the plainColumn `DELETE_USER_NAME `, but the column is missing in your table.
--
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] AdelaHong commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-920057857
I will provide the configuration again：
`CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE tf_b_bank_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';`
```
execute sql statement：
SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
Execute the final result：
When two tables have a bankno field at the same time, the bankno field of the two tables will be converted to DELETE_USER_NAME instead of only the bankno field of the specified table
java api configuration :
`@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno",null,"DELETE_USER_NAME ","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}
}`
--
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] AdelaHong commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-920049994
I will provide the configuration again
`CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE tf_b_bank_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
DELETE_USER_NAME varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';``
execute sql statement：
`SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
Execute the final result:
When two tables have bankno fields at the same time, both table bankno fields will be converted to DELETE_USER_NAME fields
java api configuration :
`@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno_cipher",null,"bankno","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}`
--
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] AdelaHong removed a comment on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong removed a comment on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-920049994
I will provide the configuration again
`CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE tf_b_bank_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
DELETE_USER_NAME varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';``
execute sql statement：
`SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
Execute the final result:
When two tables have bankno fields at the same time, both table bankno fields will be converted to DELETE_USER_NAME fields
java api configuration :
`@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno_cipher",null,"bankno","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}`
--
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] cheese8 commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-921923332
Encrypt module do not support join table segment very well for now, see #12462, #12504
--
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] AdelaHong commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-919741366
`CREATE TABLE `tf_b_bank_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`bankno` varchar(25) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE `tf_b_bank_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`bank_id` bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
`bankno` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';`
execute sql statement：
`SELECT
t.ID,
t.BANKNO,
tt.bankno banknono
FROM
tf_b_bank_user t
JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
java api configuration :
`@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno_cipher",null,"bankno","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}
}`
--
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] menghaoranss commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-919887328
@AdelaHong I couldn't reproduce your issue with [example]() , if you set `queryWithCipherColumn=false`, ShardingSphere will query with the plainColumn `DELETE_USER_NAME `, but the column is missing in your table.
--
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] AdelaHong removed a comment on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
AdelaHong removed a comment on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-920057857
I will provide the configuration again：
`CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';
CREATE TABLE tf_b_bank_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='';`
```
execute sql statement：
SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN tf_b_bank_info tt ON tt.id = t.bank_id`
Execute the final result：
When two tables have a bankno field at the same time, the bankno field of the two tables will be converted to DELETE_USER_NAME instead of only the bankno field of the specified table
java api configuration :
`@Configuration
@AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
public class ShardingConfigurer {
@Bean
public DataSource masterDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
@DependsOn({"masterDataSource"})
@Primary
public DataSource dataSource(DataSource masterDataSource) throws Exception {
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_0", masterDataSource);
Properties properties = new Properties();
properties.setProperty(SQL_SHOW.getKey(), "true");
return ShardingSphereDataSourceFactory.createDataSource(
dataSourceMap,
Arrays.asList(
createEncryptRuleConfiguration())
, properties);
}
public EncryptRuleConfiguration createEncryptRuleConfiguration() {
Map<String, ShardingSphereAlgorithmConfiguration> encryptAlgorithmConfigs = new LinkedHashMap<>();
Properties encryptProperties = new Properties();
encryptProperties.setProperty("aes-key-value", "123456");
encryptAlgorithmConfigs.put("aes", new ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
EncryptColumnRuleConfiguration columnRuleConfiguration = new EncryptColumnRuleConfiguration("BANKNO","bankno",null,"DELETE_USER_NAME ","aes");
columns.add(columnRuleConfiguration);
EncryptTableRuleConfiguration result = new EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
return encryptRuleConfiguration;
}
}`
--
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] cheese8 commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #12427:
URL: https://github.com/apache/shardingsphere/issues/12427#issuecomment-952919052
merged and supported, it can be closed now.
--
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