[GitHub] [shardingsphere] ICannerxy opened a new issue, #26658: Connection occasionally closed when using the jdbc parameter `useServerPrepStmts=true`

["ICannerxy (via GitHub)" <gi...@apache.org>]

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

   ## Bug Report
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore 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?
   
   5.1.2
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   
   ShardingSphere-Proxy
   
   ### Expected behavio
   
   Connection is always available, not closed.
   
   ### Actual behavior
   
   In the case of continuous calls, the connection will be closed after about a few minutes.
   
   ### Reason analyze (If you can)
   
   - before jdbcUrl: `useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&allowMultiQueries=true`
   - after jdbUrl: `useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useServerPrepStmts=true`
   
   When we use before jdbcUrl, Connection is ok, not closed.
   But we need to use the jdbc batch update function, so we have added the parameter `useServerPrepStmts=true` to support the batch function, and also removed the `allowMultiQueries=true` parameter. when we use after jdbUrl, the connection will be closed after about a few minutes.
   
   The error is as follows:
   
   <img width="1150" alt="image" src="https://github.com/apache/shardingsphere/assets/40480377/ee62579c-13aa-49f1-b636-3d36beffc57c">
   
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   shardingrule:
   
   ```yaml
   ds_0:
     password: xxxxxx
     minPoolSize: 1
     connectionTimeoutMilliseconds: 30000
     maxLifetimeMilliseconds: 1800000
     dataSourceClassName: com.zaxxer.hikari.HikariDataSource
     idleTimeoutMilliseconds: 60000
     maxPoolSize: 50
     url: jdbc:mysql://1.1.1.1:3306/xxx?characterEncoding=UTF8&allowMultiQueries=true&rewriteBatchedStatements=true&serverTimezone=GMT%2B8&useSSL=false
     username: admin
   ds_1:
     password: xxxxxx
     minPoolSize: 1
     connectionTimeoutMilliseconds: 30000
     maxLifetimeMilliseconds: 1800000
     dataSourceClassName: com.zaxxer.hikari.HikariDataSource
     idleTimeoutMilliseconds: 60000
     maxPoolSize: 50
     url: jdbc:mysql://1.1.1.1:3306/xxx?characterEncoding=UTF8&allowMultiQueries=true&rewriteBatchedStatements=true&serverTimezone=GMT%2B8&useSSL=false
     username: admin
   ......
   ```
   
   ```yaml
   - !SHARDING
     keyGenerators:
       SNOWFLAKE:
         type: SNOWFLAKE
     shardingAlgorithms:
       database_t_order_inline:
         props:
           algorithm-expression: ds_${Math.abs(customer_id.hashCode()) % 64}
         type: INLINE
     tables:
       t_order:
         actualDataNodes: ds_${0..63}.t_order
         databaseStrategy:
           standard:
             shardingAlgorithmName: t_order_inline
             shardingColumn: customer_id
         keyGenerateStrategy:
           column: id
           keyGeneratorName: SNOWFLAKE
         logicTable: t_order
   ......
   ```
   
   
   ### 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] github-actions[bot] commented on issue #26658: Connection occasionally closed when using the jdbc parameter `useServerPrepStmts=true`

["github-actions[bot] (via GitHub)" <gi...@apache.org>]

github-actions[bot] commented on issue #26658:
URL: https://github.com/apache/shardingsphere/issues/26658#issuecomment-1656258628

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


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