First, this is directly related to my other question: How to gracefully handle "Mysql2::Error: Invalid date" in ActiveRecord?
But I still do not want to jump through all the loops of writing migrations which fix dates. That won't be the last table with invalid dates and I need some more generic approach.
So here we go:
I'm using a legacy MySQL database which contains invalid dates, sometimes like 2010-01-00 or 0000-04-25... Rails does not load such records (older versions of Rails did).
I do not want to (and cannot) correct these dates manually or automated. It should be up to the authors of those records to correct these dates. The old system was a PHP application which allowed such annoyances. The Rails application should/will just prevent the user from saving the record until the dates are valid.
The problem does not seem to be within Rails itself, but deeper within an .so library of the rails mysql gem.
So my question is not about how to validate the date or how to insert invalid dates. I don't want to do that and that's covered by numerous answers all over stackoverflow and the rest of the internet. My question is how to READ invalid dates from MySQL that already exist in the database without Rails exploding into 1000 little pieces...
The column type is DATETIME and I'm not sure if casting to string could help because Rails chokes before any ActiveRecord related parsing kicks in.
Here's the exact error and backtrace:
$ rails c
Loading development environment (Rails 3.2.13)
irb(main):001:0> Poll.first
Poll Load (0.5ms) SELECT `polls`.* FROM `polls` LIMIT 1
Mysql2::Error: Invalid date: 2003-00-01 00:00:00
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:216:in `each'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:216:in `to_a'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:216:in `exec_query'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/mysql2_adapter.rb:224:in `select'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/querying.rb:38:in `find_by_sql'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/explain.rb:41:in `logging_query_plan'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/querying.rb:37:in `find_by_sql'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/relation.rb:171:in `exec_queries'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/relation.rb:160:in `to_a'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/explain.rb:34:in `logging_query_plan'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/relation.rb:159:in `to_a'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/relation/finder_methods.rb:380:in `find_first'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/relation/finder_methods.rb:122:in `first'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/querying.rb:5:in `__send__'
from /home/kakra/.gem/ruby/1.8/gems/activerecord-3.2.13/lib/active_record/querying.rb:5:in `first'
from (irb):1
The backtrace remains the same even when I do Poll.first.title so some date should never reach any output routine in IRB and thus should never be parsed. So suggestions to use a value before typecasting would not help.
