Mysql – delete over 90 million records, do I remove or maintain the transaction’s SQLWARNING

mariadbmariadb-5.5MySQLmysql-5.5

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'

Best Answer

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

Related Question