MySQL Forums
Forum List  »  Stored Procedures

stored procedure call causes communicaiton link failure
Posted by: Sergey Zabilo
Date: April 15, 2005 03:21PM

Hello,
I have a problem while calling the stored procedure after two - three times. Subsequent call
causes communication link failure both in mysql and through jdbc. Please advise if you
had such problems before.

Also side question: do you know how to rise exception in stored procedure ? I didn't find
clear description about it.

Thank you in advance.

I have the following stored procedure:

CREATE TABLE CSSQUEUELOCKER(ID BIGINT NOT NULL PRIMARY KEY, CONTAINERID VARCHAR(100) , TIMESTAMP TIMESTAMP);

delimiter //
CREATE PROCEDURE CSSLOCKQUEUE(IN v_queueid BIGINT, IN v_cid VARCHAR(100), IN v_force VARCHAR(1), OUT err VARCHAR(1))
BEGIN

DECLARE v_tmp VARCHAR(100);
DECLARE v_lock INT;

set err='0';
select GET_LOCK('CSSLOCKQUEUE',10) into v_lock;
select CONTAINERID into v_tmp from CSSQUEUELOCKER where ID=v_queueid;
if v_tmp IS NULL then
insert into CSSQUEUELOCKER(ID,CONTAINERID,TIMESTAMP) values(v_queueid, v_cid, CURRENT_TIMESTAMP);
else
if v_cid != v_tmp then
if v_force = 'Y' then
update CSSQUEUELOCKER set CONTAINERID=v_cid, TIMESTAMP=CURRENT_TIMESTAMP where ID=v_queueid;
else
set err='1';
end if;
end if;
end if;
select RELEASE_LOCK('CSSLOCKQUEUE') into v_lock;
END //
delimiter ;



MySql version:
mysql> status
--------------
/usr/local/mysql/bin/mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)

Connection id: 2
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.2-alpha-max
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock

JDBC driver version:
mysql-connector-java-3.1.7 ( $Date: 2005/01/25 19:11:41 $, $Revision: 1.27.4.54 $ )


Exceptions which I've go through JDBC:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1842)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2288)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1494)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:730)
at db.test.GuranteedTest.insertLock(GuranteedTest.java:226)
at db.test.GuranteedTest.main(GuranteedTest.java:78)


** END NESTED EXCEPTION **


at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2500)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2784)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1494)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:730)
at db.test.GuranteedTest.insertLock(GuranteedTest.java:226)
at db.test.GuranteedTest.main(GuranteedTest.java:78)
Exception in thread "main"

Options: ReplyQuote


Subject
Views
Written By
Posted
stored procedure call causes communicaiton link failure
4216
April 15, 2005 03:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.