SQLite3 acting very strangely

Rails
1

good evening (and morning, also!) everyone. this is my first attempt
at using sqlite3 for a rails app, and so far, not so good

when I try to save a very basic record from the controller, it gives
me back an ActiveRecord::StatementInvalid error.

“that doesn’t sound very nice”

so I switched the app to mysql and sure enough, it went through
without a hitch. unsatisfied, I switched back to sqlite3 and fired up
the console

mike@lua:~/Rails/hotspots$ ./script/console
Loading development environment.

spot = Hotspot.new :title => “testing”, :address => “222 water
st”
=> #<Hotspot:0xb732af48 @attributes={“title”=>“testing”,
“notes”=>“NULL”, “user_id”=>0, “lat”=>“NULL”, “address”=>“222 water
st”, “long”=>“NULL”}, @new_record=true>

spot.save
=> true

“???”

and here’s the source, right from the controller’s mouth:

def create
if session[:user]
spot = Hotspot.new :title => “testing”, :address => “222 water st”
spot.save
end
end

and here’s what that gives me:

Processing ApiController#create (for 127.0.0.1 at 2007-05-09 23:30:52)
[POST]
Session ID: b859a7e645f2c60ddd4747368a677b04
Parameters: {“title”=>“asdf”, “action”=>“create”, “notes”=>"",
“controller”=>“api”, “address”=>“asdf”, “lat”=>“47.54501765940571”,
“long”=>"-52.7204704284668"}
User Load (0.000560) SELECT * FROM users WHERE (login = ‘mikew’
and password = ‘test’) LIMIT 1
SQL (0.000000) SQLite3::SQLException: SQL logic error or missing
database: INSERT INTO hotspots (“title”, “notes”, “user_id”,
“address”, “lat”, “long”) VALUES(‘testing’, ‘NULL’, 0, ‘222 water st’,
‘NULL’, ‘NULL’)

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error
or missing database: INSERT INTO hotspots (“title”, “notes”,
“user_id”, “address”, “lat”, “long”) VALUES(‘testing’, ‘NULL’, 0, ‘222
water st’, ‘NULL’, ‘NULL’))

now I can’t make any sense of what’s going on

2

Have you checked your database.yml file? I suspect it might not be
set up properly.

The relevant section of database.yml should look like:
adapter: sqlite3
dbfile: db/development.db

Note that SQLite uses “dbfile” rather than “database” to point to the
database, and “dbfile” points to the actual file in the filesystem.
There’s no “username” or “password” fields, since these aren’t used by
SQLite.

For what it’s worth, once you get these sort of issues sorted out with
SQLite, I’ve found it’s an extremely robust solution for small Rails
sites.

Regards

Dave M.

3

the rails app was generated with rails appname --database=sqlite3,
so it created the database and setup the database config for me. you
can see that right above the troublesome query, it even loads data
from the user table.

the schema is all controlled by db:migrate as well, so the MySQL and
SQLite databases are identical

here is my database.yml file though:

mike@lua:~/Rails/hotspots$ cat config/database.yml

SQLite version 3.x

gem install sqlite3-ruby

development:
adapter: sqlite3
database: db/development.sqlite3
timeout: 5000

Warning: The database defined as ‘test’ will be erased and

re-generated from your development database when you run ‘rake’.

Do not set this db to the same as development or production.

test:
adapter: sqlite3
database: db/test.sqlite3
timeout: 5000

production:
adapter: sqlite3
database: db/production.sqlite3
timeout: 5000

4

my guess is that the ‘NULL’ values are the clues.

null values in the database should come back as nil in rails

ex:

u = User.find(2)
=> #<User:0xb740fd54 @attributes={“created_on”=>“2007-04-30 06:34:09”,
“last_login”=>nil, “name”=>“Bob Jones”, “login_flag”=>“0”,
“updated_on”=>“2007-04-30 06:34:09”, “active_flag”=>“1”, “id”=>“2”,
“admin_flag”=>“1”, “station”=>nil, “login”=>“bjones”}>

