Rails | Kaminari: how to limit max pages?

Question

I'm using Kaminari gem for pagination.

I have an exception when trying to run such code:

Distributor.all.page(123123213213132113322)

=> ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3078080330328302833025' at line 1: SELECT distributors.* FROM distributors LIMIT 11 OFFSET 3078080330328302833025)

I've already tried to add such config into initializer, but it doesn't help:

Kaminari.configure do |config|
  config.max_pages = 1000000000
end

What it the right way to limit max pages forcibly on #page method call or is any other way just to avoid such exception?

Answer 1

You are likely running into an undocumented MySql offset limit of 3689348814741910324 — AKA 3_689_348_814_741_910_324 AKA 3,689,348,814,741,910,324 AKA ((2^65) / 10) + 1) AKA ((2**65) / 10) + 1.
I ran into the same thing while writing our Graphiti-backed API.

Here is how I dealt with it. There will be some useful nuggets here I'm sure. Specifically, you could write your own pagination helper that wraps around the Kaminari page call which applies some filtering like I've done below. (I rescue/catch the raised error and send it back to the user as an informative message.)

class ApplicationResource < Graphiti::Resource

  ###################################################################
  #
  # Constants
  #
  ###################################################################

  # Exceeding this integer value in a query causes MySQL to overflow the integer, typically raising an error.
  # I found very little information about this limit online. I discovered it using trial and error, otherwise I'd
  # explain more.
  MYSQL_QUERY_INT_MAX = 3_689_348_814_741_910_324 # ((2**65) / 10) + 1

  CURRENT_PAGE_DEFAULT = 1 # Graphiti takes care of this behavior itself internally. This constant is for reference only.
  CURRENT_PAGE_MIN     = 1
  CURRENT_PAGE_MAX     = MYSQL_QUERY_INT_MAX

  RESULTS_PER_PAGE_DEFAULT = 20
  RESULTS_PER_PAGE_MIN     = 1
  RESULTS_PER_PAGE_MAX     = 250



  ###################################################################
  #
  # Pagination
  #
  # The `scope` draws from and builds upon the `base_scope` provided by the child resource.
  # The `current_page` and `results_per_page` come from the request
  # params in the format: /resources?page[number]=2&page[size]=10
  #
  ###################################################################
  self.default_page_size = RESULTS_PER_PAGE_DEFAULT # Override default from Graphiti::Resource which is 10.
  self.max_page_size     = RESULTS_PER_PAGE_MAX     # Override default from Graphiti::Resource which is 1,000.

  paginate do |scope, current_page, results_per_page|

    # Apply defaults if the respective parameters are omitted.
    # No need to check current_page as Graphiti internally enforces a default value (1).
    results_per_page = RESULTS_PER_PAGE_DEFAULT if results_per_page.nil?

    # Guard against invalid parameters.
    # No need to guard against RESULTS_PER_PAGE_MAX as Graphiti does that on its own via the max_page_size setting above.
    if results_per_page < RESULTS_PER_PAGE_MIN
      raise(Api::UnsupportedPageSizeMin.new(results_per_page, RESULTS_PER_PAGE_MIN))
    end
    if current_page < CURRENT_PAGE_MIN || current_page > CURRENT_PAGE_MAX
      raise(Api::InvalidPageNumber.new(current_page, CURRENT_PAGE_MIN, CURRENT_PAGE_MAX))
    end

    # Perform pagination (makes use of the kaminari gem).
    scope.page(current_page).per(results_per_page)
  end