Rails MySQL2 default wait_timeout so HUGE

Question

Whenever there is any request, this query command runs so many times in the backend:

SET NAMES utf8,  @@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

Why do Rails not just run it once?

Also, why does it set wait_timeout to 2147483, it is so huge, so many hours, it just took my server down because there were so many queries in sleep mode for more than 100 hours.

I had to update it to 180 seconds.

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  socket: /var/run/mysqld/mysqld.sock
  wait_timeout: 180

What did I do wrong. I have no idea.

Answer 1

Why does it set wait_timeout to 2147483

It's an issue with the mysql2 adapter!

mysql2_adapter uses this same wait_timeout, but passes it directly to mysql, and defaults to a much larger 2592000!!(Now it defaults to 2147483)

# increase timeout so mysql server doesn't disconnect us
wait_timeout = @config[:wait_timeout]
wait_timeout = 2147483 unless wait_timeout.is_a?(Fixnum)
variable_assignments << "@@wait_timeout = #{wait_timeout}"

execute("SET #{variable_assignments.join(', ')}", :skip_logging)

Reference