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/29 01:25:52 UTC
[GitHub] [shardingsphere] tuichenchuxin opened a new issue #12803: mysql `show table status` use wrong routeEngine
tuichenchuxin opened a new issue #12803:
URL: https://github.com/apache/shardingsphere/issues/12803
`show table status` should route to all database, but now use `ShardingDataSourceGroupBroadcastRoutingEngine`.
So some tables may missing.
## example
This demo will miss ds2's tables
```
public class Demo {
public static void main(String[] args) {
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第 1 个数据源
HikariDataSource dataSource1 = new HikariDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false");
dataSource1.setUsername("root");
dataSource1.setPassword("123456");
dataSourceMap.put("ds0", dataSource1);
// 配置第 2 个数据源
HikariDataSource dataSource2 = new HikariDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false");
dataSource2.setUsername("root");
dataSource2.setPassword("123456");
dataSourceMap.put("ds1", dataSource2);
HikariDataSource dataSource3 = new HikariDataSource();
dataSource3.setDriverClassName("com.mysql.jdbc.Driver");
dataSource3.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/demo_ds_2?serverTimezone=UTC&useSSL=false");
dataSource3.setUsername("root");
dataSource3.setPassword("123456");
dataSourceMap.put("ds2", dataSource3);
// 配置 t_order 表规则
ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("t_order", "ds${0..1}.t_order_${0..1}");
// 配置分库策略
orderTableRuleConfig.setDatabaseShardingStrategy(new ComplexShardingStrategyConfiguration("user_id,order_id", "dbShardingAlgorithm"));
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "tableShardingAlgorithm"));
// 省略配置 t_order_item 表规则...
// ...
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(orderTableRuleConfig);
// 配置分库算法
Properties dbShardingAlgorithmrProps = new Properties();
dbShardingAlgorithmrProps.setProperty("algorithm-expression", "ds${(user_id % 2)}");
shardingRuleConfig.getShardingAlgorithms().put("dbShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("COMPLEX_INLINE", dbShardingAlgorithmrProps));
// 配置分表算法
Properties tableShardingAlgorithmrProps = new Properties();
tableShardingAlgorithmrProps.setProperty("algorithm-expression", "t_order_${order_id % 2}");
shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", tableShardingAlgorithmrProps));
// 创建 ShardingSphereDataSource
DataSource dataSource = null;
try {
Properties props = new Properties();
props.setProperty("sql-comment-parse-enabled", "false");
props.setProperty("sql-show", "true");
dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), props);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
String sql = "SHOW TABLE STATUS";
try {
try (
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
try {
ResultSet rs = ps.executeQuery();
while (rs.next()) {
// ...
}
} finally {
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
--
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 #12803: mysql `show table status` use wrong routeEngine
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12803:
URL: https://github.com/apache/shardingsphere/issues/12803
--
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 #12803: mysql `show table status` use wrong routeEngine
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12803:
URL: https://github.com/apache/shardingsphere/issues/12803#issuecomment-931196702
At present, the result of executing `show table status` through the proxy is wrong, and the kernel needs to support this statement.
```sql
mysql> show table status;
+----------------+
| Name |
+----------------+
| t_order_item_0 |
| t_order |
| t_order_item_1 |
| t_order_0 |
| t_order_1 |
| t2 |
+----------------+
6 rows in set (18.20 sec)
```
The correct result should be:
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| t\_order | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2021-09-30 08:37:24 | NULL | NULL | latin1\_swedish\_ci | NULL | | |
| t\_order\_item | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2021-09-28 03:40:20 | NULL | NULL | latin1\_swedish\_ci | NULL | | |
For the Rows, Avg_row_length, Data_length, and Max_data_length fields, the results need to be processed by the merge engine.
--
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