0

I have a script that will delete rows from mysql tables when they are more than a certain date:

echo "[INFO] Remove Foreign_key_Check" >> ${LOG_FILE}
init_check=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${DATABASE_NAME} -e "SET GLOBAL FOREIGN_KEY_CHECKS=0")
init_error_code=$?

if [ $init_error_code -eq 0 ] ; then
  for table in "${DB_TABLES[@]}" ; do
    delete_response=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${DATABASE_NAME} -e "DELETE FROM ${DATABASE_NAME}.${table} where created_dt <= curdate()- interval ${RETENTION_MONTHS} month") 2>&1
    delete_error_code=$?

    echo "[INFO] DELETE RESPONSE $delete_response" >> ${LOG_FILE}
    echo "[INFO] DELETE ERROR CODE $delete_error_code" >> ${LOG_FILE}
                        
    if [ $delete_error_code -eq 0 ] ; then
        echo "[INFO] Successfully deleted $table record for more than ${RETENTION_MONTHS} months"  >> ${LOG_FILE}
    else
        echo "[ERROR] Unable to delete $table"  >> ${LOG_FILE}                           
        SUCCESS=$delete_error_code
    fi              
  done
else
    echo "[ERROR] Initializing foreign_key_check failed"
    SUCCESS=$init_error_code
fi

After running this script on cronjob, this error came up.

[INFO] DELETE RESPONSE
[INFO] DELETE ERROR CODE 141
[ERROR] Unable to delete table

What is this error code and why is not able to delete from the table?

Note: There is a database trigger that insert row into this table whenever rows in another table is deleted. However, this other table's rows are deleted first before the rows in this table is deleted.

Jason Sivan
  • 1
  • 3
  • assuming `mysql` is sending an error message to stderr, the current code is not capturing stderr in `$delete_response`; at the end of the `mysql` call try changing `... month") 2>&1` to `... month" 2>&1)`; then again I'd expect the stderr content to be sent to `cron` so where does the output from your cronjob invocation go to? is it being redirected to another log file? redirected to `/dev/null`? sent as an email to the account under which the cronjob is running? where is the error message/stderr going? – markp-fuso Apr 28 '22 at 13:39
  • alternatively, have the script print out the complete `mysql/delete` call then try to manually run the `mysql/delete` call and see what's output to your console – markp-fuso Apr 28 '22 at 13:40
  • @markp-fuso thanks i made the changes to the code. The logs are redirected to a log file instead. – Jason Sivan Apr 29 '22 at 01:15

1 Answers1

1

It seems what you are trying to do can be achieve using MySQL events scheduler.

for the script check that it is taking the ${table} variable correctly. Hint: no need for the ${DATABASE_NAME}.${table} when passing the ${DATABASE_NAME} as an argument for mysql command, ${table} should be sufficeint.

Hani
  • 66
  • 3