SQLite database version requirements for Rails 5

[yahonda (Yasuo Honda)]

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).

Getting ActiveRecord::StatementInvalid error on TravisCI for Rails 5 project

Rails 5 beta3: db:reset with 3+ migrations triggers ActiveRecord::StatementInvalid: SQLite3::SQLException

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.

INSERT INTO schema_migrations in 1 SQL

• 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

  Use SQLite3 3.7.15 which supports multiple values insert by yahonda · Pull Request #24440 · rails/rails · GitHub

• Ubuntu 14.04 LTS runs SQLite version 3.8.2

  All ActiveRecord unit tests passed without errors

Refer Redirecting…

Thanks,

[bitsweat (Jeremy Daer)]

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

[yahonda (Yasuo Honda)]

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: Active Record supports MySQL >= 5.0 by kamipo · Pull Request #23458 · rails/rails · GitHub

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: The minimum supported version of PostgreSQL is now >= 9.1 by remomueller · Pull Request #23434 · rails/rails · GitHub

PostgreSQL 9.1 supports

INSERT

I’m working on a pull request for this.

Thanks,