I''m looking to enhance the OCI adapter so that it automatically retries if it finds that the db connection had died -- this will allow the app to properly recover if the database has been bounced. I see that something similar has been implemented for the mysql adapter. My question is that in changeset 1622, the mysql #execute method was changed to add a "retries" parameter. The implication is that it would try N times. But it looks like that feature is unimplemented. Am I missing something?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 10, 2005, at 10:53 AM, Michael Schoen wrote:> I''m looking to enhance the OCI adapter so that it automatically > retries if it finds that the db connection had died -- this will > allow the app to properly recover if the database has been bounced.I think retrying is bad behavior. It should rollback the current transaction and raise an exception instead. Otherwise you are asking for data corruption.> I see that something similar has been implemented for the mysql > adapter. > > My question is that in changeset 1622, the mysql #execute method > was changed to add a "retries" parameter. The implication is that > it would try N times. > > But it looks like that feature is unimplemented. Am I missing > something?Check out ticket #428: http://dev.rubyonrails.org/ticket/428 Best to avoid the dead db connection in the first place. Plus, we can implement it in a more database-agnostic manner. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDc6LTAQHALep9HFYRAmkcAJ9bin+pwt+kwfqWvzauSDKAmNlbQgCgyRki xSR8OwfaXSAEROcajcDn5lY=rMm/ -----END PGP SIGNATURE-----
> I think retrying is bad behavior. It should rollback the current > transaction and raise an exception instead. Otherwise you are asking > for data corruption.Yep, agree w/ you there.>> I see that something similar has been implemented for the mysql adapter. >> >> My question is that in changeset 1622, the mysql #execute method was >> changed to add a "retries" parameter. The implication is that it >> would try N times. >> >> But it looks like that feature is unimplemented. Am I missing something?I presume then that the implementation in the mysql adapter is only half-done and considered less than optimal?> Check out ticket #428: http://dev.rubyonrails.org/ticket/428 > Best to avoid the dead db connection in the first place. Plus, we can > implement it in a more database-agnostic manner.Are you (or is anyone else) actively working this issue?
On 11/10/05, Michael Schoen <schoenm@earthlink.net> wrote:> > > > I think retrying is bad behavior. It should rollback the current > > transaction and raise an exception instead. Otherwise you are asking > > for data corruption. > > Yep, agree w/ you there.FWIW, I did this too, and I also agree, but I still whipped up this hack anyway that seems to work. If you''re not terribly concerned with corruption you might give it a try. I do trigger off of the specific oracle "connection closed" errors so depending on how oracle closes connections from the DB side there may not be a big chance of data corruption. Any oracle experts know the answer to that? Anyway, let me know if you come up with a better way. BTW, I''m using this with Rails 0.13 currently. require ''delegate'' module ActiveRecord module ConnectionAdapters class OCIConnectionFactory def new_connection(username, password, host) conn = OCI8.new username, password, host conn.exec %q{alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS''} conn.exec %q{alter session set nls_timestamp_format = ''YYYY-MM-DD HH24:MI:SS''} conn.autocommit = true conn end end class OCIReconnectOnError < DelegateClass(OCI8) def initialize(config, connfactory = OCIConnectionFactory.new) @username = config[:username] @password = config[:password] @host = config[:host] @connfactory = connfactory @connection = new_connection super @connection end def exec(sql, *vars) retry_count = 0 begin @connection.exec(sql, *vars) rescue Exception => e # ORA-03113: end-of-file on communication channel # ORA-03114: not connected to ORACLE if e.message =~ /ORA-0311[34]/ && retry_count < 2 @connection = new_connection retry_count += 1 retry else raise end end end def new_connection @connfactory.new_connection @username, @password, @host end end end class Base def self.oci_connection(config) #:nodoc: ConnectionAdapters::OCIAdapter.new(ConnectionAdapters:: OCIReconnectOnError.new(config), logger) end end end -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails-core/attachments/20051110/7f9ccfa8/attachment-0001.html
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 10, 2005, at 11:58 AM, Michael Schoen wrote:>> I think retrying is bad behavior. It should rollback the current >> transaction and raise an exception instead. Otherwise you are >> asking for data corruption. > > Yep, agree w/ you there. > >>> I see that something similar has been implemented for the mysql >>> adapter. >>> >>> My question is that in changeset 1622, the mysql #execute method >>> was changed to add a "retries" parameter. The implication is >>> that it would try N times. >>> >>> But it looks like that feature is unimplemented. Am I missing >>> something? > > I presume then that the implementation in the mysql adapter is only > half-done and considered less than optimal?Well, I think retrying is bad :-)>> Check out ticket #428: http://dev.rubyonrails.org/ticket/428 >> Best to avoid the dead db connection in the first place. Plus, >> we can implement it in a more database-agnostic manner. > > Are you (or is anyone else) actively working this issue?Not yet, as far as I know. Please do investigate. The ticket is tagged fd for inclusion in 1.0, but really, here at 1.0rc4, I think it is too late in the game for a big disruptive change to the connection adapters. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDc6rjAQHALep9HFYRAo0iAJ4vX9DHqSgx3xtUNmmn6zbM4Kr+bACfT4IA mnU0GmkxCtnGT/yYz48cPHw=voy1 -----END PGP SIGNATURE-----
> Well, I think retrying is bad :-)I don''t think the framework can decide if retrying a statement is good or bad. In some circumstances (marking someone inactive) the statement is effectively idempotent, so retries don''t matter. For others (debits and credits) a retry is catastrophic. AR can''t detect this. Perhaps we could have a seperate ''retry your failed statements'' plugin where developers could choose to include something like this if they know it''s safe.> > Not yet, as far as I know. Please do investigate. > > The ticket is tagged fd for inclusion in 1.0, but really, here at > 1.0rc4, I think it is too late in the game for a big disruptive > change to the connection adapters.Whatever happened to our 1.0 branch? -- Cheers Koz
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 10, 2005, at 12:31 PM, Michael Koziarski wrote:>> Well, I think retrying is bad :-) > > I don''t think the framework can decide if retrying a statement is good > or bad. In some circumstances (marking someone inactive) the > statement is effectively idempotent, so retries don''t matter. For > others (debits and credits) a retry is catastrophic. AR can''t detect > this. > > Perhaps we could have a seperate ''retry your failed statements'' plugin > where developers could choose to include something like this if they > know it''s safe.Considering we can nip the problem in the bud before it occurs with less code than doing retries, I think validating connections before using them is the way to go.>> Not yet, as far as I know. Please do investigate. >> >> The ticket is tagged fd for inclusion in 1.0, but really, here at >> 1.0rc4, I think it is too late in the game for a big disruptive >> change to the connection adapters. > > Whatever happened to our 1.0 branch?<echo> :) jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDc7biAQHALep9HFYRAu3ZAJ9/70Wos7ZO9+JDkPlFt5bcF7V7TgCfUTsf qgcGjKnFbT0vEJj9Rx8iq6E=dDOn -----END PGP SIGNATURE-----
> Considering we can nip the problem in the bud before it occurs with > less code than doing retries, I think validating connections before > using them is the way to go.How do we go about that?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 10, 2005, at 1:31 PM, Michael Schoen wrote:>> Considering we can nip the problem in the bud before it occurs >> with less code than doing retries, I think validating connections >> before using them is the way to go. > > How do we go about that?By having a request for ActiveRecord::Base.connection validate its connection periodically. Send a ping (if the driver supports it) or a dummy query. The MySQL bindings have a mysql_ping, for example. This does leave an open, broken window from when the database is purposefully restarted until the next connection validation, however. I agree that automatic reconnection is also necessary. What happens when a web request leaves, say, an open transaction? The next request would use the connection, none the wiser. Validation on first request to AR::Base.connection could include a COMMIT, for example. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDc8K6AQHALep9HFYRAkdgAKDc2Muc9uhxAIsM/FtiPGv1B+cPngCfczmr czkyVIcIWf0iZ3FYIJVrDCM=JZFR -----END PGP SIGNATURE-----
Nick, Thanks, this is very helpful. I''m working on an enhancement of this to address the concern of corrupted data in the event of a transaction. One question: I''m not understanding how your code is working with a DelegateClass. In the event of a reconnection, you''re reassigning @connection, but my understanding is that DelegateClass doesn''t allow for changing the delegated-to object. Since your call to #exec is explicitely calling @connection.exec (as opposed to just using #exec as you should be able to), it''ll work at a high-level. But if you tried to do things like twiddle autocommit, I''d expect it would be twiddling the old (dead) connection. Am I missing something? Should this intead use SimpleDelegate, and then using __setobj__ when the connection is re-connected? thanks, Michael Nick Sieger wrote:> On 11/10/05, *Michael Schoen* <schoenm@earthlink.net > <mailto:schoenm@earthlink.net>> wrote: > > > > I think retrying is bad behavior. It should rollback the current > > transaction and raise an exception instead. Otherwise you are asking > > for data corruption. > > Yep, agree w/ you there. > > > FWIW, I did this too, and I also agree, but I still whipped up this hack > anyway that seems to work. If you''re not terribly concerned with > corruption you might give it a try. I do trigger off of the specific > oracle "connection closed" errors so depending on how oracle closes > connections from the DB side there may not be a big chance of data > corruption. Any oracle experts know the answer to that? Anyway, let me > know if you come up with a better way. BTW, I''m using this with Rails > 0.13 currently. > > require ''delegate'' > > module ActiveRecord > module ConnectionAdapters > class OCIConnectionFactory > def new_connection(username, password, host) > conn = OCI8.new username, password, host > conn.exec %q{alter session set nls_date_format = ''YYYY-MM-DD > HH24:MI:SS''} > conn.exec %q{alter session set nls_timestamp_format = > ''YYYY-MM-DD HH24:MI:SS''} > conn.autocommit = true > conn > end > end > > class OCIReconnectOnError < DelegateClass(OCI8) > def initialize(config, connfactory = OCIConnectionFactory.new) > @username = config[:username] > @password = config[:password] > @host = config[:host] > @connfactory = connfactory > @connection = new_connection > super @connection > end > > def exec(sql, *vars) > retry_count = 0 > begin > @connection.exec(sql, *vars) > rescue Exception => e > # ORA-03113: end-of-file on communication channel > # ORA-03114: not connected to ORACLE > if e.message =~ /ORA-0311[34]/ && retry_count < 2 > @connection = new_connection > retry_count += 1 > retry > else > raise > end > end > end > def new_connection > @connfactory.new_connection @username, @password, @host > end > end > end > > class Base > def self.oci_connection(config) #:nodoc: > > ConnectionAdapters::OCIAdapter.new(ConnectionAdapters::OCIReconnectOnError.new(config), > logger) > end > end > end > > > > > ------------------------------------------------------------------------ > > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core
> I don''t think the framework can decide if retrying a statement is good > or bad. In some circumstances (marking someone inactive) the > statement is effectively idempotent, so retries don''t matter. For > others (debits and credits) a retry is catastrophic. AR can''t detect > this. > > Perhaps we could have a seperate ''retry your failed statements'' plugin > where developers could choose to include something like this if they > know it''s safe.I''m working on an enhancement to Nick''s code that uses the state of autocommit to determine if we''re in the middle of a transaction. And then retries if it''s safe, otherwise marks the connection as bad (to be reconnected later) and raises the error. What issues do you see with that approach?
> > I don''t think the framework can decide if retrying a statement is good > > or bad. In some circumstances (marking someone inactive) the > > statement is effectively idempotent, so retries don''t matter. For > > others (debits and credits) a retry is catastrophic. AR can''t detect > > this. > > > > Perhaps we could have a seperate ''retry your failed statements'' plugin > > where developers could choose to include something like this if they > > know it''s safe. > > I''m working on an enhancement to Nick''s code that uses the state of > autocommit to determine if we''re in the middle of a transaction. And > then retries if it''s safe, otherwise marks the connection as bad (to be > reconnected later) and raises the error. > > What issues do you see with that approach?I don''t think that the presence of a transaction can tell you whether the statement is safe to retry. You could easily be updating a single table, yet still have a non-idempotent statement. -- Cheers Koz
On 11/10/05, Michael Schoen <schoenm@earthlink.net> wrote:> > Nick, > > Thanks, this is very helpful. I''m working on an enhancement of this to > address the concern of corrupted data in the event of a transaction. > > One question: I''m not understanding how your code is working with a > DelegateClass. In the event of a reconnection, you''re reassigning > @connection, but my understanding is that DelegateClass doesn''t allow > for changing the delegated-to object.I hadn''t noticed that, but I think you''re right, it''s a bug. I must not have noticed the difference either because my code wasn''t hitting any connection adapter methods other than exec (seems unlikely?) or I just haven''t actually hit a closed connection since installing that code. If you''re able to improve upon the code (and it sounds like you are), I''d appreciate your mods. Thanks, /Nick -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails-core/attachments/20051111/dead7174/attachment.html
On 11/10/05, Michael Koziarski <michael@koziarski.com> wrote:> > > What issues do you see with that approach? > > I don''t think that the presence of a transaction can tell you whether > the statement is safe to retry. You could easily be updating a single > table, yet still have a non-idempotent statement.Can you think of an example? Would AR generate such a statement? Something like ''update mytab set mycol = mycol + 1 where ...'' would be an example but I would think that most of the update methods generated by AR would be of the form ''update mytab set mycol = ? where id = ?'' and what would be non-idempotent about that? Thinking about this a bit more, of course an insert would fall into this category but you''d probably hit a primary key violation. I appreciate your insight on this. Cheers, /Nick -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails-core/attachments/20051111/e560d562/attachment.html
Jeremy Kemper wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Nov 10, 2005, at 10:53 AM, Michael Schoen wrote: > >> I''m looking to enhance the OCI adapter so that it automatically >> retries if it finds that the db connection had died -- this will >> allow the app to properly recover if the database has been bounced. > > > I think retrying is bad behavior. It should rollback the current > transaction and raise an exception instead. Otherwise you are asking > for data corruption. >I very much agree. Automatic retries are bad. They raise more problems than they solve by trying to be too clever, but failing. What I ask for instead, is raising an exception percolating up to the dispatcher who should try to reestablish a valid connection upon the next incoming request. If the app is running under FCGI/SCGI the dispatcher could alternatively simply exit the appliction, since it will be restarted automatically. FCGI allows for specifying a restart delay, so this helps not bombarding the DB with requests during a restart. In fact the only dropped connections that I have are due to Mysql closing inactive connections after a timeout interval. Currently I simply ping my app using wget --quiet --spider on a list of urls requiring db connection every 60 minutes -- stefan
> > Whatever happened to our 1.0 branch? > > <echo> :)branches/stable is now in. We can go crazy in trunk again ;) -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
> If you''re able to improve upon the code (and it sounds like you are), > I''d appreciate your mods.I''ve posted my version as a patch, implementing Jeremy''s approach (using #active? and #reconnect!). I also put in an option to actually auto-retry, though it''s disabled by default. You can set OCI8AutoRecover.auto_retry = true to get that functionality. Though it may be dangerous, and it also doesn''t tie into Jeremy''s nice new logging stuff (tracking success of reconnects). See: http://dev.rubyonrails.org/ticket/428
Sorry to resurrect an old thread, but what about just defining a "no-op" query for each adapter. In Oracle, that would be, say, "select * from dual". Many Java connection pool implementations have such a thing, and execute it when a connection is assigned. If it fails, the connection is closed and reopened. If it works, it''s safe to run your actual code. Retries are scary. --Wilson On 11/14/05, Michael Schoen <schoenm@earthlink.net> wrote:> > If you''re able to improve upon the code (and it sounds like you are), > > I''d appreciate your mods. > > I''ve posted my version as a patch, implementing Jeremy''s approach (using > #active? and #reconnect!). I also put in an option to actually > auto-retry, though it''s disabled by default. You can set > > OCI8AutoRecover.auto_retry = true > > to get that functionality. Though it may be dangerous, and it also > doesn''t tie into Jeremy''s nice new logging stuff (tracking success of > reconnects). > > See: > > http://dev.rubyonrails.org/ticket/428 > > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core >
* Wilson Bilkovich (wilsonb@gmail.com) [051209 11:20]:> Sorry to resurrect an old thread, but what about just defining a > "no-op" query for each adapter. In Oracle, that would be, say, > "select * from dual". > Many Java connection pool implementations have such a thing, and > execute it when a connection is assigned. If it fails, the connection > is closed and reopened. If it works, it''s safe to run your actual > code. > > Retries are scary.Not only that, but with 0.14.4 we''ve turned up a problem on Oracle with transaction isolation in tests. Basically, if we have a test that calls some AR functionality which happens to use a transaction internally, using use_transactional_fixtures, with 0.14.3 the changes in that test would be isolated, with 0.14.4 (on Oracle, but not on Postgres) the changes in that test are visible outside the test. We''ve gotten it down to a fairly small test pair and it''s definitely a result of changes in oci_adapter.rb (which are almost entirely related to the new recoverable connections). When I have something clear enough to share I''ll pass it along. Rick -- http://www.rickbradley.com MUPRN: 562 | Joke: do not send random email haiku | me your vlb hardware I can''t | believe it either.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Dec 9, 2005, at 10:50 AM, Rick Bradley wrote:> Not only that, but with 0.14.4 we''ve turned up a problem on Oracle > with > transaction isolation in tests. Basically, if we have a test that > calls > some AR functionality which happens to use a transaction internally, > using use_transactional_fixtures, with 0.14.3 the changes in that test > would be isolated, with 0.14.4 (on Oracle, but not on Postgres) the > changes in that test are visible outside the test. We''ve gotten it > down > to a fairly small test pair and it''s definitely a result of changes in > oci_adapter.rb (which are almost entirely related to the new > recoverable > connections). When I have something clear enough to share I''ll > pass it > along.http://dev.rubyonrails.org/ticket/3133 jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDmezXAQHALep9HFYRAuUPAKCPH9TwTkCCtboCierQR9uVFHv3lwCdEypS uK4M5TSZr3BrkfE4uJJyHV8=UJPH -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Dec 9, 2005, at 8:18 AM, Wilson Bilkovich wrote:> Sorry to resurrect an old thread, but what about just defining a > "no-op" query for each adapter. In Oracle, that would be, say, > "select * from dual". > Many Java connection pool implementations have such a thing, and > execute it when a connection is assigned. If it fails, the connection > is closed and reopened. If it works, it''s safe to run your actual > code. > > Retries are scary.This is how the feature''s implemented: see the active? instance methods implemented by the various connection adapters. Currently, active? is called per-request. It''d be nice to cut that down, but it''d be at the expense of failed requests. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDme2OAQHALep9HFYRAqNMAJ9KAaPPPr6x0hM0vvDVYfVp9dsMTwCfaxxD 0vHt66epOb4Hfua1M1OHvaY=HChh -----END PGP SIGNATURE-----
* Jeremy Kemper (jeremy@bitsweat.net) [051209 15:50]:> http://dev.rubyonrails.org/ticket/3133I owe you a beer, man. I was about 45 minutes from making that same patch. I somehow missed that ticket in the timeline. Thanks! Rick -- http://www.rickbradley.com MUPRN: 972 | read this then posting random email haiku | via the news group worked... | If you can''t it didn''t.