Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

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

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

Unable to recreate a dropped policy with the same name in PostgreSQL-protocol ShardingSphere-Proxy.

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

ShardingSphere-Proxy is started using the PostgreSQL protocol, with metadata persisted in MySQL.

1. A database ds_3_tna_asgwas created. Storage units were configured on this database, followed by the creation of an encryption rule:

CREATE ENCRYPT RULE test02 (
    COLUMNS (
        (NAME = occupation,
         CIPHER = occupation,
         ENCRYPT_ALGORITHM (
             TYPE (NAME = 'DBESShardingEncrypt',
                   PROPERTIES ('sm3' = 2, 'sm4-key' = '662ea927fbbe4ae791f1f3d4cdc191ce'))
         )
    ))
);

At this stage, executing INSERTstatements into the test02table via ShardingSphere-Proxy resulted in successful data encryption.
2. After executing DROP ENCRYPT RULE test02, the SHOW ENCRYPT RULES;command confirmed that the test02rule no longer existed in the result set. However, inserting data into test02at this point still resulted in encrypted data being written to the database.
3. Subsequently, after executing LOAD SINGLE TABLE ds_3_tna_asg.., attempting to insert into test02caused an error: ERROR: Table or view 'test02' does not exist.
4. When trying to recreate the encrypt rule by running CREATE ENCRYPT RULE test02again, ShardingSphere-Proxy threw an error: Duplicate encrypt rule names 'test02' in database 'ds_3_tna_asg'. However, SHOW ENCRYPT RULESconfirmed that the test02rule did not exist. Checking the MySQL repository tables also showed no trace of the test02rule.
5. Only after restarting ShardingSphere-Proxy could the CREATE ENCRYPT RULE test02statement be executed successfully.

Example codes for reproduce this issue (such as a github link).