You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@shardingsphere.apache.org by "hua74ni (via GitHub)" <gi...@apache.org> on 2023/06/02 07:09:27 UTC

[GitHub] [shardingsphere] hua74ni opened a new issue, #26012: Single table SQL error "select *"

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

   ## Bug Report
   
   **For English only**, other languages will not accept.
   
   Before report a bug, make sure you have:
   
   - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
   
   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.2.1
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   ### Expected behavior
   
   Execute a single table SQL Select *, MySQL table dictionary (5 fields), table dictionary add a field (such as test, 6 fields), service restart execution dictionary select * or keep reporting errors, cannot be automatically repaired, I need to delete the zk node to not report an error.
   
   ### Actual behavior
   
   service restart execution dictionary select * or keep reporting errors, cannot be automatically repaired, I need to delete the zk node to not report an error.
   
   ### Reason analyze (If you can)
   
   The shardingsphere zk metadata table DDL is inconsistent with mysql and is not automatically updated 
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   Start with table  `dictionary` DDL:
   
   ```
   CREATE TABLE `dictionary` (
     `id` char(36) NOT NULL COMMENT '主键id',
     `name` varchar(63) NOT NULL COMMENT 'canal服务节点名称',
     `code` varchar(63) NOT NULL COMMENT 'canal服务节点ip',
     `time_create` bigint(20) NOT NULL COMMENT '创建时间',
     `time_update` bigint(20) NOT NULL COMMENT '更新时间',
     PRIMARY KEY (`id`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字典表';
   ```
   
   Start the service, Execute `dictionary` SQL: `select * from dictionary where id=:id`
   
   The MySQL dictionary table has a new field `test`: `ALTER TABLE dictionary ADD test VARCHAR(36) DEFAULT 'abc123' AFTER time_create;`
   
   changed `dictionary` DDL: 
   ```
   CREATE TABLE `dictionary` (
     `id` char(36) NOT NULL COMMENT '主键id',
     `name` varchar(63) NOT NULL COMMENT 'canal服务节点名称',
     `code` varchar(63) NOT NULL COMMENT 'canal服务节点ip',
     `time_create` bigint(20) NOT NULL COMMENT '创建时间',
     `test` varchar(36) DEFAULT 'abc123',
     `time_update` bigint(20) NOT NULL COMMENT '更新时间',
     PRIMARY KEY (`id`) USING BTREE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='字典表';
   ```
   
   Adjust the code and Restart the service, Execute `dictionary` SQL: `select * from dictionary where id=:id`
   
   Shardingsphere reported an error:
   
   ```
   
   ```
   
   
   
   ### Example codes for reproduce this issue (such as a github link).
   


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

[GitHub] [shardingsphere] RaigorJiang commented on issue #26012: mysql Single table SQL error "select *"

Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.

RaigorJiang commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1578521705

   Hi @hua74ni 
   Did you execute `ALTER TABLE` statement through ShardingSphere-JDBC?


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

[GitHub] [shardingsphere] RaigorJiang commented on issue #26012: mysql Single table SQL error "select *"

Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.

RaigorJiang commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1581227225

   The columns of each table need to be queried separately, and ShardingSphere persists this information just to reduce the number of database query operations.


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

[GitHub] [shardingsphere] hua74ni commented on issue #26012: mysql Single table SQL error "select *"

Posted by "hua74ni (via GitHub)" <gi...@apache.org>.

hua74ni commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1582186665

   > The columns of each table need to be queried separately, and ShardingSphere persists this information just to reduce the number of database query operations.
   
   all right.


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

[GitHub] [shardingsphere] hua74ni commented on issue #26012: mysql Single table SQL error "select *"

Posted by "hua74ni (via GitHub)" <gi...@apache.org>.

hua74ni commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1581011381

   > If the number of tables is large, the cost of comparison will be very high.
   > 
   > In addition, in this case, each instance will compare tables, which is obviously not cost-effective.
   Simply compare the number of tables in the database and the number of fields in a table in memory (I understand that the consumption is quite small)
   


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

