Issue 1:
Bug Report

i use shardingsphere-proxy for 5 mysql shard

use proxy for making id by showflake

but insert data is not correct

data insert to wrong shard

this is my table

CREATE TABLE `person` (
`id` varchar(32) NOT NULL,
`name` varchar(16) DEFAULT NULL,
`age` int(2) DEFAULT NULL,
`gender` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

this is my insert sql

insert `person` (`name`, `age`) VALUES ('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1)

and this is the log

[INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_4 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461833), ('a', 1, 452837411152461828)
[INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461832), ('a', 1, 452837411152461827)
[INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461831), ('a', 1, 452837411152461826)
[INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_2 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461830), ('a', 1, 452837411152461825)
[INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_3 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461829), ('a', 1, 452837411152461824)

only mod=1 is correct

so, I cannot select result from this table, such as

select * from person where `id` = '452837411152461832'

this is my config

schemaName: sharding_db

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:13386/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
ds_1:
url: jdbc:mysql://127.0.0.1:13387/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
ds_2:
url: jdbc:mysql://127.0.0.1:13388/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
ds_3:
url: jdbc:mysql://127.0.0.1:13389/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
ds_4:
url: jdbc:mysql://127.0.0.1:13390/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65

shardingRule:
tables:
person:
actualDataNodes: ds_${0..4}.person
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds_${id.toLong() % 5}
# algorithmExpression: ds${id % 2}
keyGenerator:
type: SNOWFLAKE
column: id

Which version of ShardingSphere did you use?

4.0.0

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-Proxy

Expected behavior

insert to right sharding

Actual behavior

insert to incorrect sharding

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

Issue 2:
Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

4.1.0

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

ShardingSphere-Proxy

Expected behavior

MySQL Server is 8.0.20

MySQL Connector/J 8.X Above version connection success

Actual behavior

java.sql.SQLException: Unknown system variable 'query_cache_size'

Reason analyze (If you can)

https://github.com/apache/shardingsphere/blob/master/shardingsphere-db-protocol/shardingsphere-db-protocol-mysql/src/main/java/org/apache/shardingsphere/db/protocol/mysql/constant/MySQLServerInfo.java file constant SERVER_VERSION.This constant is returned by the handshake protocol. In java client jar file com.mysql.cj.NativeSession will determine the version number of the server.Code is:

if (!versionMeetsMinimum(8, 0, 3)) {

queryBuf.append(", @@query_cache_size AS query_cache_size");

queryBuf.append(", @@query_cache_type AS query_cache_type");

}

But mysql server 8 abandon ‘query_cache_size’

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Mysql server 8.0.20

Java client maven import

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>

Example codes for reproduce this issue (such as a github link).