Ken, Glad you solved your problem.
thx On Tue, May 22, 2012 at 10:17 AM, Ken Ernst <[email protected]> wrote: > The changes to the config file to enable Oozie to discard stale JDBC > connections fixes both of the issues below. > > Thanks! > > > > On 5/17/12 3:45 PM, "Ken Ernst" <[email protected]> wrote: > > >Harsh & Alejandro, > > > >Thanks for your input. I am seeing this error manifest itself in two ways. > > > >1. As in the bug Alejandro pointed out below. That is if oozie & mysql are > >both running and mysql is restarted the error appears when you try to > >submit a job at the command line and fails immediately. > > > >2. After a period of oozie sitting idle. Oozie accepts the job and starts > >running it only to fail with the error. The oozie console log contains the > >error. > > > >For 1 a restart of oozie fixes the issue. > >For 2 a resubmit of the job and it runs fine. > > > >For case 1 the fix below works, as I could not reproduce the issue once I > >put the configuration in place. I need to wait for an idle period before I > >can test the second case. I will give an update on this once I get a > >chance to test. > > > >I did try Harsh's suggestion of adding "autoReconnect=true" to > >"oozie.service.StoreService.jdbc.url" in the oozie-site.xml but this did > >not help with case 1 and I am unsure about case 2. > > > >Thanks for all your help. > > > >Ken > > > > > > > > > > > >On 5/16/12 11:32 AM, "Alejandro Abdelnur" <[email protected]> wrote: > > > >>This may help: > >> > >>----- > >>‹ The Oozie server fails to reconnect JDBC connections after a DB > >>restart. > >> > >>Bug: None > >>Severity: Low > >>Anticipated resolution: Fixed in upcoming release > >>Workaround: The following configuration enables Oozie to discard stale > >>JDBC connections: > >>In the oozie-site.xml make the following changes to the JDBC URL > >>property oozie.service.StoreService.jdbc.url: > >> > >>${ORIGINAL_JDBC_URL},TestOnBorrow=true,TestOnReturn=false,TestWhileIdle=f > >>a > >>lse, > >>ValidationQuery=select count(*) from SLA_EVENTS > >>NOTE: The value shown above as ORIGINAL_JDBC_URL must be the JDBC URL > >>value that was in place previously for the same configuration name. > >>----- > >> > >>Ref: > >> > https://ccp.cloudera.com/display/CDHDOC/Known+Issues+and+Work+Arounds+in+ > >>C > >>DH3#KnownIssuesandWorkAroundsinCDH3-Oozie > >> > >>thx. > >> > >>On Wed, May 16, 2012 at 7:56 AM, Harsh J <[email protected]> wrote: > >>> Hey Ken, > >>> > >>> Do you get this issue right from the start or did it pop up later? Did > >>> you also try the solution offered by the log itself? I recall having > >>> to do that once before to circumvent MySQL connectivity issues. > >>> > >>> On Wed, May 16, 2012 at 7:25 PM, Ken Ernst <[email protected]> > >>>wrote: > >>>> Hi, > >>>> Running oozie client build version: 2.3.2-cdh3u3 w/ MySQL as the store > >>>> version 5.0.95. I am getting the below error. > >>>> > >>>> Exception, org.apache.oozie.store.StoreException: E0600: Could not get > >>>>connection, The last packet successfully received from the server was > >>>>70,900,302 milliseconds ago. The last packet sent successfully to the > >>>>server was 70,900,302 milliseconds ago. is longer than the server > >>>>configured value of 'wait_timeout'. You should consider either expiring > >>>>and/or testing connection validity before use in your application, > >>>>increasing the server configured values for client timeouts, or using > >>>>the Connector/J connection property 'autoReconnect=true' to avoid this > >>>>problem. > >>>> org.apache.oozie.store.StoreException: E0600: Could not get > >>>>connection, The last packet successfully received from the server was > >>>>70,900,302 milliseconds ago. The last packet sent successfully to the > >>>>server was 70,900,302 milliseconds ago. is longer than the server > >>>>configured value of 'wait_timeout'. You should consider either expiring > >>>>and/or testing connection validity before use in your application, > >>>>increasing the server configured values for client timeouts, or using > >>>>the Connector/J connection property 'autoReconnect=true' to avoid this > >>>>problem. > >>>> > >>>> My job does a bunch of imports from SQL using sqoop and starts out > >>>>like this: > >>>> <fork name="fork"> > >>>> <path start="import-table1"/> > >>>> <path start="import-table2"/> > >>>> <path start="import-table3"/> > >>>> <path start="import-table4"/> > >>>> </fork> > >>>> > >>>> <action name="import-table1"> > >>>> <sub-workflow> > >>>> > >>>><app-path>${nameNode}/user/${wf:user()}/${appRoot}/sqoop-import-table</ > >>>>a > >>>>pp-path> > >>>> <propagate-configuration/> > >>>> <configuration> > >>>> <property> > >>>> <name>tableName</name> > >>>> <value> table1</value> > >>>> </property> > >>>> </configuration> > >>>> </sub-workflow> > >>>> <ok to="join"/> > >>>> <error to="email-sqoop-failure"/> > >>>> </action> > >>>> > >>>> <etcŠ> > >>>> > >>>> Thanks. > >>>> > >>>> Ken > >>> > >>> > >>> > >>> -- > >>> Harsh J > > > > -- Alejandro
