Rake task that warns you when an auto_increment column reaches a maximum value threshold

check_auto_increment_columns_rake.rb

desc "Warn if an auto_increment column is reaching max value limit"
task check_auto_increment_columns_overflow: :environment do
  threshold_percent = (ENV["THRESHOLD"] || "90").to_i

  ActiveRecord::Base.connection.tables.each do |table|
    ActiveRecord::Base.connection.columns(table).each do |column|
      next unless column.type == :integer && column.extra.to_s =~ /auto_increment/

      auto_increment = ActiveRecord::Base.connection.select_one("SHOW TABLE STATUS LIKE '#{table}'")["Auto_increment"]
      max_value = column.cast_type.send(:max_value)
      percent_of_max_value = auto_increment / max_value.to_f * 100

      debug = "#{table}.#{column.name}: #{auto_increment} / #{max_value} = #{percent_of_max_value.round(1)}%"
      if percent_of_max_value > threshold_percent
        STDERR.puts "Warning: auto increment value of column #{table}.#{column.name} is above max threshold"
        STDERR.puts debug
      else
        puts debug
      end
    end
  end
end

# Warns when an auto_increment column reaches the maximum value threshold (in % of the max allowed value for the column).
# Works on MySQL.
#
# The threshold is 90% by default:
#
# $ rake check_auto_increment_columns_overflow
# ...
# comments.id: 663734 / 2147483648 = 0.0%
# domains.id: 13 / 128 = 10.2%
# ...
#
# The threshold can be set via rake ENV var:
#
# $ rake check_auto_increment_columns_overflow THRESHOLD=5
# ...
# comments.id: 663734 / 2147483648 = 0.0%
# Warning: auto increment value of column domains.id is above max threshold
# domains.id: 13 / 128 = 10.2%
# ...
#
# The warning is printed to STDERR which makes the rake task suitable for running in cron.
#