Invalid binding table configuration for alphabetical sharding keys in ShardingSphere-JDBC 5.5.2

[EvaDongCS]

Bug Report

Which version of ShardingSphere did you use?

5.5.2

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

ShardingSphere-JDBC

Expected behavior

When my sharding key is alphabetical, I expect to successfully configure the binding tables t_order and t_order_item.

Actual behavior

An exception occurs when configuring the binding tables:
Caused by: org.apache.shardingsphere.sharding.exception.metadata.InvalidBindingTablesException: Invalid binding table configuration.

Reason analyze (If you can)

The DATA_NODE_SUFFIX_PATTERN uses \\d+$, which causes the system to expect the suffix of the binding tables to be numeric. However, the actual table names t_order_a and t_order_item_a have alphabetical suffixes, leading to the configuration being rejected.

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

1. Create tables t_order and t_order_item, with their sharding tables named t_order_a and t_order_item_a.
2. Configure the binding tables as follows:

bindingTables:

• t_order,t_order_item

3. Start the application and observe the exception.

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

[tengyangcs]

same issue, any update for this?

[rujiaxiao]

same issue, any update for this?

[terrymanu]

Community's Reluctance to Change

1. Design Philosophy: Logic Table Over Actual Table

ShardingSphere adopts a Logic Table abstraction layer design:

• Users interact with logical tables (e.g., t_order), ShardingSphere handles routing to actual tables
• Actual table naming should be completely transparent to the application layer
• The flexibility of alphabetical suffixes primarily affects actual tables, offering limited user value

2. Lessons from 2022 Change Failure

Git history reveals an important failure case:

• August 2022: Attempted to support complex pattern (\d+[\-_]){0,}(\d+$)
• February 2023: Major rollback - the change caused compatibility issues and system instability
• Lesson: Overly flexible design brings unpredictable risks

3. Industry Standards and Performance Considerations

• Numeric sharding is the industry standard for distributed databases
• Numeric operations perform better than string matching
• Simple, consistent patterns ensure routing algorithm predictability

Enhanced Error Message Improvement Suggestions

Based on not modifying core logic, we suggest adopting enhanced error messages to improve user experience:

Recommended Changes

1. Extend InvalidBindingTablesException Class

  public final class InvalidBindingTablesException extends ShardingSQLException {

      public InvalidBindingTablesException() {
          super(XOpenSQLState.CHECK_OPTION_VIOLATION, 10, "Invalid binding table configuration.");
      }

      public InvalidBindingTablesException(final String message) {
          super(XOpenSQLState.CHECK_OPTION_VIOLATION, 10, message);
      }
  }

2. Improve Error Detection Logic

Suggest improving existing validation logic to provide detailed error messages:

  String errorMessage = validateBindingTableConfigurationWithDetails(...);
  ShardingSpherePreconditions.checkState(errorMessage == null,
      () -> new InvalidBindingTablesException(errorMessage));

3. Special Prompts for Alphabetical Suffixes

Suggest implementing error messages like:
Alphabetical table suffixes are not supported in binding tables 't_order,t_order_item'.
ShardingSphere only supports numeric suffixes (e.g., table_0, table_1, table_2) for binding tables
to ensure stable routing performance and avoid configuration complexity.

Design Philosophy:

• Users interact with logical tables (t_order), actual table naming is transparent
• Numeric suffixes follow industry standards and provide better performance
• Complex suffix patterns led to system instability in previous attempts (2022)

Consider using numeric suffixes or semantic naming through table prefixes instead.

Community Participation Invitation

Areas needing community contributor help:

1. Error Message Design: Help design more friendly and educational error messages
2. Internationalization Support: Provide multi-language support for error messages
3. Test Case Writing: Write comprehensive test cases for new error message logic
4. Documentation Updates: Update relevant documentation explaining design principles and best practices

Implementation Priority:

1. High Priority: Basic error message enhancement
2. Medium Priority: Special detection and prompts for alphabetical suffixes
3. Low Priority: Detailed prompts for other binding table configuration errors

Best Practice Recommendations

For users encountering alphabetical suffix issues, we recommend adopting the following configuration solutions:

Recommended Configuration

Option 1: Use numeric suffixes

  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..3}
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..3}

Option 2: Implement semantics through prefixes

  tables:
    order_current:
      actualDataNodes: ds_${0..1}.order_current_${0..3}
    order_archive:
      actualDataNodes: ds_${0..1}.order_archive_${0..3}

Application Layer Access

  // Application code only cares about logical tables, actual table naming is transparent
  String sql = "SELECT * FROM t_order WHERE order_id = ?";
  // ShardingSphere automatically routes to the correct actual table

Conclusion

This solution improves user experience through enhanced error messages without changing ShardingSphere's core design principles, maintaining system stability while providing clear guidance to users.

ShardingSphere should maintain simple design principles, focusing on core functionality: stable, reliable, high-performance data sharding. The flexibility of actual table naming is not a core value of the project. We
recommend users implement business semantics through logical tables rather than relying on actual table naming conventions.

We welcome community contributors to participate in this improvement to collectively enhance ShardingSphere's user-friendliness.