How to disable auto-setting of ActiveRecords(Ruby on Rails) SQL transaction config running in each connection?

I'm new to Ruby and ActiveRecord, so this may be not a good question.

I'm currently working on a project with Ruby on Rails. And when I take a look into projects query summaries, this looks like called in each transaction by ActiveRecord.

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483

I'm wondering if I can put this query into some config file and make it global, and disable this config query.
This is like a 5th most called query, so disabling this will improve performance.

Solution

You can try this by adding the following to your config/database.yml file:

default: &default
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_general_ci
  variables:
    sql_mode: STRICT_ALL_TABLES,NO_AUTO_VALUE_ON_ZERO
    sql_auto_is_null: 0
    wait_timeout: 2147483

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production
  username: myapp

Configuring the database connection in the database.yml file will prevent ActiveRecord from setting the configuration in each connection. But it will still set the configuration in the connection pool. So if you have a lot of connections, you will still see the query in the query log. You can prevent this by setting the configuration in the database server instead. Example for MySQL in the my.cnf file:

 [mysqld]
 sql_mode=STRICT_ALL_TABLES,NO_AUTO_VALUE_ON_ZERO
 sql_auto_is_null=0
 wait_timeout=2147483