Difference between revisions of "Retain Database"

Latest revision as of 22:18, 13 February 2018

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.

[edit] Database Concepts

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

[edit] Retain Data Database Schema Chart

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

[edit] 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

[edit] 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.

The dates stored in Retain are in UTC.

[edit] 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)

How far can you go with MySQL or MariaDB?

[edit] 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

[edit] 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.

[edit] Troubleshooting MySQL Issues

Some basic troubleshooting tips and tricks when MySQL explodes.

[edit] MySQL Optimization and Performance

See KB, "MySQL Maintenance for Best Performance".

[edit] 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.

[edit] Helpful MySQL 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.

[edit] MySQL

[edit] Select the minimum and maximum value in a table

SELECT MIN(f_delivered), MAX(f_delivered) FROM t_message;

[edit] 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

[edit] 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

[edit] 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;

[edit] 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)

[edit] Deletion Management specific Queries

[edit] Pre 4.2

Number of messages that (should, occasionally there have been issues) have been deleted from the t_message table and are ready to be wiped from disk:

SELECT COUNT(*) FROM t_document WHERE f_referenceCount = 0;

[edit] 4.2 and Later

Find a count of the number of messages marked for Deletion:

SELECT COUNT(*) FROM t_message WHERE (f_state % 2) > 0;

View all the messages marked for deletion. You'll be able to view the subject lines:

SELECT * FROM t_message WHERE (f_state % 2) > 0;

Get a count of all messages that should not ever be deleted:

SELECT COUNT(*) FROM t_message WHERE (f_state % 2) = 0;

View all the messages that are not marked for deletion:

SELECT * FROM t_message WHERE (f_state % 2) = 0;

[edit] 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

[edit] Oracle

[edit] Retain Table Explanations

[edit] 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"

[edit] MS SQL

[edit] Helpful Queries

[edit] Oracle

Installation tips [1]

[edit] PostgreSQL

[edit] MariaDB

MariaDB is essentially MySQL with some additional thing shoved in underneath. When installing it on SLES 12 you may find the command

prompt:~ #mysql_secure_installation

To be helpful, as it helps you set up a password (blank by default) and eliminate some of the useless stuff that you probably won't be using. I.E. the test user and database. Otherwise, feel free to use the same queries as the MySQL above. As of the writing of this article they should come back with the same information and work the same.

[edit] License queries

Queries and Instructions to get licensing numbers

CALL INSTRUCTIONS:

 1. Create a new support ticket (obviously)
 2. Run the query on the customers database.
 3. Copy the output and paste it into the ticket as a post reply.
 4. Add Brandon Engel (brandone@microfocus.com) as a CC recipient on the ticket.

Adding Brandon to the ticket so he sees the data will:

 A: Save the customer from having to make a second call.
 B: Prevent any customer from the temptation of manipulating the results.

[edit] The Queries!

Generic Query:

The one query to use on any SQL server that will JUST get the counts:

 SELECT
   COUNT(DISTINCT(ta.abook_id))
   FROM retain.t_message tm
   INNER JOIN retain.t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN retain.t_abook ta ON ta.f_uid = tum.f_uuid
 ;

Note: This Query will work on any SQL system so long as the database is named “retain”. If it is named differently make sure you change “retain” to the appropriate name. For Example: if they named the database “nibbler” it would look like this

 SELECT  
   COUNT(DISTINCT(ta.abook_id))
   FROM nibbler.t_message tm
   INNER JOIN nibbler.t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN nibbler.t_abook ta ON ta.f_uid = tum.f_uuid
 ;

Keep reading for individualized queries for each DBMS including counts of messages for each user, Because you know somebody is going to ask “Wait, why so many!!???”

[edit] MySQL:

Tell the DBMS to use the retain database USE retain;

The Query:

 SELECT  
     f_lmailbox AS  'Mailbox Name',
     f_email AS 'Email Address',
     f_dn AS 'User Name',
     FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
     FORMAT(COUNT(DISTINCT(tm.message_id)),0) AS 'Number of Messages Stored'
   FROM t_message tm
   INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
   GROUP BY abook_id
   ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ;

For the last 2 people that are still on Retain 2.6 and earlier:

 SELECT  
     f_lmailbox AS  'Mailbox Name',
     f_email AS 'Email Address',
     f_dn AS 'User Name',
     FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
     FORMAT(COUNT(no.node_id),0) AS 'Number of Messages Stored'
   FROM t_abook ta
   INNER JOIN Node no ON ta.f_uid = no.uuid
   INNER JOIN Email em ON no.node_id = em.node_id
   GROUP BY ta.abook_id
   ORDER BY COUNT(no.node_id)
 ;

[edit] Export Instructions (linux):

From command line paste in the following:

 mysql -u root -p databaseName -e "SELECT  
   f_lmailbox AS  'Mailbox Name',
   f_email AS 'Email Address',
   f_dn AS 'User Name',
   FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
   COUNT(DISTINCT(tm.message_id)) AS 'Number of Messages Stored'
 FROM t_message tm
 INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
 INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
 GROUP BY abook_id
 ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ; SELECT NOW() AS 'Export Date';" | sed 's/\t/,/g' > /export/path/arbitraryExportName.csv

Example:

 retainserver:~ #  mysql -u root -p retain -e "SELECT
   f_lmailbox AS  'Mailbox Name',
   f_email AS 'Email Address',
   f_dn AS 'User Name',
   FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
   COUNT(DISTINCT(tm.message_id)) AS 'Number of Messages Stored'
   FROM t_message tm
   INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
   GROUP BY abook_id
   ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ; SELECT NOW() AS 'Export Date';" | sed 's/\t/,/g' > /home/license_report_2017.csv

