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.

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
– markp-fuso, Commented Apr 28, 2022 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
– markp-fuso, Commented Apr 28, 2022 at 13:40
@markp-fuso thanks i made the changes to the code. The logs are redirected to a log file instead. — Jason Sivan
– Jason Sivan, Commented Apr 29, 2022 at 1:15

Hani · Accepted Answer · 2022-04-28 10:39:56Z

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.

answered Apr 28, 2022 at 10:39

Hani

763 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bash script unable to delete rows from mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related