Can not query the new added column

[a1104321118]

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

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

ShardingSphere-JDBC

Expected behavior

select results contains the column which added just now.

Actual behavior

Not contains.

Reason analyze (If you can)

1. org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource#contextManager
   this parameter cached the table scheam, column names and others, but when add a new column, it is not changed.
   Or is any config wrong in my code ?

public DataSource shardingDataSource() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>(1);
        String datasourceName = "ds0";

        dataSourceMap.put(datasourceName, dataSource());

        
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.setShardingAlgorithms(ShardingConfig.ALGORITHM_MAP);
        shardingRuleConfig.setTables(ShardingConfig.getAllConfig());

        Properties properties = new Properties();
//        properties.put("sql-show", "true");

        ModeConfiguration modeConfiguration = new ModeConfiguration("Memory", null, true);
        

        return ShardingSphereDataSourceFactory.createDataSource(modeConfiguration, dataSourceMap,
            Collections.singletonList(shardingRuleConfig), properties);
    }

2.org.apache.shardingsphere.driver.jdbc.core.resultset.ShardingSphereResultSetMetaData#getColumnCount

@Override
    public int getColumnCount() throws SQLException {
        if (sqlStatementContext instanceof SelectStatementContext) {
            if (hasSelectExpandProjections()) {
                // there will return n (not include the new column)
                return ((SelectStatementContext) sqlStatementContext).getProjectionsContext().getExpandProjections().size();
            }
            // there will return n+1 (include the new column)
            return resultSetMetaData.getColumnCount();
        }
        return resultSetMetaData.getColumnCount();
    }

3.org.apache.shardingsphere.infra.binder.segment.select.projection.impl.ShorthandProjection#actualColumns

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

0. create a new table, and query it.(Use Sharding-JDBC，sql use select *)
1. add a new column. （alter table table add column new_column varchar(255)）
2. re-query it.(Use Sharding-JDBC,sql use select *, do not restart the app)
3. if the app not restart(to ensure the cache not re-build or refresh), jdbc will return all columns, but sharding-jdbc will not mapping the new_column)

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

[azexcy]

@TeslaCN Could you help to have a look this issue?

[TeslaCN]

This is expected behavior. It's recommended to deploy ShardingSphere-JDBC and ShardingSphere-Proxy using cluster mode. Executing DDL to database directly will cause metadata inconsistency.

[a1104321118]

This is expected behavior. It's recommended to deploy ShardingSphere-JDBC and ShardingSphere-Proxy using cluster mode. Executing DDL to database directly will cause metadata inconsistency.

It means when I use sharding-jdbc and select *, I can not add or drop column when not researt the app ?

Is there has any infterface / chance to update the metadata cache ?

[TeslaCN]

This is expected behavior. It's recommended to deploy ShardingSphere-JDBC and ShardingSphere-Proxy using cluster mode. Executing DDL to database directly will cause metadata inconsistency.

It means when I use sharding-jdbc and select *, I can not add or drop column when not researt the app ?

Is there has any infterface / chance to update the metadata cache ?

Using Cluster mode could hot reload metadata. There is no such reload method in ShardingSphere-JDBC public API.

[a1104321118]

This is expected behavior. It's recommended to deploy ShardingSphere-JDBC and ShardingSphere-Proxy using cluster mode. Executing DDL to database directly will cause metadata inconsistency.

It means when I use sharding-jdbc and select *, I can not add or drop column when not researt the app ?
Is there has any infterface / chance to update the metadata cache ?

Using Cluster mode could hot reload metadata. There is no such reload method in ShardingSphere-JDBC public API.

Thanks，By the way，Do you have recommand document for shardingsphere 5.x ? I Have read official doc, But It is not helpful for me. For example, I can not find useful message about ModeConfiguration

[TeslaCN]

You may refer to:

• https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/mode/
• https://github.com/apache/shardingsphere/tree/master/examples
• https://github.com/apache/shardingsphere/blob/1c26a66b7ed195da5b3dcf02e2aeedcd5c7978f6/proxy/bootstrap/src/main/resources/conf/server.yaml

[a1104321118]

您好  我已经收到你的邮件，谢谢！HR