undefined method `downcase' for ["Set Option", "Value"]:Array: dbcc useroptions

[thomasfedb]

I'm currently getting the following error:

ActiveRecord::StatementInvalid (NoMethodError: undefined method `downcase' for ["Set Option", "Value"]:Array: dbcc useroptions):
  
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:321:in `block in handle_to_names_and_values_dblib'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:321:in `map'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:321:in `handle_to_names_and_values_dblib'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:311:in `handle_to_names_and_values'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:290:in `_raw_select'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:285:in `block in raw_select'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract_adapter.rb:589:in `block in log'
activesupport (5.0.2) lib/active_support/notifications/instrumenter.rb:21:in `instrument'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:285:in `raw_select'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:226:in `sp_executesql'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:7:in `select_rows'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:135:in `user_options'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver/database_statements.rb:152:in `user_options_dateformat'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver_adapter.rb:406:in `initialize_dateformatter'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/connection_adapters/sqlserver_adapter.rb:58:in `initialize'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/sqlserver_base.rb:13:in `new'
activerecord-sqlserver-adapter (5.0.1) lib/active_record/sqlserver_base.rb:13:in `sqlserver_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:729:in `new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:773:in `checkout_new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:752:in `try_to_checkout_new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:713:in `acquire_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:490:in `checkout'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:364:in `connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:883:in `retrieve_connection'
activerecord (5.0.2) lib/active_record/connection_handling.rb:128:in `retrieve_connection'
activerecord (5.0.2) lib/active_record/connection_handling.rb:91:in `connection'
activerecord (5.0.2) lib/active_record/model_schema.rb:442:in `load_schema!'
activerecord (5.0.2) lib/active_record/attributes.rb:233:in `load_schema!'
activerecord (5.0.2) lib/active_record/attribute_decorators.rb:28:in `load_schema!'
activerecord (5.0.2) lib/active_record/model_schema.rb:437:in `load_schema'
activerecord (5.0.2) lib/active_record/model_schema.rb:339:in `columns_hash'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `block in valid?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `each'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `all?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `valid?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:17:in `method_missing'
sorcery (0.10.2) lib/sorcery/adapters/active_record_adapter.rb:85:in `find_by_id'
...

The query involved is a fairly benign find_by_id used to find the currently authenticated user record. The error is also triggered by running .connection on a model that uses the adaptor.

I'm loading my database configuration as so:

establish_connection Rails.application.config_for(:sqlserver)

With the sqlserver.yml containing:

development:
  <<: *default
  host: localhost
  port: 10001
  username: <snip>
  password: <snip>

I have an initialiser that sets:

ActiveRecord::ConnectionAdapters::SQLServerAdapter.lowercase_schema_reflection = true

[metaskills]

Ug... I have not tested lowercase_schema_reflection in a while. But I also wonder if this is related to #535 which is actually fixed in v5.0.3 of the adapter. I noticed you were using v5.0.1. Can you try the update?

[thomasfedb]

Looks like the same trace with 5.0.3:

