search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.84k stars 6.72k forks source link

Execute tpcc，returning ERROR: insert or update on table "bmsql_stock" violates foreign key constraint "s_item_fkey_bmsql_stock" #22412

Closed peilinqian closed 3 months ago

peilinqian commented 1 year ago

Which version of ShardingSphere did you use?

we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java ShardingSphere-5.2.2-SNAPSHOT Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758 Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209) Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758 Build time: 2022-11-19T10:18:41+0800

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

ShardingSphere-Proxy

Expected behavior

Use TPCC to runDatabaseBuild ，execute success

Actual behavior

Use TPCC to runDatabaseBuild ，returning error

Reason analyze (If you can)

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

1、. runDatabaseDestroy.sh props.proxy

……
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
[90.90.44.175:36010/90.90.44.175:11000] ERROR: insert or update on table "bmsql_stock" violates foreign key constraint "s_item_fkey_bmsql_stock"
  Detail: Key (s_i_id)=(2) is not present in table "bmsql_item".
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- PostgreSQL version.
-- ----
vacuum analyze;

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

schemaName: tpcc_db
dataSources:
  ds_0:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Huawei@123
    url: jdbc:opengauss://90.90.44.171:14000/tpccdb?batchMode=on
    username: tpccuser
  ds_1:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Huawei@123
    url: jdbc:opengauss://90.90.44.171:15000/tpccdb?batchMode=on
    username: tpccuser

rules:
- !SHARDING
  bindingTables:
  - bmsql_warehouse, bmsql_customer
  - bmsql_stock, bmsql_district, bmsql_order_line
  defaultDatabaseStrategy:
    standard:
      shardingAlgorithmName: database_inline
      shardingColumn: ds_id
  defaultTableStrategy:
    none: null
  shardingAlgorithms:
    database_inline:
      props:
        algorithm-expression: ds_${ds_id % 1}
      type: INLINE
    ds_bmsql_item_inline:
      props:
        algorithm-expression: ds_${i_id % 2}
      type: INLINE
    ds_bmsql_customer_inline:
      props:
        algorithm-expression: ds_${c_w_id % 2}
      type: INLINE
    ds_bmsql_district_inline:
      props:
        algorithm-expression: ds_${d_w_id % 2}
      type: INLINE
    ds_bmsql_history_inline:
      props:
        algorithm-expression: ds_${h_w_id % 2}
      type: INLINE
    ds_bmsql_new_order_inline:
      props:
        algorithm-expression: ds_${no_w_id % 2}
      type: INLINE
    ds_bmsql_oorder_inline:
      props:
        algorithm-expression: ds_${o_w_id % 2}
      type: INLINE
    ds_bmsql_order_line_inline:
      props:
        algorithm-expression: ds_${ol_w_id % 2}
      type: INLINE
    ds_bmsql_stock_inline:
      props:
        algorithm-expression: ds_${s_w_id % 2}
      type: INLINE
    ds_bmsql_warehouse_inline:
      props:
        algorithm-expression: ds_${w_id % 2}
      type: INLINE
  tables:
    bmsql_config:
      actualDataNodes: ds_0.bmsql_config
    bmsql_item:
      actualDataNodes: ds_${0..1}.bmsql_item
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_item_inline
          shardingColumn: i_id
    bmsql_customer:
      actualDataNodes: ds_${0..1}.bmsql_customer
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_customer_inline
          shardingColumn: c_w_id
    bmsql_district:
      actualDataNodes: ds_${0..1}.bmsql_district
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_district_inline
          shardingColumn: d_w_id
    bmsql_history:
      actualDataNodes: ds_${0..1}.bmsql_history
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_history_inline
          shardingColumn: h_w_id
    bmsql_new_order:
      actualDataNodes: ds_${0..1}.bmsql_new_order
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_new_order_inline
          shardingColumn: no_w_id
    bmsql_oorder:
      actualDataNodes: ds_${0..1}.bmsql_oorder
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_oorder_inline
          shardingColumn: o_w_id
    bmsql_order_line:
      actualDataNodes: ds_${0..1}.bmsql_order_line
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_order_line_inline
          shardingColumn: ol_w_id
    bmsql_stock:
      actualDataNodes: ds_${0..1}.bmsql_stock
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_stock_inline
          shardingColumn: s_w_id
    bmsql_warehouse:
      actualDataNodes: ds_${0..1}.bmsql_warehouse
      databaseStrategy:
        standard:
          shardingAlgorithmName: ds_bmsql_warehouse_inline
          shardingColumn: w_id
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: 90.90.44.175:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

authority:
  users:
    - user: root@%
      password: root
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED

#rules:
#- !TRANSACTION
#  defaultType: XA
#  providerType: Atomikos
#- !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: false
  sql-show: true
#  check-table-metadata-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
#  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.
#    # Available sql federation type: NONE (default), ORIGINAL, ADVANCED
  sql-federation-type: ADVANCED
strongduanmu commented 1 year ago

Can you provide the sql which cause this exception?

peilinqian commented 1 year ago

build success，run benchmark error；

