[I] For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database. [shardingsphere]

["pc7364 (via GitHub)" <gi...@apache.org>]

pc7364 opened a new issue, #35054:
URL: https://github.com/apache/shardingsphere/issues/35054

   For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database. How can I achieve this custom routing strategy?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database. [shardingsphere]

["github-actions[bot] (via GitHub)" <gi...@apache.org>]

github-actions[bot] commented on issue #35054:
URL: https://github.com/apache/shardingsphere/issues/35054#issuecomment-3544217136

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database. [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #35054:
URL: https://github.com/apache/shardingsphere/issues/35054#issuecomment-3506365698

   Issue Analysis
   
     I understand you want to implement read-write separation in ShardingSphere 5.5.0 with a specific requirement:
     - All read requests should default to the primary database
     - Only specific SQL queries should be directed to the secondary database
   
     This is not a bug but a configuration requirement. ShardingSphere supports this use case through several approaches.
   
     Recommended Solutions
   
     Solution 1: Hint-based Routing (Recommended)
   
     Use Hint manager to force route queries to the primary database:
   
     // For specific queries that should go to secondary database
     try (HintManager hintManager = HintManager.getInstance()) {
         hintManager.setWriteRouteOnly(); // Force route to primary
         // Execute your query that should go to primary
         jdbcTemplate.query("SELECT * FROM table_name", rowMapper);
     }
   
     // For regular queries (remove hint to use default routing)
     try (HintManager hintManager = HintManager.getInstance()) {
         hintManager.setReadRouteOnly(); // Force route to secondary
         // Execute specific queries that should go to secondary
         jdbcTemplate.query("SELECT * FROM table_name WHERE condition", rowMapper);
     }
   
     Solution 2: Load Balance Configuration
   
     Configure your read data sources with minimal weight to minimize secondary database usage:
   
     dataSources:
       primary_ds:
         # Your primary database configuration
       secondary_ds:
         # Your secondary database configuration
   
     readWriteSplitting:
       dataSources:
         read_write_ds:
           writeDataSourceName: primary_ds
           readDataSourceNames:
             - secondary_ds
           loadBalancerName: round_robin
       loadBalancers:
         round_robin:
           type: ROUND_ROBIN
           props:
             # Set minimal weight for secondary
             secondary_ds: "1"
   
     Solution 3: Custom SQL Hint (Most Flexible)
   
     Use SQL hints to control routing for specific queries:
   
     -- Force route to primary database
     /* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY */
     SELECT * FROM table_name;
   
     -- Force route to secondary database  
     /* SHARDINGSPHERE_HINT: READ_ROUTE_ONLY */
     SELECT * FROM table_name WHERE specific_condition;
   
     Questions for Better Assistance
   
     To provide the most suitable solution, I need more information:
   
     1. Business Scenario: Why do you need read requests to default to the primary database? Is it for data consistency, performance, or other reasons?
     2. Specific SQL Definition: What defines the "specific SQL queries" that should go to the secondary database? Is it based on:
       - Table names?
       - SQL conditions?
       - Query complexity?
       - Time sensitivity?
     3. Current Configuration: Do you already have a read-write separation configuration in place?
     4. Usage Mode: Are you using ShardingSphere-Proxy or ShardingSphere-JDBC?
     5. Database Setup: What is your primary/secondary database configuration?
   
     Recommendation
   
     Based on typical use cases, I recommend Solution 1 (Hint-based Routing) as it provides:
     - Fine-grained control over query routing
     - Easy to implement and maintain
     - Clear separation of routing logic
     - No complex configuration changes
   
     Would you like me to provide a more detailed configuration example based on your specific requirements?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database. [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu closed issue #35054: For version 5.5.0, I want to implement read-write separation. However, I want all read requests to default to querying the primary database, and only specific SQL queries to be directed to the secondary database.
URL: https://github.com/apache/shardingsphere/issues/35054


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org