2

Like some others, I am having trouble with an error

"ActiveRecord::StatementInvalid: SQLite3::SQLException: object name reserved for internal use: sqlite_sp_functions: CREATE TABLE "sqlite_sp_functions" ("name" text, "text" text)"

when running rake test on a Rails project.

The offending lines in schema.rb are:

create_table "sqlite_sp_functions", id: false, force: true do |t|
  t.text "name"
  t.text "text"
end

The suggestions on the previous query about this involved editing schema.rb or deleting that file & regenerating it, but schema.rb (and the offending code) are regenerated on each migration (plus, I don't want to delete Rails-generated code without knowing the implications), so that's not really a satisfactory solution.

So what is the sqlite_sp_functions table for, and how can I get Rails to generate a schema.rb file that doesn't break rake test for the project?

Community
  • 1
  • 1
digitig
  • 1,989
  • 3
  • 25
  • 45

2 Answers2

1

as per https://stackoverflow.com/a/25077833/601782:

Add the following config line to config/application.rb or in config/environments/development.rb:

ActiveRecord::SchemaDumper.ignore_tables = /^sqlite_*/

This will ignore all tables starting with "sqlite_" in your database during the schema dump.

Community
  • 1
  • 1
Elia Schito
  • 989
  • 7
  • 18
0

Try removing the offending lines from your schema.rb and then issue:

RAILS_ENV=test rake db:reset

which will completely nuke your testing database but you shouldn't care anyway. You are not supposed to run migrations for test environments. Migrations are meant to be small (reversible) steps for environments that hold data (such as production and sometimes staging/dev).

The preferred way to handle your testing DB is to use db:schema:load as part of your testing routine which will of course delete all your DB data.

Again: Please do not try this in development mode (if you have data that you set up manually) and definitely not in production.

Other than that, you could probably delete the whole sqlite_sp_functions table from your SQLite testing DB and get rid of the issue altogether. I don't think that this has anything to do with Rails (nor is Rails generating that). I believe that SQLite created this table and your schema just picks it up (as it should). Apparently the table is used to hold stored procedures of some kind.

Kostas Rousis
  • 5,918
  • 1
  • 33
  • 38
  • `RAILS_ENV=test rake db:reset` gives me 'unknown command "RAILS_ENV". I am not (consciously) running migrations for my test environment, I'm running them for my development environment, but there's only one `schema.db`, automatically generated when I run `rake db:migrate` (on migrations generated by `rails generate scaffold`), and `rake test` (on the tests automatically created when I generated the scaffold) objects to it. I've not touched the way the tests load their data (they're picking it up just fine from automatically generated fixtures). – digitig May 20 '14 at 11:15
  • are you on windows? if so try `set RAILS_ENV=test` and then issuing `rake db:reset`. Don't forget to set back the environment once you're done. – Kostas Rousis May 20 '14 at 11:15
  • That started to run my seed files, which seemed wrong (and which would take about an hour), so I killed it. Should the seed files run in the test environment? Anyway, I've examined the test.sqlite3 database directly, and there only tables are the one's I specified (and they contain the fixture data from the last test I ran), so is a database reset likely to do anything? – digitig May 21 '14 at 09:11
  • Well if you are using seed files chances are that you need them during testing as well. If you want to skip the seeding phase `rake db:schema:load` (if you need first `rake db:drop` and `rake db:create`) – Kostas Rousis May 21 '14 at 09:22
  • Ok, at least doing the `rake db:drop` as a separate action confirmed that it was the test database that was being affected. Unfortunately, going through that process -- deleting the offending lines, executing `set RAILS_ENV=test`, `rake db:drop` `rake db:create` and `rake db:schema:load` did no good at all. On the next migration, Rake/Rails regenerated `schema.rb` with the offending lines back in. I don't need the seeds for the test cases, by the way -- everything they need is in the YAML test fixtures. – digitig May 21 '14 at 11:41
  • Depends on why and how you use `seeds.rb` I guess. So, if you browse your SQLite databases (test and/or production) you see no `sqlite_sp_functions` table? – Kostas Rousis May 21 '14 at 11:45