On Friday 22 June 2007 12:23, Alexandre Parizot wrote:
> Wei, Can you check the database error messages in the message "Main
> Log". You can find the system information in the email "System Info 7.1".
>
>
>
> Alex.
This is the script that caused the error in 7.1.0.0. (Fixed already)
The script in CVS is correct. This is the changelog regarding this error:
revision 1.6
date: 2007-02-05 23:21:05 +0000; author: alex; state: Exp; lines: +1 -1
* Merge from R7100_bugfixes_branch
* Bug:
Unicast delivery throw a DB exception when archiving the
transmission.
Fix:
The store procedure kcFTULog() use an undeclare variable (_tmp).
Wei
--
-- File Transmission Unicast stored procedures
--
CREATE OR REPLACE FUNCTION kcFTUMarkUnicastSites(
CHAR(36),
INTEGER,
CHAR(1) )
RETURNS VOID AS '
DECLARE
_tuuid ALIAS FOR $1;
_failAttempts ALIAS FOR $2;
_allowMulticast ALIAS FOR $3;
_isBroadcast CHAR(1);
_ackMode CHAR(1);
_lastSent TIMESTAMP;
_cnt INTEGER;
BEGIN
SELECT IsBroadcast, AckReportMode, LastTxEndTime INTO _isBroadcast,
_ackMode, _lastSent
FROM SendTransmissions WHERE TransmissionUUID = _tuuid;
IF( _allowMulticast = ''F'' OR _ackMode <> ''N'') THEN
BEGIN
SELECT COUNT(TransmissionUUID) INTO _cnt FROM
SendTransmissionSites WHERE TransmissionUUID=_tuuid;
IF( _cnt = 0 AND _isBroadcast = ''T'' ) THEN
BEGIN
INSERT INTO SendTransmissionSites (TransmissionUUID,
SiteID, Unicast, DeliveryStatus)
SELECT _tuuid, SiteID, ''T'', ''N''
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.Disable = ''F'' AND
UnicastSendSites.AllowMulticast = _allowMulticast;
END;
END IF;
IF( _cnt <> 0 AND _isBroadcast <> ''T'' ) THEN
BEGIN
-- update the the site IDs in SentTransmissionSites
UPDATE SendTransmissionSites
SET Unicast = ''T''
WHERE TransmissionUUID = _tuuid
AND Unicast <> ''T'' AND DeliveryStatus = ''N''
AND SiteID IN
(SELECT SiteID
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.Disable = ''F'' AND
UnicastSendSites.AllowMulticast = _allowMulticast);
END;
END IF;
END;
END IF;
-- for sites that acknowledged the receiving of the file set Unicast =
''F''
IF( _ackmode = ''A'' ) THEN
BEGIN
-- report always
UPDATE SendTransmissionSites
SET Unicast = ''F''
WHERE TransmissionUUID = _tuuid
AND SiteID IN
(SELECT SiteID
FROM TransmissionAck AS A
WHERE A.Status IN (''V'', ''C'', ''D'', ''N'')
AND A.TransmissionUUID = _tuuid);
END;
ELSIF( _ackmode = ''F'' AND _lastSent IS NOT NULL ) THEN
BEGIN
-- report on incomplete
UPDATE SendTransmissionSites
SET Unicast = ''F''
WHERE TransmissionUUID = _tuuid
AND SiteID NOT IN
(SELECT SiteID
FROM TransmissionAck AS A
WHERE A.Status IN (''E'', ''I'')
AND A.TransmissionUUID = _tuuid
AND A.ReceivedOn > _lastSent);
END;
END IF;
-- skip sites which fail too often
IF( _failAttempts > 0 ) THEN
BEGIN
-- report always
UPDATE SendTransmissionSites
SET DeliveryStatus = ''K''
WHERE TransmissionUUID = _tuuid
AND Unicast = ''T''
AND SiteID IN
(SELECT SiteID
FROM UnicastSendSites
JOIN ReceiveSites AS R ON R.ReceiveSiteID =
UnicastSendSites.ReceiveSiteID
WHERE UnicastSendSites.FailedConnectionCount >
_failAttempts
AND UnicastSendSites.LastConnectionTime >
CURRENT_TIMESTAMP + INTERVAL ''4 hours'');
END;
END IF;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION kcFTULog(
CHAR(36),
CHAR(36),
INTEGER,
INTEGER,
VARCHAR(255),
INTEGER,
INTEGER,
DECIMAL,
DECIMAL,
CHAR(1),
VARCHAR(255),
INTEGER,
DECIMAL,
DECIMAL)
RETURNS VOID AS '
DECLARE
_logid ALIAS FOR $1;
_tuuid ALIAS FOR $2;
_tid ALIAS FOR $3;
_siteid ALIAS FOR $4;
_sendToAddress ALIAS FOR $5;
_sendToPort ALIAS FOR $6;
_duration ALIAS FOR $7;
_sentBytes ALIAS FOR $8;
_sentPackets ALIAS FOR $9;
_status ALIAS FOR $10;
_errorMessage ALIAS FOR $11;
_channelID ALIAS FOR $12;
_bytes ALIAS FOR $13;
_packets ALIAS FOR $14;
_tmp INTEGER;
BEGIN
-- log the transmission
INSERT INTO UnicastSendLog (
LogID
, TransmissionUUID
, UnicastTransmissionID
, SiteID
, SendToAddress
, SendToPort
, Duration
, SentBytes
, SentPackets
, Status
, ErrorMessage
, ChannelID
)
VALUES (
_logid
, _tuuid
, _tid
, _siteid
, _sendToAddress
, _sendToPort
, _duration
, _sentBytes
, _sentPackets
, _status
, _errorMessage
, _channelID
);
-- update byte/packet count
UPDATE SendTransmissions
SET TransmissionCount = TransmissionCount + 1
, SentTxPackets = SentTxPackets + _sentPackets
, SentTxBytes = SentTxBytes + _sentBytes
WHERE TransmissionUUID = _tuuid AND TransmissionCount > 0;
IF NOT FOUND THEN
BEGIN
UPDATE SendTransmissions
SET TransmissionCount = TransmissionCount + 1
, SentTxPackets = SentTxPackets + _sentPackets
, SentTxBytes = SentTxBytes + _sentBytes
, TxPackets = _packets
, TxBytes = _bytes
WHERE TransmissionUUID = _tuuid;
-- remote management calls
SELECT COUNT(proname) INTO _tmp FROM pg_proc WHERE proname =
''kctxreceivefilesent'';
IF (_tmp > 0) THEN
PERFORM kcTxReceiveFileSent(_tuuid);
END IF;
END;
END IF;
RETURN;
END;
' LANGUAGE 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