2.X:

 retainserver:~ #  mysql -u root -p retain -e "SELECT  
     f_lmailbox AS  'Mailbox Name',
     f_email AS 'Email Address',
     f_dn AS 'User Name',
     FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
     COUNT(no.node_id) AS 'Number of Messages Stored'
   FROM t_abook ta
   INNER JOIN Node no ON ta.f_uid = no.uuid
   INNER JOIN Email em ON no.node_id = em.node_id
   GROUP BY ta.abook_id
   ORDER BY COUNT(no.node_id)
 ; SELECT NOW() AS 'Export Date';" | sed 's/\t/,/g' > /home/license_report_2017.csv

Additional query in case customer has done something stupid, like put commas in their user name field.

 SELECT  
   f_lmailbox AS  'Mailbox Name',
   f_email AS 'Email Address',
   CONCAT(f_first, ' ',f_last) AS 'User Name',
   FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
   COUNT(DISTINCT(tm.message_id)) AS 'Number of Messages Stored'
 FROM t_message tm
 INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
 INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
 GROUP BY abook_id
 ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ;

[edit] Export instructions (Windows)

Paste into MySQL Command Line Client

 SELECT  
     f_lmailbox AS  'Mailbox Name',
     f_email AS 'Email Address',
     f_dn AS 'User Name',
     FROM_UNIXTIME(ts_store) AS 'Most Recently Archived Message',
     FORMAT(COUNT(DISTINCT(tm.message_id)),0) AS 'Number of Messages Stored'
     FROM t_message tm
       INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
       INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
   GROUP BY abook_id
   ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
   INTO OUTFILE 'C:\\Users\\Administrator\\Desktop\\retainLicense.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 
 ;

Couple notes:

 You can use the “Retain” user instead of root, root is not necessary for this query.
 If you run this query from a directory and don’t specify a directory it will place the file in the current directory that your command line is in.
 For example if you cd into /home, create a directory called “reports” and cd into that directory you could just do “... sed 's/\t/;/g' > license_report_2017.csv”
 If you run the query under the “Query” section above it’ll then be cached.  So the next time that query is run it’ll take a significantly shorter amount of time.
 On the biggest systems (300k+ messages) expect around 30 minutes or more for the query to finish.
 If you get a Windows MySQL setup that the command line isn't working on install MySQL Workbench and try to export it with that.
 If you still have problems let Daniel know and he’ll help you through it.

[edit] MSSQL:

The Query:

 SELECT  
     MAX(f_lmailbox) AS  'Mailbox Name',
     MAX(f_email) AS 'Email Address',
     MAX(f_dn) AS 'User Name',
     DATEADD(S, MAX(ts_store), '1970-01-01') AS 'Most Recently Archived Message',
     COUNT(DISTINCT(tm.message_id)) AS 'Number of Messages Stored'
   FROM t_message tm
   INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
   GROUP BY abook_id
   ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ;

[edit] Export instructions:

 http://bpmdeveloper.com/sql-server-export-wizard-csv/

[edit] Oracle:

 SELECT
         ta.f_lmailbox as "Mailbox Name",
       ta.f_email as "Email Address",
       ta.f_dn as " User Name",
       ta.ts_store as "Most Recently Archived Message",
       COUNT(DISTINCT(tm.message_id))as "Number of Messages Stored"
       FROM retain.t_message tm
        INNER JOIN retain.t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
       INNER JOIN retain.t_abook ta ON ta.f_uid = tum.f_uuid
       GROUP BY ta.abook_id,ta.f_lmailbox,ta.f_email, ta.f_dn,ta.ts_store
       ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ;

Notes: Have their DBA export the above query to a csv file. That’s what they get paid to do. Of course it’d be best if you watched while they do that. Just like the Generic query at the beginning of this document you’ll need to change “retain” to the name of the database they’re using if they named it something other than “retain”.

[edit] PostGreSQL:

 SELECT  
     f_lmailbox AS "Mailbox Name",
     f_email AS "Email Address",
     f_dn AS "User Name",
     TO_TIMESTAMP(ts_store) AS "Most Recently Archived Message",
     COUNT(DISTINCT(tm.message_id)) AS "Number of Messages Stored"
   FROM t_message tm
   INNER JOIN t_uuid_mapping tum ON tm.uuid_mapping_id = tum.uid_mapping_id
   INNER JOIN t_abook ta ON ta.f_uid = tum.f_uuid
   GROUP BY abook_id
   ORDER BY COUNT(DISTINCT(tm.message_id)) ASC
 ;

Ping Daniel if you run into a PostGreSQL setup. He didn’t want to research how to export to .csv for PostGreSQL unless there was an actual need.

[edit] Resolving Added Commas

There are some customers that have done silly things to their systems and they have a comma in-between the first and last name, messing up the formatting of the .csv file (Should only apply to MySQL exports as done above)

To resolve this open the file in Notepad++ and open the “Find and Replace” dialog box. You can use CTRL+F to open find and then open the "Replace" tab as a short cut.

In the "Find what" dialog box put

 (.*,.*,.*),(.*,.*,)

In the "Replace with" dialog box put

 \1\2

[edit] Additional info about how Retain interacts with databases and other Database things of note

[edit] MySQL

Because of how we normally recommend setting the database to UTF8, which is actually not "true" UTF8 since it's a 3 byte encode rather than a 4 byte encode, as of Retain 4.4 MySQL is set to put the subject lines of messages in a xml format so that the extended characters can be stored in the database. This should be automatically detected for MySQL based databases only (So, MySQL and MariaDB) and should not be set for Oracle, MSSQL, or PostGreSQL.

It's possible that if someone tries to migrate from one of these to MySQL or MySQL to one of the others this could become an issue.

Some additional reading if you're interested: https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql