Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • MySQL 5.5.28

The error log keeps getting this error:

121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r
ows fetched, selected, or processed 
121004 12:13:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r
ows fetched, selected, or processed 
121004 12:18:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r
ows fetched, selected, or processed 

The definition:

mysql> show create event reportingdb.tvc_bookingonline_update_status\G
*************************** 1. row ***************************
               Event: tvc_bookingonline_update_status
            sql_mode: NO_AUTO_CREATE_USER
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`user`@`ip` EVENT `tvc_bookingonline_update_status` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-07-27 10:08:59' ON COMPLETION NOT PRESERVE ENABLE DO CALL `bo_tvc_booking_supdate_status_booking`( @plstbookingid_destroy)
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci

mysql> show create procedure reportingdb.bo_tvc_booking_supdate_status_booking\G
*************************** 1. row ***************************
           Procedure: bo_tvc_booking_supdate_status_booking
            sql_mode: 
    Create Procedure: CREATE DEFINER=`user`@`ip` PROCEDURE `bo_tvc_booking_supdate_status_booking`(
        OUT plstbookingid_destroy VARCHAR(1500)
    )
    DETERMINISTIC
BEGIN
    DECLARE  no_found_row INT DEFAULT 0;
    DECLARE _idbooking INT;
    DECLARE _status INT;
    DECLARE _no_contract VARCHAR(25);
    DECLARE _fromdate DATE;
    DECLARE _todate DATE;
    DECLARE _flagsync SMALLINT DEFAULT 0;
    DECLARE _ROWSCOUNT INT DEFAULT 0;
    DECLARE _lstBookDestroy VARCHAR(500);
    DECLARE _lstBookRunning VARCHAR(500);
    DECLARE _lstBookFinish VARCHAR(500);
    #LAY DU LIEU BOOKING
    DECLARE  cur_row CURSOR FOR
        SELECT B.`idbooking`, B.`status`, B.`no_contract`, B.`fromdate`, B.`todate` 
        FROM `tvc_bookings` B       
        WHERE B.`status` IN (1, 2, 3);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION  SET @error=1;
    DECLARE  CONTINUE HANDLER FOR NOT FOUND SET  no_found_row = 1;
    SELECT COUNT(*) INTO _ROWSCOUNT 
    FROM `tvc_bookings` B       
    WHERE B.`status` IN (1, 2, 3);
    IF(_ROWSCOUNT > 0) THEN
            SET AUTOCOMMIT=0;
        #BAT DAU DUYET TUNG BOOKING
        SET plstbookingid_destroy = '';
        SET _lstBookDestroy = 'List bookid destroy: ';
        SET _lstBookRunning = 'List bookid running: ';
        SET _lstBookFinish = 'List bookid finish: ';
        OPEN cur_row;
        FETCH cur_row INTO _idbooking, _status, _no_contract, _fromdate, _todate;
        WHILE no_found_row = 0 DO 
            #KIEM TRA RULE
            IF(_status = 1) THEN
                #IF((_no_contract IS NULL OR _no_contract = '') AND DATEDIFF(_fromdate, CURDATE()) <= 3) THEN
                #MODIFY DEN HET NGAY MOI HUY BOOKING
                #IF((_no_contract IS NULL OR _no_contract = '') AND DATEDIFF(CURDATE(), _fromdate) = 1) THEN#YEU CAU NEW NGAY CHAY = NOW
                IF((_no_contract IS NULL OR _no_contract = '') AND DATEDIFF(CURDATE(), _fromdate) > 0) THEN#YEU CAU NEW NGAY CHAY = NOW
                    CALL `bo_tvc_create_version_booking`( _idbooking, 'Services store huy booking den ngay chay ma chua co HD', 'Services');
                    UPDATE `tvc_bookings`  
                    SET `status` = 4,  
                        `modifiedby` = 'SERVICES',
                        `modifieddate` = CURRENT_TIMESTAMP()
                    WHERE `idbooking` = _idbooking;             
                    #OUT INFO
                    SET _lstBookDestroy = CONCAT(_lstBookDestroy, _idbooking, ',');
                END IF;       
            END IF;#IF(_status = 1)

            IF(_status = 2 AND _no_contract != '') THEN
                IF(_fromdate <= CURDATE()) THEN
                    #CHECK TABLE
                    /*
                    SELECT COUNT(*) INTO _flagsync
                    FROM `bo_tvc_sync_hdcn_thucchay` A
                    WHERE A.`booking_id` = _idbooking AND A.`updated` = 1;
                    */
                    SET _flagsync = 1;#FIX CODE CONNECT HDCN SOON

                    IF(_flagsync > 0) THEN
                        CALL `bo_tvc_create_version_booking`( _idbooking, 'Services store chuyen trang thai hop dong thanh running', 'Services');
                        UPDATE `tvc_bookings`  
                        SET `status` = 3,  
                            `modifiedby` = 'SERVICES',
                            `modifieddate` = CURRENT_TIMESTAMP()
                        WHERE `idbooking` = _idbooking;                             

                        #UPDATE FLAG DA CHUYEN                            
                        UPDATE `bo_tvc_sync_hdcn_thucchay`  
                        SET `updated` = 2
                        WHERE `booking_id` = _idbooking AND `updated` = 1;

                        SET _lstBookRunning = CONCAT(_lstBookRunning, _idbooking, ',');
                    END IF;    
                END IF;

                IF(DATE_ADD(_todate, INTERVAL 1 DAY) <= CURDATE()) THEN
                    CALL `bo_tvc_create_version_booking`( _idbooking, 'Huy book vi het ngay chay ma ko co thuc chay ben hdcn', 'Services');
                    UPDATE `tvc_bookings`  
                    SET `status` = 4,  
                        `modifiedby` = 'SERVICES',
                        `modifieddate` = CURRENT_TIMESTAMP()
                    WHERE `idbooking` = _idbooking;                             

                    #OUT INFO
                    SET _lstBookFinish = CONCAT(_lstBookFinish, _idbooking, ',');
                END IF;
            END IF;#IF(_status = 2 AND _no_contract != '') THEN

            #UPDATE CAC BOOKING DA CHAY XONG
            IF(_status = 3 AND DATE_ADD(_todate, INTERVAL 1 DAY) <= CURDATE()) THEN
                CALL `bo_tvc_create_version_booking`( _idbooking, 'Services store chuyen chuyen booking thanh chay ket thuc', 'Services');
                UPDATE `tvc_bookings`  
                SET `status` = 5,  
                    `modifiedby` = 'SERVICES',
                    `modifieddate` = CURRENT_TIMESTAMP()
                WHERE `idbooking` = _idbooking;                        
                SET _lstBookFinish = CONCAT(_lstBookFinish, _idbooking, ',');
            END IF;#IF(_status = 3 AND DATE_ADD(_todate, INTERVAL 1 DAY) <= CURDATE())
        FETCH cur_row INTO _idbooking, _status, _no_contract, _fromdate, _todate;
        END WHILE;
        CLOSE cur_row;

        #OUT INFO TO SERVICES
        SET plstbookingid_destroy = CONCAT(_lstBookDestroy, '\r\n', _lstBookRunning, '\r\n', _lstBookFinish);
    END IF;
    COMMIT;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci

