Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Migration to AWS

Hi guys,

I am new here, but I thought it would be a good idea to me to join this board in order to search for answers on some of my questions regarding migration to AWS. I've browsed the forums before posting, but couldn't find answers to most of my questions.

Currently, we're running quite old 1.7.0 OSTicket on Windows. It's a physical server running some other stuff, and since we're migration to cloud for different reasons, we're looking into moving the OSTicket to AWS. I've had quite a lot of experience with AWS, but I am not so familiar with OSTicket, so I have a couple of different scenarions in my mind.

1st would be the most simple one, one EC2 and S3 and RDS to store files / DB, but with Linux and not Windows, since I wouldn't like to pay for extra licenses on AWS, and it has a smaller footprint. Do I need to to take some special care here, with migrating the database to RDS (and should I use MySQL or MariaDB) and is it enough to just before migrating files use the S3 plugin and just copy the files over?

As a 2nd solution, which I would like more, is to have two EC2 instances behind ELB. That way, even though availability zones are not going down that often on AWS, I would have some sort of HA, which is important to me since we run OSTicket as an external ticketing software. Here, concern is about polling e-mails (can be done to set up different time intervals, one option), and sharing the attachments upload folder (both can upload to same S3 bucket, if that's supported) or to use EFS and to mount EFS as regular mount point in Linux.

My apologies in advance if I already posted something discussed in detail here, I couldn't really help myself with the things I found, and also I am quite unfamiliar with the application since the setup on Windows physical box was done ages before I started to work at this job.

I appreciate every help and comment!
«1

Comments

  • I've never used AWS so I dont have the answers that you are looking for.  

    There is the storage-s3 plugin (available at http://osticket.com/download) which lets you use Amazon S3 to store your ticket attachments.

    You might want to consider talking to the Professional support folks over at: http://osticket.com/professional-installation
  • Hi ntozier,

    Thanks for your input, I'll definitely reach out to support probably at one point.
    For now, maybe you know, I've created a backup of osticket DB on Windows with mysqldump utillity, and I've managed to succesfully import it into RDS with source command, but I am getting a bunch of warnings like 
    Warning (Code 1300): Invalid utf8 character string: 'B7E02F'
    Is this probably data tied to the files info in the DB, so MySQL is trying to validate binary as UTF8 and can bi disregarded or?

    Thanks in advance for your help!
  • Can you search your .mysql dump file for that and see what table it's in?
  • I opened the .sql dump in vim, since it's quite a large file, 3.7 GB, and I don't get anything for these character strings.

    Here is the larger snippet of warnings:

    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Query OK, 6 rows affected, 6 warnings (0.06 sec)
    Records: 6  Duplicates: 0  Warnings: 6

    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
    Warning (Code 1300): Invalid utf8 character string: 'D0CF11'
    Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
    Query OK, 20 rows affected, 20 warnings (0.06 sec)
    Records: 20  Duplicates: 0  Warnings: 20

    Warning (Code 1300): Invalid utf8 character string: 'C8C7D0'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'AAABAC'
    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
  • I've asked the devs to take a look... but I dont see any reason why it would completing about that being an invalid utf8 character string.
  • Could you please expand on the process you used to export/import your db? Specific commands used, both db versions, and collations will be very helpful. Thanks.
  • Sure thing, I've created backup on Windows Server 2008 R2, with MySQL 5.6.10.
    This is the collation on the source:

    mysql> use osticket;
    Database changed
    mysql> show variables like "character_set_database";
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | character_set_database | utf8  |
    +------------------------+-------+
    1 row in set (0.00 sec)

    And I've created dump with mysqldump -u root -p osTicket > C:\users\Administrator\Desktop\OSTicket.sql

    On the AWS, I have version Amazon Linux (which is a RHEL 6 equivalent), MySQL 5.6.27-log (that's what the select version(); says) and collation is:

    mysql> use osticket;
    Database changed
    mysql>
    mysql> show variables like "character_set_database";
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | character_set_database | latin1 |
    +------------------------+--------+
    1 row in set (0.04 sec)

    mysql>

    Can it be this is the issue Kevin? I am really sorry about troubling you with this, but I am quite a DB noob, the worst kind
  • There is your problem...the character set has to be UTF8. You exported the db in UTF8 charset and trying to import into Latin1 charset. You need to set the AWS db to UTF8 encoding so that it can read the encoding of the backup! :)
  • Btw it's never trouble! Everyone has to start somewhere! Learning databases is super essential as a dev and very interesting/fun!
  • Seems like that's not the thing. I've logged what I do inside mysql-client, and here it is (I've dropped the osticket db I've used before with wrong character set):

    mysql> create database osticket default character set utf8 default collate utf8_general_ci;
    Query OK, 1 row affected (0.03 sec)

    mysql> use osticket;
    Database changed

    mysql> show variables like "character_set_database";
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | character_set_database | utf8  |
    +------------------------+-------+
    1 row in set (0.00 sec)

    mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_general_ci |
    +--------------------+-----------------+
    1 row in set (0.00 sec)

    mysql> source /tmp/OSTicket.sql;
    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 4 rows affected, 2 warnings (0.06 sec)
    Records: 4  Duplicates: 0  Warnings: 2

    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'C9F29D'
    Query OK, 2 rows affected, 2 warnings (0.05 sec)
    Records: 2  Duplicates: 0  Warnings: 2

    Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
    Warning (Code 1300): Invalid utf8 character string: 'D95A79'
    Query OK, 9 rows affected, 9 warnings (0.06 sec)
    Records: 9  Duplicates: 0  Warnings: 9

    I've stripped this just from the entire log.
    Is there anything else that I should take care of when I export/import DB?
  • And one more thing I noticed, now that I am googling for different answers, since I am a MySQL noob:
    on the source, Windows MySQL
    mysql> show variables like 'character_set%';
    +--------------------------+---------------------------------------------------------+
    | Variable_name            | Value
         |
    +--------------------------+---------------------------------------------------------+
    | character_set_client     | cp850    |
    | character_set_connection | cp850   |
    | character_set_database   | utf8   |
    | character_set_filesystem | binary    |
    | character_set_results    | cp850     |
    | character_set_server     | utf8    |
    | character_set_system     | utf8    |
    | character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
    +--------------------------+---------------------------------------------------------+

    on the AWS:

    mysql> show variables like 'character_set%';
    +--------------------------+-------------------------------------------+
    | Variable_name            | Value                                     |
    +--------------------------+-------------------------------------------+
    | character_set_client     | utf8                                      |
    | character_set_connection | utf8                                      |
    | character_set_database   | utf8                                      |
    | character_set_filesystem | binary                                    |
    | character_set_results    | utf8                                      |
    | character_set_server     | latin1                                    |
    | character_set_system     | utf8                                      |
    | character_sets_dir       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/ |
    +--------------------------+-------------------------------------------+
    8 rows in set (0.00 sec)

    Do these set_client, set_connection and set_results variables make a difference in my case?

    Thanks once again!
  • Try changing 'set_client, set_connection, set_results' to cp850 and retry!
  • edited December 2016
    If you can execute SQL commands do:

    SET character_set_client = cp850;
    SET character_set_results = cp850;
    SET character_set_connection = cp850;

    and that should do ya!
  • Unfortunately, it doesn't, same errors all over again. Now the only difference is character_set_server but the character_set_database should overwrite that one, since the settings are per DB?

    I am quite clueless what's the issue
  • Ok so basically when you import with 'source' it uses the character_set_server charset....so you have two options:
    1.) You can change the default charset for the server to 'utf8' (every time you import anything it defaults to this)
    2.) OR You can use the mysqlclient to import and with that you can specify the charset you want to import in.

    $ mysql -u username -p --default-character-set=utf8 yourDB < yourSQLfile.sql
  • It's the same with both, I am quite more than clueless.

    I've also tried to include --default-character-set=utf8 when creating a backup, but that also didn't help
  • Try putting this at the very top of the .sql file and run the command with '--default-character-set=utf8' again:

    SET NAMES 'utf8' COLLATE 'utf8_general_ci';
  • I've never seen this, this is very particular/weird lol
  • same, unfortunately...
  • The problem might actually be in the export statement...try exporting with this:

    $ mysqldump -u username -p --default-character-set=utf8 yourDB > yourSQLfile.sql

    And importing with this:

    $ mysql -u username -p --default-character-set=utf8 yourDB < yourSQLfile.sql
  • edited December 2016
    Make sure your AWS db is still like this:

    mysql> show variables like 'character_set%';
    +-----------------------------------------+-------------------------------------------------------------------+
    | Variable_name                      | Value                                                                       |
    +-----------------------------------------+-------------------------------------------------------------------+
    | character_set_client            | utf8                                                                          |
    | character_set_connection | utf8                                                                          |
    | character_set_database     | utf8                                                                          |
    | character_set_filesystem   | binary                                                                     |
    | character_set_results         | utf8                                                                          |
    | character_set_server          | latin1                                                                       |
    | character_set_system        | utf8   |
    | character_sets_dir              | /rdsdbbin/mysql-5.6.27.R1/share/charsets/ |
    +----------------------------------------+--------------------------------------------------------------------+
  • Again, the same.

    I've created the backup like this on Windows:

    c:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump -u root -p osTicket --default-character-set=utf8 > D:\Export\OSTicket.sql 

    Changed the Parameter DB Group on AWS, so these are my db settings now:

    mysql> show variables like 'character_set%';
    +--------------------------+-------------------------------------------+
    | Variable_name            | Value                                     |
    +--------------------------+-------------------------------------------+
    | character_set_client     | utf8                                      |
    | character_set_connection | utf8                                      |
    | character_set_database   | utf8                                      |
    | character_set_filesystem | binary                                    |
    | character_set_results    | utf8                                      |
    | character_set_server     | utf8                                      |
    | character_set_system     | utf8                                      |
    | character_sets_dir       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/ |
    +--------------------------+-------------------------------------------+
    8 rows in set (0.00 sec)

    Then I do the following:

    # mysql -h my_host -P 3306 -u user -p --show-warnings
    mysql > create database osTicket;
    mysql > exit;
    # mysql -h my_host -P 3306 -u user -p --show-warnings --default-character-set=utf8 osTicket < /tmp/OSTicket.sql

    There's gotta be something I am doing wrong, I guess. 
    Thanks for your efforts, really, you've been more than great Kevin!
  • What I did today was to restore table by table and I've realized that all of my warnings come from ost_file_chunk table, which is the biggest one, with 3.5 GB in size. What I assume is that all of my files are kept in this table and that ticket info is kept in ost_ticket tables? 
    If that's the case, then importing DB like this would only mean that potentially I would lose some attachments?
    If that's true, then this doesn't pose a big issue to me, since I can afford to lose the attachments, but I wouldn't like to lose the information about ticket (e-mail info from it, entire thread and so on)
    I am not that familiar with the app structure, so I cannot tell by myself.

    Thanks!
  • Q: I would lose some attachments?
    A: Yup. All of them :)  You'll then end up with a weirdness of the ticket says there is an attachment, and the data just isn't there.  running cron..php should clear up those orphaned files though I think.
  • Thanks, I meant, for those rows where I have a warning for that table, I'll of course lose data, but I guess I won't lose attachments for those threads where the import of the associated row in ost_file_chunk table is correct?
    Running cron.php will clear them up means it will fix them or it will just remove the attachments from the application? Because RDS is charged by storage, and if I won't have any attachment, then it's better not to import that table at all, since it's almost 4 GB
  • It should remove the orphaned file from the tickets.

  • But I'll definitely lose everyhing? :)
  • If you do not import the ost_file_chuck you would lose all your attachments.
    If you edit it to remove the lines that are throwing errors... you would only lose the ones that you removed entries for.
  • I think that one is more difficult, since it's a 3.5 GB file, and tracing errors based on the imported lines is quite difficult. My idea was to import the entire DB, and then run that cron task, in order to remove the files that couldn't be imported, and to leave those "healthy" ones that could be.
  • Sounds good to me. :)
Sign In or Register to comment.