20:21:02,182 [main] INFO   jTPCC : Term-00,
20:21:02,182 [main] INFO   jTPCC : Term-00, warehouses=5
20:21:02,182 [main] INFO   jTPCC : Term-00, terminals=5
20:21:02,183 [main] INFO   jTPCC : Term-00, runMins=1
20:21:02,184 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
20:21:02,184 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
20:21:02,184 [main] INFO   jTPCC : Term-00,
20:21:02,184 [main] INFO   jTPCC : Term-00, newOrderWeight=45
20:21:02,184 [main] INFO   jTPCC : Term-00, paymentWeight=43
20:21:02,184 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
20:21:02,184 [main] INFO   jTPCC : Term-00, deliveryWeight=4
20:21:02,184 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
20:21:02,184 [main] INFO   jTPCC : Term-00,
20:21:02,184 [main] INFO   jTPCC : Term-00, resultDirectory=null
20:21:02,184 [main] INFO   jTPCC : Term-00, osCollectorScript=null
20:21:02,184 [main] INFO   jTPCC : Term-00,
20:21:02,485 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 251
20:21:02,486 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    173
20:21:02,486 [main] INFO   jTPCC : Term-00,                                                                                                                                                                     20:21:03,271 [Thread-3] ERROR  jTPCCTData : Unexpected SQLException in PAYMENT5MB / 481MB
20:21:03,271 [Thread-3] ERROR  jTPCCTData : [7.212.123.28:52156/7.212.123.28:11000] ERROR: duplicate key value violates unique constraint "bmsql_history_pkey"
  Detail: Key (hist_id)=(1) already exists.
org.postgresql.util.PSQLException: [7.212.123.28:52156/7.212.123.28:11000] ERROR: duplicate key value violates unique constraint "bmsql_history_pkey"
  Detail: Key (hist_id)=(1) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2846)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2575)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:336)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:464)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:378)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:142)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:117)
        at jTPCCTData.executePayment(jTPCCTData.java:929)
        at jTPCCTData.execute(jTPCCTData.java:99)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:160)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:748)
20:21:03,278 [Thread-2] ERROR  jTPCCTData : Unexpected SQLException in PAYMENT
[INFO ] 2022-12-28 20:21:03.256 [Connection-17-ThreadExecutor] ShardingSphere-SQL - Logic SQL: UPDATE bmsql_customer     SET c_balance = c_balance - ?,         c_ytd_payment = c_ytd_payment + ?,         c_payment_cnt = c_payment_cnt + 1     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
[INFO ] 2022-12-28 20:21:03.256 [Connection-17-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: UPDATE bmsql_customer     SET c_balance = c_balance - ?,         c_ytd_payment = c_ytd_payment + ?,         c_payment_cnt = c_payment_cnt + 1     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? ::: [1012.91, 1012.91, 3, 6, 2986]
[INFO ] 2022-12-28 20:21:03.259 [HikariPool-4 connection adder] o.o.core.v3.ConnectionFactoryImpl - [7.212.123.28:49304/10.29.180.204:16000] Connection is established. ID: 887d8d32-eb21-46cc-bdf8-29291c82b780
[INFO ] 2022-12-28 20:21:03.260 [Connection-17-ThreadExecutor] ShardingSphere-SQL - Logic SQL: INSERT INTO bmsql_history (    h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,     h_date, h_amount, h_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
[INFO ] 2022-12-28 20:21:03.260 [Connection-16-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_dist_06, s_dist_07, s_dist_08,        s_dist_09, s_dist_10     FROM bmsql_stock     WHERE s_w_id = ? AND s_i_id = ?     FOR UPDATE
[INFO ] 2022-12-28 20:21:03.260 [Connection-17-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: INSERT INTO bmsql_history (    h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,     h_date, h_amount, h_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [2986, 6, 3, 6, 3, 2022-12-28 20:21:03.058+08, 1012.91, TPZo0C    VXlGrkx9]
[INFO ] 2022-12-28 20:21:03.260 [Connection-16-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_dist_06, s_dist_07, s_dist_08,        s_dist_09, s_dist_10     FROM bmsql_stock     WHERE s_w_id = ? AND s_i_id = ?     FOR UPDATE ::: [5, 22934]
[INFO ] 2022-12-28 20:21:03.264 [Connection-16-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = ?
[INFO ] 2022-12-28 20:21:03.264 [Connection-16-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = ? ::: [30149]
[ERROR] 2022-12-28 20:21:03.265 [Connection-18-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.opengauss.util.PSQLException: [7.212.123.28:49294/10.29.180.204:16000] ERROR: duplicate key value violates unique constraint "bmsql_history_pkey"
  Detail: Key (hist_id)=(1) already exists.
        at org.opengauss.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2901)
        at org.opengauss.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2630)
        at org.opengauss.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:362)
        at org.opengauss.jdbc.PgStatement.runQueryExecutor(PgStatement.java:562)
        at org.opengauss.jdbc.PgStatement.executeInternal(PgStatement.java:539)
        at org.opengauss.jdbc.PgStatement.execute(PgStatement.java:397)
        at org.opengauss.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:156)
        at org.opengauss.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:145)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:75)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:68)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:45)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:90)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:121)
        at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:115)
        at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:75)
        at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:229)
        at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:186)
        at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:151)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:131)
        at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.JDBCPortal.bind(JDBCPortal.java:101)
        at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53)
        at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
terrymanu commented 3 months ago

Please try lasted version, the TPCC can be tested