mysql Trigger uploads for CiviCRM

CiviCRM relies heavily on mysql triggers to perform complex query functions. Occasionally these need to be updated or have new triggers added. The database trigger updates are run by the FR Tech Ops personnel in conjunction with FR Tech.

Requirements in place before an upload

Before we can do the upload we need to ensure there are a few things in place.

Approved triggers staged in gerrit
Access to the database origin server including shell and mysql access. Make sure you have a .my.cnf file or user/password information for db access
Access to the "failover" database host in order to stop the replication thread

Prep work for the upload process

Get the trigger scripts +2'd and pushed to production

Capture the state of the old triggers

mysqldump -uroot --routines --no-create-info --no-data --no-create-db --skip-opt --add-drop-trigger civicrm > triggers.before.$(date +%Y%m%d)

Copy generated trigger scripts from the active civicrm application server to the frdb origin server

Fetch the scripts from the civicrm server

rsync -var --rsh=ssh civi1002:/srv/org.wikimedia.civicrm/drupal/sites/default/civicrm/extensions/wmf-civicrm/sql/ scripts.$(date +%Y%m%d)/

Push the trigger scripts to the frdb origin server (frdb1005):

rsync -var --rsh=ssh scripts.$(date +%Y%m%d) frdb1005:

Running the upload process

Stop queues

Depending on the update, this may not be required.

Stop replication on one frdb replica server

mysql "stop replica"

Run the trigger update script on the frdb origin server

mysql civicrm < scripts.$(date +%Y%m%d)/file_with_trigger_updates.mysql

Verify the newly installed triggers

Capture the trigger state

mysqldump -uroot --routines --no-create-info --no-data --no-create-db --skip-opt --add-drop-trigger civicrm > triggers.after.$(date +%Y%m%d)

Verify the line count

In some cases the line count will be the same if it's just updating existing triggers
If triggers are added or deleted, the line count should differ

# wc -l triggers.before.$(date +%Y%m%d) triggers.after.$(date +%Y%m%d)

diff the triggers (choose one)

You should see a difference in the before and after trigger information.

Verify the diff of the mysql triggers using the holy grail for diffs

git diff -U0 --word-diff=color --no-index -- triggers.before.$(date +%Y%m%d) triggers.after.$(date +%Y%m%d) | less -R

Use a more standard version

diff triggers.before.$(date +%Y%m%d) triggers.after.$(date +%Y%m%d)

You may need to use vimdiff to actually see where the differences are, this may entail scrolling way to the right to find the actual update.

 # vimdiff triggers.before.$(date +%Y%m%d) triggers.after.$(date +%Y%m%d)

Confirming the upload process

Verify with the FR Tech member responsible for the update that the new behavior is in place and performing as expected

Post update actions

Restart the replication thread process on the frdb replica server
Restart the queues if stopped