Rails Rails 5 升级出现的问题 ‘undefined method `sql_type' for "NUMBER":String’
*从 rails3 升级到 rails5 获取数据，报标题错误 *
涉及到的代码块
for i in 0...in_size
in_query.push("RPTNO in (#{all_bug_numbers[max_params_length*i...max_params_length*(i+1)].join(",")})")
end
bugs = Bug.find_by_sql("SELECT RPTNO, SUBJECT, STATUS, PRI_FOR_DEV, PROGRAMMER, CUSTOMER from RPTHEAD WHERE (#{in_query.join(' OR ')})")
for bug in bugs
bug_db_data[bug[:rptno].to_i] = bug
end
报错‘undefined method `sql_type' for "NUMBER":String‘
Full Trace
activerecord (5.0.0.1) lib/active_record/connection_adapters/column.rb:8:in `sql_type'
activerecord (5.0.0.1) lib/active_record/connection_adapters/abstract/quoting.rb:63:in `lookup_cast_type_from_column'
activerecord (5.0.0.1) lib/active_record/model_schema.rb:359:in `block in load_schema!'
activerecord (5.0.0.1) lib/active_record/model_schema.rb:355:in `each'
activerecord (5.0.0.1) lib/active_record/model_schema.rb:355:in `load_schema!'
activerecord (5.0.0.1) lib/active_record/attributes.rb:233:in `load_schema!'
activerecord (5.0.0.1) lib/active_record/attribute_decorators.rb:28:in `load_schema!'
activerecord (5.0.0.1) lib/active_record/model_schema.rb:349:in `load_schema'
activerecord (5.0.0.1) lib/active_record/model_schema.rb:256:in `columns_hash'
activerecord (5.0.0.1) lib/active_record/querying.rb:41:in `find_by_sql'
app/controllers/strategy_backlogs_controller.rb:397:in `retrieve_strate_line_increment_ers'
之前有位仁兄提到的更改代码
#in_query.push("RPTNO in (#{all_bug_numbers[max_params_length*i...max_params_length*(i+1)].join(",")})")
in_query.push("RPTNO in (#{all_bug_numbers[max_params_length*i...max_params_length*(i+1)].join(',')})")
log
(3224.6ms) SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, data_type_owner AS sql_type_owner, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'BUG' AND table_name = 'RPTHEAD' AND hidden_column = 'NO' ORDER BY column_id
Completed 500 Internal Server Error in 9286ms (ActiveRecord: 3519.8ms)
NoMethodError (undefined method `sql_type' for "NUMBER":String):
验证过，依然报错，这里 Bug.find_by_sql, 是从 oracle 获取数据，dev 使用的是 mysql 数据库，分析查看代码，追踪到 rails4 activerecord-5.0.0.1/lib/active_record/querying.rb
def find_by_sql(sql, binds = [], preparable: nil)
result_set = connection.select_all(sanitize_sql(sql), "#{name} Load", binds, preparable: preparable)
column_types = result_set.column_types.dup
columns_hash.each_key { |k| column_types.delete k }
message_bus = ActiveSupport::Notifications.instrumenter
payload = {
record_count: result_set.length,
class_name: name
}
message_bus.instrument('instantiation.active_record', payload) do
result_set.map { |record| instantiate(record, column_types) }
end
end
result_set 返回数据很正常，column_types = result_set.column_types.dup，返回{},也就是获取不到 column_type.
导致后续 columns_hash.each_key { |k| column_types.delete k },报错 1.查看 activerecord-3.2.21 更改 find_by_sql 方法，会导致其他错误。 2.更改 bug sql 方式为
Bug.where(in_query.join(' OR '))
会报涉及 gem 的错误 3.我是否少用了其他 rails4 或者 5 中的相关的 gem，检查，rails5 必须的 gem，都已安装。
*** LOCAL GEMS ***
aasm (4.11.1, 4.11.0, 4.1.0)
actioncable (5.0.0.1)
actionmailer (5.0.0.1, 4.2.7, 3.2.21)
actionpack (5.0.0.1, 4.2.7.1, 4.2.7, 3.2.21)
actionview (5.0.0.1, 4.2.7.1, 4.2.7)
activejob (5.0.0.1, 4.2.7)
activemodel (5.0.0.1, 4.2.7, 3.2.21)
activemodel-serializers-xml (1.0.1)
activerecord (5.0.0.1, 4.2.7, 3.2.21)
activerecord-oracle_enhanced-adapter (1.7.5, 1.7.1, 1.5.6, 1.4.3)
activeresource (3.2.21)
activesupport (5.0.0.1, 4.2.7.1, 4.2.7, 3.2.21)
acts_as_reportable (1.1.1)
acts_as_tree (2.6.0, 2.5.1, 2.1.0)
acts_as_votable (0.10.0)
arel (7.1.4, 7.1.2, 7.1.1, 6.0.3, 3.0.3)
awesome_print (1.7.0, 1.6.1)
bcrypt (3.1.11)
bigdecimal (1.2.8)
builder (3.2.2, 3.0.4)
bundle (0.0.1)
bundler (1.13.0, 1.12.5)
byebug (9.0.6, 9.0.5)
choice (0.2.0, 0.1.7)
chronic (0.10.2)
chunky_png (1.3.7, 1.3.4)
climate_control (0.0.3)
cocaine (0.5.8, 0.5.7)
coffee-rails (4.2.1, 3.2.2)
coffee-script (2.4.1, 2.3.0)
coffee-script-source (1.10.0, 1.9.1)
color (1.8, 1.7.1)
compass (1.0.3)
compass-core (1.0.3)
compass-import-once (1.0.5)
concurrent-ruby (1.0.2)
devise (4.2.0)
did_you_mean (1.0.2, 1.0.0)
dynamic_form (1.1.4)
enumerize (2.0.0)
erubis (2.7.0)
execjs (2.7.0, 2.4.0)
fastercsv (1.5.5)
ffi (1.9.14, 1.9.8)
font-awesome-rails (4.6.3.1)
globalid (0.3.7)
haml (4.1.0.beta.1, 4.0.7, 4.0.6)
haml-contrib (1.0.0.1)
haml-rails (0.9.0)
hashie (3.4.4)
hike (1.2.3)
html2haml (2.0.0)
i18n (0.7.0)
io-console (0.4.6, 0.4.5)
journey (1.0.4)
jquery-rails (4.2.1, 3.1.4, 3.1.2)
jquery-ui-rails (5.0.5)
json (2.0.2, 1.8.3, 1.8.2)
kaminari (0.17.0)
libv8 (3.16.14.15 x86_64-linux)
loofah (2.0.3)
mail (2.6.4, 2.5.4)
method_source (0.8.2)
mime-types (3.1, 1.25.1)
mime-types-data (3.2016.0521)
mimemagic (0.3.2, 0.3.0)
mini_portile (0.6.2)
mini_portile2 (2.1.0)
minitest (5.9.1, 5.9.0, 5.8.3)
multi_json (1.12.1, 1.11.0)
mysql2 (0.4.5, 0.4.4, 0.3.18)
nested_form (0.3.2)
net-telnet (0.1.1)
nio4r (1.2.1)
nokogiri (1.6.8.1, 1.6.8, 1.6.6.2)
omniauth (1.3.1)
orm_adapter (0.5.0)
paper_trail (5.2.2, 5.2.1, 3.0.7)
paperclip (5.1.0, 4.3.7, 4.2.1)
pdf-writer (1.1.8)
pkg-config (1.1.7)
polyglot (0.3.5)
power_assert (0.3.1, 0.2.6)
pr_geohash (1.0.0)
protected_attributes (1.1.3)
psych (2.1.1, 2.0.17)
quiet_assets (1.1.0)
rack (2.0.1, 1.6.4, 1.4.7, 1.4.5)
rack-cache (1.6.1, 1.2)
rack-pjax (1.0.0, 0.8.0)
rack-ssl (1.3.4)
rack-test (0.6.3)
rails (5.0.0.1, 4.2.7, 3.2.21)
rails-controller-testing (1.0.1)
rails-deprecated_sanitizer (1.0.3)
rails-dom-testing (2.0.1, 1.0.7)
rails-erd (1.5.0, 1.3.1)
rails-html-sanitizer (1.0.3)
rails-observers (0.1.2)
rails_admin (0.8.1)
railties (5.0.0.1, 4.2.7.1, 4.2.7, 3.2.21)
rake (11.3.0, 11.2.2, 10.4.2)
rank (0.0.2)
rb-fsevent (0.9.8, 0.9.7, 0.9.4)
rb-inotify (0.9.7, 0.9.5)
rdoc (4.2.2, 4.2.1, 3.12.2)
ref (2.0.0)
remotipart (1.2.1)
request_store (1.3.1)
responders (2.3.0)
rsolr (1.1.2, 1.0.13, 1.0.12)
ruby-graphviz (1.2.2, 1.0.9)
ruby-oci8 (2.2.2, 2.1.8)
ruby-ole (1.2.12)
ruby-plsql (0.6.0)
ruby_parser (3.8.3, 3.8.2)
rubyXL (3.3.22, 3.3.21)
rubyzip (1.2.0)
rufus-scheduler (3.2.2)
ruport (1.6.3)
safe_yaml (1.0.4)
sass (3.4.22, 3.4.13)
sass-rails (5.0.6, 3.2.6)
sexp_processor (4.7.0)
spreadsheet (1.1.3)
sprockets (4.0.0.beta3, 3.7.0, 2.2.3)
sprockets-rails (3.2.0, 3.0.0.beta2)
strong_parameters (0.1.4)
sunspot (2.2.6, 2.0.0.pre.130115)
sunspot_rails (2.2.6, 2.0.0.pre.130115)
sunspot_solr (2.2.6, 1.3.3)
test-unit (3.2.1, 3.1.5)
therubyracer (0.12.2)
thor (0.19.1)
thread_safe (0.3.5)
tilt (2.0.5, 1.4.1)
transaction-simple (1.4.0.2)
treetop (1.4.15)
turbolinks (5.0.1)
turbolinks-source (5.0.0)
tzinfo (1.2.2, 0.3.52, 0.3.43)
uglifier (3.0.3, 3.0.2, 2.7.1)
warden (1.2.6)
websocket-driver (0.6.4)
websocket-extensions (0.1.2)
whenever (0.9.7, 0.9.4)
will_paginate (3.1.5, 3.1.3, 3.1.0, 3.0.7)
有没有仁兄遇到过此类问题，求解。
上面提到的 result_set 返回结果
<ActiveRecord::Result:0x007fa88df12bc8
@columns=["rptno", "subject", "status", "pri_for_dev", "programmer", "customer"],
@rows=[[15903810, "ENH RESTRICT UPDATE/DELETE/CORRECTION OF ABSENCE BASED ON A RULES/CRITERIA"
, 97, nil, ....]]
@hash_rows=nil, @column_types={}>
这里@column_types={} 返回为空，所以后续的代码
column_types = result_set.column_types.dup
返回为{},导致后面 Exception
上面 log 提到的
(3224.6ms) SELECT column_name AS name, data_type AS sql_type, data_default, nullable, virtual_column, hidden_column, data_type_owner AS sql_type_owner, DECODE(data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit, DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale FROM all_tab_cols WHERE owner = 'BUG' AND table_name = 'RPTHEAD' AND hidden_column = 'NO' ORDER BY column_id
SQL 单独拿出来在 oracle 数据库查询，返回结果是正确的。也就是问题出现在中间代码，转化 column_type 出了问题。但是我想这个是 gem，自身需要做的工作，使用者不需要去修改 gem 吧。。。。
rails3 版本，此段代码没有任何没问题，返回正常，页面也正常。是因为使用的 activerecord-3.2.21，涉及到的 gem 里面的 function 逻辑不同，不需要获取 column_type。
def find_by_sql(sql, binds = [])
logging_query_plan do
connection.select_all(sanitize_sql(sql), "#{name} Load", binds).collect! { |record| instantiate(record) }
end
end
activerecord-5.0.0.1 此段代码
def find_by_sql(sql, binds = [], preparable: nil)
result_set = connection.select_all(sanitize_sql(sql), "#{name} Load", binds, preparable: preparable)
column_types = result_set.column_types.dup
columns_hash.each_key { |k| column_types.delete k }
message_bus = ActiveSupport::Notifications.instrumenter
payload = {
record_count: result_set.length,
class_name: name
}
message_bus.instrument('instantiation.active_record', payload) do
result_set.map { |record| instantiate(record, column_types) }
end
end
不管 gem 底层如何处理的，这个和使用者无关，现在这个问题方向---去 oracle 数据库中涉及 table-RPTHEAD，找原因？ 这个也许不是好的方向。因为已经跨服务器了，其他平台也在用这个 table，况且对应的 sql 在 oracle 里面单独查询，没有问题，返回正常。
上面提到的 columns_hash.这里是取不到值，会报错，查看 API ‘Returns a hash of column objects for the table associated with this class’
无法匹配 oracle，对应的数据类型，这里如何解决？
