How to upgrade OTRS 3.0 to 3.1 on Windows

I’ve using OTRS on a windows server for quite some time now and one of the big problems that I’ve found is the lack of information and documentation for my OS. Windows Racism is what I call it. The OTRS even comes with a manual on how to upgrade it… but on LINUX, for us windows user, not so much luck. This manual is a good guideline but not exactly what we need. Recently I’ve managed to finally upgrade OTRS from 3.0.7 to 3.0.13 to 3.1.12 on windows, is not that hard and the manual actually comes in handy as a guide line but there are several other steps to be performed.

This is the UPGRADING manual that comes with OTRS, is a piece of dog shit for hackers, gods, and gurus using linux-crap. For us, normal, average people using the allmighty Microsoft Windows 2008 R2, there is nothing… until now.

Ok.. some basics:
– Read all this manual first, then read it again and once you have understand it put it into motion.
– Usea a TEST enviroment first… but you are very smart so you already knew that one.
– This worked for me… you may or may not have diffrent settings.
– Steps 8 to 11 are the most prone to errors. If something goes wrong in any of these steps, then you are fucked, your data might be corrupted and you have to star all over again, delete everything from step 3.

So.. here is the quick how to :

STOP all services.

1. You have to make a back up of everything, this not negotiable. Back up your entire X:\Program Files\OTRS folder. What I did is to RENAME the folder from X:\Program Files\OTRS to  X:\Program Files\OTRS 3.0.12  (X being the drive where you have your OTRS installed, usually is C:\ but in my case is F:\ … yeah, fucked up but there it is and it works the same).  This way you have an instant back up.
2. Rename the START MENU folder… yes, the one with all the shortcuts to OTRS 3.0.12 , trust me on this one, it will come in handy later.
3. Install OTRS 3.1.12… just like that, install it as if it were your first time. Don’t worry about data loss. Remember, everything is safe and sound on the OTRS 3.0.12 folder we created on step 1. The new installation will create a NEW folder so it will not overwrite anything. If you feel a little nervous about this step, go head and copy the previous installations somewhere else, like a pen drive or some shit.
4. Go trough the web installer with all the defaults, you don’t have to configure ANYTHING, go trough the DB defaults, skip the email configurations, we don’t need that shit. What we need is a CLEAN OTRS INSTALLATION. OK??  If you are a n00b or very anal you may wonder.. WHY? OK, I’ll tell you: All the defaults will be replaced later by our own configurations and database.
5. Here is where the manual actually comes in handy, from your previous OTRS:
   5.1 From the folder X:\Program Files\OTRS 3.0.12, COPY the following files
   • FROM X:\Program Files\OTRS 3.0.12\Kernel\Config.Pm TO X:\Program Files\OTRS\Kernel\
   • FROM X:\Program Files\OTRS 3.0.12\Kernel\Config\GenericAgent.Pm TO X:\Program Files\OTRS\Kernel\Config
   • FROM X:\Program Files\OTRS 3.0.12\Kernel\Config\Files\ZZZAuto.pm TO X:\Program Files\OTRS\Kernel\Files
   • …yes… replace the new files with the old ones.
     These are the only files you need. These and only these. Don’t copy any other fucking file.
6. Now is time to copy the database. No big deal, just copy the following folder from
   X:\Program Files\OTRS 3.0.12\data\otrs paste it [and replace it] in X:\Program Files\OTRS\data\otrs
7. Start Services.  Go to Star –> All programs –>OTRS –> TOOLS –> OTRS Services Start … damn you are dumb.
8. Now comes the tricky part: In the manual you’ll find the following at step 7:
   Set file permissions
      ——————–   If the tarball is used, execute:     shell> cd /opt/otrs/
        shell> bin/otrs.SetPermissions.pl

      with the permissions needed for your system setup.

   Let me save you some time with this: BULLSHIT. Tarball?? What in the name of all that is holly is a tarball?? Fucking disgusting… shell?? Like a sea shell?? Who am I? the Fucking Mermaid??. This script will not work on Windows, so don’t even try it. YET it is needed, but -How?- you may ask. Good luck for you I’m here to help you. This is simple: You must have all permissions for the folder OTRS. It doesn’t matter if you already are the System Admin, sometimes you have to give yourself full permissions. To do this: Right click on the OTRS folder –>Properties –> Security Tab. Here, and just to be safe, give full control to all users.

