Rails: How to handle existing invalid dates in database?

Question

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.

Can you cast the value to a string instead? – Mike Christensen Aug 01 '13 at 16:53 — Mike Christensen, Aug 01 '13 at 16:53
What is the column type? – Chris Heald Aug 01 '13 at 16:53 — Chris Heald, Aug 01 '13 at 16:53
@ChrisHeald added the column type to my question: DATETIME – hurikhan77 Aug 01 '13 at 16:57 — hurikhan77, Aug 01 '13 at 16:57

score 2 · Answer 1 · answered Dec 08 '14 at 03:09

2

I think the simplest solution that worked for me was to set in database.yml file cast: false, e.g. for development section

development
  <<: *default
  adapter: mysql2    
  (... some other settings ...)
  cast: false

answered Dec 08 '14 at 03:09

bartgras

442
3
13

1

This sounds interesting... I'll follow up on it. – hurikhan77 Dec 08 '14 at 09:28

score 0 · Answer 2 · answered Aug 01 '13 at 17:04

0

try this out

ActiveRecord::AttributeMethods::BeforeTypeCast provides a way to read the value of the attributes before typecasting and deserialization.

http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/BeforeTypeCast.html

answered Aug 01 '13 at 17:04

Sachin Singh

7,107
6
40
80

1

That won't work because the code never reaches that point. An invalid date exception is thrown from within the mysql gem which is out of ActiveRecords scope... I already tried that just to realize that the error is generated somewhere in C code of the mysql gem. – hurikhan77 Aug 01 '13 at 17:18
I think you need to find a way for ActiveRecord to tell mySql to cast the date to a string in your *query*. Ie, `SELECT DATE_FORMAT(YourCol, '%c-%e-%Y')` – Mike Christensen Aug 01 '13 at 17:29

Rails: How to handle existing invalid dates in database?

2 Answers2

Linked