Bash script unable to delete rows from mysql table

Question

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.

Answer 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.