导轨：如何处理现有的无效日期在数据库中？ [英] Rails: How to handle existing invalid dates in database?

问题描述

首先，这直接关系到我的其他问题： <一href="http://stackoverflow.com/questions/16389313/how-to-gracefully-handle-mysql2error-invalid-date-in-activerecord">How要妥善处理和QUOT; Mysql2 ::错误：无效的日期和QUOT;在ActiveRecord的？

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.

所以，在这里我们去：

我使用的是其中包含无效的日期遗留下来的MySQL数据库，有时像2010-01-00或0000-04-25 ... Rails没有加载这些记录（较旧版本的Rails一样）。

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).

我不希望（也不能）手动或自动纠正这些日期。它应该是达到这些记录的作者来纠正这些日期。旧的系统是一个PHP应用程序，它允许这样的烦恼。 Rails应用程序应该/只会prevent用户从保存记录，直到日期是否有效。

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.

看起来问题不被内Rails的本身，而是更深层次的轨道mysql的宝石的.so库中。

The problem does not seem to be within Rails itself, but deeper within an .so library of the rails mysql gem.

所以我的问题不是关于如何验证日期或如何插入无效日期。我并不想这样做，而这涉及众多的答案各地计算器和互联网的休息。我的问题是如何读取从MySQL无效的日期已经在数据库中存在没有Rails的爆炸为1000小块......

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...

列类型为 DATETIME ，我不知道，如果强制转换为字符串，可以帮助由于Rails之前

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

回溯保持，甚至当我做 Poll.first.title 所以一些日期应永远不会达到内部评级法的任何输出例行程序，因此不应该被解析相同。因此，建议使用前值类型转换不会有所帮助。

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.

推荐答案

我觉得工作对我来说是最简单的解决方案是设置在的database.yml 文件中的主演：假，例如：发展部分

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

这篇关于导轨：如何处理现有的无效日期在数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！