Issue 1:
Multi-values insertion bug with key-generator in sharding databases/tables

We can reproduce the bug in a rule:

shard db by id%2 : ds_0,ds_1

shard table by id%3 : user0,user1,user2

Case 1, literal multi-values insertion

Execute：

insert into user(name,age) values ('name-11',11),('name-12',12),('name-13',13),('name-14',14)

Actual SQL：

..ds_1 ::: insert into user1(`name`,`age`, id) values ('name-11', 11, 441401574401507332)
..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-12', 12, 441401574401507331)
..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-13', 13, 441401574401507330)
..ds_0 ::: insert into user1(`name`,`age`, id) values ('name-14', 14, 441401574401507329)

This are two issues：

ids in descending order

wrong sharding tables, lead to that those data can't be selected

The first row, id=441401574401507332，id%3=>1，so shard to table user1，it's right。

The second row, id=441401574401507331，id%3=>0，so should shard to table user0，but shard to table user2, it's wrong。

The second row, id%3=>2，so should shard to table user2，but shard to table user0, it's wrong, too。

The last row, id%3=>1，so shard to table user1，it's right。

We can find if make ids reverse, remainders are 1,2,0,1，just match the sharding tables number.

We learn more into shardingsphere sourcecodes，it generator 4 keys and binding them to sharding tables at first, like the ascending order. And then binding keys and literal columns fields(name, age)，it reverse keys to descending order by List.descendingIterator. Finally, each key orders can't match its own row.

The related codes here：

org.apache.shardingsphere.sharding.rewrite.token.generator.impl.keygen.GeneratedKeyInsertValuesTokenGenerator：generateSQLToken()
Iterator<Comparable<?>> generatedValues = generatedKey.getGeneratedValues().descendingIterator();

Case 2, multi-values insertion with placeholders

SQL：

insert into user(name,age) values (?,?),(?,?),(?,?),(?,?)

Java codes：

try(PreparedStatement preparedStatement = con.prepareStatement(sql)){
for (int i = 0; i < 4; i++){
preparedStatement.setString(i * 2 + 1,"name-" + i);
preparedStatement.setInt(i * 2 + 2,i);
}
preparedStatement.execute();
}

Actual SQL：

..ds_0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 0, 441306093067436035]
..ds_1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 1, 441306093067436034]
..ds_0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 2, 441306093067436033]
..ds_1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 3, 441306093067436032]

The same problem，but codes is in different class：

org.apache.shardingsphere.sharding.rewrite.parameter.impl.ShardingGeneratedKeyInsertValueParameterRewriter：rewrite()
Iterator<Comparable<?>> generatedValues = shardingRouteContext.getGeneratedKey().get().getGeneratedValues().descendingIterator();

This case is also reported by issue #4306 .

fix

We will fix it by replace descendingIterator to iterator.

Issue 2:
Background

At present, when community users use ShardingSphere, they report that due to the large number of managed data sources, a large number of single tables are loaded into the memory by default, resulting in slower startup speed and taking up more memory space.

Considering that in the data source registered by the user, there are a large number of single tables that do not need to be used or managed by ShardingSphere, so it is necessary to design a mechanism that allows users to actively import single tables on demand, avoiding automatic Load single table metadata.

Metadata loading logic optimization

Start-up phase

According to the process of the startup phase, the automatic loading of a single table mainly depends on the automatic loading logic in SingleTableRule, so the loading logic needs to be removed. In addition, the SingleTableRuleConfiguration configuration needs to be added to support the needs of JDBC end users to import a single table, and the DistSQL LOAD statement configuration is stored through SingleTableRuleConfiguration to persist the YAML configuration to ZK. SingleTableRuleConfiguration is configured as follows:

# YAML configuration
databaseName: sharding_db

- !SINGLE
tables:
- ds_0.t_single
# PostgreSQL, openGauss support multiple schemas
- ds_1.public.t_config,ds_1.test.t_config
- ds_2.*
- ds_3.*
- *.*
# controls CREATE TABLE routing, required in DistSQL
defaultDataSource: ds_0

