weixiang1862 opened a new issue, #11009: URL: https://github.com/apache/skywalking/issues/11009
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/skywalking/issues?q=is%3Aissue) and found no similar issues. ### Apache SkyWalking Component OAP server (apache/skywalking) ### What happened I use mysql as my skywalking storage backend, so I add `mysql-connector-java:8.0.20` to classpath. The oap server throw the following exception when persistent metrics reported by otel collector: ``` java.sql.BatchUpdateException: Duplicate entry 'meter_xxx_bW9uZ29kYjo6Y2x1c3Rlcg==.' for key 'PRIMARY' at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?] at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:?] at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?] at java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[?:?] at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.util.Util.getInstance(Util.java:167) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.util.Util.getInstance(Util.java:174) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:755) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796) ~[mysql-connector-java-8.0.20.jar:8.0.20] at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.1.0.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.1.0.jar:?] at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.executeBatch(BatchSQLExecutor.java:84) ~[classes/:?] at org.apache.skywalking.oap.server.storage.plugin.jdbc.BatchSQLExecutor.invoke(BatchSQLExecutor.java:73) ~[classes/:?] at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.lambda$flush$1(JDBCBatchDAO.java:78) ~[classes/:?] at java.util.HashMap.forEach(HashMap.java:1337) ~[?:?] at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCBatchDAO.flush(JDBCBatchDAO.java:75) ~[classes/:?] at org.apache.skywalking.oap.server.core.storage.PersistenceTimer.lambda$extractDataAndSave$3(PersistenceTimer.java:135) ~[classes/:?] at java.util.concurrent.CompletableFuture$AsyncRun.run$$$capture(CompletableFuture.java:1736) [?:?] at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java) [?:?] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?] at java.lang.Thread.run(Thread.java:834) [?:?] ``` I found `BatchSQLExecutor` only accept `1` as legal value and then put the metric to sessionCache after execute batch insert： ``` private void executeBatch(PreparedStatement preparedStatement, int pendingCount, String sql, List<PrepareRequest> bulkRequest) throws SQLException { final var start = System.currentTimeMillis(); final var executeBatchResults = preparedStatement.executeBatch(); final var isInsert = bulkRequest.get(0) instanceof InsertRequest; for (int i = 0; i < executeBatchResults.length; i++) { ----> if (executeBatchResults[i] == 1 && isInsert) { // Insert successfully. ((InsertRequest) bulkRequest.get(i)).onInsertCompleted(); } else if (executeBatchResults[i] == 0 && !isInsert) { // Update Failure. ((UpdateRequest) bulkRequest.get(i)).onUpdateFailure(); } } if (log.isDebugEnabled()) { long end = System.currentTimeMillis(); long cost = end - start; log.debug("execute batch sql, batch size: {}, cost:{}ms, sql: {}", pendingCount, cost, sql); } } ``` Unfortunately, the mysql-connector-java return `-2` after successful batch insert (h2 driver return 1):  And I found `java.sql.Statement.SUCCESS_NO_INFO` is also a legally value when successfuly execute `executeBatch` method in jdbc statement define:  ### What you expected to happen Cache the metrics when `preparedStatement.executeBatch()` returns `java.sql.Statement.SUCCESS_NO_INFO`, then the MetricsPersistentWorker can using update to handing exist metric rather than insert. ### How to reproduce 1. set SW_STORAGE to mysql in application.yaml 2. add mysql-connector-java dependency ``` <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> ``` 3. execute any batch insert and watch the return value of `final var executeBatchResults = preparedStatement.executeBatch();`  ### Anything else _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
