Context: If you are running Calpendo on a server on your own network and you are storing data which is classified as personally identifiable information (PII) in certain jurisdictions or protected health information (PHI) in the USA it may not be possible to provide direct access to your server for members of the Exprodo support team.  This KB therefore outlines a method for downloading a copy of your database and stripping out sensitive data so that the database copy can be sent to us for the purposes of debugging and testing new configurations.


Suitable for: Admins of Calpendo hosted on their own internal networks with sensitive data stored on that Calpendo.


Objective: work through how to download a copy of the database and strip out sensitive data using SQL commands.

Steps: 

  1. Log in as an Admin or Root and go to Admin > Dump Database
  2. Change the drop-down to the option that says "Include all tables except history, statistics and attachment data"
  3. Click the "dump database" option
  4. After a brief pause, your browser should download a copy of the dump file. If it doesn't, then you will need to click on the link showing on the page that will then cause the dump file to download.
  5. Find the downloaded file on the computer you are working on. It will be called something like
    gzip -v foo | mysql bar
  6. Log in to a computer that has MySQL or MariaDB installed on it and copy the downloaded dump file onto it
  7. Create a new database with a command like this: 
    1. sudo mysqladmin create calpendo_test
  8. Load the dump file into using the following command:
    1. gzip -cd database.Calpendo.calpendo.v9.0.51.2020-02-28.sql.gz | sudo mysql calpendo_test
  9. Now you need to modify the data in the database to remove any private information. For this, you will need to identify the columns which contain data which needs to be written over with null. For example, if there's a booking property called "patientDetails", then you would look in the bakery at that property to see the name of the column. In this case, it might be "patient_details". If so, then you would remove this from the database with: 
    1. update bookings set patient_details = null;
  10. Repeat until all the private information is removed.
  11. Take a dump of the cleaned database using:
    1. sudo mysqldump calpendo_test | gzip > cleaned-calpendo.sql.gz
  12. If you don't have an account yet on https://customers.exprodo.com then go there and register as a new user. We will then approve your request.
  13. Once you have an approved account, log in to https://customers.exprodo.com and upload the file there and contact us.