9. Now you have to run the following script: DBUpdate-to-3.1.mysql.sql located at X:\Program Files\OTRS\OTRS\Scripts But then again, this is intended for Linux, good luck trying to execute from the Command Line on windows. To walk around this issue you’ll need a program to execute individual querys such as MySQL Workbench, wich you can download from here http://dev.mysql.com/downloads/workbench/ There are several other programs but this is the one I use. No matter what program you use, a connection with the DataBase has to be made. I’m not gonna tell you how to configure your program to make this so you are shit out of luck.(Before you begin to execute query, remember to have a connection from your program to the OTRS database)Anyway.. now we enter the most delicate part. Risk of loss of data, be carefull.
   9.1 Open the DBUpdate-to-3.1.mysql.sql with notepad, you’ll see something like this
   # ———————————————————-
   #  driver: mysql, generated: 2012-07-05 09:13:21
   # ———————————————————-
   # ———————————————————-
   #  alter table ticket_index
   # ———————————————————-
   ALTER TABLE ticket_index CHANGE queue queue VARCHAR (200) NULL;
   ALTER TABLE ticket_index ALTER queue DROP DEFAULT;
   UPDATE ticket_index SET queue = ” WHERE queue IS NULL;
   ALTER TABLE ticket_index CHANGE queue queue VARCHAR (200) NOT NULL;
   # ———————————————————-
   #  alter table ticket_index
   # ———————————————————-
   ALTER TABLE ticket_index CHANGE s_state s_state VARCHAR (200) NULL;
   ALTER TABLE ticket_index ALTER s_state DROP DEFAULT;
   UPDATE ticket_index SET s_state = ” WHERE s_state IS NULL;
   ALTER TABLE ticket_index CHANGE s_state s_state VARCHAR (200) NOT NULL;
   …and more and more and more…

   Just in case you don’t know shit about life, the universe, sex and MySQL, each line is a MySQL query and is delimited by ” ;  ” For example:
        ALTER TABLE ticket_index ALTER s_state DROP DEFAULT;
   That’s a query.

   # ———————————————————-
   #  alter table ticket_index
   # ———————————————————-
   That is NOT a query.

   CREATE TABLE gi_debugger_entry_content (
       id BIGINT NOT NULL AUTO_INCREMENT,
       gi_debugger_entry_id BIGINT NOT NULL,
       debug_level VARCHAR (50) NOT NULL,
       subject VARCHAR (255) NOT NULL,
       content LONGBLOB NULL,
       create_time DATETIME NOT NULL,
       PRIMARY KEY(id),
       INDEX gi_debugger_entry_content_create_time (create_time),
       INDEX gi_debugger_entry_content_debug_level (debug_level)
   ); <— haha  sad face
   That is a big query.

   I copied the whole content of the script and paste it inside the MySQL Workbench to execute query by query (from the note pad, select and copy all the text). The first time I tried to execute the script as a whole but it didn’t work, so executed query by query.. tedious but you’ll thank me later. The following image is how it looks once the text has been pasted inside the MySQL workbench. You’ll notice a blue dot at the beginning of some lines, each blue dot represents a query. The blue texts are comments, ignore them.   
   I went trough all the file, executing each blue dot one by one. You may try to execute them all at once, if you have no errors, good, if you have one or more errors, then try it one by one.  There is a big danger here, because you are dealing with data, if something goes wrong or if shit hits the fan you have the risk to have data corruption. So, just to be sure, execute query by query… or go trough all the script at once you lazy fuck, don’t tell me I didn’t warn you.

10. Once you have executed the previous script with NO ERRORS, execute the next script from the command line,  located at X:\Program Files\OTRS\OTRS\Scripts\DBUpdate-to-3.1.plAs you can see in this screenshot I fucked up with the file name. The command is:
    perl DBUpdate-to-3.1.pl
    …
    now… what tha hell is going on??? If you’ve followed my instructions (specially step 8 & 9), you’ll begin to see a lot of weird shit on your screen. This is normal, depending on your DB size it’ll take some time to finish (think somewhere around 20 – 45 min). There are 24 steps in this script and all of them have to finish with NO ERRORS. All of them. you see that??? Now THATS a motherfucking script running smoothly god damn it. NOW THAT’S the way your script should look like after its done.. yeah baby, I think I felt my dick move.
11. There is another SQL script that we have to execute, once again, from the MySQL Workbench. This one is DBUpdate-to-3.1-post.mysql.sql located at
    X:\Program Files\OTRS\OTRS\Scripts\ 

    Again, open the file using NOTEPAD and copy the whole content into MySQL Workbench. You can execute the whole file at once, you don’t need to go query by query.

12. And you are almost done, the most critical and important part is finished… Now, the manual ask us to run the following scripts, again from the command line go to
    X:\Program Files\OTRS\OTRS\bin and execute:
    •              perl otrs.RebuildConfig.pl
    •              perl otrs.DeleteCache.pl

    You’ll see no weird shit with these ones… hell, not even a confirmation message. Just type the first command and hit enter, type the second command and hit enter.

AND YOU ARE DONE!!! Good job, now try to log on to your system using the same credentials you used to have on your previous version. It should work. If there is an error message then you fucked up, you did something wrong, you fail, you have to do it all over again.

Note 1: Although the UPGRADING manual is a piece of shit intended for linux, you HAVE TO READ IT, understand it and figure it out. It won’t work for shit if you have a Windows system but still…
Note 2: On the upgrading manual, steps from 10 to 21… fuck’em. I didn’t even read’em.

Note 3. If you had any modifications to the source code, they are gone now. You’ll have to do them all over again.

Note 4. Depending on your DB configuration, you may have to copy the folder with all the attachements from your previous installation to the new one.

Doubts?… leave a comment.