stored procedure call causes communicaiton link failure
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"
Subject
Views
Written By
Posted
stored procedure call causes communicaiton link failure
4216
April 15, 2005 03:21PM
2342
April 15, 2005 04:15PM
2277
May 04, 2005 10:41AM
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.