ActiveRecord::StatementInvalid (NoMethodError: undefined method `downcase' for ["Set Option", "Value"]:Array: dbcc useroptions):
  
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:323:in `block in handle_to_names_and_values_dblib'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:323:in `map'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:323:in `handle_to_names_and_values_dblib'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:313:in `handle_to_names_and_values'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:292:in `_raw_select'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:287:in `block in raw_select'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract_adapter.rb:589:in `block in log'
activesupport (5.0.2) lib/active_support/notifications/instrumenter.rb:21:in `instrument'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:287:in `raw_select'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:228:in `sp_executesql'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:7:in `select_rows'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:135:in `user_options'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver/database_statements.rb:154:in `user_options_dateformat'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver_adapter.rb:408:in `initialize_dateformatter'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/connection_adapters/sqlserver_adapter.rb:58:in `initialize'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/sqlserver_base.rb:13:in `new'
activerecord-sqlserver-adapter (5.0.3) lib/active_record/sqlserver_base.rb:13:in `sqlserver_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:729:in `new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:773:in `checkout_new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:752:in `try_to_checkout_new_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:713:in `acquire_connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:490:in `checkout'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:364:in `connection'
activerecord (5.0.2) lib/active_record/connection_adapters/abstract/connection_pool.rb:883:in `retrieve_connection'
activerecord (5.0.2) lib/active_record/connection_handling.rb:128:in `retrieve_connection'
activerecord (5.0.2) lib/active_record/connection_handling.rb:91:in `connection'
activerecord (5.0.2) lib/active_record/model_schema.rb:442:in `load_schema!'
activerecord (5.0.2) lib/active_record/attributes.rb:233:in `load_schema!'
activerecord (5.0.2) lib/active_record/attribute_decorators.rb:28:in `load_schema!'
activerecord (5.0.2) lib/active_record/model_schema.rb:437:in `load_schema'
activerecord (5.0.2) lib/active_record/model_schema.rb:339:in `columns_hash'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `block in valid?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `each'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `all?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:59:in `valid?'
activerecord (5.0.2) lib/active_record/dynamic_matchers.rb:17:in `method_missing'
sorcery (0.10.2) lib/sorcery/adapters/active_record_adapter.rb:85:in `find_by_id'
...

[metaskills]

Could you confirm that the return value from dbcc useroptions described here is at play here? #535 (comment)

[thomasfedb]

I'm getting this back using TinyTDS:

[
  {"Set Option"=>"textsize", "Value"=>"64512"},
  {"Set Option"=>"language", "Value"=>"us_english"},
  {"Set Option"=>"dateformat", "Value"=>"mdy"},
  {"Set Option"=>"datefirst", "Value"=>"7"},
  {"Set Option"=>"lock_timeout", "Value"=>"-1"},
  {"Set Option"=>"isolation level", "Value"=>"read committed snapshot"}
]

[metaskills]

Did you do as array in the example?

[thomasfedb]

Full incantation was:

pp TinyTds::Client.new(config).execute("dbcc useroptions").to_a

[metaskills]

Could you show me this... TinyTds::Client.new(config).execute('dbcc useroptions').each(as: :array), that was the code in the other issue and what the adapter would be doing. Just want to make sure

[thomasfedb]

[
  [
    ["textsize", "64512"],
    ["language", "us_english"],
    ["dateformat", "mdy"],
    ["datefirst", "7"],
    ["lock_timeout", "-1"],
    ["isolation level", "read committed snapshot"]
  ],
  []
]

[thomasfedb]

@metaskills let me know if there are any other diagnostics I can run. Cheers.

[metaskills]

Yup! That looks like the issue. Maybe I did not put the code into the Rails v5 branch. Would you mind taking a look till I get back to this later tonight?

[thomasfedb]

Hey @metaskills, so the reason why the prior fix doesn't work form me is that I have enabled the setting to downcase all column names, and so the processing inside handle_to_names_and_values_dblib happens before the fix inserted into user_options can fix the nesting.

A few ways to fix this:

1. Work out why the weird result set is being returned and avoid this.
2. Move rows = rows.first if rows.size == 2 && rows.last.empty? into handle_to_names_and_values_dblib and add columns = columns.first before it attempts to downcase column names when nesting has been detected.
3. Avoid handling column names in handle_to_names_and_values_dblib unless options[:ar_result] is set. If this is not set they are computed and discarded.

What do you think?

[metaskills]

The 1st option would be awesome no matter what. I've never been able to find out why this happens. I have a hunch it has something to do with a specific setting. It certainly is not the default I have seen. In theory if we could counter it with another set... it would fix so many bug reports.

From there, I might choose option 3. But open to whatever works.

[rjyagono]

Hi im also having same issue im using ff. The error is only when i start a rails console.
Rails v 3.2.16
Version: freetds v1.00.27
TDS version: 7.3

[metaskills]

@rjyagono The 3-2-stable branch does have the user options patch. So I am assuming you are using lowercase_schema_reflection too? If so, please see if you can identify Option 1 above.

[thomasfedb]

@metaskills if you can advise how best to get tiny_tds rigged up with gdb then I'm prepared to give it a crack. For now I'm monkey-patching option 3 above.