What can I do to get rid of this error?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround.

Suppose that a table t1 is empty. The following procedure selects from it, raising a No Data condition:

CREATE PROCEDURE p1()
BEGIN
DECLARE a INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET @handler_invoked = 1;
END;
SELECT c1 INTO a FROM t1;
END;

As can be seen from the following sequence of statements, the condition is not cleared by handler invocation (otherwise, the SHOW WARNINGS output would be empty). But as can be seen by the value of @handler_invoked, the handler was indeed invoked (otherwise its value would be 1).

mysql> SET @handler_invoked = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p1();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @handler_invoked;
+------------------+
| @handler_invoked |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
There are two ways to work around this issue:

Add an extra dummy statement that clears warnings at the end of the condition handler:

CREATE PROCEDURE p1()
BEGIN
DECLARE a INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET @handler_invoked = 1;
SELECT 1 FROM (SELECT 1) AS t;
END;
SELECT c1 INTO a FROM t1;
END;

This works for CONTINUE and EXIT handlers.

"This issue is resolved as of MySQL 5.6.3 and no workaround is needed." http://dev.mysql.com/doc/refman/5.5/en/condition-handling.html

As this is just a case of the warning message not being cleared, it's not dangerous to data integrity. The work around above clears the warning message but also produces "dummy" output.

Here two very useful blog posts which contain additional details on this matter:

https://blogs.oracle.com/svetasmirnova/entry/warning_and_error_information_in https://blogs.oracle.com/svetasmirnova/entry/warning_and_error_information_in1

Cristian

share|improve this answer

According to this bug report, adding

SELECT TABLE_NAME INTO TableName FROM information_schema.`TABLES` LIMIT 1;

after COMMIT should do the trick

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.