Repository: apache/skywalking (language: python)

Issue:
[Feature] Support multiple MySQL instances as a sharding cluster powered by ShardingSphere.
### Search before asking

- [X] I had searched in the [issues](https://github.com/apache/skywalking/issues?q=is%3Aissue) and found no similar feature requirement.


### Description

As ShardingSphere provides a new DistSQL concept recently, it gives us an opportunity to evaluate whether it is good enough to create dynamic routing rules automatically.

SkyWalking creates new tables(in MySQL and PostgreSQL) according to metrics definition based on MAL and OAL, the traditional configuration file based sharding is not suitable for us.

Also, we need to use `hint` mechanism to try to implement TTL deleting through `truncate`, rather than the heavy `delete`.

### Use case

Support Sharding and Time-series based rolling for metrics/logs/traces/events/profiling telemetry data in SkyWalking

### Related issues

Doc of ShardingSphere, https://shardingsphere.apache.org/document/5.1.2/en/reference/distsql/syntax/rdl/rule-definition/sharding/create-default-sharding-strategy/

### Are you willing to submit a PR?

- [ ] 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)



Discussion / hints:
Hello, it looks cool. ;-) 
Sure, [ShardingSphere](https://shardingsphere.apache.org/) not only provides the DistSQL(SQL-Like) to manage your distributed or sharding DB and table but makes itself more cloud-native on K8S by providing an [operator](https://medium.com/codex/database-plus-embracing-the-cloud-shardingsphere-on-cloud-solution-released-29916290ad06).
MySQL-Sharding is supported since 9.3.0, ref https://skywalking.apache.org/docs/main/next/en/setup/backend/backend-storage/#mysql-sharding

We are working with ShardingSphere community to expand better compatibility, because right now, this feature is only working with 5.1.2 release, but fails in 5.2.0 and 5.2.1

A blog would be posted to provide more introduction after SkyWalking's 9.3.0 release.

Fix patch:
diff --git a/docs/en/changes/changes.md b/docs/en/changes/changes.md
index b91a85258cd8..286c476fe680 100644
--- a/docs/en/changes/changes.md
+++ b/docs/en/changes/changes.md
@@ -70,6 +70,7 @@
 * Tweak interval settings of BanyanDB.
 * Support monitoring AWS Cloud EKS.
 * Bump BanyanDB Java client to 0.3.0-rc0.
+* [**Breaking Change**] The supported version of ShardingSphere-Proxy is upgraded from 5.1.2 to 5.3.1. Due to the changes of ShardingSphere's API, versions before 5.3.1 are not compatible.
 
 #### UI
 
diff --git a/docs/en/setup/backend/backend-storage.md b/docs/en/setup/backend/backend-storage.md
index edc65625d7bc..2dfd67a64ce7 100644
--- a/docs/en/setup/backend/backend-storage.md
+++ b/docs/en/setup/backend/backend-storage.md
@@ -12,7 +12,7 @@ Natively supported storage:
 - OpenSearch
 - ElasticSearch 6, 7, 8
 - MySQL
-- MySQL-Sharding(Shardingsphere-Proxy 5.1.2)
+- MySQL-Sharding(Shardingsphere-Proxy 5.3.1)
 - TiDB
 - PostgreSQL
 - BanyanDB
@@ -278,7 +278,7 @@ MySQL-Sharding plugin provides the MySQL database sharding and table sharding, t
 leverage [Shardingsphere-Proxy](https://shardingsphere.apache.org/document/current/en/overview/#shardingsphere-proxy)
 to manage the JDBC between OAP and multi-database instances, and according to the sharding rules do routing to the database and table sharding.
 
-Tested Shardingsphere-Proxy 5.1.2 version, and MySQL Client driver 8.0.13 version is currently available.
+Tested Shardingsphere-Proxy 5.3.1 version, and MySQL Client driver 8.0.13 version is currently available.
 Activate MySQL and Shardingsphere-Proxy as storage, and set storage provider to **mysql-sharding**.
 
 **NOTE:** MySQL driver is NOT allowed in Apache official distribution and source codes.
diff --git a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/ShardingSphereTableInstaller.java b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/ShardingSphereTableInstaller.java
index 685db6afcc81..252ff8688dab 100644
--- a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/ShardingSphereTableInstaller.java
+++ b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/ShardingSphereTableInstaller.java
@@ -19,6 +19,8 @@
 package org.apache.skywalking.oap.server.storage.plugin.jdbc.shardingsphere;
 
 import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
 import java.util.List;
 import java.util.Set;
 import org.apache.skywalking.oap.server.core.CoreModule;
@@ -30,6 +32,7 @@
 import org.apache.skywalking.oap.server.library.client.jdbc.JDBCClientException;
 import org.apache.skywalking.oap.server.library.client.jdbc.hikaricp.JDBCHikariCPClient;
 import org.apache.skywalking.oap.server.library.module.ModuleManager;
+import org.apache.skywalking.oap.server.storage.plugin.jdbc.TableMetaInfo;
 import org.apache.skywalking.oap.server.storage.plugin.jdbc.h2.H2TableInstaller;
 
 /**
@@ -55,7 +58,7 @@ public ShardingSphereTableInstaller(Client client,
     @Override
     public boolean isExists(Model model) throws StorageException {
         boolean isRuleExecuted = false;
-        boolean isTableExist = delegatee.isExists(model);
+        boolean isTableExist = isTableExists(model);
         JDBCHikariCPClient jdbcClient = (JDBCHikariCPClient) client;
         ConfigService configService = moduleManager.find(CoreModule.NAME).provider().getService(ConfigService.class);
         int ttl = model.isRecord() ? configService.getRecordDataTTL() : configService.getMetricsDataTTL();
@@ -65,6 +68,20 @@ public boolean isExists(Model model) throws StorageException {
         return isTableExist && !isRuleExecuted;
     }
 
+    private boolean isTableExists(Model model) throws StorageException {
+        TableMetaInfo.addModel(model);
+        JDBCHikariCPClient jdbcClient = (JDBCHikariCPClient) client;
+        try (Connection conn = jdbcClient.getConnection()) {
+            ResultSet resultSet = jdbcClient.executeQuery(conn, String.format("SHOW LOGICAL TABLES LIKE '%s'", model.getName()));
+            if (resultSet.next()) {
+                return true;
+            }
+        } catch (SQLException | JDBCClientException e) {
+            throw new StorageException(e.getMessage(), e);
+        }
+        return false;
+    }
+
     @Override
     public void start() {
         delegatee.start();
diff --git a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/dao/ShardingHistoryDeleteDAO.java b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/dao/ShardingHistoryDeleteDAO.java
index 3b1a0e87f3e8..4719e0c3b16a 100644
--- a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/dao/ShardingHistoryDeleteDAO.java
+++ b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/main/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/dao/ShardingHistoryDeleteDAO.java
@@ -90,19 +90,16 @@ public void deleteHistory(Model model, String timeBucketColumnName, int ttl) thr
             }
             List<String> realTables = new ArrayList<>();
             try (Connection connection = client.getConnection()) {
-                ResultSet resultSet = connection.getMetaData()
-                                                .getTables(connection.getCatalog(), null, model.getName() + "_20%", null);
+                ResultSet resultSet = client.executeQuery(connection, String.format("SHOW SINGLE TABLES LIKE '%s'", model.getName() + "_20%"));
                 while (resultSet.next()) {
-                    realTables.add(resultSet.getString("TABLE_NAME"));
+                    realTables.add(resultSet.getString(1));
                 }
 
                 //delete additional tables
                 for (String additionalTable : model.getSqlDBModelExtension().getAdditionalTables().keySet()) {
-                    ResultSet additionalTableRS = connection.getMetaData()
-                                                            .getTables(connection.getCatalog(), null,
-                                                                       additionalTable + "_20%", null);
+                    ResultSet additionalTableRS = client.executeQuery(connection, String.format("SHOW SINGLE TABLES LIKE '%s'", additionalTable + "_20%"));
                     while (additionalTableRS.next()) {
-                        realTables.add(additionalTableRS.getString("TABLE_NAME"));
+                        realTables.add(additionalTableRS.getString(1));
                     }
                 }
             } catch (JDBCClientException | SQLException e) {


Test patch:
diff --git a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/TCITShardingSphere.java b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/TCITShardingSphere.java
index 351d74bab6bf..af23873c686a 100644
--- a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/TCITShardingSphere.java
+++ b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/java/org/apache/skywalking/oap/server/storage/plugin/jdbc/shardingsphere/TCITShardingSphere.java
@@ -133,7 +133,7 @@ public static void setup() {
     public static Collection<Object[]> versions() {
         return Arrays.asList(new Object[][] {
             {
-                "5.1.2",
+                "26999bd02c3b1613c3508373963334d698c37361",
                 DataSourceType.MYSQL
             }
         });
diff --git a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/conf-mysql/server.yaml b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/conf-mysql/server.yaml
index dc66c7cfe4ff..a49ab5675bd2 100644
--- a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/conf-mysql/server.yaml
+++ b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/conf-mysql/server.yaml
@@ -32,31 +32,26 @@
 #      timeToLiveSeconds: 60
 #      maxRetries: 3
 #      operationTimeoutMilliseconds: 500
-#  overwrite: false
 #
-rules:
- - !AUTHORITY
-   users:
-     - root@:root
-   provider:
-     type: ALL_PERMITTED
- - !TRANSACTION
-   defaultType: XA
-   providerType: Atomikos
-   # When the provider type is Narayana, the following properties can be configured or not
-   props:
-     recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts
-     recoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
-     recoveryStoreUser: root
-     recoveryStorePassword: 12345678
- - !SQL_PARSER
-   sqlCommentParseEnabled: true
-   sqlStatementCache:
-     initialCapacity: 2000
-     maximumSize: 65535
-   parseTreeCache:
-     initialCapacity: 128
-     maximumSize: 1024
+#authority:
+#  users:
+#    - user: root@%
+#      password: root
+#  privilege:
+#    type: ALL_PERMITTED
+
+transaction:
+  defaultType: XA
+  providerType: Atomikos
+
+sqlParser:
+  sqlCommentParseEnabled: true
+  sqlStatementCache:
+    initialCapacity: 2000
+    maximumSize: 65535
+  parseTreeCache:
+    initialCapacity: 128
+    maximumSize: 1024
 
 props:
   max-connections-size-per-query: 1
@@ -78,6 +73,6 @@ props:
   sql-federation-enabled: false
     # Available proxy backend driver type: JDBC (default), ExperimentalVertx
   proxy-backend-driver-type: JDBC
-  proxy-mysql-default-version: 5.7.22 # In the absence of schema name, the default version will be used.
+  proxy-mysql-default-version: 8.0.13 # In the absence of schema name, the default version will be used.
   proxy-default-port: 3307 # Proxy default port.
   proxy-netty-backlog: 1024 # Proxy netty backlog.
diff --git a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/docker-compose-mysql.yml b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/docker-compose-mysql.yml
index e3227a2523f4..abde1e1586dc 100644
--- a/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/docker-compose-mysql.yml
+++ b/oap-server/server-storage-plugin/storage-shardingsphere-plugin/src/test/resources/docker-compose-mysql.yml
@@ -17,7 +17,7 @@ version: '2.1'
 
 services:
   sharding-proxy:
-    image: apache/shardingsphere-proxy:${SS_VERSION}
+    image: ghcr.io/apache/shardingsphere-proxy:${SS_VERSION}
     volumes:
       - ./download-mysql.sh:/opt/shardingsphere-proxy/download-mysql.sh
       - ./conf-mysql:/opt/shardingsphere-proxy/conf
diff --git a/test/e2e-v2/script/shardingsphere-proxy/base-compose.yml b/test/e2e-v2/script/shardingsphere-proxy/base-compose.yml
index 1b5efee50e32..16003782cda0 100644
--- a/test/e2e-v2/script/shardingsphere-proxy/base-compose.yml
+++ b/test/e2e-v2/script/shardingsphere-proxy/base-compose.yml
@@ -17,7 +17,7 @@ version: '2.1'
 
 services:
   sharding-proxy:
-    image: apache/shardingsphere-proxy:5.1.2
+    image: ghcr.io/apache/shardingsphere-proxy:26999bd02c3b1613c3508373963334d698c37361
     volumes:
       - ./../prepare/setup-oap/download-mysql.sh:/opt/shardingsphere-proxy/download-mysql.sh
     networks:
diff --git a/test/e2e-v2/script/shardingsphere-proxy/conf-mysql/server.yaml b/test/e2e-v2/script/shardingsphere-proxy/conf-mysql/server.yaml
index dc66c7cfe4ff..9a738fc1d0f6 100644
--- a/test/e2e-v2/script/shardingsphere-proxy/conf-mysql/server.yaml
+++ b/test/e2e-v2/script/shardingsphere-proxy/conf-mysql/server.yaml
@@ -32,31 +32,25 @@
 #      timeToLiveSeconds: 60
 #      maxRetries: 3
 #      operationTimeoutMilliseconds: 500
-#  overwrite: false
 #
-rules:
- - !AUTHORITY
-   users:
-     - root@:root
-   provider:
-     type: ALL_PERMITTED
- - !TRANSACTION
-   defaultType: XA
-   providerType: Atomikos
-   # When the provider type is Narayana, the following properties can be configured or not
-   props:
-     recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts
-     recoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
-     recoveryStoreUser: root
-     recoveryStorePassword: 12345678
- - !SQL_PARSER
-   sqlCommentParseEnabled: true
-   sqlStatementCache:
-     initialCapacity: 2000
-     maximumSize: 65535
-   parseTreeCache:
-     initialCapacity: 128
-     maximumSize: 1024
+#authority:
+#  users:
+#    - user: root@%
+#      password: root
+#  privilege:
+#    type: ALL_PERMITTED
+
+transaction:
+  defaultType: LOCAL
+
+sqlParser:
+  sqlCommentParseEnabled: true
+  sqlStatementCache:
+    initialCapacity: 2000
+    maximumSize: 65535
+  parseTreeCache:
+    initialCapacity: 128
+    maximumSize: 1024
 
 props:
   max-connections-size-per-query: 1
@@ -67,17 +61,17 @@ props:
   check-table-metadata-enabled: false
   show-process-list-enabled: false
     # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
-    # The default value is -1, which means set the minimum value for different JDBC drivers.
+  # The default value is -1, which means set the minimum value for different JDBC drivers.
   proxy-backend-query-fetch-size: -1
   check-duplicate-table-enabled: false
   proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
     # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
-    # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
+  # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
   proxy-backend-executor-suitable: OLAP
   proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
   sql-federation-enabled: false
-    # Available proxy backend driver type: JDBC (default), ExperimentalVertx
+  # Available proxy backend driver type: JDBC (default), ExperimentalVertx
   proxy-backend-driver-type: JDBC
-  proxy-mysql-default-version: 5.7.22 # In the absence of schema name, the default version will be used.
+  proxy-mysql-default-version: 8.0.13 # In the absence of schema name, the default version will be used.
   proxy-default-port: 3307 # Proxy default port.
   proxy-netty-backlog: 1024 # Proxy netty backlog.