Unable to output MySQL tables which involve dates in Sequel
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 err开发者_如何学Goor is not shown when a table which does not feature a date is accessed. This is running on Debian.
Heads up:
Sequel::MySQL.convert_invalid_date_time = nil
no longer works, but I just fixed this same problem by setting the option on my DB object:
DB = Sequel.connect("mysql://localhost/#{DB_NAME}")
DB.convert_invalid_date_time = nil
Hope this helps someone else!
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.
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.
精彩评论