Retain Database

The database communication settings for Retain are set under Server Configuration | Communications. You may make changes within the Communications tab. After changing any communications options, it is strongly recommended that Tomcat be restarted immediately.

The settings for the Communications tab are set in the initial setup of Retain. These include the connection to the SQL database server, the Retain database, and the notification, SMTP information. If any changes in the system or corrections are needed, they should be configured here.

Database Concepts

Here is a slide presentation on SQL basics, including information on the Retain database: SQL Basics

Retain Data Database Schema Chart

Here is a Google Drawing showing the Retain message database mapping.

Database entries for user mailboxes in Retain 3.5.0:

• Object GUID in t_appuid.f_auid
• Mailbox GUID in t_exchobj.f_uid
• Object GUID also in t_exchobj.f_value

Retain would store the object GUID in the t_appuid.f_auid and compare that value with the "GUID" cell in the exchangeuser.csv file to determine user uniqueness.

MS changed how things were done in O365 and the Object GUID would change resulting in duplicate user entries, but the Exchange GUID would stay stable. So we changed our code to deal with that change. Jiri bug: RET-1988 added to Retain 3.5.1. The duplicate users can be resolved with mergeDuplicateUsers.jsp

Database entries for user mailboxes in Retain 3.5.1 and later:

• Exchange GUID in t_appuid.f_auid
• Mailbox GUID in t_exchobj.f_uid
• Object GUID also in t_exchobj.f_value

Now, in Retain 3.5.1 and later, Retain stores the Exchange mailbox GUID in t_appuid.f_auid and compares that value with the "Exchange GUID" cell in the exchangeuser.csv to determine user uniqueness.

NOTE: t_exchobj.f_type tells us what type of data is stored in the t_exchobj.f_value field, whether it is the object GUID (which it should be) or the mailbox GUID.

MySQL

This is technically not supported by GWAVA (really we can't, this information is to help GWAVA technicians and partners understand the MySQL underpinnings so that they can create working servers for Retain)

Installation

MySQL Quick Installation notes 5.5 (mostly information from the Docs)

Installing MySQL manually (I.E. sans the Retain install script) for 5.5 and 5.6. Main download link can be found here

MYSQL 5.5 Download site

Backup

Framework for getting your database backed up so that you can sleep at night. Includes a section on how to import that database if you need to get it back into play.

Troubleshooting MySQL Issues

Some basic troubleshooting tips and tricks when MySQL explodes.

MySQL Optimization and Performance

See KB, "MySQL Maintenance for Best Performance".

Helpful MySQL links

There is a LOT of information on the MySQL sites. These should help you find what you want to know. The links are for 5.5 but there is a link on the left side of the page that will send you over to the corresponding article that you're in for 5.6 if you just click on "MySQL 5.6 Manual":

• MySQL main document page.
• my.cnf option file and information on the switches used in the file.
• innodb configuration.

Another great resource is stackoverflow which has saved my bacon on many occasions.

Helpful Queries

Put queries here that you feel are helpful. Be sure to indicate what the query is doing <grin>.

Note: As a general rule DO NOT use a query on a customers system unless you are absolutely sure that you're not going to get a huge return. If a query has many results to return or has a lot of data to sift through it could bring the server to it's knees. Remember: A customer system is not a test system. Here's an article on how to kill a process in MySQL if you manage to mess it up Sometimes it is helpful to use a COUNT statement on your select statement first to see if you're going to have a lot of data to sift through. Also, the LIMIT (MySQL) or TOP (MSSQL) statement can be helpful. However, the SQL server will still have to find all the values that fit your query, it won't go to the limit or top value and then stop.

MySQL

Select the minimum and maximum value in a table

select min(f_delivered), max(f_delivered) from t_message;

Select list of users who have had mail archived

select f_first, f_last, f_mailbox from t_abook where f_uid in (select f_uuid from t_uuid_mapping) order by abook_id \g

Select list of users who have never had mail archived

select f_first, f_last, f_mailbox from t_abook where f_uid not in (select f_uuid from t_uuid_mapping) order by abook_id \g

Find Hashes of specific date range from when the attachment was archived

SELECT hash FROM t_document WHERE (date BETWEEN *1433200981 AND *1439076181) ORDER BY date DESC;

If you know the exact timestamp

SELECT hash FROM t_document WHERE date = xxxxxxxxxx;

Re-create the admin user in a Retain system where the user tables are blank

Use the SQL file found here to fix this issue.

WARNING: Only do this if the tables are blank, otherwise you could break something.

Note: The dates are timestamps in Unix time (or POSIX/Epoch, if you prefer)

Retain Table Explanations

Audit Table: Action Codes

• Archived ="A"
• Audit="I"
• Deleted ="D"
• Export="X"
• Forward ="F"
• Litigation Hold="H"
• Login="L"
• Publish ="P"
• Read ="R"
• Restore="T"
• Schedule/Worker/Profile/Job "J"
• Search="S"
• Server="E"
• Switchmail="M"
• Users="U"

dumpTables script

In some cases, you may need to dump the database by tables, we have a bash script to do this.

Retain Database Dump Tables Script