[GitHub] [shardingsphere] hua74ni commented on issue #26012: mysql Single table SQL error "select *"

Posted by "hua74ni (via GitHub)" <gi...@apache.org>.

hua74ni commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1580151761

   > 
   
   All right, I think When the instance starts, it will check whether the zk metadata table and the actual mysql table DDL are consistent, and whether the inconsistency is consistent or needs to be persisted
   
   


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

[GitHub] [shardingsphere] RaigorJiang commented on issue #26012: mysql Single table SQL error "select *"

Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.

RaigorJiang commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1580620584

   
   > All right, I think When the instance starts, it will check whether the zk metadata table and the actual mysql table DDL are consistent, and whether the inconsistency is consistent or needs to be persisted
   
   If the number of tables is large, the cost of comparison will be very high.
   
   In addition, in this case, each instance will compare tables, which is obviously not cost-effective.
   


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

[GitHub] [shardingsphere] RaigorJiang commented on issue #26012: mysql Single table SQL error "select *"

Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.

RaigorJiang commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1579933765

   
   
   Then this is the problem, ShardingSphere scans the metadata and persists it, but if you do not execute DDL through ShardingSphere, it means that you have not notified ShardingSphere to make changes.
   
   If so, it is recommended to deploy a proxy to execute the DDL, or execute the REFRESH DistSQL through proxy.
   If there is no Proxy, you can only delete the data in zk, but this may cause configuration loss, which is not recommended.
   
   > > Hi @hua74ni Did you execute `ALTER TABLE` statement through ShardingSphere-JDBC?
   > 
   > Not executed through ShardingSphere-JDBC（Business projects do not execute `ALTER TABLE` normally）, Execute `ALTER TABLE` directly in MySQL, Then restart the service
   
   


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

[GitHub] [shardingsphere] hua74ni commented on issue #26012: mysql Single table SQL error "select *"

Posted by "hua74ni (via GitHub)" <gi...@apache.org>.

hua74ni commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1579854741

   > Hi @hua74ni Did you execute `ALTER TABLE` statement through ShardingSphere-JDBC?
   
   Not executed through ShardingSphere-JDBC（Business projects do not execute `ALTER TABLE` normally）, Execute `ALTER TABLE` directly in MySQL, Then restart the service


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

[GitHub] [shardingsphere] hua74ni commented on issue #26012: mysql Single table SQL error "select *"

Posted by "hua74ni (via GitHub)" <gi...@apache.org>.

hua74ni commented on issue #26012:
URL: https://github.com/apache/shardingsphere/issues/26012#issuecomment-1580152252

   > Then this is the problem, ShardingSphere scans the metadata and persists it, but if you do not execute DDL through ShardingSphere, it means that you have not notified ShardingSphere to make changes.
   > 
   > If so, it is recommended to deploy a proxy to execute the DDL, or execute the REFRESH DistSQL through proxy. If there is no Proxy, you can only delete the data in zk, but this may cause configuration loss, which is not recommended.
   > 
   > > > Hi @hua74ni Did you execute `ALTER TABLE` statement through ShardingSphere-JDBC?
   > > 
   > > 
   > > Not executed through ShardingSphere-JDBC（Business projects do not execute `ALTER TABLE` normally）, Execute `ALTER TABLE` directly in MySQL, Then restart the service
   
   
   All right, I think When the instance starts, it will check whether the zk metadata table and the actual mysql table DDL are consistent, and whether the inconsistency is consistent or needs to be persisted
   
   


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

[GitHub] [shardingsphere] RaigorJiang closed issue #26012: mysql Single table SQL error "select *"

Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.

RaigorJiang closed issue #26012: mysql Single table SQL error "select *"
URL: https://github.com/apache/shardingsphere/issues/26012


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