Hi everyone,
Migration to v4 is finally live. I wanted to detail the comments process in case others might find it useful.
I could see writing a scripted solution for this being a very difficult process, and I’m sure any official solution will be far more elegant than the one I am about to describe.
As long as it's made abundantly clear that...
A) you should back everything up before you touch your database,
B) this isn't an officially or unofficially supported hack, and
C) you should back things up first,
we don't have any problem with you posting it here. :-)
You read it above. This is an extremely unsupported process and SHOULD NOT BE ATTEMPTED ON A LIVE SYSTEM WITHOUT PROPER BACKUPS AND TESTING.
Your environment is likely to be different from mine. Ostensibly the process should be adaptable regardless of your infrastructure. I’ll detail ours in this post only to provide a backdrop against which these steps were performed.
As Hildy mentioned:
-- BACK UP everything before trying to undertake a comments import.
-- Do not expect WGM folks to offer assistance with this. I happen to have a co-worker who is extremely knowledgeable in SQL and that’s what made it work.
-- BACK UP everything prior to touching any of the DBs, even just for exporting tables.
A few caveats of my own:
DO NOT TRY THIS ON A PRODUCTION SERVER If it is possible, create a duplicate of both your Cerb 3 and Cerb 4 databases for purposes of testing this in your environment.
-- This method required the following tools:
Command line or GUI query access to your Cerberus 3 database.
Command line or GUI query access to your Cerberus 4 database.
Microsoft Excel or similar
A good raw text editor like Ultra Edit.
--If you are not comfortable using the SQL command line or GUI query tools like MySQL query browser, or phpMyAdmin then this hack is NOT FOR YOU. Exercise a little more patience and wait for the official tools from WGM.
-- Understand also that my own time for answering questions is limited, so be ready to do some foot work if you want to try this on your own. I am confident that I can answer general questions about the process, but questions specific to your environment will rely on your own adventuresome spirit and creative ingenuity to resolve.
--This outline details the steps I took when doing my production server migration. Modify them as appropriate for testing prior to doing ANYTHING on your production server. By following any of the steps in this or subsequent posts on this topic you agree that I’m not responsible for anything that occurs, good or bad, right or wrong, awesome or fail. You agree that as an adventurous and creative adult you are taking responsibility for attempting a 100% unsupported hack.
All that being said, here we go:
The Environment
The source:
Cerberus 3.2 Release (Build 317)
Windows 2000 Sever (running in VMWare)
MySQL 4.1
MySql Query Browser
The Destination:
Cerberus 4.0 RC1 (Build 603)
Ubuntu 8.04 (VMWare)
MySQL 5.051a
phpMyAdmin 2.11.3
Preparatory steps before doing anything with the tables
1. Ensure that all incoming mailbox checks in Cerberus are disabled. We don’t want any new mail coming in during our exports.
2. Ensure that all agents are logged out of the system. No activity in Cerberus at all.
3. If any teams are already using Cerberus 4, ensure that all incoming mail is off and that all agents are logged out of the system.
4. Run existing message export tools and parse into cerb 4 as instructed. You’ll need all of the tickets in the system prior to doing anything with comments.
Fun with comment tables
I used MySQL query browser to export the next_step table from my cerb 3 db and saved them to an excel file. CSV works too, but as you’ll see later I had to do some changing to delimiter and enclosure characters in order to import with phpMyAdmin.
I noted the fields in this table: id, ticket_id, date_created, created_by_agent_id, and note then took a look at the destination side.
Cerb4 stores ticket comments in the ticket_comment table. Imagine that. There is also an important table, ticket_comment_seq but we’ll come back to that later. Note that this table contains the fields: id, ticket_id, worker_id, created, and comment.
We need a repository for the comments temporarily, so I created the table cerb3_comments with the following fields: id, ticket_id, created_by_agent_id, date_created, and note. Note that these are the cerb3 fields in the cerb4 order. Probably not important, but just how I did it.
Using the gui tools above, I took note of the new worker_id value for each worker, as well as the old ones and jotted them on a sheet of paper in a little table. We’ll have to come back to the cerb4 DB several times in the following steps as well.
Correcting Ticket Masks
For a variety of reasons, I had a handful of tickets that did not get masked with the crucial CERB3-xxxxx mask. It was easy enough to search for them using a Mask “not equal” search. Most of them were message threads that were (for whatever reason) missing the first message of the thread. Only took a few minutes to merge them back into threads then take note of their message ID numbers.
For each message, I checked the full header for the old Cerberus ID number <insert header name here>. Again, jotted down the number I needed next to the id number of the mis-masked ticket.
Back to the cerb4 DB. Found each id and edited the mask to represent CERB3-<old ticket number>. This also goes fairly quickly.
Prepping comments for import
This turned out to be a bit of a time consumer. First, I got the comments excel file and arranged the columns to match my destination table (cerb4.cerb3_comments). Then I highlighted *just* the created_by_agent_id column. I have about 11 agents, and discovered the best thing to do is do a find replace on the selection, old id to worker name. Once all those are names, then go worker name to new id.
My excel skills are somewhat limited, and I had trouble just straight exporting a CSV file that phpMyAdmin would accept for import. Biggest problem is that the text in actual comment is likely to contain any characters that would be used for an enclosure or delimiter character.
To get around this I did a bit of searching through the text, and settled on | as a delimiter and ~ as an enclosure. Even with this route I had to re-import the data to my holding table several times after correcting “incorrect field count” errors which meant either an enclosure or delimiter character within the text of the Note/Comment field.
Again, excel is not my strong point, so I basically inserted enough columns between the tables to create the characters I needed. In the end it looked something like this (if you imagine that each marker character is in it’s own excel field.):
~1~|~4~|~2~|~1144429507~|~This is a test comment. What does Cerberus do when I enter comments in this field? ~
Once all those are drag-copied down the entire list (we had about 6000 comments as it turns out) return to the top row and in an empty column at the far end, I used the concatenate function (selecting all the previous columns in that row) to get all the text into one cell. Drag-copied that equation down the who list and blammo.
Just to be save I copied out that column of concatenated cells into ultra-edit to save it as a csv without Excel adding trailing or leading commas or quotes that I had to deal with.
Imported that file to the cerb3_comments table. Note, at this point the worker IDs are updated, but the ticket IDs are not. Also the data is in a table that does not display anywhere in the cerb4 interface. Also, this is best done in chunks. I did roughly 1000 at a time, 6 chunks essentially). Each time the query below results in successful comment importing I cleared cerb3_comments and started again with the next chunk.
Mysterious SQL-Ninja-Magic
At least it is to me. I didn’t write this query, a co-worker did. This basically moves the cerb3_comments data to the ticket_comment table while updating the ticket_id field by comparing the CERB3-xxx mask against the existing ticket_id, and replacing that with the new ticket_id value in the id field of the matching ticket.
USE cerb4;
INSERT INTO ticket_comment (id, ticket_id, worker_id, created, comment)
SELECT cerb3_comments.id, ticket.id, cerb3_comments.created_by_agent_id, cerb3_comments.date_created, cerb3_comments.note
FROM cerb3_comments, ticket
WHERE SUBSTRING(ticket.mask,7) = cerb3_comments.ticket_id;
<continues>