my guess is that you are storing the actual string NULL in the
database somehow when you shouldn’t be and mysql is being forgiving
about it whereas sqlite3 is not.

look in your model or your migrations for where you might be setting
default values incorrectly.

other than that, nothing else jumps out as being strange or wrong.

5

On 5/11/07, mikew [email protected] wrote:

oh, when I do fetch the record in the console, those null values come
back as nil for ruby
what I don’t understand is how the same code can be used in the
console and the controller and work in one and not the other

Do you know which version of sqlite3 you’re using?

This might help:

Ruby on Rails — Using SQLite3 with Rails

d.

6

well, something is causing the empty attributes to be set to “NULL”
instead of nil.

here’s some data i have in an sqlite3 db

sqlite> select * from topics where parent_id IS NULL limit 1;
id name url_name parent_id

1 Technology technology

script/console

t = Topic.find(1)
=> #<Topic:0x382ce10 @attributes={“name”=>“Technology”, “id”=>“1”,
“url_name”=>“technology”, “parent_id”=>nil}>

t = Topic.new :name => “Foo”, :url_name => “foo”
=> #<Topic:0x382910c @new_record=true, @attributes={“name”=>“Foo”,
“parent_id”=>nil, “url_name”=>“foo”}>

another thing you might try is to go into the sqlite3 console and
check out the schema for the table
$>sqlite3 db/development_db
sqlite> .schema <table_name>

and see what the default is

let us know what you find out

7

oh, when I do fetch the record in the console, those null values come
back as nil for ruby

what I don’t understand is how the same code can be used in the
console and the controller and work in one and not the other

the models are blank except for the has_many/belongs_to/habtm
relationships. and the migrations don’t set any default values

8

oh sorry chris, you were right. I thought I saw it returning ‘nil’
values, but it turns out it sets and returns NULL values

$ ./script/console
Loading development environment.

spot = Hotspot.new :title => “test”, :address => “2 signal hill rd”
=> #<Hotspot:0xb73ebea0 @attributes={“created_on”=>nil,
“title”=>“test”, “notes”=>“NULL”, “user_id”=>0, “lat”=>“NULL”,
“address”=>“2 signal hill rd”, “long”=>“NULL”}, @new_record=true>

spot.save
=> true

Hotspot.find :first
=> #<Hotspot:0xb7364e28 @attributes={“created_on”=>“2007-05-11
11:43:13”, “title”=>“test”, “id”=>“1”, “notes”=>“NULL”,
“user_id”=>“0”, “address”=>“2 signal hill rd”, “lat”=>“NULL”,
“long”=>“NULL”}>

here’s the schema:

$ sqlite3 db/development.sqlite3
SQLite version 3.3.13
Enter “.help” for instructions
sqlite> .schema hotspots
CREATE TABLE hotspots (“id” INTEGER PRIMARY KEY NOT NULL, “title”
varchar(255) DEFAULT NULL, “address” varchar(255) DEFAULT NULL,
“notes” varchar(255) DEFAULT NULL, “user_id” integer DEFAULT NULL,
“lat” varchar(255) DEFAULT NULL, “long” varchar(255) DEFAULT NULL,
“created_on” datetime DEFAULT NULL);
sqlite>

as you can see I’m using sqlite 3.3.13. that’s no biggie though, I’ll
try manually compiling 3.3.7 and the qslite3-ruby gem and see if that
works out

9

after installing sqlite3 3.3.7 and reinstalling sqlite3-ruby there’s
still no change. I still get that big error in the controller and NULL
values in the console

also, this is interesting. here is the migration used to create the
table:

$ cat db/migrate/001_create_hotspots.rb
class CreateHotspots < ActiveRecord::Migration
def self.up
create_table :hotspots do |t|
t.column :title, :string
t.column :address, :string
t.column :notes, :string
t.column :user_id, :integer
t.column :lat, :string
t.column :long, :string
t.column :created_on, :datetime
end
end

def self.down
drop_table :hotspots
end
end

