`execute` returns incompatible result between `Mysql2Adapter` and `TrilogyAdapter`.

[ohbarye]

I'm new to trilogy project and am not sure if the problem below is an intentional behavior or not. Please feel free to close this one if it's not suitable here!

Incompatible behavior

I'm trying to replace mysql2 with trilogy (and activerecord-trilogy-adapter), and found incompatible behavior between ActiveRecord::ConnectionAdapters::Mysql2Adapter and ActiveRecord::ConnectionAdapters::TrilogyAdapter.

ActiveRecord::ConnectionAdapters::Mysql2Adapter returns an array of record values.

# ActiveRecord::ConnectionAdapters::Mysql2Adapter
c = ActiveRecord::Base.connection
result = c.execute('SHOW FULL TABLES WHERE table_type = "VIEW"')
   (23.0ms)  SHOW FULL TABLES WHERE table_type = "VIEW"
result.first
=> ["balance_summaries", "VIEW"]

ActiveRecord::ConnectionAdapters::TrilogyAdapter just returns a hash of record values.

# ActiveRecord::ConnectionAdapters::TrilogyAdapter
c = ActiveRecord::Base.connection
result = c.execute('SHOW FULL TABLES WHERE table_type = "VIEW"')
   (43.1ms)  SHOW FULL TABLES WHERE table_type = "VIEW"
result.first
=> {"Tables_in_core_api_development"=>"balance_summaries", "Table_type"=>"VIEW"}

What does this behavior break?

My actual problem is that Trilogy::ProtocolError happens when calling Scenic.database.views.

Note: Scenic is a gem to manage database views, and it has been working well with mysql2 and scenic-mysql_adapter.

This gem uses an adapter's execute method and its result. So, the result structure change breaks its original behavior. I think it's a rather problem that scenic-mysql_adapter should adapt trilogy, but I am writing this issue first because I'm not sure if the incompatibility is intentional or not.

https://github.com/EmpaticoOrg/scenic-mysql_adapter/blob/d0ce1bd226ca17dfbca2f89dd9eec552eacce148/lib/scenic/adapters/my_sql.rb#L80-L83

irb(main):002:0> Scenic.database.views
   (50.7ms)  SHOW FULL TABLES WHERE table_type = "VIEW"
   (14.0ms)  SHOW CREATE VIEW `["Tables_in_my_test_database", "users"]`
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:131:in `query': Trilogy::ProtocolError: 1146: Table 'my_test_database.["Tables_in_my_test_database", "users"]' doesn't exist (ActiveRecord::StatementInvalid)
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:131:in `query': 1146: Table 'my_test_database.["Tables_in_my_test_database", "users"]' doesn't exist (Trilogy::ProtocolError)

Backtrace

> Scenic.database.views rescue puts($!.backtrace)

/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:131:in `query'                                            
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:131:in `block (2 levels) in raw_execute'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy_adapter.rb:213:in `block in with_trilogy_connection'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy_adapter.rb:210:in `with_trilogy_connection'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:129:in `block in raw_execute'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-7.0.8/lib/active_record/connection_adapters/abstract_adapter.rb:752:in `block in log'
/usr/local/bundle/ruby/3.2.0/gems/activesupport-7.0.8/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-7.0.8/lib/active_record/connection_adapters/abstract_adapter.rb:743:in `log'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:128:in `raw_execute'
/usr/local/bundle/ruby/3.2.0/gems/activerecord-trilogy-adapter-3.1.2/lib/active_record/connection_adapters/trilogy/database_statements.rb:20:in `execute'
/usr/local/bundle/ruby/3.2.0/gems/scenic-mysql_adapter-1.0.1/lib/scenic/adapters/my_sql.rb:59:in `execute'
/usr/local/bundle/ruby/3.2.0/gems/scenic-mysql_adapter-1.0.1/lib/scenic/adapters/my_sql.rb:87:in `view_definition'
/usr/local/bundle/ruby/3.2.0/gems/scenic-mysql_adapter-1.0.1/lib/scenic/adapters/my_sql.rb:21:in `block in views'
/usr/local/bundle/ruby/3.2.0/gems/scenic-mysql_adapter-1.0.1/lib/scenic/adapters/my_sql.rb:18:in `map'
/usr/local/bundle/ruby/3.2.0/gems/scenic-mysql_adapter-1.0.1/lib/scenic/adapters/my_sql.rb:18:in `views'
(irb):7:in `<main>'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/workspace.rb:113:in `eval'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/workspace.rb:113:in `evaluate'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/context.rb:497:in `evaluate'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:581:in `block (2 levels) in eval_input'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:770:in `signal_status'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:561:in `block in eval_input'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/ruby-lex.rb:253:in `block (2 levels) in each_top_level_statement'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/ruby-lex.rb:235:in `loop'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/ruby-lex.rb:235:in `block in each_top_level_statement'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/ruby-lex.rb:234:in `catch'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb/ruby-lex.rb:234:in `each_top_level_statement'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:560:in `eval_input'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:494:in `block in run'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:493:in `catch'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:493:in `run'
/usr/local/bundle/ruby/3.2.0/gems/irb-1.6.4/lib/irb.rb:416:in `start'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/commands/console/console_command.rb:74:in `start'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/commands/console/console_command.rb:19:in `start'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/commands/console/console_command.rb:106:in `perform'
/usr/local/bundle/ruby/3.2.0/gems/thor-1.2.2/lib/thor/command.rb:27:in `run'
/usr/local/bundle/ruby/3.2.0/gems/thor-1.2.2/lib/thor/invocation.rb:127:in `invoke_command'
/usr/local/bundle/ruby/3.2.0/gems/thor-1.2.2/lib/thor.rb:392:in `dispatch'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/command/base.rb:87:in `perform'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/command.rb:48:in `invoke'
/usr/local/bundle/ruby/3.2.0/gems/railties-7.0.8/lib/rails/commands.rb:18:in `<main>'
<internal:/usr/local/lib/ruby/3.2.0/rubygems/core_ext/kernel_require.rb>:37:in `require'
<internal:/usr/local/lib/ruby/3.2.0/rubygems/core_ext/kernel_require.rb>:37:in `require'
/usr/local/bundle/ruby/3.2.0/gems/bootsnap-1.16.0/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:32:in `require'
bin/rails:6:in `<main>'

[khhizr007]

I ran into the same problem when we switched our applications from mysql2 to trilogy. On investigation we found the problem to be the response we get from the execute method.

When we used mysql2 as our database adapter the respose to the ActiveRecord::Base.connection.execute method was Mysql2::Result object. But when we switched to trilogy we get ActiveRecord::Result object as the response. The result from mysql2 we can use directly by calling the to_a method on the response, but this breaks when we switch to trilogy.

I am not sure if this is the intended behaviour of the gem, but if it is indeed the intended behaviour then this needs to be made clear in the docs. Because with this difference in behaviour between the gems we cannot just start using trilogy by replacing mysql2 as this will break some applications dependent on raw sql queries.