Restore is not one step

[kaihendry]

From https://unee-t.slack.com/archives/C7GL8EHFG/p1555030044168700?thread_ts=1555029839.168300&cid=C7GL8EHFG

The procedure is said to be:

1. restore the database from a clean backup:
   https://github.com/unee-t/bz-database/blob/master/db%20snapshots/unee-t_BZDb_clean_current.sql
2. (if you need the lambda to work) update the lambdas
   https://github.com/unee-t/bz-database/blob/master/db%20scripts%20to%20add%20objects%20to%20the%20schema/Add_all_lambda_related_objects_v4.33.1.sql
3. Copy the tables (and JUST the tables) from envo A to envo B.

Problems with the instructions above is that:

• it's not one step
• it's not clear how to update the lambdas
• i have no idea how to copy just the table from env to another
• sometimes i don't have two envs, my source is typically a sql dump

Problem: Restore from a SQL dump

I have a dev-backup-1554901903.sql that has some triggers as:

• DEFINER=unee_t_root
  and others as:
• DEFINER=root

and therefore inconsistent and therfore the restore halts as root when reaching the unee_t_root trigger stanzas, and similarly halts as unee_t_root when encountering root stanzas.

I'm not quite sure:

• why root can't restore unee_t_root triggers
• why we even have uneet_t_root, i.e. two super users that can't seem to perform each others super user actions
• how to get out this mess... two dumps?! one for root, and one for unee_t_root?!

Proposed solution:

unee_t_root definers are renamed to root. And unee_t_root which has already cost several hours in confusion/complexity can be dropped.

[kaihendry]

Related AWS support issue: https://console.aws.amazon.com/support/cases#/5745178081/en

AWS's support solution is:

There are two ways how you can handle the following error.

1. Replace the user of the definer to a single user. This means that either keep all the definer as "unee_t_root" or as "bugzilla". And then use the specified user for restoring the dump.

2. Remove the definer from all the triggers inside the dump file. After the definer is removed and the restoration is performed, all the triggers will have the master user as the definer now.

[kaihendry]

Just restored dev as root by removing the DEFINER elements from the SQL dump. https://client-aysiq7n9tw8hfu5rvr6mzf.uilicious.com/studio/project/NAigEgKD6y8qeNMrAZ65oF/monitoring/testRun/MeAY55KGLJ2ziagKs5whNk

I'm leaving this open if you want to fix the appropriate triggers with unee_t_root.