ActiveRecord::StatementInvalid in update method using Oracle

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.

Hello there,

We're experiencing exactly the same problem: an ORA-01861 error when
performing an update that intermittently fails in production, but not on
development or staging servers.

A quick question: do you connect to multiple databases from this
application? Have you found a fix? Which version of Rails are you
rolling on?

~ M

fotn wrote:

Which version of Rails are you rolling on?

Initially we were using Rails 1.1.6 with Ruby 1.8.4, ruby-oci8 adapter
0.1.6
We have recently upgraded to Rails 2.0.2, Ruby 1.8.6, ruby-oci8
adapter 1.0.0 and activerecord-oracle-adapter

The issue is ostensibly a failure to convert the date/datetime string
to the correct oracle format. The initial context of this error seems
to be resolved; however, we had the problem reappear (in a different
application) when using the activerecord_store session (it has been
remedied for the time being by returning to cookie session store).

do you connect to multiple databases from this application?

The production databases are load balanced on two servers; however,
each Rails application only talks to a single instance, e.g. if the
tnsnames.ora has an entry for PROD which load balances server1 and
server2, and a database prd, the entry in the database.yml file is

database: server1:1521/prd

so it always talks to a single instance of the production database,
even though the databases themselves are load-balanced.

Have you found a fix?

Not directly; however, upgrading to Rails 2.0.2 seems to have improved
the situation, even if it has not remedied it entirely.