对mysql的分库分表,升级到5.0以后,表名全变成小写了。直接连mysql,没有问题,通过shardingsphere连就全是小写,而且没有顺序,但实际操作表的时候,还是区分大小写的。怎么回事?
请问是通过哪个语句查询的表名?
亲, shardingsphere 不区分大小写的, 如果您用的是 mysql linux 版本的, 可以设置一下忽略大小写哈.
可以输入这个命令查看下 : show variables like '%lower_case_table_names%';
我的mysql是5.7.21,装在centos7上,在shardingsphere4.11上看,表名是区分大小写的,shardingsphere升级到5.0,mysql没有动,在5.0里执行下面的命令
新建一个表TEST,分别执行desc和show tables命令,结果是矛盾的,
4.11没有这个问题。
show tables;
这个test表是我测试表名大小写的,就是单独的一个表,没有进行分库分表。
好的,感谢反馈,我再看下
@zhuhongwen 你好,我试了下,show tables 展示的结果有点问题,都小写处理了,我会在新版本中优化下。
mysql> desc TEST;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| CONTENT | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.36 sec)
mysql> desc test;
ERROR 1146 (42S02): Table 'demo_ds_1.test' doesn't exist
mysql> SHOW TABLES LIKE 'test';
+-----------------------+------------+
| Tables_in_sharding_db | Table_type |
+-----------------------+------------+
| test | BASE TABLE |
+-----------------------+------------+
1 row in set (1.23 sec)
mysql> SHOW TABLES LIKE 'TEST';
Empty set (0.00 sec)
- 这个字段小写也有问题的,oracle库,sql转化完执行字段名全部小写了,导致查询失败;
- 表里的字段名都是大写的
- 版本是5.1.0
2022-03-11 15:37:46.015 INFO 15088 --- [ main] ShardingSphere-SQL : Logic SQL: select * from IRP_METADATAPROBE
2022-03-11 15:37:46.017 INFO 15088 --- [ main] ShardingSphere-SQL : SQLStatement: OracleSelectStatement(lock=Optional.empty, modelSegment=Optional.empty)
2022-03-11 15:37:46.017 INFO 15088 --- [ main] ShardingSphere-SQL : Actual SQL: logic_db ::: select "IRP_METADATAPROBE"."m_guid", "IRP_METADATAPROBE"."mf_guid", "IRP_METADATAPROBE"."m_name", "IRP_METADATAPROBE"."m_proberange", "IRP_METADATAPROBE"."m_job_guid", "IRP_METADATAPROBE"."M_DATASOURCE" AS "m_datasource" from IRP_METADATAPROBE
java.sql.SQLSyntaxErrorException: ORA-00904: "IRP_METADATAPROBE"."m_job_guid": 标识符无效
麻烦补充下复现问题的步骤,谢谢
- 环境:
oracle11的数据库,springboot项目,依赖的是shardingsphere 5.1.0; - 正常的建表语句
create table IRP_METADATAPROBE
(
M_GUID VARCHAR2(32) not null,
MF_GUID VARCHAR2(32),
M_NAME VARCHAR2(100),
M_TITLE VARCHAR2(100),
M_TYPE NUMBER(10),
M_ORDER NUMBER(20),
M_DESCRIPTION VARCHAR2(200),
M_MODIFYTIME VARCHAR2(20),
M_DATASOURCE VARCHAR2(32),
M_SCHEMA VARCHAR2(50),
M_INCLUDE_TABLE CLOB,
M_EXCLUDE_TABLE CLOB,
M_CREATE_USER VARCHAR2(32),
M_CREATE_TIME VARCHAR2(20),
M_PROBERANGE NUMBER(1),
M_JOB_GUID VARCHAR2(32),
M_DATASOURCE_ENCRYPT VARCHAR2(255)
)
- test文件,只做了数据加密
@SpringBootTest
class ShardingApplicationOracle {
static String tableName = "IRP_METADATAPROBE";
static String logicColumn = "M_DATASOURCE";
static String cipherColumn = "M_DATASOURCE_ENCRYPT";
private DataSource getDataSource() throws SQLException {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@10.2.20.82:1521:ORCL");
ds.setUsername("CHRQ_YBPCS_YFK");
ds.setPassword("CHRQ_YBPCS_YFK");
// BasicDataSource ds = new BasicDataSource();
// ds.setDriverClassName("com.mysql.jdbc.Driver");
// ds.setUrl("jdbc:mysql://localhost:3306/bi?useUnicode=true&characterEncoding=UTF-8");
// ds.setUsername("root");
// ds.setPassword("");
Collection<RuleConfiguration> configs = new ArrayList<>();
Collection<EncryptTableRuleConfiguration> tables = new ArrayList<>();
Collection<EncryptColumnRuleConfiguration> columns = new ArrayList<>();
String plainColumn = logicColumn;
String encryptorName = "aes_encrypt";
EncryptColumnRuleConfiguration encryptColumnRuleConfiguration = new EncryptColumnRuleConfiguration(logicColumn,cipherColumn, null,plainColumn,encryptorName);
columns.add(encryptColumnRuleConfiguration);
EncryptTableRuleConfiguration encryptTableRuleConfiguration = new EncryptTableRuleConfiguration(tableName, columns, null);
tables.add(encryptTableRuleConfiguration);
Map<String, ShardingSphereAlgorithmConfiguration> encryptors = new HashMap<>();
String encryptType = "AES";
Properties properties = new Properties();
properties.setProperty("aes-key-value","123456abc");
ShardingSphereAlgorithmConfiguration shardingSphereAlgorithmConfiguration = new ShardingSphereAlgorithmConfiguration(encryptType, properties);
encryptors.put(encryptorName, shardingSphereAlgorithmConfiguration);
EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(tables, encryptors, false);
configs.add(encryptRuleConfiguration);
Properties props = new Properties();
props.setProperty("sql-show","true");
// props.setProperty("max-connections-size-per-query","10");
// ModeConfiguration modeConfiguration = new ModeConfiguration("Standalone", null, false);
return ShardingSphereDataSourceFactory.createDataSource(ds, configs, props);
}
@Test
void query(){
String sql = "select * from " + tableName;
try(Connection connection = getDataSource().getConnection()) {
PreparedStatement ps = connection.prepareStatement(sql);
try(ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()){
System.out.println(String.format("%s %s", resultSet.getString(logicColumn)
,resultSet.getString(cipherColumn)));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
好的,还有这个问题 获取DataSource的时候失败,原因是SqlValidator.Config.DEFAULT值为空
也是提的一个问题 ,咱们中间件是只能在springboot的架构上部署嘛,常规的web项目可以嘛!
@zhaoyangfang https://github.com/apache/shardingsphere/pull/16130 这个 PR 已经修复了你反馈的问题,可以晚些使用 master 分支帮忙验证下。