Check if ANY database record has been changed, added, deleted, etc. today?

[dev77]

Is there a way to tell if ANY change was made to the database today? Maybe a log entry or some :switch-file" somewhere?

I want to write a nightly bash script that can see if any changes were made to the database in the last 24 hours (or some time interval) and if so I want to engage code I already have to dump the database and load my backup EspoCRM system. (I have two installs of EspoCRM on the same server, in case one goes down during an update or something.) There is a bit of overhead in this and I don't want to reload the backup database if no changes have been made.

Any ideas on how to check if ANY record in the DB has been modified, or if there was an addition or a deletion? Maybe there is a mysql command or query I can use if there is nothing in EspoCRM? I'll research that.

[shalmaxb]

Perhaps this might help: https://www.espocrm.com/tips/activity-stream/

[dev77]

Perhaps this might help: https://www.espocrm.com/tips/activity-stream/

This is very interesting. I didn't know about Streams.

I think I will be easier to work from the mySQL side than the EspoCRM side, but I've not figure out or found the way to do that (yet).

How would I access this info outside of EspoCRM via a bash script? My current logic is simple:


Here is what I do now. I have a simple empty text file called OFF-espo-backup-SWITCH.txt. If I add or change or delete anything in EspoCRM during the day I have to remember to change the name of the file to "ON".

This code runs every night (I edited it for simplicity here):

# Is the switch file "ON" or "OFF" ... meaning does it exist or not?

FILE=ON-espo-backup-SWITCH.txt


# Test to see if switch is set by checking to see if the file "ON" exists. If file does not exist, exit.

if [ ! -f "$FILE" ]; then
exit

fi

# $FILE is ON, so we turn if OFF and run the rest of the script to do the backup

mv   ON-espo-backup-SWITCH.txt      OFF-espo-backup-SWITCH.txt 
.
.
.

I don't want to have to 'remember' to turn ON the switch file. That is what this is all about. Not a big deal.

(Note: I've tried to write a PHP script for me to execute that would rename the switch file but I can't get it to work yet... some kind of permission issue somewhere, so I do it manually. i don't make changes every day to the database and if I did miss a day I'd 'catch up' the next day.)

[dev77]

It took a while but I figured it out. The problem is that EspoCRM writes the "Modified At" date in UTC time. So an account record could be dated 8 hours ahead of me. Thus I have to check to see if a record was modified today and tomorrow! (I tried to figure out the time-zone conversion but I never got it to work.)

Here is the bash script code:

mycount=$(mysql espocrm2_db -e "SELECT COUNT(*) FROM account WHERE DATE(modified_at) = CURRENT_DATE OR DATE(modified_at) = DATE_ADD(CURRENT_DATE,INTERVAL 1 DAY) ORDER BY modified_at DESC;")

tot1="${mycount//[!0-9]/}"


if (( $tot1 <= 0 ))
then
#echo "we got nothing"
exit
fi

#echo "we have a hit and are ready to do the backup and load to the other system"

.
.
.
#

[espcrm]

Not sure about how to do it with bash script but for formula it quite simple; https://docs.espocrm.com/administrat...datetimeformat

I did a quick search and found these resources that you could do a test to see if you can (1) get it to work and (2) adopt it for your script.

https://linuxconfig.org/convert-timezones-to-my-local-timezone-using-linux-command-line

https://superuser.com/questions/164339/timezone-conversion-by-command-line

[dev77]

Thanks for the info. I'll have to study it a bit, but I rewrote the script to take advantage of the mySQL conversion functions. I enter my changes during the day but the script most often runs in the early hours of the next day... except when I run it manually... so I need to know what day I'm running it... if next day I have to 'get' yesterday's changes. That is the reason for the "if/then' here.

curhr=$(date +%H)


if (( $curhr > 07 && $curhr < 23 )); then
     #echo "it is between 6AM and 11 PM"

     mycount=$(mysql espocrm2_db -e "SELECT COUNT(*) FROM account
     WHERE date(CONVERT_TZ(modified_at,'+00:00','-07:00')) = CURRENT_DATE ORDER BY modified_at DESC")

else
     #echo "it is not between 6AM and 10PM"

     mycount=$(mysql espocrm2_db -e "SELECT COUNT(*) FROM account
     WHERE date(CONVERT_TZ(modified_at,'+00:00','-08:00')) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)
     ORDER BY modified_at DESC")
fi

tot1="${mycount//[!0-9]/}"


if (( $tot1 <= 0 ))
then
     #echo "there were no changes"
    exit
fi

{do rest of backup}

[espcrm]

I notice you do a IF statement first... I can't read code at all but shouldn't be be converting the time before you use this statement?

That is: you want this to run between 6am and 11pm YOUR TIMEZONE and not 6am-11pm UTC+0 timezone?

But I see you doing a convert afterward instead? I guess you can do a cheat method but just adapting the code time till you get it to where you want? Then figure out how-to convert timezone afterward.

[dev77]

"shouldn't be be converting the time before you use this statement?"

I want to find out what hour I'm running the script. My server is in my timezone so no conversion is necessary. If the hour is between 7AM and 11PM then I am running the script manually and so I want to check on changes to the database that happened today. But the script runs via cron at 1:30 AM so I need to check the database for changes YESTERDAY. That is why you see two different SQL queries... one for CURRENT DATE and the other one with
DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY) which tells the mySQL to take the current (server) date and subtract (i.e. SUB) one day from it, giving yesterday.