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/07/10 11:36:01 UTC

[GitHub] [shardingsphere] TeslaCN opened a new issue, #18738: Handling SET statement in ShardingSphere-Proxy

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

## Refactor

### Which version of ShardingSphere did you use?

5.1.2

### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

### Consider removing BroadcastDatabaseBackendHandler

If we execute SQL (SET statement) like the following, `BroadcastDatabaseBackendHandler` will execute SQL in all data sources of all logic databases (schemas).
```sql
set sql_mode = ''
```

Why shouldn't Proxy broadcast SQL to all logic data sources?
- It is expensive if there are too many data sources.
- PostgreSQL's connection is created with fixed database, which cannot be changed on the fly.
- If the SET statement worked in session scope, the connections used to execute the SQL will be returned to data sources, which pollutes connections in data sources. This issue cannot cure this problem, but may reduce the impact of pollution.

### After removed

Define a SPI interface to check whether the Proxy should passthrough variables in SET statement to data sources (a whitelist written in codes).

1. Except for the statements that need to be specially processed (autocommit, etc.), the Proxy does not do any actual operations for parameters that are not in the whitelist;
2. (For MySQL) SET GLOBAL / PERSIST / PERSIST_ONLY in the whitelist is fully routed in the current logic database. If the current logic database is null, no actual operation is performed; (the `BroadcastDatabaseBackendHandler` can be removed)
3. The SET SESSION parameter in the whitelist:
- Putting the SET statement into the playback collection, just like how the Proxy handles the `begin`;
- Neither acquire any new connections, nor release existing connections if in a transaction;
- When executing a SET statement in a transaction, the existing connection needs to traverse and execute the SET statement;
- All connections are not directly returned to the connection pool after use (client disconnection, transaction end, etc.) and need to be destroyed.
4. (For MySQL) SET @user-defined variable handling refers to 3;
5. When the SET operation target value is default, clear the SET SESSION in the playback set;
6. (For PostgreSQL) RESET operation refers to 5;
7. (For PostgreSQL) SET LOCAL transaction-level scope is not currently supported.

How to evict the connection?
- HikariCP provides method `com.zaxxer.hikari.HikariDataSource#evictConnection`

--
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] strongduanmu commented on issue #18738: Handling SET statement in ShardingSphere-Proxy

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #18738:
URL: https://github.com/apache/shardingsphere/issues/18738#issuecomment-1247532655

   @wuwen5 Yes, the latest version is `5.2.0`. Currently, there is a bug in github, we cannot update 5.1.3 to 5.2.0, we will try to update milestone again after this bug is 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] tristaZero closed issue #18738: Handling SET statement in ShardingSphere-Proxy

Posted by GitBox <gi...@apache.org>.

tristaZero closed issue #18738: Handling SET statement in ShardingSphere-Proxy
URL: https://github.com/apache/shardingsphere/issues/18738


-- 
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] tristaZero closed issue #18738: Handling SET statement in ShardingSphere-Proxy

Posted by GitBox <gi...@apache.org>.

tristaZero closed issue #18738: Handling SET statement in ShardingSphere-Proxy
URL: https://github.com/apache/shardingsphere/issues/18738


-- 
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] wuwen5 commented on issue #18738: Handling SET statement in ShardingSphere-Proxy

Posted by GitBox <gi...@apache.org>.

wuwen5 commented on issue #18738:
URL: https://github.com/apache/shardingsphere/issues/18738#issuecomment-1247492038

   @TeslaCN `5.1.3` It is not released. Is it released in `5.2.0`?


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