Mysql Database field inconsistent。

[90yangkang]

Mysql version : 5.7.18-cynos-log Shardingsphere Proxy version : 5.1.2 Description： When select [page_put_path] , result hava [page_put_path]。 But when select * , not result [page_put_path] .

[sandynz]

Hi @90yangkang , could you submit issue with template? e.g. ShardingSphere configuration etc

[90yangkang]

Hi @90yangkang , could you submit issue with template? e.g. ShardingSphere configuration etc

configuration info : config-sharding.yaml info：

databaseName: db_mall dataSources: db1: url: jdbc:mysql://localhost:3306/db1 username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 db2: url: jdbc:mysql://localhost:3306/db2 username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 db3: url: jdbc:mysql://localhost:3306/db3 username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1

rules:

• !SHARDING defaultDatabaseStrategy: none: defaultTableStrategy: none:

server.yaml info：

mode: type: Cluster repository: type: ZooKeeper props: namespace: dbmall server-lists: 1.1.1.1:2181 retryIntervalMilliseconds: 500 timeToLiveSeconds: 60 maxRetries: 3 operationTimeoutMilliseconds: 500 overwrite: true

rules:

• !AUTHORITY users:
  • root@%:root
  • sharding@:sharding provider: type: ALL_PERMITTED
• !TRANSACTION defaultType: LOCAL

  When the provider type is Narayana, the following properties can be configured or not

• !SQL_PARSER sqlCommentParseEnabled: true sqlStatementCache: initialCapacity: 2000 maximumSize: 65535 parseTreeCache: initialCapacity: 128 maximumSize: 1024

props: max-connections-size-per-query: 1 kernel-executor-size: 16 # Infinite by default. proxy-frontend-flush-threshold: 128 # The default value is 128. proxy-hint-enabled: true sql-show: false check-table-metadata-enabled: false show-process-list-enabled: false

Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.

# The default value is -1, which means set the minimum value for different JDBC drivers.

proxy-backend-query-fetch-size: -1 check-duplicate-table-enabled: false proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.

Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution

# and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.

proxy-backend-executor-suitable: OLAP proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation. sql-federation-enabled: false

Available proxy backend driver type: JDBC (default), ExperimentalVertx

proxy-backend-driver-type: JDBC proxy-mysql-default-version: 5.7.18 # In the absence of schema name, the default version will be used. proxy-default-port: 3307 # Proxy default port. proxy-netty-backlog: 1024 # Proxy netty backlog.

[sandynz]

There's no configured sharding table, is page_data_put single table? And dataSources name doesn't match db_pre in SQL. It's better to paste real steps and configurations to re-produce this issue.

[90yangkang]

this is demo, On project ,dataSources same。

[tuichenchuxin]

make sql-show: true, And we could see the actual sql is with comment.

[90yangkang]

make sql-show: true, And we could see the actual sql is with comment.

I maked sql-show: true，actual sql is : Actual SQL: db_pre ::: / ShardingSphere hint: dataSourceName=db_pre / SELECT FROM page_data_put <== Columns: id, page_data_version_id, name, mark, cos_path, create_by, create_time, update_by, update_time, delete_flag, business_source, scene_id, business_channel <== Row: 1, 1650329764271161346, 品牌首页, app, mall_next/page_data/haval-test/app_index.json, lp, 2022-08-23 14:01:54, xyh01, 2023-04-24 10:44:15, 1, mall, 1612702987873218562, null

The returned result does not have this column of page_put_path.

[tuichenchuxin]

I saw the actual SQL run on db_pre. Seems route result it's correct.

[90yangkang]

I saw the actual SQL run on db_pre. Seems route result it's correct.

yes ，SQL is executed without problems，This field was added later, and I guess it was caused by the metadata not syncing in time, which was an accidental phenomenon.

[tuichenchuxin]

I saw the actual SQL run on db_pre. Seems route result it's correct.

If it is not executed through shardingsphere, the altered of the table will not be synchronized

[90yangkang]

I saw the actual SQL run on db_pre. Seems route result it's correct.

If it is not executed through shardingsphere, the altered of the table will not be synchronized

@tuichenchuxin Okay, I've got it . I added new fields through Navicat . Now, I understand that this operation is not appropriate. Thank you for your reply. I will close this issue .