Unable to output MySQL tables which involve dates in Sequel

Question

I'm trying to use Sequel to access a MySQL database in Ruby. When I try accessing a table which involves a date column, I am presented with an error. When I access a table without, it functions fine. What is wrong?

Example Code:

require 'rubygems'
require 'sequel'

DB = Sequel.connect(:adapter=>'mysql', :host=>'localhost', :database=>'db', :user=>'user', :password=>'password')

event = DB[:table]

puts event.all

Error:

/usr/lib/ruby/1.8/date.rb:956:in `new_by_frags': ArgumentError: invalid date (Sequel::InvalidValue)

The error is not shown when a table which does not feature a date is accessed. This is running on Debian.

Answer 1

The final solution involved switching to use the data_objects Ruby gem. This avoided issues using the native C MySQL driver.

The code is adjusted as follows:

require 'rubygems'
require 'sequel'

# connect to the db
DB = Sequel.connect('do:mysql://user:pass@localhost/database')

Possibly this could cause performance issues, but this is beyond the scope of my initial issue.

Thanks to lexu for commenting on the original question.

Answer 2

I had the same problem. It was caused by Sequel choking on MySQL's zero date ‘0000-00-00’. The solution I used was to set

Sequel::MySQL.convert_invalid_date_time = nil

(found here: http://groups.google.com/group/sequel-talk/browse_thread/thread/152a4131bd280966).

If you control the DB, you could also prevent MySQL storing zero dates using the NO_ZERO_DATE SQL mode: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_zero_date.