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/04/30 03:11:51 UTC
[GitHub] [shardingsphere] sandynz opened a new issue, #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
sandynz opened a new issue, #17218:
URL: https://github.com/apache/shardingsphere/issues/17218
## Bug Report
### Which version of ShardingSphere did you use?
master branch, commit 64683ecf52f8fd7b7918d8b61f8ace4c5a410116
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Proxy
### Expected behavior
From testing result, native MySQL and PostgreSQL's table name is case insensitive.
If table name is `t_order` in sharding rule, and create table with name `T_ORDER` in Proxy, tables metadata should be created in registry center.
### Actual behavior
No tables metadata in registry center, and it cause other issues.
After restarting proxy, `/medata/scaling_db/schemas/scaling_db/tables/` appear.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Run in native MySQL (use MySQL cli):
```
drop database if exists scaling_ds_0;
create database scaling_ds_0 default charset utf8;
drop database if exists scaling_ds_1;
create database scaling_ds_1 default charset utf8;
```
Enable `server.yaml`, use default configuration. It's not necessary to enable `config-sharding.yaml`, we'll use DistSQL to do testing.
Run in Proxy (use MySQL cli):
```
mysql> create database scaling_db;
Query OK, 0 rows affected (0.14 sec)
mysql> use scaling_db
Database changed
mysql> ADD RESOURCE ds_0 (
-> URL="jdbc:mysql://127.0.0.1:3306/scaling_ds_0?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD=root,
-> PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
-> ), ds_1 (
-> URL="jdbc:mysql://127.0.0.1:3306/scaling_ds_1?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD=root,
-> PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
-> );
Query OK, 0 rows affected (0.85 sec)
mysql> CREATE SHARDING TABLE RULE t_order(
-> RESOURCES(ds_0,ds_1),
-> SHARDING_COLUMN=order_id,
-> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
-> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
-> ), t_order_item(
-> RESOURCES(ds_0,ds_1),
-> SHARDING_COLUMN=order_id,
-> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
-> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
-> );
Query OK, 0 rows affected (2.30 sec)
mysql> CREATE TABLE T_ORDER (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
CREATE TABLE T_ORDER_ITEM (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, creation_date DATE, PRIMARY KEY (item_id));
Query OK, 0 rows affected (0.53 sec)
mysql>
mysql> CREATE TABLE T_ORDER_ITEM (item_id INT NOT NULL, order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, creation_date DATE, PRIMARY KEY (item_id));
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> select count(1) from T_ORDER;
ERROR 1997 (C1997): Runtime exception: [All tables must be in the same datasource.]
mysql> select count(1) from t_order;
ERROR 1997 (C1997): Runtime exception: [All tables must be in the same datasource.]
mysql> drop table t_order;
ERROR 1146 (42S02): Table 't_order' doesn't exist
```
Log in Proxy:
```
[ERROR] 2022-04-30 10:51:44.554 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.lang.IllegalStateException: All tables must be in the same datasource.
at com.google.common.base.Preconditions.checkState(Preconditions.java:508)
at org.apache.shardingsphere.singletable.route.SingleTableSQLRouter.validateSameDataSource(SingleTableSQLRouter.java:83)
at org.apache.shardingsphere.singletable.route.SingleTableSQLRouter.route(SingleTableSQLRouter.java:64)
at org.apache.shardingsphere.singletable.route.SingleTableSQLRouter.decorateRouteContext(SingleTableSQLRouter.java:89)
at org.apache.shardingsphere.singletable.route.SingleTableSQLRouter.decorateRouteContext(SingleTableSQLRouter.java:43)
at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:70)
at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:53)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.route(KernelProcessor.java:54)
at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:46)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:123)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:72)
at org.apache.shardingsphere.proxy.backend.text.data.impl.SchemaAssignedDatabaseBackendHandler.execute(SchemaAssignedDatabaseBackendHandler.java:56)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:97)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:107)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
Metadata in registry center:
<img width="236" alt="图片" src="https://user-images.githubusercontent.com/42492540/166087994-468fedf0-1cae-40ad-871b-3b22054bd10d.png">
### Example codes for reproduce this issue (such as a github link).
--
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] zjcnb commented on issue #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #17218:
URL: https://github.com/apache/shardingsphere/issues/17218#issuecomment-1124724796
@sandynz Hi, I think this problem are fixed in master branch. You can try it again.
--
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] sandynz commented on issue #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #17218:
URL: https://github.com/apache/shardingsphere/issues/17218#issuecomment-1118113624
Hi @zjcnb and @strongduanmu , could you help to have a look at 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] zjcnb commented on issue #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
Posted by GitBox <gi...@apache.org>.
zjcnb commented on issue #17218:
URL: https://github.com/apache/shardingsphere/issues/17218#issuecomment-1118114655
Sure, Thank you for your feedback, i will check it.
--
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] zjcnb closed issue #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
Posted by GitBox <gi...@apache.org>.
zjcnb closed issue #17218: No tables metadata created in registry center if table name is lowercase in sharding rule and create table with uppercase table name
URL: https://github.com/apache/shardingsphere/issues/17218
--
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