mysql - 更新 JSON 列花费的时间太长

Question

  create_table "drugs", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
    t.integer "item_seq"
    t.string "name"
    t.string "short_description"
    t.string "short_notice"
    t.json "package_insert"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false

我正在尝试更新上面显示的模型 Drug。它有大约 50,000 行数据，我正在使用 ruby​​ 代码使用从单独的 api 服务器获取的数据来更新模型中的项目。我要更新的列是 package_insert，它的类型是 json。我只是想更新 package_insert(BAR_CODE, EDI_CODE) 的某些键。

我遇到的问题是我无法更新多行，因为它会在第一行执行提交后抛出错误。

ruby 代码如下所示。

require 'json'
require 'net/http'
require 'active_support/core_ext/hash'

for i in 1..501
    response = Net::HTTP.get_response(URI.parse(ENV["API_SERVER"])).body
    hashed_response = Hash.from_xml(response)
    hashed_response['response']['body']['items']['item'].each do |item|
        #select drug to be updated
        drug_to_change = Drug.find_by(item_seq: item['ITEM_SEQ'])
        unless drug_to_change.nil?
            package = JSON.parse(drug_to_change.package_insert)
            package['DRB_ITEM']['BAR_CODE'] = item['BAR_CODE']
            if !item['EDI_CODE'].nil?
                package['DRB_ITEM']['EDI_CODE'] = item['EDI_CODE']
            end

            drug_to_change.update(package_insert: package.to_json)
        else
            new_item = Drug.new
            new_item.item_seq = item['ITEM_SEQ']
            new_item.name = item['ITEM_NAME']
            #update
            a = Hash.new
            a["DRB_ITEM"] = item
            new_item.save
        end
    end
end

当我运行时

