You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@lenya.apache.org by Gintare Ragaisiene <gi...@gmail.com> on 2010/01/27 16:35:18 UTC
Probjems in communication with DB
Hi,
I have problem with communication with my MySQL database. The problem is
no inserted records in the db after INSERT close executed. When I connect
with outside client to the same db and try to act it gives me "Lock wait
timetout exceeded; try restart transaction". I have code :
public class EditNewsletters extends DocumentUsecase{
private DataSourceComponent datasource;
protected void initParameters() {
super.initParameters();
}
protected void prepareView()throws Exception{
super.prepareView();
String view_already_prepared =
this.getParameterAsString("EditNewsletters.view_already_prepared");
if(view_already_prepared == null){
//this.manageDbData();
this.testWithLmps();
this.setParameter("EditNewsletters.view_already_prepared",
"true");
}
}
protected void manageDbData(){
try {
ServiceSelector selector = (ServiceSelector)
manager.lookup(DataSourceComponent.ROLE + "Selector");
this.datasource = (DataSourceComponent)
selector.select("humana");
Connection myConnection = this.datasource.getConnection();
Statement stmt = myConnection.createStatement();
stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM')");
String query = "SELECT * FROM area";
ResultSet result = stmt.executeQuery(query);
System.out.println("EditNewsletters result =
"+result.getFetchSize());
stmt.close();
myConnection.close();
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ServiceException ex) {
ex.printStackTrace();
}
}
private void testWithLmps(){
try {
ServiceSelector selector = (ServiceSelector)
manager.lookup(DataSourceComponent.ROLE + "Selector");
this.datasource = (DataSourceComponent) selector.select("lmps");
Connection myConnection = this.datasource.getConnection();
Statement stmt = myConnection.createStatement();
String query = "INSERT INTO question VALUES (0,
'aaaaaaaaaaaaaa?')";
stmt.execute(query);
myConnection.close();
System.out.println("EditNewsletters q = "+query);
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ServiceException ex) {
ex.printStackTrace();
}
}
protected void doExecute() throws Exception {
super.doExecute();
System.out.println("EditNewsletters doExecute()");
}
}
in cocoon.xconf :
<jdbc logger="core.datasources.humana" name="humana">
<pool-controller max="10" min="5"/>
<auto-commit>false</auto-commit>
<dburl>jdbc:mysql://localhost:3306/humana?useUnicode=true&characterEncoding=UTF-8</dburl>
<user>root</user>
<password>lau</password>
</jdbc>
plz, help
thanks
Re: Probjems in communication with DB
Posted by Gintare Ragaisiene <gi...@gmail.com>.
Issue is solved, there was needed myConnection.commit() . Thanks everybody
for help, here's the code :
private void testWithLmps(){
Connection myConnection = null;
try {
ServiceSelector selector = (ServiceSelector)
manager.lookup(DataSourceComponent.ROLE + "Selector");
this.datasource = (DataSourceComponent) selector.select("lmps");
myConnection = this.datasource.getConnection();
Statement stmt = myConnection.createStatement();
String query = "INSERT INTO question VALUES (0,
'aaaaaaaaaaaaaa?')";
stmt.execute(query);
System.out.println("EditNewsletters q = "+query);
myConnection.commit();// because auto-commit=false
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ServiceException ex) {
ex.printStackTrace();
}finally{
if(myConnection != null){
try {
myConnection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
Gintare
On Thu, Jan 28, 2010 at 9:49 AM, Gintare Ragaisiene <
gintare.ragaisiene@gmail.com> wrote:
> o, I forgot to mention, I did restart of mysql server and lenya server.
>
>
> On Thu, Jan 28, 2010 at 9:48 AM, Gintare Ragaisiene <
> gintare.ragaisiene@gmail.com> wrote:
>
>> ok, now my code looks like this:
>>
>> But the problem remains the same, no inserted records, and the same
>> timeout transaction error message after outside client interaction with db.
>>
>>
>> private void testWithLmps(){
>>
>> Connection myConnection = null;
>>
>> try {
>>
>> ServiceSelector selector = (ServiceSelector)
>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>> this.datasource = (DataSourceComponent)
>> selector.select("lmps");
>>
>> myConnection = this.datasource.getConnection();
>> Statement stmt = myConnection.createStatement();
>>
>> String query = "INSERT INTO question VALUES (0,
>> 'aaaaaaaaaaaaaa?')";
>> stmt.execute(query);
>>
>>
>> System.out.println("EditNewsletters q = "+query);
>>
>> } catch (SQLException ex) {
>> ex.printStackTrace();
>> } catch (ServiceException ex) {
>> ex.printStackTrace();
>> }finally{
>> if(myConnection != null){
>> try {
>>
>> myConnection.close();
>> } catch (SQLException ex) {
>> ex.printStackTrace();
>> }
>> }
>> }
>> }
>>
>>
>>
>> On Wed, Jan 27, 2010 at 10:33 PM, Michael Wechner <
>> michael.wechner@wyona.com> wrote:
>>
>>> Andreas Hartmann wrote:
>>>
>>>> Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
>>>>
>>>>> Hi,
>>>>>
>>>>> I have problem with communication with my MySQL database. The problem
>>>>> is no inserted records in the db after INSERT close executed. When I
>>>>> connect with outside client to the same db and try to act it gives me
>>>>> "Lock wait timetout exceeded; try restart transaction". I have code :
>>>>>
>>>>>
>>>> protected void manageDbData(){
>>>>> try {
>>>>> ServiceSelector selector = (ServiceSelector)
>>>>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>>>>> this.datasource = (DataSourceComponent)
>>>>> selector.select("humana");
>>>>> Connection myConnection = this.datasource.getConnection();
>>>>> Statement stmt = myConnection.createStatement();
>>>>>
>>>>> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
>>>>> <ma...@GMAIL.COM>')");
>>>>>
>>>>> String query = "SELECT * FROM area";
>>>>> ResultSet result = stmt.executeQuery(query);
>>>>> System.out.println("EditNewsletters result =
>>>>> "+result.getFetchSize());
>>>>>
>>>>
>>>> Is this code executed?
>>>>
>>>>
>>>>> stmt.close();
>>>>> myConnection.close();
>>>>>
>>>>
>>>> I'm not too familiar with JDBC, but I'd assume that the close()
>>>> statements should go into the finally clause to avoid exhaustion of the
>>>> connection pool if exceptions occur.
>>>>
>>>
>>> I can confirm this. You really need to make sure that every connection is
>>> being closed no matter what exception happened during the connection,
>>> because otherwise your system will be "blocked" quickly
>>>
>>> Cheers
>>>
>>> Michael
>>>
>>>
>>>>
>>>> BTW, your question is not really related to Lenya, maybe you get better
>>>> hints on a more appropriate mailing list (e.g., on the Cocoon user list).
>>>>
>>>> -- Andreas
>>>>
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
>>> For additional commands, e-mail: user-help@lenya.apache.org
>>>
>>>
>>
>
Re: Probjems in communication with DB
Posted by Gintare Ragaisiene <gi...@gmail.com>.
o, I forgot to mention, I did restart of mysql server and lenya server.
On Thu, Jan 28, 2010 at 9:48 AM, Gintare Ragaisiene <
gintare.ragaisiene@gmail.com> wrote:
> ok, now my code looks like this:
>
> But the problem remains the same, no inserted records, and the same timeout
> transaction error message after outside client interaction with db.
>
>
> private void testWithLmps(){
>
> Connection myConnection = null;
>
> try {
>
> ServiceSelector selector = (ServiceSelector)
> manager.lookup(DataSourceComponent.ROLE + "Selector");
> this.datasource = (DataSourceComponent)
> selector.select("lmps");
>
> myConnection = this.datasource.getConnection();
> Statement stmt = myConnection.createStatement();
>
> String query = "INSERT INTO question VALUES (0,
> 'aaaaaaaaaaaaaa?')";
> stmt.execute(query);
>
>
> System.out.println("EditNewsletters q = "+query);
>
> } catch (SQLException ex) {
> ex.printStackTrace();
> } catch (ServiceException ex) {
> ex.printStackTrace();
> }finally{
> if(myConnection != null){
> try {
>
> myConnection.close();
> } catch (SQLException ex) {
> ex.printStackTrace();
> }
> }
> }
> }
>
>
>
> On Wed, Jan 27, 2010 at 10:33 PM, Michael Wechner <
> michael.wechner@wyona.com> wrote:
>
>> Andreas Hartmann wrote:
>>
>>> Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
>>>
>>>> Hi,
>>>>
>>>> I have problem with communication with my MySQL database. The problem
>>>> is no inserted records in the db after INSERT close executed. When I
>>>> connect with outside client to the same db and try to act it gives me
>>>> "Lock wait timetout exceeded; try restart transaction". I have code :
>>>>
>>>>
>>> protected void manageDbData(){
>>>> try {
>>>> ServiceSelector selector = (ServiceSelector)
>>>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>>>> this.datasource = (DataSourceComponent)
>>>> selector.select("humana");
>>>> Connection myConnection = this.datasource.getConnection();
>>>> Statement stmt = myConnection.createStatement();
>>>>
>>>> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
>>>> <ma...@GMAIL.COM>')");
>>>>
>>>> String query = "SELECT * FROM area";
>>>> ResultSet result = stmt.executeQuery(query);
>>>> System.out.println("EditNewsletters result =
>>>> "+result.getFetchSize());
>>>>
>>>
>>> Is this code executed?
>>>
>>>
>>>> stmt.close();
>>>> myConnection.close();
>>>>
>>>
>>> I'm not too familiar with JDBC, but I'd assume that the close()
>>> statements should go into the finally clause to avoid exhaustion of the
>>> connection pool if exceptions occur.
>>>
>>
>> I can confirm this. You really need to make sure that every connection is
>> being closed no matter what exception happened during the connection,
>> because otherwise your system will be "blocked" quickly
>>
>> Cheers
>>
>> Michael
>>
>>
>>>
>>> BTW, your question is not really related to Lenya, maybe you get better
>>> hints on a more appropriate mailing list (e.g., on the Cocoon user list).
>>>
>>> -- Andreas
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
>> For additional commands, e-mail: user-help@lenya.apache.org
>>
>>
>
Re: Probjems in communication with DB
Posted by Gintare Ragaisiene <gi...@gmail.com>.
ok, now my code looks like this:
But the problem remains the same, no inserted records, and the same timeout
transaction error message after outside client interaction with db.
private void testWithLmps(){
Connection myConnection = null;
try {
ServiceSelector selector = (ServiceSelector)
manager.lookup(DataSourceComponent.ROLE + "Selector");
this.datasource = (DataSourceComponent) selector.select("lmps");
myConnection = this.datasource.getConnection();
Statement stmt = myConnection.createStatement();
String query = "INSERT INTO question VALUES (0,
'aaaaaaaaaaaaaa?')";
stmt.execute(query);
System.out.println("EditNewsletters q = "+query);
} catch (SQLException ex) {
ex.printStackTrace();
} catch (ServiceException ex) {
ex.printStackTrace();
}finally{
if(myConnection != null){
try {
myConnection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
On Wed, Jan 27, 2010 at 10:33 PM, Michael Wechner <michael.wechner@wyona.com
> wrote:
> Andreas Hartmann wrote:
>
>> Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
>>
>>> Hi,
>>>
>>> I have problem with communication with my MySQL database. The problem
>>> is no inserted records in the db after INSERT close executed. When I
>>> connect with outside client to the same db and try to act it gives me
>>> "Lock wait timetout exceeded; try restart transaction". I have code :
>>>
>>>
>> protected void manageDbData(){
>>> try {
>>> ServiceSelector selector = (ServiceSelector)
>>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>>> this.datasource = (DataSourceComponent)
>>> selector.select("humana");
>>> Connection myConnection = this.datasource.getConnection();
>>> Statement stmt = myConnection.createStatement();
>>>
>>> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
>>> <ma...@GMAIL.COM>')");
>>>
>>> String query = "SELECT * FROM area";
>>> ResultSet result = stmt.executeQuery(query);
>>> System.out.println("EditNewsletters result =
>>> "+result.getFetchSize());
>>>
>>
>> Is this code executed?
>>
>>
>>> stmt.close();
>>> myConnection.close();
>>>
>>
>> I'm not too familiar with JDBC, but I'd assume that the close() statements
>> should go into the finally clause to avoid exhaustion of the connection pool
>> if exceptions occur.
>>
>
> I can confirm this. You really need to make sure that every connection is
> being closed no matter what exception happened during the connection,
> because otherwise your system will be "blocked" quickly
>
> Cheers
>
> Michael
>
>
>>
>> BTW, your question is not really related to Lenya, maybe you get better
>> hints on a more appropriate mailing list (e.g., on the Cocoon user list).
>>
>> -- Andreas
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
> For additional commands, e-mail: user-help@lenya.apache.org
>
>
Re: Probjems in communication with DB
Posted by Michael Wechner <mi...@wyona.com>.
Andreas Hartmann wrote:
> Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
>> Hi,
>>
>> I have problem with communication with my MySQL database. The problem
>> is no inserted records in the db after INSERT close executed. When I
>> connect with outside client to the same db and try to act it gives me
>> "Lock wait timetout exceeded; try restart transaction". I have code :
>>
>
>> protected void manageDbData(){
>> try {
>> ServiceSelector selector = (ServiceSelector)
>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>> this.datasource = (DataSourceComponent)
>> selector.select("humana");
>> Connection myConnection = this.datasource.getConnection();
>> Statement stmt = myConnection.createStatement();
>>
>> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
>> <ma...@GMAIL.COM>')");
>>
>> String query = "SELECT * FROM area";
>> ResultSet result = stmt.executeQuery(query);
>> System.out.println("EditNewsletters result =
>> "+result.getFetchSize());
>
> Is this code executed?
>
>>
>> stmt.close();
>> myConnection.close();
>
> I'm not too familiar with JDBC, but I'd assume that the close()
> statements should go into the finally clause to avoid exhaustion of
> the connection pool if exceptions occur.
I can confirm this. You really need to make sure that every connection
is being closed no matter what exception happened during the connection,
because otherwise your system will be "blocked" quickly
Cheers
Michael
>
>
> BTW, your question is not really related to Lenya, maybe you get
> better hints on a more appropriate mailing list (e.g., on the Cocoon
> user list).
>
> -- Andreas
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
For additional commands, e-mail: user-help@lenya.apache.org
Re: Probjems in communication with DB
Posted by Vinod Kumar <vi...@propco.co.uk>.
the problem here seems to be using the statement object twice without
closing the object
Statement stmt = myConnection.createStatement();
stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
<ma...@GMAIL.COM>')");
immediately after this line, there is a possibility that the transaction
is locked and hence the problem
I would suggest, close the connection here
create a new statement and try this code and it should work
String query = "SELECT * FROM area";
ResultSet result = stmt.executeQuery(query);
On 27/01/10 16:11, Andreas Hartmann wrote:
> Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
>> Hi,
>>
>> I have problem with communication with my MySQL database. The problem
>> is no inserted records in the db after INSERT close executed. When I
>> connect with outside client to the same db and try to act it gives me
>> "Lock wait timetout exceeded; try restart transaction". I have code :
>>
>
>> protected void manageDbData(){
>> try {
>> ServiceSelector selector = (ServiceSelector)
>> manager.lookup(DataSourceComponent.ROLE + "Selector");
>> this.datasource = (DataSourceComponent)
>> selector.select("humana");
>> Connection myConnection = this.datasource.getConnection();
>> Statement stmt = myConnection.createStatement();
>>
>> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
>> <ma...@GMAIL.COM>')");
>>
>> String query = "SELECT * FROM area";
>> ResultSet result = stmt.executeQuery(query);
>> System.out.println("EditNewsletters result =
>> "+result.getFetchSize());
>
> Is this code executed?
>
>>
>> stmt.close();
>> myConnection.close();
>
> I'm not too familiar with JDBC, but I'd assume that the close()
> statements should go into the finally clause to avoid exhaustion of
> the connection pool if exceptions occur.
>
>
> BTW, your question is not really related to Lenya, maybe you get
> better hints on a more appropriate mailing list (e.g., on the Cocoon
> user list).
>
> -- Andreas
>
>
--
Vinod Kumar
Head of Development
FOR AND ON BEHALF OF:
Technology Blueprint Ltd
23 Clemens Street
Royal Leamington Spa
Warwickshire
CV31 2DW
T: 0845 004 7142 (Ext. 5200)
E: vinod@propco.co.uk
W: www.propco.co.uk
The contents of this email are confidential and may be privileged, and
are intended only for the use of the person or company named herein. Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Technology Blueprint Limited.
If you are not the intended recipient of this email or a person
responsible for delivering it to the intended recipient, you are hereby
notified that any distribution, copying or dissemination of the
information herein is strictly prohibited
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
For additional commands, e-mail: user-help@lenya.apache.org
Re: Probjems in communication with DB
Posted by Andreas Hartmann <an...@apache.org>.
Am 27.01.10 16:35, schrieb Gintare Ragaisiene:
> Hi,
>
> I have problem with communication with my MySQL database. The problem
> is no inserted records in the db after INSERT close executed. When I
> connect with outside client to the same db and try to act it gives me
> "Lock wait timetout exceeded; try restart transaction". I have code :
>
> protected void manageDbData(){
> try {
> ServiceSelector selector = (ServiceSelector)
> manager.lookup(DataSourceComponent.ROLE + "Selector");
> this.datasource = (DataSourceComponent)
> selector.select("humana");
> Connection myConnection = this.datasource.getConnection();
> Statement stmt = myConnection.createStatement();
>
> stmt.execute("INSERT INTO email VALUES (0, 'AAA@GMAIL.COM
> <ma...@GMAIL.COM>')");
>
> String query = "SELECT * FROM area";
> ResultSet result = stmt.executeQuery(query);
> System.out.println("EditNewsletters result =
> "+result.getFetchSize());
Is this code executed?
>
> stmt.close();
> myConnection.close();
I'm not too familiar with JDBC, but I'd assume that the close()
statements should go into the finally clause to avoid exhaustion of the
connection pool if exceptions occur.
BTW, your question is not really related to Lenya, maybe you get better
hints on a more appropriate mailing list (e.g., on the Cocoon user list).
-- Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@lenya.apache.org
For additional commands, e-mail: user-help@lenya.apache.org