[esb-java-user] wso2, dblookup/dbreport loses mysql connections

Harm Verhagen harm.verhagen at gmail.com
Tue Sep 30 04:36:38 PDT 2008


https://issues.apache.org/jira/browse/SYNAPSE-456
(with synapse.xml)

2008/9/30 Indika Kumara <indika at wso2.com>

>  Hi
> Harm
>
> Please create a JIRA for this issue and attach synapse.xml , any logs and
> details about mysql and its configuration.
>
>
> Thanks
> Indika
>
> Harm Verhagen wrote:
>
> Adding autocommit = true to my dblookup mediators did NOT work.
>
> This morning same problem. two servers running wso2esb giving these
> exeptions.
> one not recovering without restarting wso2, the other somehow recovered
> after a few calls. (same configuration, just less debug logging on)
>
>
> Regards,
> Harm
>
> 2008/9/29 Indika Kumara <indika at wso2.com>
>
>>  I will check issue with 'autocommit'. Thanks
>>
>> BTW , In the Mysql forum, I see a same issue and some solutions [1]. Could
>> you go through that?.
>>
>> You have another option to set connection pools using synape.properties
>> file. Please read section under 'Setting up Synapse DataSources' in the [2].
>> Corresponding sample is at [3].
>>
>> Here, all configuration options are same to DBCP configuration options so
>> that you can configure using [4].
>>
>> Thanks
>>
>> Indika
>>
>>
>>
>> [1] http://forums.mysql.com/read.php?39,42763,42763
>>
>> [2] http://wso2.org/project/esb/java/1.7.1/docs/ESB_Samples_Setup.html
>>
>> [3]
>> http://wso2.org/project/esb/java/1.7.1/docs/ESB_Samples.html#Sample363
>>
>> [4] http://commons.apache.org/dbcp/configuration.html
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Harm Verhagen wrote:
>>
>> Yeah. I suspected the same thing, I'm already running this.
>> I'll report later.
>>
>> *side note:  *I noticed that dbreport mediator does not allow  to set
>> autocommit to true.. (well it does... but when you actually do this you get
>> errors...   because it seems the dbreport mediator calls commit itself. (my
>> query has no explicit commit).)
>> ERROR DBReportMediator Error execuring insert statement : update
>> transaction set customer_id = ?  where id = ? against DataSource :
>> jdbc:mysql://localhost:3306/esb java.sql.SQLException: Can't call commit
>> when autocommit=true
>> Probably the UI shouldn't expose autocommit true/false  for dbreport ?
>>
>> Regards,
>> Harm
>>
>> 2008/9/29 Indika Kumara <indika at wso2.com>
>>
>>> Hi Harm
>>>
>>> According to the
>>> http://www.mikeschubert.com/archives/2006/08/javanetsocketex.html ,
>>>
>>> I feel enabling auto commit even for query may solve issue. For set
>>> default auto commit,
>>> you have to add property with name 'autocommit' and value equal to
>>> 'true'.
>>>
>>> Could you try this out ? .BTW, if issue will not be resolved, please
>>> create JIRA and attached anything needs to reproduce issue.
>>>
>>> Thanks
>>> Indika
>>>
>>> Harm Verhagen wrote:
>>>
>>>  testonborrow is not helping.
>>> 8 hours later in the morning, same problem...
>>>
>>> Can this be related to queries & commit (or better said the lack of
>>> commits) ?  (Is dblookup doing commits ?)
>>>
>>>
>>> 2008-09-29 10:02:17,838 [127.0.0.1-vloeki_v01] [HttpServerWorker-9] DEBUG
>>> DBLookupMediator Successfully prepared statement : select id
>>> from transaction_uuid where uuid = ? against DataSource :
>>> jdbc:mysql://localhost:3306/esb
>>> 2008-09-29 10:02:17,839 [127.0.0.1-vloeki_v01] [HttpServerWorker-9] ERROR
>>> DBLookupMediator Error executing statement : select id from t
>>> ransaction_uuid where uuid = ? against DataSource :
>>> jdbc:mysql://localhost:3306/esb
>>> com.mysql.jdbc.CommunicationsException: Communications link failure due
>>> to underlying exception:
>>>
>>> ** BEGIN NESTED EXCEPTION **
>>>
>>> java.net.SocketException
>>> MESSAGE: Broken pipe
>>>
>>> STACKTRACE:
>>>
>>> java.net.SocketException: Broken pipe
>>>         at java.net.SocketOutputStream.socketWrite0(Native Method)
>>>         at
>>> java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
>>>         at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
>>>         at
>>> java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
>>> ....
>>>
>>>
>>> *datasource*
>>> snippet from my synapse.xml that defines the datasource
>>>  <syn:dblookup>
>>>             <syn:connection>
>>>                 <syn:pool>
>>>                     <syn:password>esb</syn:password>
>>>                     <syn:user>esb</syn:user>
>>>                     <syn:property name="testonborrow" value="true"/>
>>>                     <syn:url>jdbc:mysql://localhost:3306/esb</syn:url>
>>>                     <syn:driver>com.mysql.jdbc.Driver</syn:driver>
>>>                 </syn:pool>
>>>             </syn:connection>
>>>             <syn:statement>
>>>                 <syn:sql><![CDATA[select * from customer c where token =
>>> ?;]]></syn:sql>
>>>                 <syn:parameter xmlns:ns1="http://org.apache.synapse/xsd"
>>> xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
>>> expression="get-property('token')" type="VARCHAR"/>
>>>                 <syn:result name="customer_id" column="id"/>
>>>             </syn:statement>
>>>         </syn:dblookup>
>>>
>>>
>>>
>>> Regards,
>>> Harm
>>>
>>>
>>> On Sun, Sep 28, 2008 at 9:56 PM, Harm Verhagen <harm.verhagen at gmail.com>wrote:
>>>
>>>>
>>>>
>>>>  It seems like the connection from the ESB to MySQL got broken.. BTW,
>>>>> are these on the same machine by any chance? .. The problem seems to be the
>>>>> Apache DBCP connection pool not handling the stale connection properly. I
>>>>> believe you have defined your datasource using the synapse.properties?.. Can
>>>>> you try setting the 'testOnBorrow' to true? [1] .. also see the last
>>>>> paragraph of [1]
>>>>>
>>>>> asankha
>>>>>
>>>>> [1] http://wiki.apache.org/commons/DBCP
>>>>>
>>>>>   Yes, the esb & mysql run on the same machine.
>>>> I defined the datasource in my synapse.xml file
>>>>
>>>> I'm running the esb now with:
>>>> <syn:property name="testonborrow" value="true"/>
>>>> for every dblookup and every dbreport mediator
>>>>
>>>> I'll report later if this is more stable.
>>>>
>>>> Regards,
>>>> Harm
>>>>
>>>>
>>>   ------------------------------
>>>
>>> _______________________________________________
>>> Esb-java-user mailing listEsb-java-user at wso2.orghttp://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>>>
>>> ------------------------------
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - http://www.avg.com
>>> Version: 8.0.169 / Virus Database: 270.7.5/1696 - Release Date: 9/28/2008 1:30 PM
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> Esb-java-user mailing list
>>> Esb-java-user at wso2.org
>>> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>>>
>>>
>>  ------------------------------
>>
>> _______________________________________________
>> Esb-java-user mailing listEsb-java-user at wso2.orghttp://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>>
>> ------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.com
>> Version: 8.0.169 / Virus Database: 270.7.5/1696 - Release Date: 9/28/2008 1:30 PM
>>
>>
>>
>>
>>
>> _______________________________________________
>> Esb-java-user mailing list
>> Esb-java-user at wso2.org
>> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>>
>>
>  ------------------------------
>
> _______________________________________________
> Esb-java-user mailing listEsb-java-user at wso2.orghttp://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>
> ------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
>
> Version: 8.0.173 / Virus Database: 270.7.5/1698 - Release Date: 9/29/2008 7:25 PM
>
>
>
>
>
> _______________________________________________
> Esb-java-user mailing list
> Esb-java-user at wso2.org
> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.wso2.org/pipermail/esb-java-user/attachments/20080930/dcb25aa8/attachment-0001.htm 


More information about the Esb-java-user mailing list