# terminal
bin/rails runner lib/file_name.rb
# result
Running via Spring preloader in process 51929
Traceback (most recent call last):
        85: from -e:1:in `<main>'
        84: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/site_ruby/2.5.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        83: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/site_ruby/2.5.0/rubygems/core_ext/kernel_require.rb:54:in `require'
        82: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:285:in `load'
        81: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:257:in `load_dependency'
        80: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:285:in `block in load'
        79: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:50:in `load'
        78: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:50:in `load'
        77: from /Users/jamie/Desktop/test_branch/bin/rails:9:in `<main>'
        76: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:291:in `require'
        75: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:257:in `load_dependency'
        74: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/dependencies.rb:291:in `block in require'
        73: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:29:in `require'
        72: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:20:in `require_with_bootsnap_lfi'
        71: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/loaded_features_index.rb:65:in `register'
        70: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `block in require_with_bootsnap_lfi'
        69: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `require'
        68: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/railties-5.2.2/lib/rails/commands.rb:18:in `<main>'
        67: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/railties-5.2.2/lib/rails/command.rb:46:in `invoke'
        66: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/railties-5.2.2/lib/rails/command/base.rb:65:in `perform'
        65: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/thor-0.20.3/lib/thor.rb:387:in `dispatch'
        64: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/thor-0.20.3/lib/thor/invocation.rb:126:in `invoke_command'
        63: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/thor-0.20.3/lib/thor/command.rb:27:in `run'
        62: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/railties-5.2.2/lib/rails/commands/runner/runner_command.rb:38:in `perform'
        61: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:55:in `load'
        60: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/bootsnap-1.3.2/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:55:in `load'
        59: from /Users/jamie/Desktop/test_branch/lib/renew_all.rb:5:in `<main>'
        58: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/core_ext/range/each.rb:9:in `each'
        57: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/core_ext/range/each.rb:9:in `each'
        56: from /Users/jamie/Desktop/test_branch/lib/renew_all.rb:9:in `block in <main>'
        55: from /Users/jamie/Desktop/test_branch/lib/renew_all.rb:9:in `each'
        54: from /Users/jamie/Desktop/test_branch/lib/renew_all.rb:17:in `block (2 levels) in <main>'
        53: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/persistence.rb:424:in `update'
        52: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/transactions.rb:385:in `with_transaction_returning_status'
        51: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/transactions.rb:212:in `transaction'
        50: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/database_statements.rb:259:in `transaction'
        49: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/transaction.rb:236:in `within_new_transaction'
        48: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize'
        47: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/transaction.rb:252:in `block in within_new_transaction'
        46: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/transaction.rb:213:in `commit_transaction'
        45: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize'
        44: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/transaction.rb:223:in `block in commit_transaction'
        43: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/connection_adapters/abstract/transaction.rb:128:in `commit_records'
        42: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activerecord-5.2.2/lib/active_record/transactions.rb:346:in `committed!'
        41: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:816:in `_run_commit_callbacks'
        40: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:133:in `run_callbacks'
        39: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:517:in `invoke_after'
        38: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:517:in `each'
        37: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:517:in `block in invoke_after'
        36: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:261:in `block in conditional'
        35: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/callbacks.rb:426:in `block in make_lambda'
        34: from /Users/jamie/searchkick_forked/lib/searchkick/model.rb:78:in `reindex'
        33: from /Users/jamie/searchkick_forked/lib/searchkick/record_indexer.rb:51:in `reindex'
        32: from /Users/jamie/searchkick_forked/lib/searchkick/record_indexer.rb:74:in `reindex_record'
        31: from /Users/jamie/searchkick_forked/lib/searchkick/logging.rb:27:in `store'
        30: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/notifications.rb:168:in `instrument'
        29: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
        28: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/activesupport-5.2.2/lib/active_support/notifications.rb:168:in `block in instrument'
        27: from /Users/jamie/searchkick_forked/lib/searchkick/logging.rb:28:in `block in store'
        26: from /Users/jamie/searchkick_forked/lib/searchkick/index.rb:125:in `store'
        25: from /Users/jamie/searchkick_forked/lib/searchkick/bulk_indexer.rb:43:in `bulk_index'
        24: from /Users/jamie/searchkick_forked/lib/searchkick/indexer.rb:11:in `queue'
        23: from /Users/jamie/searchkick_forked/lib/searchkick/logging.rb:126:in `perform'
        22: from /Users/jamie/searchkick_forked/lib/searchkick/indexer.rb:18:in `perform'
        21: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/elasticsearch-api-6.1.0/lib/elasticsearch/api/actions/bulk.rb:95:in `bulk'
        20: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/elasticsearch-transport-6.1.0/lib/elasticsearch/transport/client.rb:131:in `perform_request'
        19: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/elasticsearch-transport-6.1.0/lib/elasticsearch/transport/transport/http/faraday.rb:20:in `perform_request'
        18: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/elasticsearch-transport-6.1.0/lib/elasticsearch/transport/transport/base.rb:266:in `perform_request'
        17: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/elasticsearch-transport-6.1.0/lib/elasticsearch/transport/transport/http/faraday.rb:23:in `block in perform_request'
        16: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/connection.rb:387:in `run_request'
        15: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/rack_builder.rb:143:in `build_response'
        14: from /Users/jamie/searchkick_forked/lib/searchkick/middleware.rb:9:in `call'
        13: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday_middleware-aws-sigv4-0.2.4/lib/faraday_middleware/request/aws_sigv4.rb:15:in `call'
        12: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/adapter/net_http.rb:38:in `call'
        11: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/adapter/net_http.rb:92:in `with_net_http_connection'
        10: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/adapter/net_http.rb:43:in `block in call'
         9: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/faraday-0.15.4/lib/faraday/adapter/net_http.rb:87:in `perform_request'
         8: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:1455:in `request'
         7: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:909:in `start'
         6: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:920:in `do_start'
         5: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:935:in `connect'
         4: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/timeout.rb:103:in `timeout'
         3: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/timeout.rb:93:in `block in timeout'
         2: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:937:in `block in connect'
         1: from /Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:937:in `open'
/Users/jamie/.rbenv/versions/2.5.3/lib/ruby/2.5.0/net/http.rb:937:in `initialize': execution expired (Faraday::ConnectionFailed)

rails 控制台结果中的相同代码:

irb(main):017:0>drug_to_change.update(package_insert: package.to_json)
   (17.2ms)  BEGIN
   (0.3ms)  COMMIT
  Drug Store (30237.8ms)  {"id":1,"exception":["Faraday::ConnectionFailed","execution expired"],"exception_object":"execution expired"}
Traceback (most recent call last):
        1: from (irb):17
Faraday::ConnectionFailed (execution expired)

我猜测问题是由于 mysql 提交花费的时间太长。我该如何解决这个问题？提前致谢。

SHOW CREATE TABLE drugs;

Answer 1

错误 Faraday::ConnectionFailed 表明您在更新链(package_insert= setter、模型验证或回调)中的某处有一个 http 请求失败并出现此错误。事实也证明了这一点，通过控制台进行的简单更新会产生相同的结果。

由于错误是由简单的 obj.update(package_insert: some_json) 重现的，因此它与您提供的脚本没有直接关系。查看您的模型，尤其是 before_*/after_*，很可能有一些 after_save 回调会发出 http 请求(根据经验- 来自模型回调的外部请求通常不是一个好主意，除非您确定如何以及为什么这样做)