yet here’s the schema.rb in the end

$ cat db/schema.rb

This file is autogenerated. Instead of editing this file, please use

the

migrations feature of ActiveRecord to incrementally modify your

database, and

then regenerate this schema definition.

ActiveRecord::Schema.define(:version => 4) do
…
create_table “hotspots”, :force => true do |t|
t.column “title”, :string, :default => “NULL”
t.column “address”, :string, :default => “NULL”
t.column “notes”, :string, :default => “NULL”
t.column “user_id”, :integer, :default => 0
t.column “lat”, :string, :default => “NULL”
t.column “long”, :string, :default => “NULL”
t.column “created_on”, :datetime
end
…
end

10

okay, I’ve cleaned up those NULL values. the problem was I had
ubuntu’s libsqlite3-ruby1.8 package installed. once I uninstalled that
the values were being stored properly as nil. but I still get the
StatementInvalid error from the controller

Processing ApiController#create (for 127.0.0.1 at 2007-05-11 12:35:15)
[POST]
Session ID: 74e977db220146ab742e2eb0811854d4
Parameters: {“title”=>“asdf”, “action”=>“create”, “notes”=>"",
“controller”=>“api”, “address”=>“asdf”, “lat”=>“47.55857307874545”,
“long”=>"-52.71583557128906"}
User Load (0.000549) SELECT * FROM users WHERE (login = ‘mikew’
and password = ‘test’) LIMIT 1
SQL (0.000000) SQLite3::SQLException: SQL logic error or missing
database: INSERT INTO hotspots (“created_on”, “title”, “notes”,
“user_id”, “address”, “lat”, “long”) VALUES(‘2007-05-11 12:35:15’,
‘testing’, NULL, NULL, ‘222 water st’, NULL, NULL)

ActiveRecord::StatementInvalid (SQLite3::SQLException: SQL logic error
or missing database: INSERT INTO hotspots (“created_on”, “title”,
“notes”, “user_id”, “address”, “lat”, “long”) VALUES(‘2007-05-11
12:35:15’, ‘testing’, NULL, NULL, ‘222 water st’, NULL, NULL))

11

at the sqlite3 command prompt run

.schema hotspots

and make sure the columns that are being inserted with NULL values can
take NULL.

also, cut/paste that query from the error into the prompt and see what
happens

12

the only field that won’t accept null is the id field

CREATE TABLE hotspots (“id” INTEGER PRIMARY KEY NOT NULL, “title”
varchar(255) DEFAULT NULL, “address” varchar(255) DEFAULT NULL,
“notes” varchar(255) DEFAULT NULL, “user_id” integer DEFAULT NULL,
“lat” varchar(255) DEFAULT NULL, “long” varchar(255) DEFAULT NULL,
“created_on” datetime DEFAULT NULL);

I cut and pasted the failing code from the error message into the
sqlite prompt and it went through without a hitch

$ sqlite3 db/development.sqlite3
SQLite version 3.3.7
Enter “.help” for instructions
sqlite> SELECT * FROM hotspots;
sqlite> INSERT INTO hotspots (“created_on”, “title”, “notes”,
“user_id”, “address”, “lat”, “long”) VALUES(‘2007-05-11 18:42:52’,
‘testing’, NULL, NULL, ‘222 water st’, NULL, NULL);
sqlite> SELECT * FROM hotspots;
1|testing|222 water st|||||2007-05-11 18:42:52
sqlite>

13

Mike,

It still looks to me as though the database.yml is wrong. I think

development:
adapter: sqlite3
database: db/development.sqlite3
timeout: 5000

should be

development:
adapter: sqlite3
dbfile: db/development.sqlite3
timeout: 5000

and similarly for test and production.

Regards,

Andrew

14

I posted on the Rails Trac, but that doesn’t seem to be going anywhere
either
http://dev.rubyonrails.org/ticket/8360

so for now, the app has switched to MySQL

15

actually either works, however :database is the preferred option due
to consistency.

http://dev.rubyonrails.org/ticket/2644