Situation
onsite_activity_view: Some kind of issue that generates an error during mysqldump
mysqldump -h xxxxxx.us-east-2.rds.amazonaws.com --set-gtid-purged=OFF -u openemr -p openemr > openemr-newphoe-600p1-2021-06-13-1954h
Enter password: (entered the password)
Then it generated the following error:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `onsite_activity_view`': View 'openemr.onsite_activity_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
To look into this more, I tried dropping and recreating the view;
Initializing connection…
Connection ready. Took 0.027 seconds.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘onsite_activity_view’ at line 1. (Error #1064) 0.051 seconds. (Line 1).
Finished. Total time: 0.079 seconds.
DROP onsite_activity_view;
CREATE OR REPLACE onsite_activity_view
AS
select `openemr`.`onsite_portal_activity`.`status` AS `status`,`openemr`.`onsite_portal_activity`.`narrative` AS `narrative`,`openemr`.`onsite_portal_activity`.`table_action` AS `table_action`,`openemr`.`onsite_portal_activity`.`table_args` AS `table_args`,`openemr`.`onsite_portal_activity`.`action_user` AS `action_user`,`openemr`.`onsite_portal_activity`.`action_taken_time` AS `action_taken_time`,`openemr`.`onsite_portal_activity`.`checksum` AS `checksum`,`openemr`.`patient_data`.`title` AS `title`,`openemr`.`patient_data`.`fname` AS `fname`,`openemr`.`patient_data`.`lname` AS `lname`,`openemr`.`patient_data`.`mname` AS `mname`,`openemr`.`patient_data`.`DOB` AS `DOB`,`openemr`.`patient_data`.`ss` AS `ss`,`openemr`.`patient_data`.`street` AS `street`,`openemr`.`patient_data`.`postal_code` AS `postal_code`,`openemr`.`patient_data`.`city` AS `city`,`openemr`.`patient_data`.`state` AS `state`,`openemr`.`patient_data`.`referrerID` AS `referrerID`,`openemr`.`patient_data`.`providerID` AS `providerID`,`openemr`.`patient_data`.`ref_providerID` AS `ref_providerID`,`openemr`.`patient_data`.`pubpid` AS `pubpid`,`openemr`.`patient_data`.`care_team` AS `care_team`,`openemr`.`users`.`username` AS `username`,`openemr`.`users`.`authorized` AS `authorized`,`openemr`.`users`.`fname` AS `ufname`,`openemr`.`users`.`mname` AS `umname`,`openemr`.`users`.`lname` AS `ulname`,`openemr`.`users`.`facility` AS `facility`,`openemr`.`users`.`active` AS `active`,`openemr`.`users`.`title` AS `utitle`,`openemr`.`users`.`physician_type` AS `physician_type`,`openemr`.`onsite_portal_activity`.`date` AS `date`,`openemr`.`onsite_portal_activity`.`require_audit` AS `require_audit`,`openemr`.`onsite_portal_activity`.`pending_action` AS `pending_action`,`openemr`.`onsite_portal_activity`.`action_taken` AS `action_taken`,`openemr`.`onsite_portal_activity`.`id` AS `id`,`openemr`.`onsite_portal_activity`.`activity` AS `activity`,`openemr`.`onsite_portal_activity`.`patient_id` AS `patient_id` from ((`openemr`.`onsite_portal_activity` left join `openemr`.`patient_data` on((`openemr`.`onsite_portal_activity`.`patient_id` = `openemr`.`patient_data`.`pid`))) left join `openemr`.`users` on((`openemr`.`patient_data`.`providerID` = `openemr`.`users`.`id`)));
OpenEMR Version
I’m using OpenEMR version 6.0.0p1
Browser:
I’m using: (not relevant)
Operating System
I’m using: Ubuntu 16.04.7 LTS (GNU/Linux 4.4.0-1128-aws x86_64)
–RBL