Sorry about the length, but it includes what has already been tried and I'm at my wits end...
I have in production a RoR app which utilises an existing Oracle database. The app has its own user which has views, which updates the underlying tables using triggers, so that I can maintain RoR conventions for column names, etc. Recently, when attempting to update information, it has been throwing an ActiveRecord::StatementInvalid exception, by way of 'ORA-01861: literal does not match string format'.
First: checked the format of the fields. The dates in the SQL statement on the error page do not match the Oracle standard, but this doesn't seem to be the underlying cause. There are callbacks in place to ensure that the date fields in use are of the correct data type before saving. So it would appear that it's not the format of the data, even if that's what Oracle claims. (Further reasons for this conclusion are explained below.)
Next: the error ONLY occurs on the production servers (which utilise load-sharing), never on the test server (single machine). For a time it will occur more on one server than the other. Unfortunately, the problem will then swap to the other server and the cycle repeats itself.
Finally: The problem is intermittent (but getting worse). I have run multiple tests through jMeter on both the production servers and our test server and not been able to reproduce the error even under high loading conditions. Nor have I been able to reproduce the error manually on the test server through the app -- only on the production servers. (I have been using the production database in all instances). Note: in the jMeter tests, I have tested each of http://host1/app, http://host2/app and http://www/app (fully qualified in all cases) where host1 and host2 are the individual production servers.
So: In spite of what Oracle claims, it would appear that it is not a formatting error, nor is it the load-sharing mechanism, and it is impossible to replicate the error under test conditions. The only time it occurs is with the production database on the production servers. There has not been a similar problem with any other apps (Rails or otherwise), either -- only this one. The other apps do not update the same information, or as frequently, however.
Has anyone had a similar problem with Oracle/RoR (or even with another database)?
As an interim solution, I have included a rescue statement for the exception, which re-attempts to update the information. If it fails another 5 times, it fails more gracefully. I am interested in a more definitive solution, however.
Example:
ActiveRecord::StatementInvalid in [...]Controller#update OCIError: ORA-01861: literal does not match format string: UPDATE [table_name] SET [...], updated_at = '2007-10-02 09:36:59', [...], date_of_birth = '2007-10-02 00:00:00', [...], member_since = '2007-10-02 00:00:00', [...] WHERE id = '1'
Sorry about having to cut the other fields, I realise it makes it harder; however, much of the information is sensitive. I have been through each of these fields individually to see which cause the problems if I cut and paste into Oracle -- only the dates had a problem doing this directly; all other fields are fine.
I look forward to hearing some new suggestions as to how I might be able to solve this problem. Thanks.