Assist Support Guide

Requirements

MySQL WorkBench

Hosted Systems

All hosted systems are documented in SharePoint, along with users and passwords.

You must have access to the SharePoint to read this document.

https://apteanonline.sharepoint.com/teams/CALIDUS-CONNECTIONS/Shared%20Documents/Forms/AllItems.aspx?id=%2Fteams%2FCALIDUS%2DCONNECTIONS%2FShared%20Documents%2FCalidus%20Assist&viewid=fffc6c78%2D04de%2D4e7d%2Db3ec%2D62168ff125fb

Updating Data En Mass

By default, MySQL will not allow updating of data without explicit WHERE or LIMIT clauses. To enable this:

• Go to "Edit/Preferences"
• Choose "SQL Editor" Under "Query Results"
• Check "Safe Updates"
• Restart MySQL WorkBench.

Warning: You will then be able to (for example) delete all data inadvertently, so enable this option with care.

Viewing BLOB fields

By default, MySQL WorkBench displays all text fields as "BLOB", which you must then right-click and choose "Open Value in Editor". To enable viewing the text directly in the results, do the following:

• Go to "Edit/Preferences"
• Choose "SQL Editor" Under "Query Results"
• Check "Treat BINARY/VARBINARY as nonbinary character string"
• Restart MySQL WorkBench.

Note: Enabling this option comes with a performance hit - some text fields contain the entire page data, so enable this with care.

Recovering passwords from MYSQL Workbench

First go check where your encrypted file is. Usually it is stored at %AppData%\MySQL\Workbench\workbench_user_data.dat

If different, amend the commands below.

Use Powershell

Add-Type -AssemblyName System.Security
$cipher = Get-Content $env:APPDATA\MySQL\Workbench\workbench_user_data.dat -Encoding Byte -Raw
$scope = [System.Security.Cryptography.DataProtectionScope]::CurrentUser
$mysqlpwd = [System.Security.Cryptography.ProtectedData]::Unprotect( $cipher, $null, $scope )
[System.Text.UTF8Encoding]::UTF8.GetString($mysqlpwd)

Common Queries

-- Assist SQL

-- Find the page ID
SELECT * FROM obs_assist.obs_page
WHERE PAGE_TITLE LIKE '%SDD_366558%';


-- Find all revisions (to change comments)
SELECT * FROM obs_assist.obs_revision
where rev_page IN 
(SELECT page_id FROM obs_assist.obs_page
WHERE PAGE_TITLE LIKE '%SDD_366558%'
);

-- Find unpatrolled pages to set them patrolled
SELECT * 
FROM obs_assist.obs_recentchanges
where rc_patrolled = 0;

UPDATE obs_assist.obs_recentchanges
SET rc_patrolled = 1
where rc_user_text = 'StylesC'
and rc_patrolled = 0;

-- Check the logs for details of PDF production
SELECT * FROM mts_assist.oh_logging
where log_action ='PdfBook'
order by log_id desc;

-- For v1.34 and later, comments have changed:
SELECT log.*, CONVERT(comment.comment_text USING utf8) comment
FROM epod_assist.epodlogging log 
left join epod_assist.epodcomment comment
on comment.comment_id = log.log_comment_id
where log_action ='PdfBook'
ORDER BY log_id DESC
LIMIT 20;