12.27.07

Resolved: SQLite3::SQLException: SQL logic error or missing database

Posted in Ruby, sqlite3 at 11:00 am by Robert Horvick

After doing some cleanup I reset my Sqlite3 databases and tried to run tests to make sure that everything was still ok. The command I ran was:

$ rake test

and the output from each test was a failure with the message:

34) Error:
test_should_update_user(UsersControllerTest):
SQLite3::SQLException: SQL logic error or missing database

So I did the following:

  1. Check that test.sqlite3 exists.
  2. Rebuild test.sqlite3 using “rake db:reset”
  3. retry … still failed.
  4. Try loading fixtures using “rake db:fixtures:load”
C:\InstantRails\rails_apps\atompublog>rake db:fixtures:load
(in C:/InstantRails/rails_apps/atompublog)
rake aborted!
SQLite3::SQLException: no such table: atom_entries: DELETE FROM atom_entries WHERE 1=1

(See full trace by running task with --trace)

Ah ha!

That table shouldn’t exist (and it doesn’t…) so there should not be a fixture loading into it.

I checked my fixtures and there was one named “atom_entries.yml”. After deleting that file the fixtures loaded, the tests ran and I was confident that my cleanup changes did not break anything.

A quick google search shows that a lot of people have hit this problem so hopefully this post helps some of them track down one of the potential causes.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

10 Comments »

  1. Mark said,

    December 27, 2007 at 4:14 pm

    FYI- If your using SQLite for testing you might wanna try:
    http://agilewebdevelopment.com/plugins/memory_test_fix

    It makes your tests run much faster.

  2. YodaYid said,

    January 28, 2008 at 4:16 pm

    This post helped me a lot - I was going crazy because my tests wouldn’t run, and there was a bug in my fixtures (I was missing a field that I had defined as NOT NULL, which broke the INSERT statement). It would be nice if the errors were a bit more verbose when the unit test fails (when I ran the INSERT statement directly in sqlite3, it immediately told me exactly what the problem was).

    Anyway, thanks!

  3. Stephen Ball said,

    February 19, 2008 at 9:21 am

    Another way to track down this bug is to run the unit test file directly, e.g. ruby test/unit/page_test.rb

    Big stack trace results, and at the top:

    ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: pages: DELETE FROM pages WHERE 1=1

    That said, I get this error straight from a fresh rails scaffold setup so something is clearly not behaving as it should.

  4. Vern said,

    February 26, 2008 at 6:30 am

    3 days ago I didn’t even know what the freaking heck Ruby on Rails or SQlite was. I’m a dagnab artist not a rocket scientist!

    Anyway, I am getting the same error, a table that shouldn’t exist is getting referenced and I can’t figure out how to fix it as described. What “clean up” did you do?

    I am in the early learning stages. I’m creating a database with one table, for some reason it is creating a table reference that is the same name as the application. I also found this table.yml in the fixtures. If I delete it what else needs to be done to remove it from the system? A controller is referencing it as well instead of the correct table but they were created by ruby and I just can’t figure out how deep this incorrect table reference goes. Can’t fix it “by hand” it seems.

  5. Hiral said,

    April 3, 2008 at 1:30 pm

    I am still getting the error
    ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: pages: DELETE FROM pages WHERE 1=1

    It happens when I run my unit test seperately and when I include fixtures
    fixtures :students (without including this there is no problem)
    Surely something is breaking by loading fixtures.

  6. aotianlong said,

    April 10, 2008 at 6:08 am

    very useful,thank you.

  7. Graham Pengelly said,

    April 17, 2008 at 9:34 am

    Cheers… Was considering ending it all then after a morning of head scratching. My problem was a bit different but fixture related so you put me on the right track. I had two ‘users’ in a fixture and had given them the same id, causing the fixture to fail…

    Thanks

    Graham

  8. Danie said,

    April 29, 2008 at 2:24 pm

    Hooray for you! Finally got my app to run with the info in this post!
    Thanks a million ;)

  9. Sipa said,

    June 4, 2008 at 10:32 am

    i am getting this error when i try to install phpbb3 om my server.

    Error: SQL logic error or missing database [1]

    this is the last stage once you complete the instillation and press login button in your installations end after changing your install folder in phpbb3 at the root.

    What could be next possible stage to fix this and get going?

    Please help.
    ~~!! Sipa !!~~

  10. Ade said,

    July 2, 2008 at 1:42 pm

    This problem will also be triggered if your fixture columns don’t match up with your schema (for example, if you have recently migrated your database but forgotten to update your fixtures).

    Unfortunately, this error is totally unhelpful. What really helped me was simply switching my test database to MySQL. In my case, what had been this cryptic error from SQLite3 was reported by MySQL as “Unknown column ‘name’ in ‘field list’: INSERT INTO `shipping_addresses` etc.”, which made me instantly recognize the source of the error (I had changed “name” to first_name and last_name and forgotten to update the fixture).

    So my advice, if you’re getting this error: try change the db to MySQL (even just temporarily) to see if its superior error message will assist you.

Leave a Comment