Issue 1:
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.0.0

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

Sharding-JDBC

Expected behavior

primary key produced by key generator

Logic SQL:

insert into test_complex	(name,age,create_time,modified_time,user_id,phone,address)

values(?,?,?,?,?,	?,?) , (?,?,?,?,?,?,?)

route result of sharding key(id)

logicName=sharding_db, value=461910991453999105, hash=1101970151 , mod=1

logicName=sharding_db, mod=1 ,route =sharding_db_1

logicName=test_complex, value=461910991453999105, hash=1101970151 , mod=7

logicName=test_complex, mod=7 ,route =test_complex_7

logicName=sharding_db, value=461910991453999108, hash=1101970154 , mod=0

logicName=sharding_db, mod=0 ,route =sharding_db_0

logicName=test_complex, value=461910991453999108, hash=1101970154 , mod=2

logicName=test_complex, mod=2 ,route =test_complex_2

Actual behavior

Actual SQL: sharding_db_1 ::: insert into test_complex_3	(name,age,create_time,modified_time,user_id,phone,address,id) values

(?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?)

::: [null, 1, 2020-04-28 15:09:21.415, 2020-04-28 15:09:21.415, 09c53d8e85934bbf8aeeec3d4711c0c95, 09c53d8e85934bbf8aeeec3d4711c0c95, 时代峰峻克鲁赛德, 461910991453999108, null, 1, 2020-04-28 15:09:21.415, 2020-04-28 15:09:21.415, 09c53d8e85934bbf8aeeec3d4711c0c98, 09c53d8e85934bbf8aeeec3d4711c0c98, 时代峰峻克鲁赛德, 461910991453999105]

Reason analyze (If you can)

ShardingGeneratedKeyInsertValueParameterRewriter rewrite generateKey values had a descendingIterator

the params had a descending order of route result, cause th actual sql is wrong

why use descendingIterator here ? if use common iterator would cause some problems?

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

Which version of ShardingSphere did you use?

5.1.1

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

ShardingSphere-Proxy

Expected behavior

i hava three databases of mysql，one for write，tow for read，i use ShardingSphere-Proxy and config readwrite-splitting, when a select not in transaction, i think it will route to read database .

Actual behavior

begin, all my examples run good util a request with @transactional ，after this request，all select sql route to write database even in other request

Reason analyze (If you can)

i debug the ShardingSphere-Proxy，find the TransactionHolder.isTransaction=true，but i can't understand why a request with transaction can affect other request

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

config-readwrite-splitting.yaml

schemaName: x1db_shardingsphere

dataSources:

write_ds_141:

url: jdbc:mysql://10.110.34.141:3306/x1db?serverTimezone=UTC&useSSL=false&characterEncoding=utf8

username: root

password:

connectionTimeoutMilliseconds: 30000

idleTimeoutMilliseconds: 60000

maxLifetimeMilliseconds: 1800000

maxPoolSize: 50

minPoolSize: 1

read_ds_151:

url: jdbc:mysql://10.110.34.151:3306/x1db?serverTimezone=UTC&useSSL=false&characterEncoding=utf8

username: root

password:

connectionTimeoutMilliseconds: 30000

idleTimeoutMilliseconds: 60000

maxLifetimeMilliseconds: 1800000

maxPoolSize: 50

minPoolSize: 1

read_ds_152:

url: jdbc:mysql://10.110.34.152:3306/x1db?serverTimezone=UTC&useSSL=false&characterEncoding=utf8

username: root

password:

connectionTimeoutMilliseconds: 30000

idleTimeoutMilliseconds: 60000

maxLifetimeMilliseconds: 1800000

maxPoolSize: 50

minPoolSize: 1

rules:

!READWRITE_SPLITTING

dataSources:

readwrite_ds:

type: Static

props:

write-data-source-name: write_ds_141

read-data-source-names: read_ds_151, read_ds_152

server.yaml

rules:

!AUTHORITY

users:

root@%:shardingsphere@141

provider:

type: ALL_PRIVILEGES_PERMITTED

!SQL_PARSER

sqlCommentParseEnabled: true

sqlStatementCache:

initialCapacity: 2000

maximumSize: 65535

concurrencyLevel: 4

parseTreeCache:

initialCapacity: 128

maximumSize: 1024

concurrencyLevel: 4

props:

max-connections-size-per-query: 1

kernel-executor-size: 16

proxy-frontend-flush-threshold: 128

proxy-opentracing-enabled: false

proxy-hint-enabled: false

sql-show: true

check-table-metadata-enabled: false

show-process-list-enabled: false

proxy-backend-query-fetch-size: -1

check-duplicate-table-enabled: false

proxy-frontend-executor-size: 0

proxy-backend-executor-suitable: OLAP

proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.

sql-federation-enabled: false

proxy-backend-driver-type: JDBC

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