You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "RaigorJiang (via GitHub)" <gi...@apache.org> on 2026/04/11 16:03:57 UTC
[I] Proxy may lose null parameter when executing prepared statement [shardingsphere]
RaigorJiang opened a new issue, #38612:
URL: https://github.com/apache/shardingsphere/issues/38612
## Bug Report
### Which version of ShardingSphere did you use?
5.5.4-SNAPSHOT 5f7e8abe7079815f79515f33154122701aae2920
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
The goal is for the proxy (MySQL protocol) to behave consistently with native MySQL.
### Actual behavior
When executing PreparedStatement and specifying the only param value as null, native MySQL executes successfully, while proxy reports an error.
```java
@SneakyThrows
private static void queryWithPreparedStatement(final HikariDataSource dataSource) {
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT * FROM t_single where name = ?")) {
log.info("jdbcUrl: {}", dataSource.getJdbcUrl());
statement.setString(1, null);
// statement.setString(1, "name");
statement.execute();
ResultSet resultSet = statement.getResultSet();
while (resultSet.next()) {
log.info("{}, {}, {}", resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
}
}
}
```
- Native MySQL
<img width="1211" height="209" alt="Image" src="https://github.com/user-attachments/assets/43dc0dc5-c293-4cc8-8c50-79e538d950e2" />
- Proxy
<img width="1109" height="340" alt="Image" src="https://github.com/user-attachments/assets/50055c7a-764a-476c-a82d-e5e8185802f8" />
### Reason analyze (If you can)
When only one parameter is NULL, the MySQL client driver (such as MySQL Connector/J) employs the following optimization strategy:
- Short form optimization: When all parameters are detected to be NULL, the driver may choose to send the data packet in short form, without sending parameter type information, because:
- NULL values do not require specific data type information for decoding
- Reduce network transmission overhead
- Assume the server can handle the data based on type information obtained during the pre-compilation stage
- Missing parameter type information:
- In the MySQLComStmtExecutePacket constructor, if newParametersBoundFlag is not PARAMETER_TYPE_EXIST, newParameterTypes is set to Collections.emptyList()
- This results in preparedStatement.getParameterTypes() also being empty, thus preventing the setting of params.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
- Create logic database
```sql
create database sharding_db;
use sharding_db;
```
- Register storage unit
```sql
REGISTER STORAGE UNIT ds_0 (
URL="jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true",
USER="root",
PASSWORD="123456",
PROPERTIES("maximumPoolSize"=10,"minimumIdle"=1)
);
```
- Create table
```sql
DROP TABLE IF EXISTS t_single;
CREATE TABLE `t_single` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`pwd` varchar(50) NOT NULL,
`addr` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
);
```
- Insert data
```sql
INSERT INTO t_single (name, pwd, addr) values ('name', 'pwd', 'addr');
SELECT * FROM t_single;
```
- Execute query from JDBC client
--
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
Re: [I] Proxy may lose null parameter when executing prepared statement [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #38612:
URL: https://github.com/apache/shardingsphere/issues/38612#issuecomment-4231121344
Hi @ym0506
A reasonable approach is to check the nullBitmap in MySQLComStmtExecutePacket to determine if there are any null parameters.
I have completed the coding and local testing and am submitting a PR. Thank you for your help.
--
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
Re: [I] Proxy may lose null parameter when executing prepared statement [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #38612: Proxy may lose null parameter when executing prepared statement
URL: https://github.com/apache/shardingsphere/issues/38612
--
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
Re: [I] Proxy may lose null parameter when executing prepared statement [shardingsphere]
Posted by "ym0506 (via GitHub)" <gi...@apache.org>.
ym0506 commented on issue #38612:
URL: https://github.com/apache/shardingsphere/issues/38612#issuecomment-4230469325
Thanks for the detailed repro and analysis. I took a look at [`MySQLComStmtExecutePacket`](https://github.com/apache/shardingsphere/blob/5f7e8abe7079815f79515f33154122701aae2920/database/protocol/dialect/mysql/src/main/java/org/apache/shardingsphere/database/protocol/mysql/packet/command/query/binary/execute/MySQLComStmtExecutePacket.java) and [`MySQLComStmtExecuteExecutor`](https://github.com/apache/shardingsphere/blob/5f7e8abe7079815f79515f33154122701aae2920/proxy/frontend/dialect/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/command/query/binary/execute/MySQLComStmtExecuteExecutor.java), and the likely gap seems to be:
- when `new_params_bind_flag = 0`, `MySQLComStmtExecutePacket` leaves `newParameterTypes` empty
- `MySQLComStmtExecuteExecutor#updateAndGetPreparedStatement()` only refreshes cached `parameterTypes` when the flag is `PARAMETER_TYPE_EXIST`
- on the first execute with null-only params, `preparedStatement.getParameterTypes()` is still empty, so `readParameters(...)` ends up losing the null slot instead of preserving the expected parameter list
So this looks a bit broader than the single-parameter repro: it should affect the first execute whenever the client omits execute-time type metadata and the proxy does not have cached execute types yet. The MySQL [`COM_STMT_EXECUTE`](https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_stmt_execute.html) packet format also suggests the proxy needs a fallback path for this case.
My initial fix direction would be to add a fallback for this branch so the proxy can still preserve parameter count / null slots when execute-time types are omitted on the first execution, and then cover it with both packet-level and executor-level tests. If that direction sounds reasonable, I can work on a PR.
--
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