terrymanu commented on issue #38717: URL: https://github.com/apache/shardingsphere/issues/38717#issuecomment-4553998428
Hi @dangmingyang, thanks for the detailed report. This looks like a bug in the ShardingSphere-Proxy MySQL INSERT response path, not a usage issue. The INSERT itself reaches the backend and commits, but Proxy appears to return an incorrect `last_insert_id` for the explicit signed auto-increment value `-3`, which then becomes `18446744073709551613` on the MySQL client side. The likely area to fix is the generated-key response handling around `GeneratedKeyContextEngine`, `StandardDatabaseProxyConnector`, and `UpdateResponseHeader`. We should not fabricate a generated key / `last_insert_id` for an auto-increment column value that was explicitly supplied by the client. I suggest labeling this as `type: bug`, `in: Proxy`, and `db: MySQL`. A fix should include a regression case for Proxy + MySQL + rule-managed table, with an explicit negative value inserted into a signed `AUTO_INCREMENT` column, and verify that the row is committed without returning a spurious generated key. The reply above is based on the analysis below; the detailed reasoning is kept here for reference and follow-up contributors. ### Problem Understanding - **Issue:** Explicit negative value inserted into a signed MySQL `AUTO_INCREMENT` column through Proxy fails on the client, while the row is committed. - **Topology:** ShardingSphere-Proxy 5.5.3 + MySQL + ENCRYPT rule. - **Observed Evidence:** Issue body, Proxy generated-key response path, MySQL OK packet path. ### Root Cause - **Observation:** The issue reports that `INSERT INTO dmy_1 (id, name, pwd) VALUES (-3, ...)` returns `[22001] Value '18446744073709551613' is outside of valid range for type java.lang.Long`. - **Observation:** `GeneratedKeyContextEngine` collects explicit values from generated-key columns. - **Observation:** `StandardDatabaseProxyConnector` passes auto-increment generated values into `UpdateResponseHeader`. - **Observation:** `UpdateResponseHeader` uses those values to calculate `lastInsertId`, and MySQL OK packet writes that value to the client. - **Inference:** Proxy is treating an explicit client-supplied auto-increment value as a generated-key response value. - **Confidence:** High ### Problem Analysis - **Issue Type:** Bug - **Evidence:** The row is committed, but the client receives an incorrect generated-key/`last_insert_id` response. - **Compatibility Checklist:** Behavior affected; config unaffected; API/SPI unaffected; SQL compatibility limited to MySQL Proxy INSERT response behavior. ### Code-Level Design Suggestions - **Affected Modules:** `infra/binder/core`, `proxy/backend/core`, `proxy/frontend/dialect/mysql` - **Key Classes:** `GeneratedKeyContextEngine`, `StandardDatabaseProxyConnector`, `UpdateResponseHeader`, `MySQLOKPacket` - **Required Test Scope:** Add regression tests for explicit signed negative auto-increment values under rule-managed tables. - **Rollback Hint:** Revert the generated-key response change if it affects existing generated-key behavior. ### Problem Conclusion - **Evidence Confidence:** High - **Severity:** S2 - **Impact Scope:** Proxy + MySQL + rule-managed INSERT into signed auto-increment column - **Topology:** Proxy - **Issue Type:** Bug - **Recommended Labels:** `type: bug`, `in: Proxy`, `db: MySQL` - **Next Action:** Fix generated-key response handling and add regression tests. - **Compatibility:** Behavior - **Regression Scope:** Explicit generated-key values, negative numeric values, ENCRYPT/rule-managed tables, MySQL OK packet `last_insert_id` -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
