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 >
