0

SQL Server: mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1

OS: Slackware 14.1

That's 90 million old radius bank records I want to remove, leaving only those this month. The script would be this:

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL
DELIMITER $$
USE ${DB} $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
# tm: time column of table, tb: table
CREATE PROCEDURE sp_monthly_table_cleanup(IN tb varchar(30), IN tm varchar(30), OUT msg varchar(70))
        BEGIN

                ######## rollback block in case of errors ##########
                DECLARE EXIT HANDLER FOR 1062
                BEGIN
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | Duplicate keys error encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                        # GET DIAGNOSTICS NOT SUPPORT IN MYSQL 5.5, ONLY >= MYSQL 5.6
                        # GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
                        # SET msg=CONCAT('FAILED: Routine failed for table ',tb,' | SQL State: ',@sqlstate,' | Errno: ',@errno,' | Msg: ',@text);
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | SQLException encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLWARNING
                        BEGIN
                            SET msg=CONCAT('FAILED: Routine failed for table ',tb,'. SQLWarning encountered');
                            ROLLBACK;
                        END;
                  START TRANSACTION;
                      SET @str4=CONCAT('DELETE FROM ',tb,' WHERE ',tm,' < date_sub(NOW(), INTERVAL 1 MONTH)');
                      PREPARE stmt4 FROM @str4;
                      EXECUTE stmt4;
                      DEALLOCATE PREPARE stmt4;
                      SET msg=CONCAT('SUCCESS: routine successfully executed for table ',tb,' on ',NOW());
                      COMMIT; 

       END $$
DELIMITER ;
EOFMYSQL

for t in "${!TABLES[@]}";
do
        echo ${t};
        echo ${TABLES[${t}]};
        mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL2 >> ${CLEARLOG}
        CALL sp_monthly_table_backup('${t}','${TABLES[${t}]}',@Msg);
        Select @Msg;
EOFMYSQL2

done

END=$(date +%s);
RUNTIME=$((END-START));

echo -e "Script finished on $(date -d @${END}) \n Runtime: ${RUNTIME} seconds" >> ${CLEARLOG};

STARTOPTRADACCT=$(date +%s);

echo -e "Starting optimization of table radacct on $(date -d @${STARTOPTRADACCT})" >> ${CLEARLOG};

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} --execute "OPTIMIZE TABLE radacct";
ENDOPTRADACCT=$(date +%s);
RUNTIMEOPTRADACCT=$((END-START));

echo -e "Optimization of table radacct finished on $(date -d @${ENDOPTRADACCT}) \n Runtime: ${RUNTIMEOPTRADACCT} seconds" >> ${CLEARLOG};

While checking the result file, I noticed that delete was terminated because of a SQLWarning:

Starting script on Wed Jan  8 15:06:17 -03 2020 
 Server timestamp: 1578506777
@Msg
FAILED: Routine failed for table radacct. SQLWarning encountered
Script finished on Wed Jan  8 22:50:03 -03 2020 
 Runtime: 27826 seconds
Starting optimization of table radacct on Wed Jan  8 22:50:03 -03 2020
Optimization of table radacct finished on Thu Jan  9 01:22:13 -03 2020 
 Runtime: 27826 seconds

Is it important that I keep this rollback to SQLWarning, or can I pull it out and still maintain data integrity?

UPDATE: I just saw a warning in the database log, shown just as the script started. But did mysql go to all the trouble of deleting it first and then seeing this warning shown at the beginning of it and rolling it back?

200108 15:06:17 [ERROR] mysqld: Table './mysql/proc' is marked as crashed and should be repaired
200108 15:06:17 [Warning] Checking table:   './mysql/proc'
4
  • You should have partitioned your table.
    – James
    Jan 9, 2020 at 12:48
  • 1
    Interesting how you call deleting records a backup. Somebody not familiar with your definition of backup is in for a lot of joy one day.
    – mustaccio
    Jan 9, 2020 at 13:54
  • kkkk is because I didn't put the whole code in the post, until I edited the name of proc not to be called attention anymore; the full script backs up and then cleans up the old records, I left in the post only the cleaning part Jan 9, 2020 at 14:29
  • REPAIR TABLE mysql.proc
    – danblack
    Oct 9, 2020 at 4:10

1 Answer 1

1

If you are deleting much of a table it is often better (faster, etc) to copy the table over without the 'old' rows:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE dt > NOW() - INTERVAL 1 MONTH;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;   -- or dump it for archive purposes

Better yet, make that new table PARTITIONed by day so that you can daily do the very fast DROP PARTITION to get rid of the month-old data and REORGANIZE PARTITION to get a new partition for tomorrow. Details in http://mysql.rjweb.org/doc.php/partitionmaint

If you need to "dump" or "archive" the month-old partition, see the discussion on "transportable tablespaces". (Note: MySQL 5.6 has one method and newer versions have another.)

Details on other deletion techniques: http://mysql.rjweb.org/doc.php/deletebig

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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