0

I am new to MySQL, normally use SQL Server. I have a MySQL server (version 5.1.73) and I have two databases, syslog and syslog_archive.

There are three tables:

  • syslog.logs
  • syslog_archive.logs
  • syslog_archive.tmp_logs

When executing the below procedure, I receive the following error.

Error Code: 2014 Commands out of sync; you can't run this command now

Table Schema for all three tables:

CREATE TABLE `logs` (
  `host` varchar(32) DEFAULT NULL,
  `facility` varchar(10) DEFAULT NULL,
  `priority` varchar(10) DEFAULT NULL,
  `level` varchar(10) DEFAULT NULL,
  `tag` varchar(10) DEFAULT NULL,
  `sdate` date DEFAULT NULL,
  `stme` time DEFAULT NULL,
  `program` varchar(15) DEFAULT NULL,
  `msg` text CHARACTER SET latin1,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stime` text,
  PRIMARY KEY (`seq`),
  KEY `host` (`host`),
  KEY `program` (`program`),
  KEY `sdate` (`sdate`),
  KEY `stime` (`stme`),
  KEY `priority` (`priority`),
  KEY `facility` (`facility`)
) ENGINE=InnoDB AUTO_INCREMENT=38500021 DEFAULT CHARSET=utf8;

Procedure Code:

USE syslog_archive;
DROP procedure IF EXISTS `spArchiveLogRecords`;

DELIMITER $$
CREATE PROCEDURE spArchiveLogRecords
(
    retentionDays INT #Number of days of records to keep in the primary syslog database
    , batchSize INT #How many rows to process per batch
    , batchPause INT #How long to wait in between batches
)
spArchiveLogRecords_Label:BEGIN

    DECLARE retentionDaysChr VARCHAR(10);
    DECLARE batchSizeChr VARCHAR(10);
    DECLARE todayChr CHAR(10);
    DECLARE records INT;

    #Input validation
    IF (retentionDays < 0) THEN
        SELECT 'retentionDays must be a positive number.'
        UNION ALL
        SELECT 'No work has been conducted.';

        LEAVE spArchiveLogRecords_Label;
    END IF;
    IF (batchSize < 0) THEN
        SELECT 'batchSize must be a positive number.'
        UNION ALL
        SELECT 'No work has been conducted.';

        LEAVE spArchiveLogRecords_Label;
    END IF;
    IF (batchPause < 0) THEN
        SELECT 'batchPause must be a positive number.'
        UNION ALL
        SELECT 'No work has been conducted.';

        LEAVE spArchiveLogRecords_Label;
    END IF;

    #Variable initialization
    SET todayChr = CAST(CURDATE() AS CHAR(10));
    SET retentionDaysChr := RTRIM(CAST(retentionDays AS CHAR(10))); 
    SET batchSizeChr := RTRIM(CAST(batchSize AS CHAR(10)));   

    SET @sqlText = CONCAT("INSERT INTO syslog_archive.tmp_logs
                    ( `host`, facility, priority, `level`, tag
                    , sdate, stme, program, msg, seq, stime )
                    SELECT l.`host`, l.facility, l.priority, l.`level`, l.tag
                    , l.sdate, l.stme, l.program, l.msg, l.seq, l.stime
                    FROM syslog.logs l  
                    LEFT JOIN syslog_archive.logs al ON l.seq = al.seq  
                    WHERE l.sdate <= CURDATE() - INTERVAL ",retentionDaysChr," DAY 
                        AND al.seq IS NULL  
                    LIMIT ",batchSizeChr);

    #debugging
    #SELECT @sqlText;
    #end debugging 

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    batch_loop : LOOP

        #Truncate stage
        TRUNCATE TABLE syslog_archive.tmp_logs;

        #Load stage from primary
        PREPARE stageStmt FROM @sqlText;   
        EXECUTE stageStmt;
        SET records := FOUND_ROWS();
        DEALLOCATE PREPARE stageStmt;

        IF (records = 0) THEN
            LEAVE batch_loop;
        END IF;

        START TRANSACTION;

            #Insert into archive
            INSERT INTO syslog_archive.logs
            ( 
                `host`, facility, priority, `level`, tag
                , sdate, stme, program, msg, seq, stime 
            )
            SELECT `host`, facility, priority, `level`, tag
                , sdate, stme, program, msg, seq, stime 
            FROM syslog_archive.tmp_logs;

            #Oddly, table aliases broke the query with an
            #Unknown table 'l' in MULTI DELETE
            #error message.
            DELETE syslog.logs
            FROM syslog.logs 
            INNER JOIN syslog_archive.tmp_logs 
                ON syslog_archive.tmp_logs.seq = syslog.logs.seq;

        COMMIT;

        #debugging
        #LEAVE batch_loop;
        #end debugging

        SELECT SLEEP(batchPause);        

    END LOOP;
END$$
DELIMITER ;

CALL spArchiveLogRecords(1, 100000, 2);
1

The answer is probably among

http://forums.mysql.com/read.php?22,370578 (Lock wait timeout exceeded; try restarting transaction)
http://forums.mysql.com/read.php?98,591655 (2014 - commands out of sync; you can't run this command now)
http://forums.mysql.com/read.php?52,419733 (stored procedure issue in php script)
http://forums.mysql.com/read.php?52,407069 (ERROR 2014: "Commands out of sync;" when doing a SELECT After CALL)
http://forums.mysql.com/read.php?98,406327 (After execution of stored procedure output is [BLOB - 0B])
http://forums.mysql.com/read.php?98,386018 (How to do stored procedure return multiple rows ?)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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