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.