Using SQLite3 and MySQL on Windows

Way back in 2007, Rails 2.0.2 changed the default database from MySQL to SQLite3, making it easier for newcomers to get up and running with Rails quickly without having to worry about setting up a database. Unfortunately for Windows users, trying to install the SQLite3 gem with gem install sqlite3-ruby will result in the following error:

Building native extensions.  This could take a while…
ERROR:  Error installing sqlite3-ruby:
        ERROR: Failed to build gem native extension.

This happens because the gem makes use of some C code which needs to be compiled. Most Windows users won’t have the necessary compilers setup and therefore gem installation fails.

Rather than give yourself a migraine trying to get the gem to compile, the simple solution is to use a slightly older version of the gem (version 1.2.3 instead of the latest version 1.2.4), as this has a pre-compiled Windows binary available. To use this gem you need to specify the version on the command line like so:

gem install sqlite3-ruby -v1.2.3

MySQL gem is borked (or yet another Windows problem)

If you get this bizarre error message when you try to use MySQL in your Ruby or Rails code:

undefined method `each' for #<Mysql:0x4841af8>

…and you’re running MySQL 5.1 then you’ve hit a compatibility problem between the gem and the MySQL 5.1 client library. One solution that has been found to work is to use an older version of the client library DLL. Simply download the DLL, copy it into your Ruby installation’s bin folder, restart MySQL and normal service should resume.

Greetings ...

Yes I has that same problem with SQLite compile and later with ParseTree, not to mention several other gem packages that I didn't/couldn't bother investigate because of the C broken c compile.

Fortunately Tim on merb-talk pointed me to this blog on here:

http://thewebfellas.com/blog/2007/12/2/imagescience-and-rubyinline-on-windows ~ Rob Anderton

It is a good start to get a compile, and not the whole story. In my book there are two responsible chunks of code here. The ruby platform config targets machines, it needs to target compilers. I spent the better part of my life ensuring code worked on 4 or 5 Pascal, C, and C++ compilers, for different platforms and operating systems. Targeting an operating system as if it is a static entity is not practical imho.

The helpful bit is to look at your ruby config file and the config.h files (per Rob Anderton blog).

The second bit is the RubyInline code itself. The diagnostics can be more helpful to figure out what is happening, imho. As a general aside on a lot of code, closed and open source. My distinction between 'a-programmer' and 'a-coder' is that coders are optimistic and programmers consider what goes wrong first; they don't simply write code as-if it will just work all the time.

That later point includes these problematic Gems that optimistically look at the C compile working, instead of finding a way to test it works. I can paraphrase one bug response that said, not interested in wasting time supporting a brain-dead windows for that gem. Which is fair enough. Everyone volunteers time on Open Source, ... It is that person's right.

Why not include "might not work on Windows" in the manifest? If that's the contract with the 'consuming' developer public.

On the helpful side, RubyInline (internally) seem have old-fashioned ideas on the compile and link requirements. In my case I'm still working out the parameters for MS Express 2008 that work for the gems. The RubyInline test cases work OK. Unfortunately all I can say for now, is if your problem isn't solved with Rob Anderton's blog and a few updates on compile switches; make a copy of RubyInline.rb and add some puts lines to figure out what you need to do (or email me, in case I got lucky and things compile for me).

One 'more' thing. I almost forgot why I took time-out of a very frustrating day to comment. I use the ...

gem install sqlite3-ruby -v1.2.3

v1.2.3 gem myself, and I worked that out almost by accident. However, given my other challenges, I took at look at the SQLite v1.2.3 gem 'make', the reason it works is because it doesn't compile, it uses the Windows dynamic library infrastructure the way it was meant to work.

Also, there's no functional difference between 1.2.3 and 1.2.4. It is all cosmetic.

I'd like to encourage readers to lobby for a "--nomake" gem option that will probably allow most of us to get on with out lives, reducing the unnecessary compile stages to a number approaching zero.

Plan B in my world domination by good practice is to encourage gem builders to compile in the sensible DLL-s or lib.a for the target machine (rubyconfig).

Argue the point with your friends. On my side of "life" I took up marketing to learn how, among other things, "how come open source sells less than you know who?" This example and Rob A's blog are good example 'of why'.

AND gentle reader, I leave that riddle for you to ponder. Before answering it later. If you want my opinion or even what the research shows, visit my blog and ask questions.

Aloha, Will

4 comments

Skip to comment form

Comment on Using SQLite3 and MySQL on Windows by Luis Lavena

April 18th, 2009 @ 16:16 – permalink

Good post Michael!

Work has been made in this front, for those bugs and compilation issues. See both forks of mysql-gem and sqlite3-ruby at GitHub:

http://github.com/luislavena/sqlite3-ruby/tree/master

http://github.com/luislavena/mysql-gem/tree/master

Cheers, Luis Lavena
Comment on Using SQLite3 and MySQL on Windows by Toni

April 30th, 2009 @ 13:34 – permalink

Hi Michael,

I am running now Rails 2.3.2 on Ruby 1.8.7-p72 for Windows Vista-32 with MySQL 5.1.34 with the mysql-gem 2.7.3.

I have been running also into the same problems as you did and solved it also with this older version of the client library DLL. During the installation of mysql I told him to configure the PATH environment variable. I just made a little application with the scaffold and everything seems now to work. Hopefully it stays like that. I didnt installed again the GUI Tools of MySQL, because there is also the DLL included. I will install the GUI Tool later to see if it irritates my system.

Sometimes the Rails Installation can give you a hard time :-)

Cheers
Comment on Using SQLite3 and MySQL on Windows by will

July 7th, 2009 @ 09:15 – permalink
Greetings ...

Yes I has that same problem with SQLite compile and later with ParseTree, not to mention several other gem packages that I didn't/couldn't bother investigate because of the C broken c compile.

Fortunately Tim on merb-talk pointed me to this blog on here:

http://thewebfellas.com/blog/2007/12/2/imagescience-and-rubyinline-on-windows ~ Rob Anderton

It is a good start to get a compile, and not the whole story. In my book there are two responsible chunks of code here. The ruby platform config targets machines, it needs to target compilers. I spent the better part of my life ensuring code worked on 4 or 5 Pascal, C, and C++ compilers, for different platforms and operating systems. Targeting an operating system as if it is a static entity is not practical imho.

The helpful bit is to look at your ruby config file and the config.h files (per Rob Anderton blog).

The second bit is the RubyInline code itself. The diagnostics can be more helpful to figure out what is happening, imho. As a general aside on a lot of code, closed and open source. My distinction between 'a-programmer' and 'a-coder' is that coders are optimistic and programmers consider what goes wrong first; they don't simply write code as-if it will just work all the time.

That later point includes these problematic Gems that optimistically look at the C compile working, instead of finding a way to test it works. I can paraphrase one bug response that said, not interested in wasting time supporting a brain-dead windows for that gem. Which is fair enough. Everyone volunteers time on Open Source, ... It is that person's right.

Why not include "might not work on Windows" in the manifest? If that's the contract with the 'consuming' developer public.

On the helpful side, RubyInline (internally) seem have old-fashioned ideas on the compile and link requirements. In my case I'm still working out the parameters for MS Express 2008 that work for the gems. The RubyInline test cases work OK. Unfortunately all I can say for now, is if your problem isn't solved with Rob Anderton's blog and a few updates on compile switches; make a copy of RubyInline.rb and add some puts lines to figure out what you need to do (or email me, in case I got lucky and things compile for me).

One 'more' thing. I almost forgot why I took time-out of a very frustrating day to comment. I use the ...
```
gem install sqlite3-ruby -v1.2.3
```
v1.2.3 gem myself, and I worked that out almost by accident. However, given my other challenges, I took at look at the SQLite v1.2.3 gem 'make', the reason it works is because it doesn't compile, it uses the Windows dynamic library infrastructure the way it was meant to work.

Also, there's no functional difference between 1.2.3 and 1.2.4. It is all cosmetic.

I'd like to encourage readers to lobby for a "--nomake" gem option that will probably allow most of us to get on with out lives, reducing the unnecessary compile stages to a number approaching zero.

Plan B in my world domination by good practice is to encourage gem builders to compile in the sensible DLL-s or lib.a for the target machine (rubyconfig).

Argue the point with your friends. On my side of "life" I took up marketing to learn how, among other things, "how come open source sells less than you know who?" This example and Rob A's blog are good example 'of why'.

AND gentle reader, I leave that riddle for you to ponder. Before answering it later. If you want my opinion or even what the research shows, visit my blog and ask questions.

Aloha, Will
Comment on Using SQLite3 and MySQL on Windows by Rob Anderton

July 25th, 2009 @ 22:59 – permalink

Luis has just released version 1.2.5 of the SQLite gem with added Windows goodness: you can take a look here.

You can use Markdown in your comment as well as plain HTML. You can use <filter:jscode lang="ruby"> and </filter:jscode> tags to surround code blocks (supported languages are css, html, javascript and ruby). Your email address will not be published.

If your comment doesn’t appear immediately after posting it could have been marked as spam. Don’t worry: we regularly check for and approve incorrectly filtered comments so you shouldn’t have to wait too long for it to be shown.

ruby on rails, jruby, aws, ec2, exalead