SQLite database version requirements for Rails 5

Hi,

This is my first post to Rails core mailing list.

I’d like to propose that Rails 5 minimum version of SQLite 3.8 or higher.

( Here I am talking about SQLite database itself, not talking about sqlite3 gem. )

Usually I can open a pull request, but I need to know how to support SQLite 3.8 at Travis CI environment,

which can currently runs 3.7.11 by default or 3.7.15 which causes failures at ActiveRecord unit test.

  • Background

Recently there are some issues reported. When more than 2 migrations migrated at the same time it fails with

ActiveRecord::StatementInvalid: SQLite3::SQLException: near ",": syntax error: INSERT INTO "schema_migrations" (version).

https://github.com/rails/rails/issues/24288

https://github.com/rails/rails/issues/24677

Since this commit will be available to Rails users who migrated to Rails 5. It has not backported to 4.2 or older version of Rails.

https://github.com/rails/rails/commit/42dd2336b31a8d98776d039a2b9fd7f834156a78

  • Ubuntu releases and SQLite versions
  • Ubuntu 12.04 LTS runs SQLite version 3.7.9, which does not support multiple values in one insert statement.

  • Ubuntu 12.04 LTS + Travis CI PPA runs SQLite version 3.7.15.1

    causes at two failures in NestedThroughAssociationsTest

    which did not reproduce using SQLite 3.7.9 and 3.8.2

    https://github.com/rails/rails/pull/24440

  • Ubuntu 14.04 LTS runs SQLite version 3.8.2

    All ActiveRecord unit tests passed without errors

Refer https://docs.travis-ci.com/user/ci-environment/#Virtualization-environments

Thanks,

Looks like OS X 10.9 Mavericks has SQLite 3.7.13, 10.10 Yosemite has 3.8.5, and 10.11 El Capitan has 3.8.10.2.

I think we’re fairly safe to bump the version requirement.

On the other hand, it’s pretty cheap/easy to support 3.7.x if we only do multi-insert if the db supports it:

if connection.supports_multi_insert?
  …

Given that we’ve had multiple bug reports about it and how confusing it is to troubleshoot, that’s probably the friendlier choice.

Best,
Jeremy

Thanks for the suggestion.

not bumping minimum requirement version to 3.8. The most biggest reason is still some active platform Ubuntu 12.04 LTS and OS X 10.9 Mavericks will be “unsupported” which installs SQLite 3.7.9 by default.

Here are supports_multi_insert? return values for each adapters.

  • SQLite: true if sqlite_version >= ‘3.7.11’

https://www.sqlite.org/releaselog/3_7_11.html

Enhance the INSERT syntax to allow multiple rows to be inserted via the VALUES clause.

  • MySQL: true

Rails 5 requires MySQL 5.0 or higher, which supports multi row insert.

Refer: https://github.com/rails/rails/pull/23458

https://dev.mysql.com/doc/refman/5.0/en/insert.html

“13.2.5 INSERT Syntax”

  • PostgreSQL: true

Rails 5 requires 9.1 or higher, which supports multi row insert.

Refer: https://github.com/rails/rails/pull/23434

PostgreSQL 9.1 supports

http://www.postgresql.org/docs/9.1/static/sql-insert.html

I’m working on a pull request for this.

Thanks,