Problem with procedure
Posted by:
Chris work
Date: March 02, 2008 09:02PM
Hi,
When the MySQL try to synchronize stored procedure, it shows this error.
How can I fix it? Thank you.
DB Version: 5.0.22
Error.log
080303 10:44:02 InnoDB: Started; log sequence number 0 48007
080303 10:44:02 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.22-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
080303 10:44:02 [Note] Slave SQL thread initialized, starting replication in log 'bin.000001' at position 48633, relay log '/var/log/mysql/relay.000067' position: 48517
080303 10:44:02 [ERROR] Slave: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/' at line 65' on query. Default database: 'nm'. Query: 'CREATE DEFINER=`root`@`localhost` PROCEDURE `ackAlarm`(
IN _Alarm_ID BigINT
)
BEGIN
DECLARE _Alarm_Counter INT DEFAULT 0;
DECLARE _Device_ID_Logical_Group INT DEFAULT 0;
DECLARE _done INT DEFAULT 0;
DECLARE _Logical_Group_ID INT DEFAULT 0;
DECLARE _RAW_Severity INT DEFAULT 0;
DECLARE _Device_ID INT DEFAULT 0;
DECLARE _Device_Table_Severity INT DEFAULT 0;
DECLARE _Device_Table_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _Device_Severity INT DEFAULT 0;
DECLARE _Device_ACK_YN VARCHAR(5) DEFAULT 'N';
DECLARE _Logical_Group_Severity INT DEFAULT 0;
DECLARE _Logical_Group_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _check_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _c CURSOR FOR
SELECT Logical_Group_ID
FROM v_logical_group_device
WHE
080303 10:44:02 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin.000001' position 48633
080303 10:44:02 [Note] Slave I/O thread: connected to master 'slaveMachine@10.6.209.171:3306', replication started in log 'bin.000002' at position 98
Source:
DELIMITER $$
DROP PROCEDURE IF EXISTS `nm`.`ackAlarm` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ackAlarm`(
IN _Alarm_ID BigINT
)
BEGIN
DECLARE _Alarm_Counter INT DEFAULT 0;
DECLARE _Device_ID_Logical_Group INT DEFAULT 0;
DECLARE _done INT DEFAULT 0;
DECLARE _Logical_Group_ID INT DEFAULT 0;
DECLARE _RAW_Severity INT DEFAULT 0;
DECLARE _Device_ID INT DEFAULT 0;
DECLARE _Device_Table_Severity INT DEFAULT 0;
DECLARE _Device_Table_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _Device_Severity INT DEFAULT 0;
DECLARE _Device_ACK_YN VARCHAR(5) DEFAULT 'N';
DECLARE _Logical_Group_Severity INT DEFAULT 0;
DECLARE _Logical_Group_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _check_Ack_YN VARCHAR(5) DEFAULT 'N';
DECLARE _c CURSOR FOR
SELECT Logical_Group_ID
FROM v_logical_group_device
WHERE Device_ID = _Device_ID_Logical_Group;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
SELECT Ack_YN
INTO _check_Ack_YN
FROM alarm
WHERE Alarm_ID = _Alarm_ID;
IF _check_Ack_YN = 'N' THEN
UPDATE alarm
SET Ack_YN = 'Y'
WHERE Alarm_ID = _Alarm_ID;
SELECT d.Device_ID, d.Severity, d.Ack_YN
INTO _Device_ID, _Device_Table_Severity, _Device_Table_Ack_YN
FROM alarm a, device d
WHERE a.Agent_ID = d.Agent_ID
AND a.Managed_Object_Device = d.Managed_Object_Device
AND a.Alarm_ID = _Alarm_ID;
SELECT a.Severity, a.ACK_YN
INTO _Device_Severity, _Device_ACK_YN
FROM alarm a, device d
WHERE d.Device_ID = _Device_ID
AND a.Agent_ID = d.Agent_ID
AND a.Managed_Object_Device = d.Managed_Object_Device
AND a.Cleared_YN = 'N'
ORDER BY a.Severity DESC, a.ACK_YN ASC LIMIT 1;
IF _Device_Table_Severity <> _Device_Severity OR _Device_Table_Ack_YN <>
_Device_ACK_YN THEN
UPDATE device
SET Severity = _Device_Severity, ACK_YN = _Device_ACK_YN
WHERE Device_ID = _Device_ID;
SET _Device_ID_Logical_Group = _Device_ID;
OPEN _c;
REPEAT
FETCH _c INTO _Logical_Group_ID;
IF NOT _done THEN
SELECT d.Severity, d.Ack_YN
INTO _Logical_Group_Severity, _Logical_Group_Ack_YN
FROM device d, v_logical_group_device lgd
WHERE lgd.Logical_Group_ID = _Logical_Group_ID
AND lgd.Device_ID = d.Device_ID
ORDER BY d.Severity DESC, d.Ack_YN ASC LIMIT 1;
UPDATE logical_group
SET Severity = _Logical_Group_Severity, Ack_YN = _Logical_Group_Ack_YN
WHERE Logical_Group_ID = _Logical_Group_ID
AND (Severity <> _Logical_Group_Severity
OR Ack_YN <> _Logical_Group_Ack_YN);
END IF;
UNTIL _done END REPEAT;
CALL disableAudio(_Alarm_ID);
ELSE
CALL disableAudio(_Alarm_ID);
END IF;
END IF;
END $$
DELIMITER ;
Thanks.
Chris