如何妥善处理和QUOT; Mysql2 ::错误：无效的日期和QUOT;在ActiveRecord的？ [英] How to gracefully handle "Mysql2::Error: Invalid date" in ActiveRecord?

问题描述

我要建在一个传统的数据库，也有一些破记录在不同的表在Rails 3.2的应用程序。其中给予最头疼的问题是，它包括无效的日期。

I'm building a Rails 3.2 app upon a legacy database which also has some broken records in different tables. One of the issues giving the most headache is that it includes invalid dates.

我设置一个沙箱，我手动固定有一次让我的code工作。现在是时候进行部署。因为这个原因，所述沙箱复位每天晚上和从活动数据库中复制，鼬重建索引和迁移被重新施加。我们将部署到沙箱中往往能得到的最后修订部署到现场安装前。

I've setup a sandbox which I manually fixed one time to get my code working. Now it's time for deployment. For this reason, the sandbox is reset every night and copied from the live database, ferret indexes are rebuilt, and migrations are re-applied. We are going to deploy to the sandbox often to get in the last fixes before deploying to the live setup.

由于传统的PHP应用程序，而这个新的Rails应用程序需要并行几个星期跑个月，我们不能简单地一次性修复时间（更新：只是为了澄清，该意味着它们在同一数据库在同一时间运行）。我需要一种方法来自动完成这个，可能与迁移或耙任务（我会去为后者）。

As the legacy PHP app and this new Rails app need to run in parallel for a few weeks to months, we cannot simply one-time-fix the dates (Update: just for clarification, that means they run on the same database at the same time). I need a way to automate this, maybe with a migration or rake task (I'd go for the latter).

但问题是：ActiveRecord的电抗器上加载这些记录，所以我没有办法调查记录，并在红宝石code做了一些硬codeD假设固定日期

But the problem is: ActiveRecord chokes on loading such records so I have no way to investigate the record and fix the dates by some hardcoded assumptions made in ruby code.

第二个问题是，传统的数据库有不一致的地方，因为PHP code没有使用的交易和一些code路径被打破，留下孤儿和破表约束后面。我将与处理，因为它们发生，其中大部分已经照顾的车型。第一个问题去的日期。

A second problem is that the legacy database has inconsistencies because the PHP code did not use transactions and some code paths are broken and left orphans and broken table constraints behind. I will deal with that as they occur, most of them is already taken care of in the models. First problem goes with the dates.

您如何通常会解决这个问题？也许甚至还有一些神奇的宝石在那里，它支持通过拦截异常和运行一些尝试，以修复code迁移与破纪录遗留数据库...

How would you usually fix this? Maybe there's even some magic gem out there which supports migrating legacy databases with broken records by intercepting exceptions and running some try-to-fix code...

迁移路径使用MySQL，和三个生产环境（稳定与活数据库，具有相同的数据库分期，和沙箱与数据库克隆复位每天晚上）。我们决定不这样做一次性的数据映射/迁移，因为我们无法取代完整的遗留应用程序在一个步骤（它包括一个CMS的约50000篇，数以百计的主题，用图片和下载庞大的文件数据库，支持约10个网站，约12年的数据和工作，从不同的编程技巧凌乱的PHP code，重复的code，从不同的迁移阶段，RSS内容拉动，从合作伙伴网站混文章/帖子从那里进入文章时间表我们自己的应用程序的话题，和更多有趣的东西...

The migration path uses MySQL, and three production environments (stable with live database, staging with the same database, and sandbox with a database clone reset every night). We decided against doing a one-time data mapping / migration because we cannot replace the complete legacy application in one step (it consists of a CMS with about 50000 articles, hundreds of topics, huge file database with images and downloads, supporting about 10 websites, about 12 years of data and work, messy PHP code from different programming skills, duplicated code from different migration stages, pulling in RSS content from partner sites to mix articles/posts from there into the article timelines in our own application's topics, and a lot more fun stuff...

第一步是迁移后端应用程序，以获得一致的管理和发布接口。遗留前端应用仍然需要写入数据库（评论和游客创造其他内容）。因此，固定数据库的过程必须能够定期自动运行。

First step is to migrate the backend application to get a consistent admin and publishing interface. The legacy frontend applications still need to write to the database (comments and other content created by visitors). So the process of fixing the database must be able to run unattended on a regular basis.

我们已经在地方，妥善处理在belongs_to的和的has_many断模式依赖修复。回形针一体化的设计与所有发明了神奇的文件名映射工作。而空气制动宝石报告所有应用程序崩溃给我们的管理平台安装，所以我们得到的所有左怪癖的快速概述。

We already have fixes in place that gracefully handle broken model dependencies in belongs_to and has_many. Paperclip integration has been designed to work with all the fantastic filename mappings invented. And the airbrake gem reports all application crashes to our redmine installation so we get a quick overview of all the left quirks.

在旧的应用程序已经被修改了最新的MySQL版本的工作，并已迁移到当前的MySQL数据库服务器。

The legacy applications have already been modified to work with the latest MySQL version and has been migrated to a current MySQL database server.

推荐答案

我有同样的问题。解决的办法是告诉mysql2不进行铸造，像这样的：

I had the same problem. The solution was to tell mysql2 not to perform casting, like this:

client.query(sql, cast: false).each do |row|
  row['some_date'] = Date.parse(row['some_date']) rescue(nil)
end

请参阅 mysql2文档的关于如何建立客户对象的详细信息。如果需要的话，进入轨道DB配置通过的ActiveRecord :: Base.configurations 。

See mysql2 documentation for details on how to build client object. If required, access rails db config via ActiveRecord::Base.configurations.

这篇关于如何妥善处理和QUOT; Mysql2 ::错误：无效的日期和QUOT;在ActiveRecord的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！