# ZK structure (the original YAML will be loaded into a single table and then expanded and stored)
- !SINGLE
tables:
- ds_0.t_single
# PostgreSQL, openGauss support multiple schemas
- ds_1.public.t_config,ds_1.test.t_config
- ds_2.t_1,ds_2.t_2
- ds_3.t_3,ds_3.t_4
- ds_4.t_5,ds_4.t_6
# controls CREATE TABLE routing, required in DistSQL
defaultDataSource: ds_0

The tables attribute allows users to configure single tables to be loaded, supports MySQL-style database.table configuration, and PostgreSQL/openGauss-style database.schema.table configuration. If users need to load all configurations, they can import all single tables under a certain data source through the * wildcard. If the user-configured data source is in a logical data source, the data source will be converted to a logical data source.

defaultDataSource is used to support the current DistSQL statement SET DEFAULT SINGLE TABLE STORAGE UNIT = ds_0, defaultDataSource will be used for CREATE TABLE statement routing processing;

The optimized new processing flow is as follows:

In the startup phase, the DatabaseRuleConfiguration will be initialized first. When the SingleTableRuleConfiguration is initialized, the single table will be loaded according to the YAML configuration, and the expanded configuration will be built, then stored in the SingleTableRuleConfiguration, and then the SingleTableRuleConfiguration will be persisted in the ZK;

Initialize DatabaseRule according to RuleConfiguration, and SingleTableRule initialization will load a single table according to the tables attribute configured in SingleTableRuleConfiguration;

Get all the tables according to the TableContainedRule interface method, and load the real metadata;

Decorate real metadata as logical metadata;

Metadata is persisted to ZK and synchronized to each instance;

Register storage unit

Execute REGISTER STORAGE UNIT DistSQL, the optimized execution flow is as follows:

Call RegisterStorageUnitBackendHandler and verify the connectivity of the storage data source;

Then call the ContextManager#addResources method to add storage resources through the management center;

Call the ShardingSphereDatabasesFactory.create method to recreate the logic library object for adding storage resources (this step is consistent with the logic of loading metadata in the startup phase, first initialize SingleTableRuleConfiguration, and then initialize SingleTableRule, SingleTableRule will load a single table according to SingleTableRuleConfiguration, metadata loading engine Load according to Table in Rule);

Replace the corresponding ShardingSphereDatabase object in ShardingSphereMetaData.

Execute DDL statements

Execute the DDL statement because the logic itself is to load metadata according to a certain table, so the logic of loading metadata does not need to be adjusted, but after executing the DDL statement, refresh the SingleTableRuleConfiguration, persist the newly created single table into ZK, and synchronize to individual instances. The new processing flow is as follows:

Process DDL statements through KernelProcessor, execute and merge;

MetaDataRefreshEngine calls CreateTableStatementSchemaRefresher to execute metadata refresh logic;

CreateTableStatementSchemaRefresher internally calls the GenericSchemaBuilder#build method to load and decorate the metadata of the new table;

Synchronize metadata information through other Proxy nodes through the SchemaAlteredEvent event;

Through the RuleConfigurationsChangedEvent event, the single table added or deleted by the DDL statement is synchronized to zk and each node;

New DistSQL

LOAD SINGLE TABLE ds_0.t_single, ds_1.public.t_config, ds_1.test.t_config [INTO databaseName];
LOAD SINGLE TABLE ds_2.*, ds_3.* [INTO databaseName];

-- DistSQL queries the underlying used and unused single tables, not loaded into memory
SHOW SINGLE TABLES
SHOW UNLOADED SINGLE TABLES

Development task

Rename SingleTableXXX to SingleXXX, such as SingleTableRuleConfiguration to SingleRuleConfiguration

Add SingleTableRuleConfiguration tables configuration, and implement configuration persistence & initialization loading single table logic;

SingleTableRule load single table logic adjustment, load according to SingleTableRuleConfiguration configuration, no configuration will not be loaded;

MetaDataRefresher adds SingleTableRuleConfiguration refresh after DDL statement is executed;

Add LOAD, UNLOAD, SHOW LOADED TABLE, SHOW UNLOADED TABLE DistSQL

Add test case

Add document description