Migration not working on sqlite3, works on MySQL


#1

I’m working through AWDwRoRv2 and have run into a snag on page 80.

The code is a migration:
http://pastie.textmate.org/64674

The error I get occurs when I run “rake db:migrate” using sqlite3 as
my database:
SQLite3::SQLException: near “ADD”: syntax error: ALTER TABLE
products ADD “price” decimal(8,2) DEFAULT 0

It works if I use MySQL instead. Is there a problem with sqlite3,
rails, or a configuration error on my computer?

Miles


#2

Not sure. Running on fumes here, but check that you’re not using
incompatible options for sqlite3. Also, see if changing 0 to 0.00
helps. Again, I’m running on fumes.

HTH


#3

I’ve had a few SQL-based problems moving to SQLite, but I first had a
few problems with compatability. I can’t speak for your environment,
but I ended up re-installing SQLite and anything I could find similar
to it to get it working correctly. I wish I had good notes from that
day to remember what all the hangups were, but I was trying to rush
out of town and wanted some rails apps to take with me on my laptop
without taking the time to install mysql or something similar.

I threw together a fast test to make sure I couldn’t see anything else
wrong with what you’ve provided. It’s:

  • a fresh rails app (v1.2.3)
  • with a sqlite3 database configured
  • and a migration that uses a decimal type
  • with precision “8,2” and default 0

I’ve dropped it on www.showcase60.com/test_for_milesf.tar.gz if you’d
like to take a look.

Warm regards,

David


#4

Thanks David. The app you sent me throws an error too, so I’m
assuming it’s something wrong with my install. Really appreciate your
help :slight_smile:

Cheers,

Miles


#5

removed_email_address@domain.invalid wrote:

The error I get occurs when I run “rake db:migrate” using sqlite3 as
my database:
SQLite3::SQLException: near “ADD”: syntax error: ALTER TABLE
products ADD “price” decimal(8,2) DEFAULT 0

Are you on Mac? If so, then you need to install the most recent version
of SQLite3. The version of SQLite3 that comes on 10.4 does not recognize
the ‘ADD (COLUMN)’ syntax. You may also need to uninstall the
sqlite3-ruby gem and reinstall it.

Worked for me.

Karl


#6

Yep, that did it. I installed the latest version of sqlite from
source and ripped 'n reinstalled the sqlite3-ruby gem and all works
fine now.

Thanks for the help!

Miles

On May 25, 11:49 pm, Karl S. removed_email_address@domain.invalid